March 19, 2009 at 2:04 pm
Execute SQL Task object in an SSIS package calls a sproc that contains a call to "EXEC (@query)" where @query is parsed together on the fly based on the incoming params.
[Execute SQL Task] Error: Executing the query "exec usp_rtrh ?, ?, ?" failed with the following error: "The label 'H' has already been declared...
If I comment out the EXEC step in the sproc, the call is successful, so I know it's the EXEC...
Why is it thinking there's a label in the proc? I've already tried EXEC sp_executesql @query, but got the same (idiotic) message.
I searched my entire sproc for out of place "H"s, but only saw them in "VARCHAR" declares and the like. Already tried soaking up the return value from the EXEC as well (as in "EXEC @a = sp_executesql @query")
PLEASE HELP! This is a hair puller and keeping me from the finish line on this task and more like it.
THANKS SSC!
full error:
[Execute SQL Task] Error: Executing the query "exec usp_rtrh ?, ?, ?" failed with the following error: "The label 'H' has already been declared. Label names must be unique within a query batch or stored procedure.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
March 19, 2009 at 2:18 pm
Have you looked at what the actual value of @query is being set to? Perhaps it's not being set correctly?
March 19, 2009 at 2:20 pm
If I run the sproc from Management Studio, it runs just fine, so I know the SQL is well formed.
March 19, 2009 at 2:38 pm
OK, I'm for the most part a beginner so forgive me for even trying to help here, but I'm trying to help....
I was getting very odd errors on a project I did recently when trying to exec a store procedure from my SSIS package. I dont remember the error message I received, however, I do remember the problem was the stored procedure I was calling contained temp tables, and for some reason it was causing problems. I had to add the line 'SET NOCOUNT ON' before executing the stored procedure, and then it started working.
Again, not sure if there are temp tables in anything you are calling, but just offering the only information I have.
March 19, 2009 at 2:47 pm
No temp tables in my spoc. Installed the NOCOUNT ON and re-ran, but got the same error.
March 19, 2009 at 2:50 pm
ok, that's all I got 🙂 hopefully someone more knowledgable will respond to your issue.
good luck!!!
March 19, 2009 at 10:35 pm
Either print the @query value from your SSIS task or else insert it into a table, before the attempted EXEC. Then cut and paste it here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 3:52 am
ya...it would really help if you could post the print @Query here. Otherwise we all would be aiming in the dark.. 😛
March 20, 2009 at 8:33 am
Pretty boilerplate stuff really. Is it a "whisper down the lane" permissions issue?
select @query yields:
INSERT INTO a2db_rawhistory.dbo.tx_wb5asc ([WBKEY], [RECNO], [SEGMENTCOUNTER], [TUBINGINCHES], [TUBINGFRACTIONNUMERATOR], [TUBINGFRACTIONDENOMINATOR], [DEPTHSET], [PACKERSET], CurrentAsOf)
SELECT r.[WBKEY], r.[RECNO], r.[SEGMENTCOUNTER], r.[TUBINGINCHES], r.[TUBINGFRACTIONNUMERATOR], r.[TUBINGFRACTIONDENOMINATOR], r.[DEPTHSET], r.[PACKERSET], 'Mar 20 2009 8:08AM' FROM aZZZZaw.dbo.tx_wb5asc r
Like I said... the sproc WORKS when run in Management Studio thusly:
declare @a int
exec @a = usp_rtrh @rawdb = 'a2db_raw', @rtrhdb = 'a2db_rawhistory', @tname = 'tx_oilfield'
select @a
Tried executing with BypassPrepare set to both true and false to the same result.
I was able to set the "IsStoredProcedure" field to "True" momentarily, but the value switched back before run time... should I be exploring getting this to work? Would that keep SSIS from parsing the undelrying code?
THANKS!
March 20, 2009 at 8:59 am
Greg J (3/20/2009)
select @query yields:INSERT INTO a2db_rawhistory.dbo.tx_wb5asc ([WBKEY], [RECNO], [SEGMENTCOUNTER], [TUBINGINCHES], [TUBINGFRACTIONNUMERATOR], [TUBINGFRACTIONDENOMINATOR], [DEPTHSET], [PACKERSET], CurrentAsOf)
SELECT r.[WBKEY], r.[RECNO], r.[SEGMENTCOUNTER], r.[TUBINGINCHES], r.[TUBINGFRACTIONNUMERATOR], r.[TUBINGFRACTIONDENOMINATOR], r.[DEPTHSET], r.[PACKERSET], 'Mar 20 2009 8:08AM' FROM aZZZZaw.dbo.tx_wb5asc r
Just to confirm: is this the @query contents from an SSIS instance that failed?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 9:00 am
Also, we will need to see the listing of the "usp_rtrh" stored procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 9:11 am
Yes sir.
SSIS package has an Execute SQL Task that calls the sproc "usp_RTRH" with 3 arguments. That call works in Management Studio, but when run in the package, it yields the message in my first post...
"[Execute SQL Task] Error: Executing the query "exec usp_rtrh ?, ?, ?" failed with the following error: "The label 'H' has already been declared. Label names must be unique...."
... and the Execute SQL Task object fails. (getting that message from the Progress tab)
??? Label name? I have NO GOTO statements in the sproc code!
March 20, 2009 at 9:41 am
RBarry, you in DE? I lived in Hockessin/Newark/Greenville growing up. Went to Univ. of DE.
Here's the definition of the underlying sproc:
----------------------------------
CREATE PROC [dbo].[usp_RTRH] (
@rawdb VARCHAR(128),
@rtrhdb VARCHAR(128),
@tname VARCHAR(128))
AS
SET NOCOUNT ON
SET @rawdb = ISNULL(@rawdb, 'a2db_raw')
SET @rtrhdb = ISNULL(@rtrhdb, 'a2db_rawhistory')
-- @tname is req'd
DECLARE @subquery VARCHAR(MAX)
DECLARE @dt VARCHAR(32)
SET @dt = GETDATE()
DECLARE @loopcol VARCHAR(128)
DECLARE @query1 VARCHAR(MAX)
DECLARE @query2 VARCHAR(MAX)
DECLARE colcur CURSOR
FOR
SELECT [name]
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = @tname
AND is_computed = 0;
-- UPDATE section... no column list needed
SET @query1 = 'UPDATE ' + @rtrhdb + '.dbo.' + @tname + '
SET [CurrentAsOf] = ''' + @dt + ''' FROM ' + @rawdb + '.dbo.' + @tname + ' r
INNER JOIN ' + @rtrhdb + '.dbo.' + @tname + ' rh
ON r.A2DBchecksum = rh.A2DBchecksum'
-- EXEC sp_executesql @query
EXEC (@query1)
SET @query1 = 'DELETE FROM ' + @rawdb + '.dbo.' + @tname + '
FROM ' + @rawdb + '.dbo.' + @tname + ' r
INNER JOIN ' + @rtrhdb + '.dbo.' + @tname + ' rh
ON r.A2DBchecksum = rh.A2DBchecksum'
--EXEC @a = sp_executesql @query
EXEC (@query1)
OPEN colcur
FETCH NEXT FROM colcur INTO @loopcol
WHILE @@FETCH_STATUS = 0
BEGIN
SET @subquery = @subquery + ', [' + @loopcol + ']'
FETCH NEXT FROM colcur INTO @loopcol
END
SET @subquery = SUBSTRING(@subquery,3,9999)
SET @query1 =
'INSERT INTO '+ @rtrhdb + '.dbo.' + @tname + ' (' +
@subquery + ', CurrentAsOf)
'
SET @query2 =
'SELECT ' +
REPLACE(@subquery,'[','r.[') + ', ''' + @dt + '''
FROM ' + @rawdb + '.dbo.' + @tname + ' r'
--EXEC @a = sp_executesql @query
EXEC (@query1 + @query2)
DEALLOCATE colcur
GO
March 20, 2009 at 9:54 am
Greg J (3/20/2009)
I have NO GOTO statements in the sproc!
The error is not about GOTO's it is about labels. It is probably malformed dynamic sql, but that is not the only possibility. I need to see the sProc in order to have any chance of figurining it out.
Why is SSIS parsing the underlying SQL code anyway? Esp. when I set BypassPrepare = True??
I doubt that it is, this is a SQL error, not an SSIS error. "Prepare" just validate the command string that you are sending to SQL Server. Since there is nothing wrong with that command string, BypassPrepare has no effect.
What is this "IsStoredProcedure" setting, and why won't it stay as "True" when I set it? Should I be exploring that option?
Doubt it. From BOL:
The value of this property is always false, unless the task uses an ADO connection manager, as this property is only used by the runtime when using an ADO connection manager.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 10:01 am
My office is in Claymont. One of my best friends grew up in Hockessin and still lives there.
OK, right off the bat: I see 3 dysnamic queries, but you only showed us the text from one. Can you show us all of them? thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply