Adding a new table to a complex join statement

All help appreciated.

I currently use the following select statement as a custom query.

SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)

Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].

I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards

here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql

Adding a line return to an SQL messge

HI all,
I'm adding a custom SQL message. How do I insert a new line, line feed. So
that the message is split between a few lines
Thanks
RobertRobert
SELECT 'HELLO'+ CHAR(13)+'World'
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||select 'error' + char(13) + 'message'
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Robert Bravery" wrote:

> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>
>|||A "proper" CR+LF pair is:
SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar';
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||A "proper" CR+LF pair is:
SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar';
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Another method:
EXEC sp_addmessage 60000, 10, 'This
is
a
multi-line
message'
RAISERROR(60000, 10, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Thanks
Robert
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHwpxnflGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Another method:
> EXEC sp_addmessage 60000, 10, 'This
> is
> a
> multi-line
> message'
> RAISERROR(60000, 10, 1)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert Bravery" <me@.u.com> wrote in message
> news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
So
>|||This begs the question about why format output from SQL Server instead of
the client application...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Sometimes it's just a heck of a lot easier to do in SS than a given client.
A good example is a spreadsheet I created for one of my coworkers that draws
data from some SS queries. Word wrap wasn't working the way he wanted on
some column headers and adding the CR between 2 key parts of the field alias
solved the problem.
Randall Arnold
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:upvDfGhlGHA.836@.TK2MSFTNGP02.phx.gbl...
> This begs the question about why format output from SQL Server instead of
> the client application...
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
>|||>> I'm adding a custom SQL message. How do I insert a new line, line feed. S
o that the message is split between a few lines <<
Why are you doing display in the database and not the front end? The
basic principle of a tiered architecture is that display is done in the
front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.

Adding a global reference to a custom assembly

I have a custom assembly that around 150 reports all need to make use of. Is there a way for me to put this assembly reference in some global settings file so that I don't have to add the reference for every report individually? SQL Server 2005/VS 2005.

I don't think this is possible. Each report is compilied in its own assembly and global references are not supported.

Adding a custom named instance to Windows Firewall exception list

We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.

Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?

You can add a firewall exception for the specific executable rather than the port number. Details are available in this KB Article,

Mike

Adding a custom named instance to Windows Firewall exception list

We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.

Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?

You can add a firewall exception for the specific executable rather than the port number. Details are available in this KB Article,

Mike

sql

Add Version to Custom Report

Hello
Has anybody found a good way to add version control to a report? I could
add a text box to the report and enter Version:20060927. Because this is a
manual operation some developers may forget and it will become unreliable.
If someone found a better way please let me know.
Thanks
Johnuse Globals!ExecutionTime expression. It will display the date and time
when the report is generated. Playing around with it will help you produce
what you wanted.
hth,
"John A Kushwarra" <jkushwarra@.empire.edu> wrote in message
news:uMVVWyj4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Hello
> Has anybody found a good way to add version control to a report? I could
> add a text box to the report and enter Version:20060927. Because this is
> a manual operation some developers may forget and it will become
> unreliable. If someone found a better way please let me know.
> Thanks
> John
>sql

Add services to SSIS IServiceProvider model?

Is there any mechanism for being able to add custom services into the SSIS service provider model so that they can be used by components at design and runtime? i.e. if I can currently access service implementations such as the IDtsConnectionService from the Initialize method of a custom component (or in a UI type editor), is there a way to add a service of my own?

Thanks

Interesting - we've never thought about this. What kind of services do you plan to add there? Note that the services are for design time only, they are no available at runtime.|||

Thankyou for the response - actually design-time only services would still be useful in this case - I'm writing custom components against a JD Edwards Oracle database, but I'm having to interpret the data dictionary schema for business users to assist them in writing their queries (it has to be available offline).

I currently have metadata available to the components so that they can present meaningful field information to users before it is mapped onto the underlying schema, but it would be extremely useful if my components could utilise a service that wrapped this metadata functionality for them.

Add a variable from custom component

Hi

I am writing a custom transformation component that utilises a user variable.

Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.

Is there a way to add a user variable with package scope from a custom component, either at run time or design time?

Thanks . . . Ed

I do not think it is possible currently.

Thanks.

add a reference in scriptComponent

I would like to use a custom build class library written in C# inside of the vb script. Does anyone know how to add the reference to the scriptComponent project once you open script through design script button?

By the way I am using visul studio 2005.

Thanks!

Jun,

You'll need to have your class assembly added to the GAC. Procedure here.

After that, you can add it by selecting the Project > Add Reference... in the opened script editor.

|||

That's not the only thing that you need to do. Read this:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)

(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx)

-Jamie

|||It may also be valuable to point out that this same process can be used to call web services from script components/tasks as well (helpful for complete control over web service calls instead of using limited built in SSIS web service task). You can use wsdl.exe to create the proxy class then go through the same steps outlined above.|||

ADMariner wrote:

It may also be valuable to point out that this same process can be used to call web services from script components/tasks as well (helpful for complete control over web service calls instead of using limited built in SSIS web service task). You can use wsdl.exe to create the proxy class then go through the same steps outlined above.

And (in case anyone is still reading/interested ) in Katmai you'll be able use Web References just as you can in Visual Studio today. So no need to even use WSDL.exe

-Jamie

|||Thanks a lot! I will try.|||

I installed my dll into GAC and also put it under .net directory. But when I add reference to my dll in ScriptCommponent project. Before adding any code , I could not save project anymore. It complained "Object reference not set to an instance of an object". Anyone know what I missing here?

Thanks!

|||

Jun Fan wrote:

I installed my dll into GAC and also put it under .net directory. But when I add reference to my dll in ScriptCommponent project. Before adding any code , I could not save project anymore. It complained "Object reference not set to an instance of an object". Anyone know what I missing here?

Thanks!

You don't actually have to save the project. Just closing down VSA will store all of the code within your script task/component.

Try just doing that and see if you still run into problems.

-Jamie

|||

Wow, it works without save project. I can't believe it. Thanks very very much!

I am new from java world to window world. Do you mind light me some more?

What I am trying to do is that taking input columns and using script to build a object that my webservice recogonized and serialized object and store serialized data to database. I used transformation type of scriptComponent, and added output column named serializedObject as Byte Stream type from ScriptComponent. In VB script serializedObject is byte[] , so I assign byte[] return from serializing object to it. But when I run intergation service I go error complain "The value is too large to fit in the column data area of the buffer."

Anybody have suggestion?

Thanks!

|||

Jun Fan wrote:

Wow, it works without save project. I can't believe it. Thanks very very much!

I am new from java world to window world. Do you mind light me some more?

What I am trying to do is that taking input columns and using script to build a object that my webservice recogonized and serialized object and store serialized data to database. I used transformation type of scriptComponent, and added output column named serializedObject as Byte Stream type from ScriptComponent. In VB script serializedObject is byte[] , so I assign byte[] return from serializing object to it. But when I run intergation service I go error complain "The value is too large to fit in the column data area of the buffer."

Anybody have suggestion?

Thanks!

The default length of the byte stream type (i.e. DT_BYTES) when you add a column of that type to the script component is 50. My guess is that the length of the value you are tryig to put in there is more than 50.

-Jamie