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

Script that talks to multiple DBs cant be put into a SP Expand / Collapse
Posted Thursday, April 7, 2011 3:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 24, 2015 9:39 AM
Points: 211, Visits: 564
I have a similar situation. I am running a script against our DW database.
The script collects data from 3 different DBs and stores them in temp tables.
Joins those temp tables and slects required results.

I need to be able to put this script in SSIS to export it to a flat file. And I get the
'No available i/p columns' error.

As per your suggested options i tried to put it into a PROC , but was not able to since
a USE database statement is not allowed in a procedure, function or trigger.

How do i fix my problem.

Thanks in Advance.

Today is the tomorrow you worried about yesterday
Post #1090280
Posted Friday, April 8, 2011 6:14 AM



Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 13,886, Visits: 35,818
for crossdatabase stuff inside a procedure , you only got two choices;
1: can you adapt the specific script to use 3 part names?
you know, SELECT * FROM Production.dbo.Invoices

2: switch to dynamic SQL and EXEC a string: multiple commands are simply semi-colon delimited.
SET @isql = 'USE SandBox; SELECT * FROM dbo.Invoices; SELECT @@version;'


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1090540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse