An INSERT EXEC statement cannot be nested.

i wanted to store the output of my store proc in a temp table and i wsa doin
d
this:
INSERT #temp EXEC sproc
and it turned out i cannot do this if my sproc has another insert...exec
thing going on within it.
Is there any way i can store the output of my sproc somehow ?
thanks in advanceAbhishek Pandey (AbhishekPandey@.discussions.microsoft.com) writes:
> i wanted to store the output of my store proc in a temp table and i wsa
> doind this: >
> INSERT #temp EXEC sproc
> and it turned out i cannot do this if my sproc has another insert...exec
> thing going on within it.
> Is there any way i can store the output of my sproc somehow ?
Answered in comp.databases.ms-sqlserver. Please to do not post to multiple
newsgroups independently.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

An explicit value for the identity column in table

for some unknow reasons.. my store proc stop working.. and i got an error..
i have installaed the latest SP4 for SQL server 2000 and still have the problem !
any ideas why ??
Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."
CREATE procedure LCMS_Modules_Add

@.PageID int,
@.ModuleDefID int,
@.Panename nvarchar(32),
@.Title nvarchar(128),
@.Admins nvarchar(256)

as

insert into LCMS_Modules
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)
GO

Have you reordered the columns in your LCMS_Modules table? I amguessing that you did, and that the identity column had previously beenat the end and is now in the beginning or somewhere in the middle.
Specify your column names in your INSERT statement. This is a best practice anyway and should take care of your problem.
insert into LCMS_Modules (column1, column2, column3, ...etc..., column15),
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)

An existing connection was forcibly closed by the remote host

Hi,

I am using ASP.NET 2.0 application to fetch data from a SQL server 2005 using stroed proc.

Stored proc is also using one table from Linked Server (SQL 2000). Linked Server is configured to use login's current security context.

I am getting the following error message

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/15/2006 4:26:30 PM
Event time (UTC): 6/15/2006 8:26:30 PM
Event ID: 988964e6fa5249e38b1bc6c9a5ecd1e4
Event sequence: 850
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/MyApp-1-127948732130920720
Trust level: Full
Application Virtual Path: /MyApp
Application Path: C:\Inetpub\wwwroot\MyApp\
Machine name: MyServer
Process information:
Process ID: 352
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://MyServer/MyApp/Page1.aspx
Request path: /MyServer/MyApp/Page1.aspx
User host address: XX.XX.XXX.XX
User: DomainName/UserName

Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Please Guide

Thanks

Is it intermittent or consistent?

1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This looks like a consistent issue. It normally means that you need to configuration IIS account to access SQL Server. You can take a look at this link, http://support.microsoft.com/kb/247931/en-us.

2. TCP Provider: An existing connection was forcibly closed by the remote host.

This normally is an intermittent issue. It happens when you enable connection pooling and server closes the connection for reason like connection clean up, restart, network failure, the ASP.NET( sqlclient ) use dead connection from the pool to write data and thus get the failure you see. Re-retry connection should solve the issue.

|||

Hi Nan,

Re: 1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

IIS is configured properly to access data from SQL Server and it is working fine when IIS & SQL are on the same box. But the above message comes only when the stored proc tries to access data from a 2nd SQL Server (linked server which is not on the same box as IIS). Somehow it is not passing the credential to the 2nd SQL Server. 2nd SQL server is linked using login's current security context. Also ASP.NET 2 web.config file says <identity impersonate="true" />. I am using NT domain right now. Is this issue of "Double hop"?

Re: 2. TCP Provider: An existing connection was forcibly closed by the remote host.

Is connection pooling is by default? Can we change so that it should not use it?

Thanks

Shafiq

An alternative for Dynamic SQL required

