Help with SQL errors in Profiler

I noticed recently errors logged, each 5 second in the event viewer. I ran
Profiler by adding Errors and warning, RPCcompleted, Stored proc...
I see the same errors logged in profiler as the oners logged in event viewer
but can not find the text or the SP generating the error. errors in Profiler
appear as follows :
Exception : Error: 208, Severity: 16, State: 1
ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16, State: 1
2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
My web site continu to work and I don't see error messages on my pages.
I use DBCC INPUTBUFFER(SPID), I get the following result :
RPC Event0sp_executesql;1
A microsofot consultanty asked me to set up profiler sessions where I
capture SQL_BatchStarting & RPC:Starting
or SQL:stmtstarting
I get
---Start Profiler
result--
exec [PEP_ETC]..sp_procedure_params_rowset N'CreateSearchSP', 1, NULL, NULL
SET NO_BROWSETABLE ON
RPC:Starting
declare @.P1 int
set @.P1=-1
exec sp_prepare @.P1 output, N'@.P1 varchar(2),@.P2 varchar(8),@.P3
varchar(3),@.P4 varchar(1),@.P5 varchar(2700),@.P6 varchar(255),@.P7
varchar(100),@.P8 varchar(600),@.P9 varchar(3),@.P10 varchar(3),@.P11
varchar(2),@.P12 varchar(100)', N'exec CreateSearchSP @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8, @.P9, @.P10, @.P11, @.P12', 1
select @.P1
SQL_BatchStarting
SET FMTONLY ON exec sp_execute 7,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
',' ' SET FMTONLY OFF
---End Profiler
result--
then the error comes here
---Start Profiler
result--
EXCEPTION : Error: 208, Severity: 16, State: 1
ErrorLog
2004-09-28 15:18:38.45 spid66 Error: 208, Severity: 16, State: 1
2004-09-28 15:18:38.45 spid66 Invalid object name '##CC'..
---End Profiler
result--
In the SP CreateSearchSP, I have a dynamic insert statement as follows :
EXEC "Select xx, yyyy into ##CC" + @.sTableName + " where labalablab "
This SP procedure is called from several places (ASP pages and from within
other SPs inside SQL) with same user and IIS appli.
So what I need to know is just the text that is calling this stored procedure.
When I browse the page that heavily use it, I don't see any error. So it is
sure either when SQL under heavy load can't execute it correctly or the error
is generated when calling it from somewhere else which I would like to
discover.
So what I need to know is just the text that is calling this stored procedure?
Try following steps.
Place a filter on SPIDS to see only that spid that is resulting into error.
That should give you the commands being executed from that spid only.
Hope fro there you should be able to figure out which SP is calling it.
|||Thanks for the response. How can Ifigure out the SPID before running profiler?
I am not so expert in SPID maniuplating, If I am not mistaken, between the
momemnt where you have a SPID and the moment t for exemple to use dbcc
inputbuffer, the sql phrase might change, no ?
Thanks
"Vikas Kumar" wrote:

> Try following steps.
> Place a filter on SPIDS to see only that spid that is resulting into error.
> That should give you the commands being executed from that spid only.
> Hope fro there you should be able to figure out which SP is calling it.
>