Post reply

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

  • April 7, 2011 at 3:39 pm

    #234416

    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.

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)    [/font]

  • April 8, 2011 at 6:14 am

    #1309080

    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

    --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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply