Script that talks to multiple DBs cant be put into a SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Paul Ibison » Script that talks to multiple DBs cant be put...

Script that talks to multiple DBs cant be put into a SP

Rate Topic

Display Mode

Topic Options

Author

Message

Minnesota - Viking

Posted Thursday, April 07, 2011 3:39 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:54 AM
Points: 201, Visits: 456

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.

Smile

================================== Smile

Today is the tomorrow you worried about yesterday

Post #1090280

Lowell

Posted Friday, April 08, 2011 6:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

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

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

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1090540

« Prev Topic | Next Topic »

Permissions