Execute SQL Task issues

I keep getting the following error on some execute sql tasks:

[Execute SQL Task] Error: Executing the query "DELETE cam FROM CUSTCTL.xref_corp_acct_mst AS xcam JOIN CUSTCTL.corp_acct_mst AS cam ON cam.corp_acct_uuid = xcam.corp_acct_uuid WHERE xcam.source_id = ?; DELETE CUSTCTL.xref_corp_acct_mst WHERE source_id = ?;" failed with the following error: "Invalid object name 'cam'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

They all use the same OLE DB Connection to SQL Server 2005. One of them works, but the other 6 fail with an error message similar to the one above. If I hardcode the parameters to a value, they work fine. I have the parameters defined as 0 and 1, with a type of LONG and being set by the same global variable.

I am at a loss as to what is going on. This worked fine the last time I touched it, and has only been failing recently. I profiled the connection, but I never see the script being sent to SQL Server, so I am guessing that IS is failing this before it gets that far.

ARGH!

Brandon

If it works when you hardcode the values can I suggest that you an expression to build the SQL rather than a parameterised statement.

-Jamie

|||

I have not used the syntax you show here but maybe try this if I understand the intent of the SQL:

DELETE FROM CUSTCTL.corp_acct_mst
WHERE corp_acct_uuid IN (
SELECT corp_acct_uuid
FROM CUSTCTL.xref_corp_acct_mst
WHERE source_id = ? );
DELETE FROM CUSTCTL.xref_corp_acct_mst WHERE source_id = ?;

This SQL revision should likely achieve the same thing and may get you past the error you are seeing as a workaround and still being able to use parameterized SQL.

Fred

|||

The query that I had worked before, but is not working. Just for giggles, I restructured the query like you have it with the same results... Something must have changed outside the package, because the source I checked in when it was operational has not been modified since - I just have no idea what it is.

Very frustrating.

|||

Has SP1 been applied on either your development tools or the server? We had to roll back from it due to a very odd bug that is supposed to be fixed with SP2.

I am experiencing a similar change in behavior on something different were I am using OLEDB and then ADO.NET. It was working for 3 days straight and then poof... stopped working without any redeployment of code and now will not work what so ever!

|||

The workstations where all the development is done are all SP1, but I believe the servers are still RTM. Hadn't heard anything about this - is there a KB article on this?

Thanks!

Brandon

|||

There is no KB article on it as far as I know but the bug I ran into specifically was around the FOR XML AUTO when the data was > 500 characters. It would add some extra data to the block before appending together.

I am not sure if there are any incompatabilities between SP1 and RTM when you develop as I do not know if they have changed any elements in the dtsx file structure.

You might want to try to simplify the issue and create a sample package that just has the basic tasks needed and be prepared to send to MS for assistance.

BTW, that issue I was getting last week after no code changes is now gone again without code changes! Nobody here seems to know why it happened and then disappeared

|||BTW, I think this

"Possible failure

reasons: Problems with the query, "ResultSet" property not set

correctly, parameters not set correctly, or connection not established

correctly."

is some general garbage thrown in all the time, with no regard for whether it is relevant. It used to mislead me until I figured out that (I think) it is a dumb tag thrown in with no particular reason.