|
Home > Archive > Microsoft SQL Server forum > March 2006 > connecting in a script to another db
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
connecting in a script to another db
|
|
| Jeff Kish 2006-03-23, 1:31 pm |
| 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 2006-03-23, 8:34 pm |
| 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
| |
| Jeff Kish 2006-03-23, 8:34 pm |
| On 23 Mar 2006 13:16:41 -0800, "sdyckes" <stephendyckes@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
|
|
|
|
|