I have a stored procedure I wrote in a SQLExpress 2005 Database that works fine when I run it from the SQL Server Manager Console but fails when I call it from my web application.

In the application I am using System.Data.SqlClient with the SqlConnection, SqlCommand and SqlDataReader classes. The procedure takes 3 parameters which I pass in.

When I run it in my application I get this message:

Cannot insert explicit value for identity column in table '#tmpAnalyzers

The stored procedure has IDENTITY_INSERT set on for that table so there is no problem running the stored procedure.

I read something about SQLClient having problem with SET commands but as this is a stored procedure it should not be an issue.

Does anyone know why it fails and how to make it work?

Thanks,


What you don't know can hurt you!

__________________
What you don\'t know can hurt you!

Hi David. Topic moved per your request and replies about moving it deleted. :)

Jim Minatel
Acquisitions Director
Wiley Technology Publishing
WROX Press
Blog: http://wroxblog.typepad.com/
Wrox online library: http://wrox.books24x7.com

I don't know what the problem actually is, but I have to ask... Why on earth would you build a Temp Table with an IDENTITY column only to override it? On the surface, that just doesn't sound right from the "git".

--Jeff Moden

Jeff, I agree 100% with you. The problem is when you build a temp table using:

SELECT * INTO #tmp FROM SOMETABLE ...

and SOMETABLE has an identity column in it SQLServer assumes you want that column in the temporary table to be identity as well. As usual, there are a number of ways to skin a cat. One would be to cast the offending column into a bigint type...

SELECT Cast(MYIDCOL as bigint) AS MYIDCOL, ANOTHER_COL, ... INTO #tmp FROM SOMETABLE ...

However, the solution to my problem was to take my right palm and smack it firmly against my forehead! When I originally created the stored procedure I had neglected to prepend the schema to the procedure name. I realized immediately that I had goofed and repaired that. However, at the time, I couldn't find the wrongly created procedure. In my application I had also neglected the schema but didn't pick up on that right away. As you can imagine, any changes I made to the procedure with the correct schema were not reflected in the one under the dbo schema or in my application! I'm still trying to get the egg off of my face.

What you don't know can hurt you!

Well explained, David. Thanks for the feedback. You have an egg problem... me... I keep getting a tennis **** stuck in my mouth :D

--Jeff Moden

Now isn't that funny... this forum censors the word s*h*o*e

--Jeff Moden