Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

sql