Hey guys I've something like this which is a reauirement. I dont want to use
a dynamic SQL
eg is
create proc proc1
@.ownername varchar(100),
@.IPaddress varchar(15)
as
select a,b,c from Table1 t1
join Table2 t2 on t1.col1=t2.col2
where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
+'%') OR
(t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
(t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
go
Hopefully you can see the situation here.
I have an app where I can select (either or fname or lname) and I can also
select (either or internal IP or external IP). The above proc will work for
either in both cases.
What I want to do in the stored proc is somehow check these conditions. I
dont want to use dynamic SQL. Can you suggest an alternative which will help
in performance when compard to Dynamic SQL? Thank you.One obvious performance issue is that you are performing a LIKE comparison
on a value that is left truncated. For example, if OwnerName is indexed,
then this is will result in an index scan:
where OwnerName LIKE 'John%'
However, the following would result in a non-indexed table scan:
where OwnerLastName LIKE '%Smith'
where OwnerLastName LIKE '%Smith%'
Also, read this document; specifically the paragraphs about "sargable"
comparison arguments.
http://www.microsoft.com/technet/pr...s/inside14.mspx
Actually, dynamic SQL may be the solution to your problem. If you could
construct your SQL on the application side or construct the SQL within the
procedure in a variable and execute using the T-SQL Exec function.
http://msdn.microsoft.com/library/d...br />
05ro.asp
You can use the Show Execution Plan feature of Query Analyzer to determine
if your query is properly utilizing an index.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:8FD1CC5B-A8F9-4080-A75C-65D85E331CF9@.microsoft.com...
> Hey guys I've something like this which is a reauirement. I dont want to
> use
> a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
> +'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
> go
>
> Hopefully you can see the situation here.
> I have an app where I can select (either or fname or lname) and I can also
> select (either or internal IP or external IP). The above proc will work
> for
> either in both cases.
> What I want to do in the stored proc is somehow check these conditions. I
> dont want to use dynamic SQL. Can you suggest an alternative which will
> help
> in performance when compard to Dynamic SQL? Thank you.|||On Fri, 17 Mar 2006 09:05:26 -0800, Tejas Parikh wrote:

>Hey guys I've something like this which is a reauirement. I dont want to us
e
>a dynamic SQL
>eg is
>create proc proc1
>@.ownername varchar(100),
>@.IPaddress varchar(15)
>as
>select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
>where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
>+'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
>go
>
>Hopefully you can see the situation here.
>I have an app where I can select (either or fname or lname) and I can also
>select (either or internal IP or external IP). The above proc will work for
>either in both cases.
>What I want to do in the stored proc is somehow check these conditions. I
>dont want to use dynamic SQL. Can you suggest an alternative which will hel
p
>in performance when compard to Dynamic SQL? Thank you.
Hi Tejas,
Lots of useful information for this type of problem can be found on
Erland's page: http://www.sommarskog.se/dyn-search.html.
Hugo Kornelis, SQL Server MVP|||Tejas Parikh (TejasParikh@.discussions.microsoft.com) writes:
> Hey guys I've something like this which is a reauirement. I dont want to
> use a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%' + ISNULL(@.ownername,
> A.OwnerFirstName) +'%') OR
> (t1.OwnerLastName LIKE '%' + ISNULL(@.ownername,
> A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.ExternalIP) + '%') OR
> (t1.InternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.InternalIP)+'%'))
> go
Well, as long as you have the % first in the LIKE operations, this is
going to table scan no matter what you do, so there would not be much
point with using dynamic SQL for better performance. Your current code
would work fine.
I would recommend that you leave it to the users to specify any initial %
if they need it. Then there is a at least a ghost of a chance for
indexes to be used. It's difficult to give detailed suggestions though,
as I don't know the tables, and I suspect that your real procedures have
more than these two parameters.
But my article, that Hugo also pointed you to, might give you some
ideas: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Am I locking it up...

Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.

But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @.@.error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.

Tomwhat do you see when you do sp_who2?

Any blocking?

Do you call any other sprocs?

Do you execute any extended procs?|||Nope I do not see any blocking. No, but will and no. Any ideas?

I did find this. By the way I am using your template.

Server: Msg 2732, Level 16, State 1, Procedure CreateParContracts, Line 879
Error number 0 is invalid. The number must be from 13000 through 2147483647
This happens when I reach the sp_error lable at the end of stored proc. Here is the code

SP_Error:
If @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
If (@.Error_Type = 50002)
BEGIN
Rollback TRAN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END
RAISERROR @.Error_Type @.Error_Message

the line 879 seems to be pointing to if statement "If @.Error_Type = 50001"|||Can you attach the sproc so we can look at it?|||Ok I believe I have attached the file. Actually I did not orginally produce this script. It was just a set of t-sql code being run each time they need to load these. I am trying to automate the process. Couple of things.

1. The attachment is not the one I could get to compile. I kept getting a Server: Msg 133, Level 15, State 1, Procedure CreateParContracts, Line 862
A GOTO statement references the label 'SP_Error' but the label has not been declared.

??

2. If I remove the sp_exit and just put it the end of the stored procedure then I have the snippet of code that I just posted. So I don't know. Looks ok to me but I have something wrong. :eek:

Thanks Brett

Tom|||I'm looking...

first very bad thing is the creation of a permanent table...what if the sproc is run at the same time...change that to use a temporary table...and even though the temp table goes away at the end of the batch, it's always a good idea to drop it on the way out...

I'm still looking|||Well, I agree but i didn't think it would hurt anything leaving it in since it would get run once a month and by one person. But, if this were to be used by more than one user than yes it has to use a temp table. As matter of fact I think it would better to just rewrite the whole thing. Since there are a lot of things that bother me about this script.|||Yeah..there are around 25 separate steps...

And where did you get that code? Error location btw is a debugging tool I use when I want to know which statement failed..I updated them..

Try this attachment|||It was just a script file that was being run with 4 other script files to process this data. The one you looked at was the first one that got ran. There are four others. This is all processed into files that get loaded to a AS400. As stated earlier I decided to try and create a sp for each script file. Then run all 5 from a containter. This may not work as they are. But I really don't have the time to rewrite them. I wonder if it will hang now. I will try it out. Thanks a lot for your help. I will let know if I get it working.

Thanks
Tom|||Good Luck...You might want to consider breaking it up.

BUT Where did you get that code?

That's definitely part of my template and definetly my error handling...

My Blog Entry (http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx)

Now I gotta make sure it runs|||I was afraid of this but now it just keeps running and running and running. It might have something to do with some code trying to delete or change data that as yet to be committed from being inserted before. Would that make sense? If I move the Begin Tran down near the end it runs fine.

Tom|||Yes, I got the error handleing from your template. The rest is a mixture of what was there and what I normally put in. Perhaps I am not understanding question. :confused:|||Doesn't make sense.....if you didn't have any at all, it is implicitly 1 transaction anyway for that spid...and would rollback with an error that was severe enough...

Run the sproc with the BEGIN TRAN at the top...

And while it's just sitting there...run sp_who2 to see what's up...

You might want to start profiler as well (On a client)