By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

connecting in a script to another db

P: n/a
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
Mar 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.DATABASE.OWNER.TABLE.

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

Mar 23 '06 #2

P: n/a
On 23 Mar 2006 13:16:41 -0800, "sdyckes" <st***********@gmail.com> wrote:
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.DATABASE.OWNER.TABLE.

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

Thanks Steve. They will be on the same SQL Server, so I can run with this.

Regards
Jeff Kish
Mar 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.