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
How can I configure or what should I do in my profiler to discover the
statement or SP that causing the error. I should signal that just before
error messages I have RPC:Completed messages for different SP
Hi,
Please make sure that you have events from TSQL group included in your
trace. The most useful would be:
SQL:StmtStarting or SQL:BatchStarting.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>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 Event 0 sp_executesql;1
> How can I configure or what should I do in my profiler to discover the
> statement or SP that causing the error. I should signal that just before
> error messages I have RPC:Completed messages for different SP
|||Hi and so many thanks.
I 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?
"Maciek Sarnowicz [MSFT]" wrote:

> Hi,
> Please make sure that you have events from TSQL group included in your
> trace. The most useful would be:
> SQL:StmtStarting or SQL:BatchStarting.
> Regards,
> Maciek Sarnowicz
> SQL Server Tools SDE
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>
>
sql

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.
>

Help with query - anyone?

I have 3 tables,

first table is
CRT with crtID and crtNAME parameters, second table is
CRTP with crtpID and crtpNAME parameters and third is
FORM with crtpID and crtID parameters.

Third table joins parameters from other two whit their keys - crtpID
and crtID.

My question is how to make query to list crtNAME and crtpNAME in my
results??

For example FORM contains,

crtpID, crtID
001, 005
002, 005
003, 007
etc.

I want to list names associated to those ID-s joined in third table...

I hope that you understand me - thanks very much for any help.SELECT CRT.crtNAME, CRTP.crtpNAME
FROM CRT
JOIN FORM
ON CRT.crtID = Form.crtID
JOIN CRTP
ON Form.crtpID = CRTP.crtpID

Roy Harvey
Beacon Falls, CT

On 18 Aug 2006 00:57:21 -0700, "legenda" <dispet@.gmail.comwrote:

Quote:

Originally Posted by

>I have 3 tables,
>
>first table is
>CRT with crtID and crtNAME parameters, second table is
>CRTP with crtpID and crtpNAME parameters and third is
>FORM with crtpID and crtID parameters.
>
>Third table joins parameters from other two whit their keys - crtpID
>and crtID.
>
>My question is how to make query to list crtNAME and crtpNAME in my
>results??
>
>
>For example FORM contains,
>
>crtpID, crtID
>001, 005
>002, 005
>003, 007
>etc.
>
>I want to list names associated to those ID-s joined in third table...
>
>I hope that you understand me - thanks very much for any help.

help with nested Query

Hi
I have 2 tables. The first has employee information and the second has
payroll information. I need to find out people who are not in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter it and find
out for each payroll.
I don't think i have explained it very well so here is the data set.
hope someone can help me with this.
Thanks in advance
prit

Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333

TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333

Required RESULT required(Missing employees from payroll)
IDNumSSN
1333333333
2111111111I think this could be what you're looking for:

SELECT I.idnum, E.ssn
FROM
(SELECT DISTINCT idnum
FROM PayrollDetail) AS I
CROSS JOIN Employees AS E
LEFT JOIN PayrollDetail AS D
ON I.idnum = D.idnum
AND E.ssn = D.ssn
WHERE D.idnum IS NULL

If you have another table for the entity represented by Idnum then use that
table in place of the derived table "I".

--
David Portas
SQL Server MVP
--

Help with insert sql statements...

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.

Is there a way for the insert statements to be merged so that I won't be executing two statements?

Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)

Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)

Thanks.

If you really need to have it in one statement, you could put all of your logic into a stored procedure and simply call it from you application.