connecting in a script to another db

[Jeff Kish]

hi.

I've tried to use the exec sql connect to ... in query analyzer, but could not
get it to parse.


Can someone show me how in a script, say the middle, I can connect to another
database and execute the remaining script lines there.

This way I can have a script update two separate db's at once.

Thanks
Jeff

Jeff Kish

[sdyckes]

Re: connecting in a script to another db

Jeff,

You can specify the from clause of SQL statements as
DATABASE.OWNER. TABLE, as long as they are on the same SQL Server. You
may also utilize the USE DATABASE command to change which database you
script will execute in. But, if you have different servers you want to
access, then that will not work unless you have set up Linked Servers.
In which case you would add the servername to the beginning of your
sting: SERVERNAME.DATA BASE.OWNER.TABL E.

Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
T1.ReportID = T2ReportID

use DB2 --This Example will use both Databases to update the column

Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
T1 where T4.ReportID = T1.ReportID

Steve

[Jeff Kish]

Re: connecting in a script to another db

On 23 Mar 2006 13:16:41 -0800, "sdyckes" <stephendyckes@ gmail.com> wrote:
[color=blue]
>Jeff,
>
> You can specify the from clause of SQL statements as
>DATABASE.OWNER .TABLE, as long as they are on the same SQL Server. You
>may also utilize the USE DATABASE command to change which database you
>script will execute in. But, if you have different servers you want to
>access, then that will not work unless you have set up Linked Servers.
>In which case you would add the servername to the beginning of your
>sting: SERVERNAME.DATA BASE.OWNER.TABL E.
>
>Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
>T1.ReportID = T2ReportID
>
>use DB2 --This Example will use both Databases to update the column
>
>Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
>T1 where T4.ReportID = T1.ReportID
>
>Steve[/color]
Thanks Steve. They will be on the same SQL Server, so I can run with this.

Regards
Jeff Kish