March 20, 2009 at 10:16 am
Here are the UPDATE and DELETE statements that get parsed together. I generated them just now and ran them successfully in Management Studio.
UPDATE a2db_rawhistory.dbo.tx_oilfield SET [CurrentAsOf] = 'Mar 20 2009 10:13AM' FROM a2db_raw.dbo.tx_oilfield r INNER JOIN a2db_rawhistory.dbo.tx_oilfield rh ON r.A2DBchecksum = rh.A2DBchecksum
DELETE FROM a2db_raw.dbo.tx_oilfield FROM a2db_raw.dbo.tx_oilfield r INNER JOIN a2db_rawhistory.dbo.tx_oilfield rh ON r.A2DBchecksum = rh.A2DBchecksum
Further, the sproc runs in Management Studio and does the work of the parsed statements when I call it in Management Studio with hard values!
declare @a int
exec @a = usp_rtrh @rawdb = 'a2db_raw', @rtrhdb = 'a2db_rawhistory', @tname = 'tx_oilfield'
select @a
March 20, 2009 at 10:42 am
I had few problems with the Execute SQL Task in SSIS when executing procedures with parameters. Then, what I've done is, instead of setting parameters in Execute SQL Task, I've prepared the EXECUTE text in a variable using Expressions and passed this variable to the Execute Task. Though, I know the error is related to SQL and not SSIS, but may be you can just try and see if it works.
--Ramesh
March 20, 2009 at 12:04 pm
OK, nothing apparent so far. I think that you will need to setup a profiler/trace collection on this SSIS connection and capture all of the SQL commands/procs/statements. Somewhere in the chain fo execution that command string(s) is being corrupted or misinterpreted.
[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 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply