I have a stored procedure owned by [dbo] and inside of this stored procedure I have the following code:
where [TABLE_TYPE] = 'BASE TABLE'
[TABLE_NAME] = 'tmpExample'
drop table [tmpExample]
create table [tmpExample]
intID int IDENTITY(1,1) primary key,
After this code I do various INSERT and DELETE statements against this table. Now ... the problem lies in how its executed. I have another user, say [user1], that executes this stored procedure from a .Net application through the Microsoft's SQLClient object. When the stored procedure is executed [user1] owns the [tmpExample] table after it's created, however it throws an error of Invalid object name 'tmpExample'.
The odd thing is that if you take that code out of the stored procedure, throw it in SQL Query Analyzer, and run it, it works. Another odd thing is that if you execute the stored procedure from SQL Query Analyzer it fails just as in the .Net app. The fix was that I had to create the stored procedure as [user1].
My question is, why if back when [dbo] owned the stored procedure and [user1] ran it (which had [user1] owning the [tmpExample] table), is it throwing that exception of Invalid object name 'tmpExample'? [user1] owns that table so why could it not then modify it by INSERT, UPDATE, and/or DELETE statements? And why does executing the stored procedure from SQL Query Analyzer fail yet the code itself in SQL Query Analyzer works?
Please help my madness!!!!!
Scott in Texas
.Net Developer (ASP, VB, C#)