Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Executing stored procedures on Linked Servers Expand / Collapse
Author
Message
Posted Wednesday, August 5, 2009 9:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
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







Post #765593
Posted Wednesday, August 5, 2009 9:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
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? -- Stephen Stills
Post #765625
Posted Thursday, August 6, 2009 9:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
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.
Post #766283
Posted Thursday, August 6, 2009 12:17 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
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? -- Stephen Stills
Post #766450
Posted Tuesday, August 11, 2009 5:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
Thanks again.
Post #768501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse