August 5, 2009 at 9:29 am
Hi,
I currently have two DB's (DB1 and DB2) setup on an instance of SQL server. DB2 is essentially a reporting store which I populate with data from DB1
Currently: my Extract script takes the following parameters:
@sourceDatabase sysname, @YearMonthStart int, @YearMonthEnd int
Within the script I list a number of tables to be populated based on these parameters.
INSERT INTO TableX
EXEC ('USE '+ @sourceDatabase + ' EXEC spPopulateTableX')
I am now migrating DB2 to new server and the two servers are setup as linked servers.
So I cannot use 'USE' anmore.
So essentially my questions is how do a reference the source database in my script?
All help is appreciated.
G
August 5, 2009 at 9:53 am
Try this: EXEC database.schema.procedureName
If your database is called Reports, and your procedure is dbo.InventorySummary you would use:
EXEC Reports.dbo.InventorySummary.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 6, 2009 at 9:00 am
Thanks Bob that works for me.
A bit off topic...in order to execute the storedProcs on the DB1 the linked server needs to be configured to allow remote procedure calls.
Is this a good practice security wise?
Should I perhaps look at ways to get the data without executing the storeprocs on the DB1.
August 6, 2009 at 12:17 pm
It creates yet another vulnerability, if you don't maintain control over who is allowed to execute which stored procedures. Someone more DBA focused could give you a better answer than I can.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 11, 2009 at 5:39 am
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply