Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: DTS and stored procedures

    I have a sproc defined many databases. It has the same neme in every databases and does the same in every databases too.

    I have a table that lists all the companies where the sproc exists. (BISYMAP)
    The field that holds the database name is INTERID.

    I need to run every instance of the sproc that exists on my SQL server. I did a cursor that returns the database name into a variable and I use an EXEC statement to run the sprocs. I build the name using the variable filled by the cursor. (@TgtCoy)

    Here it is.
    __________________________________________________ __

    /*List all companies from system table and put it in @TgtCoy*/
    DECLARE @TgtCoy AS CHAR(5)

    DECLARE Coys CURSOR FOR
    SELECT DISTINCT RTRIM(INTERID)
    FROM BISYMAP
    WHERE INTERID <> ''

    OPEN Coys

    FETCH NEXT FROM Coys INTO @TgtCoy

    WHILE @@FETCH_STATUS = 0
    BEGIN
    /* Execute biCoMapGlAccount within each company*/
    EXEC('EXEC ' + @TgtCoy + '.dbo.biCoMapGlAccount')
    FETCH NEXT FROM Coys INTO @TgtCoy
    END
    CLOSE Coys
    DEALLOCATE Coys

    __________________________________________

    This runs fine in SQL Query Analyzer. The issue is when I try to run this from an SQL Task in a DTS package. I can't even save the task. SQL tries to validate the query before saving and returns the following error: ... Could not find stored procedure '.dbo.biCoMapGlAccount'.

    I also tried to put this into system stored procedure and call it from my DTS package but I get the same error.

    Anybody ever had to deal with such a situation? If so, I would certainly appreciate a hint or two on how you didi it.

    Thanks in advance

    Rombolt
    Last edited by Rombolt; 09-19-04 at 00:17.

  2. #2
    Join Date
    Sep 2004
    Posts
    2

    Don't waste your time...

    I found the answer to my problem... and I'm almost ashamed to tell.

    I insisted on pressing the "Parse Query" button after filling the query in the DTS SQL Task, and that's when I would get the error... Well, guess what, all I had to do was NOT to press it. That way the expression is not evaluated and if it is syntaxly correct, it will work fine at runtime.

    If you press "parse query", it looks like SQL evaluates the query line by line so, in my case, it failed.

    That's all there was to it.

    Thanks

    Rombolt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •