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

I have a Session table:

Username Type
------
bob private
bob private
sally private
bob duet

And I have an event table

Username Date Type
--------
bob 1/1/04 private
bob 1/2/04 private
bob 1/3/04 private
bob 1/3/04 duet
bob 1/3/04 duet

The way it works is this. A client signs up for appointments, and
they are placed in the event table. They buy sessions whenever they
want. The session table works like a clearing account. When they buy
sessions, rows are added to the session table, and when they use
sessions, rows are deleted. If they have an appointment, but no
sessions left, then they need to buy more. They can and will be
signed up for more appointments then sessions that they have bought.
I somehow need to join these tables, to show whether a client needs
to pay for an appointment or not. So the result set I want is:

Username Date Type Pay?
-----------
bob 1/1/04 private No
bob 1/2/04 private No
bob 1/3/04 private Yes
bob 1/3/04 duet No
bob 1/3/04 duet Yes

I can't actually delete sessions from the session table since this is
dataset is for display purposes, and the session still need to exist.

Does anybody have any ideas?

Thank you,

BenIt helps if you include DDL with your posts so that we can see what the keys
and constraints are.

Neither table, as posted, appears to have a primary key. In the Sessions
table there seems to be no obvious reason to add redundant rows to represent
the quantity of sessions purchased. Just add a quantity column and increment
or decrement it as appropriate. Since you say you are deleting the rows
anyway you are presumably not keeping any additional information about
individual session purchases?

In the Events table you have a duplicate row so for simplicity I've changed
your sample data slightly. Presumably you can include the date and time of
the event to make it unique? Or do you allow multiple events for the same
user at the same time?

Here's the DDL and sample data:

CREATE TABLE Sessions (username VARCHAR(10), type VARCHAR(10), session_qty
INTEGER NOT NULL CHECK (session_qty>=0), PRIMARY KEY (username, type))

CREATE TABLE Events (username VARCHAR(10), eventdate DATETIME, type
VARCHAR(10), PRIMARY KEY (username, eventdate, type))

INSERT INTO Sessions VALUES ('bob', 'private',2)
INSERT INTO Sessions VALUES ('sally', 'private',1)
INSERT INTO Sessions VALUES ('bob', 'duet',1)

INSERT INTO Events VALUES ('bob', '20040101', 'private')
INSERT INTO Events VALUES ('bob', '20040102', 'private')
INSERT INTO Events VALUES ('bob', '20040103', 'private')
INSERT INTO Events VALUES ('bob', '20040103', 'duet')
INSERT INTO Events VALUES ('bob', '20040104', 'duet')

Here's the query:

SELECT E.username, E.eventdate, E.type,
CASE WHEN S.session_qty >=
(SELECT COUNT(*)
FROM Events
WHERE username = E.username
AND type = E.type
AND eventdate <= E.eventdate)
THEN 'No' ELSE 'Yes' END AS to_pay
FROM Events AS E
LEFT JOIN Sessions AS S
ON S.username = E.username
AND S.type = E.type

--
David Portas
SQL Server MVP
--

Help with installing VS Tools

Hi,

I have a problem with the installation of SSCE31VSTools-ENU.exe into VS2005. The installation ended after a few seconds although in the event log there is information that the product is successfuly installed. However, when I run Visual studio there is no project under Smart Device regarding SQL Compact database or something similar (there are only templates for: device application, class library, control library, console application, empty project and nothing more)

VS2005 is in this version 8.0.50727.762 (SP.050727-7600)

In application event log there is: Product: Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005 [ENU] -- Configuration completed successfully.'"

Has anyone some idea if there is a problem or how to create SQL Compact database?

Thank you

Jarda

Have you tried installing the released bits?|||

Hi William,

I have the same problem that the gentlemen in the initial message described. I tried installing the released bits as you recommended, but the problem continued. Do you have any other suggestions?

Thanks for your help.

Mario

|||

Hi,

yes, it's yesterday release (Date Published: 1/11/2007).

Thanks

Jarda

|||

Did you install in the correct order?

The VSTools must be installed before the SQL Server ce Runtime.

Robert Wishlaw

|||

Hi,

my installastion order was:

Compact SDK

Compact Tools

Compact SQL edition

I hope that SDK didn't install runtime.

Thanks

Jarda

|||

Hi,

I made another try with installation. Uninstall all items regarding SQL Compact edition and try to install VS Tools first. However I got this message

"The upgrade patch cannot be installed by the Windows Installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch." :-(

Other items can be installed well (SDK, runtime).

I have no previous (beta) installation of Compact edition on my machine.

Please help me

Thanks

Jarda

btw. the link to download the VS Tools from MS site is wrong.

|||

jardajirava wrote:

the link to download the VS Tools from MS site is wrong.

Which link?

Robert Wishlaw|||

jardajirava wrote:

Hi,

I made another try with installation. Uninstall all items regarding SQL Compact edition and try to install VS Tools first. However I got this message

"The upgrade patch cannot be installed by the Windows Installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch." :-(

Other items can be installed well (SDK, runtime).

I have no previous (beta) installation of Compact edition on my machine.

Please help me

Thanks

Jarda

btw. the link to download the VS Tools from MS site is wrong.

Are you installing

Microsoft SQL Server 2005 Compact Edition Server Tools

or

Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005 Service Pack 1

?

The first is only for installations with IIS and SQL Server 2005. (not Express)

Be sure that the versions of the VS tools and runtime are the same. Get the current version of the VSTools from the link in the Related Resources section of the runtime download page at

http://www.microsoft.com/downloads/details.aspx?FamilyID=85e0c3ce-3fa1-453a-8ce9-af6ca20946c3&DisplayLang=en#QuickInfoContainer

Robert Wishlaw|||

Hi,

it is Tools for Visual Studio 2005 Service Pack 1 (VSTools). All downloads I made yesterday (11.1.2007) I try to download it again now but same information is displayed.

I have installed this extensions/addons in my Visual studio 2005:

Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41168
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41168
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41168
Microsoft Visual C++ 2005

Microsoft Visual Studio Tools for Office 77626-009-0000007-41168
Microsoft Visual Studio Tools for the Microsoft Office System

Microsoft Visual Web Developer 2005 77626-009-0000007-41168
Microsoft Visual Web Developer 2005

Microsoft Web Application Projects 2005 77626-009-0000007-41168
Microsoft Web Application Projects 2005
Version 8.0.50727.762

Visual Studio 2005 Tools for Office Second Edition 77626-009-0000007-41168
Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System

Extensions for Windows WF
Windows Workflow Foundation Tools for Visual Studio

Microsoft Visual Studio 2005 Professional Edition - ENU Service Pack 1 (KB926601)
This service pack is for Microsoft Visual Studio 2005 Professional Edition - ENU.
If you later install a more recent service pack, this service pack will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/926601

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00

Windows Installer XML Version 3.0
Votive 2005
Windows Installer XML (WiX) Toolset, Version 3.0
Primary Contributor: Justin Rockwood
Additional Contributors: Bob Arnson
Copyright ? Microsoft Corporation. All rights reserved.

XmlMvp.XPathmania 1.0
XmlMvp.XPathmania

Maybe this helps

Thanks

Jarda

|||

I am experiencing the same problem. After downloading the most recent bits (1/11/07) from the MS website I receive the error, on attempting to install the VS Tools for Visual Studio 2005 SP1 (SSCE31VStools-Enu.msi) that:

"The upgrade patch cannot be installed by the Windows Installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch."

I have confirmed I have installed VS 2005 SP1.

So... why does it reference an upgrade patch? I am installing the RTW version fresh, as I never installed the Beta.

Anyway... pretty frustrating :(

Ryan

|||

Sorry to reply to myself here... I discovered yesterday that my WMI installation is all messed up. This is rather odd as I develop WMI queries all the time against my localhost (just read queries, so I don't mess with the repositories etc).

I know WMI was working on Thursday before I installed the 3.1 Release of January 11 (in which I also uninstalled RC1). At any rate, WMI's class registrations are really messed up in a way the support articles and WMIDiag can't fix. Error message from WMI is 0x80040154 - Class not registered.

So... it makes some sense that the installer would not be able to find the correct product installed if it uses WMI to do so.

I am not able to point to the uninstall of RC1 or the install of the release version as "the problem", but it's an odd coincidence.

|||

I also get the same "The upgrade patch cannot be installed ..." message on VS SP1 when trying to install the tools.

|||

these post were helpful - I'm not going to try to install upgrade

Thanks

|||

Hi,

I just wanted to share that I was finally able to install the VS tools. The problem was that I had to uninstall and reinstall Visual Studio 2005 completely, then installed Visual Studio 2005 SP1, and finally installed the newly released code (including VS tools--> "SSCE31VSTools-ENU.exe").

I hope it helps.

Regards,

Mario