Stored Procedure skipping commands
in [Programming SQL]
|
From: david on 24 Mar 2010 12:17 Hi, I have a user with a stored procedure in 2008 SP1 that appears to be ignoring the query so no rsults are returned. When called from code he receives no record set and when I call teh SP from managements studio, I get 'Command(s) completed successfully.' The stored procedure basically consists or a declare, a set and a query with several CTEs. when I run profiler, I see the declare, the set and then these 4 events: SP:StmtStarting (textData is null) SP:Recompile (TextData is null) SQL:StmtRecompile (TextData is null) SP:CacheInsert (TextData contains the SP name) then it exits the SP. When run the debugger, the same thing happens I step into the SP, step through the declare, the set and when I hit step into again, it exits the stored procedure. Does anyone have any ideas what might cause this? I assume if I rebuild the SP again it will work, because that is what he has done in the past, but I would like to prevent this from happening at all if possible. thanks, David
From: Sylvain Lafontaine on 24 Mar 2010 14:35 Add � SET NOCOUNT ON � at the very beginning of your SP. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "david" <daveg.1(a)comcast.net> wrote in message news:648d7ba8-20ac-494e-8e79-7bddc4ffb2b4(a)k19g2000yqn.googlegroups.com... > Hi, I have a user with a stored procedure in 2008 SP1 that appears to > be ignoring the query so no rsults are returned. When called from > code he receives no record set and when I call teh SP from managements > studio, I get 'Command(s) completed successfully.' The stored > procedure basically consists or a declare, a set and a query with > several CTEs. > > when I run profiler, I see the declare, the set and then these 4 > events: > SP:StmtStarting (textData is null) > SP:Recompile (TextData is null) > SQL:StmtRecompile (TextData is null) > SP:CacheInsert (TextData contains the SP name) > then it exits the SP. > > When run the debugger, the same thing happens I step into the SP, step > through the declare, the set and when I hit step into again, it exits > the stored procedure. > > Does anyone have any ideas what might cause this? I assume if I > rebuild the SP again it will work, because that is what he has done in > the past, but I would like to prevent this from happening at all if > possible. > > thanks, > > David
From: david on 24 Mar 2010 14:51 On Mar 24, 2:35 pm, "Sylvain Lafontaine" <sylvainlafontaine2...(a)yahoo.ca> wrote: > Add « SET NOCOUNT ON » at the very beginning of your SP. > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site:http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > "david" <dave...(a)comcast.net> wrote in message > > news:648d7ba8-20ac-494e-8e79-7bddc4ffb2b4(a)k19g2000yqn.googlegroups.com... > > > > > Hi, I have a user with a stored procedure in 2008 SP1 that appears to > > be ignoring the query so no rsults are returned. When called from > > code he receives no record set and when I call teh SP from managements > > studio, I get 'Command(s) completed successfully.' The stored > > procedure basically consists or a declare, a set and a query with > > several CTEs. > > > when I run profiler, I see the declare, the set and then these 4 > > events: > > SP:StmtStarting (textData is null) > > SP:Recompile (TextData is null) > > SQL:StmtRecompile (TextData is null) > > SP:CacheInsert (TextData contains the SP name) > > then it exits the SP. > > > When run the debugger, the same thing happens I step into the SP, step > > through the declare, the set and when I hit step into again, it exits > > the stored procedure. > > > Does anyone have any ideas what might cause this? I assume if I > > rebuild the SP again it will work, because that is what he has done in > > the past, but I would like to prevent this from happening at all if > > possible. > > > thanks, > > > David- Hide quoted text - > > - Show quoted text - Thank you, but it already is. David
From: david on 24 Mar 2010 15:39 On Mar 24, 3:12 pm, "Tibor Karaszi" <tibor_please.no.email_kara...(a)hotmail.nomail.com> wrote: > Perhaps you have two procedures with same name but in different schemas? > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > Alas, no. At this point I would happy if it were that simple. I did even go and add "dbo." to my EXEC command to see if that might do anything, but no, still no result set, just "Command(s) completed successfully." Thank you, David
|
Pages: 1 Prev: DBMail Problems Next: DBTYP.NET Studio 2010 - Fastest Database Comparison Suite Released |