Mystery Database IDs in Profiler

I'm currently running Profiler sessions to track down Lock Timeout
problems.

My Profiler view contains (amongst others) the dbid column.

Much of the time, this displays familiar dbids, such as 2 (tempdb) and
5 (my main user db). However, it also regularly displays IDs of 0 and
132.

Using SELECT DB_NAME(), these translate as "master" and "NULL"
respectively.

Does anyone know:
a) why dbid = 0 translates to "master", when the actual id of this
database is 1, and
b) why Profiler reports these dbids in the first place?Hi

Database ID is a default data column and should be populated for all events.
What other columns are you capturing? Which event is displaying this value?

Not directly related to your question but these may help:
http://support.microsoft.com/defaul...kb;en-us;832524
http://msdn.microsoft.com/library/d...atabse_5xrn.asp
http://support.microsoft.com/kb/271509/EN-US/

John

"Phil" <philip.yale@.gmail.com> wrote in message
news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> I'm currently running Profiler sessions to track down Lock Timeout
> problems.
> My Profiler view contains (amongst others) the dbid column.
> Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> 5 (my main user db). However, it also regularly displays IDs of 0 and
> 132.
> Using SELECT DB_NAME(), these translate as "master" and "NULL"
> respectively.
> Does anyone know:
> a) why dbid = 0 translates to "master", when the actual id of this
> database is 1, and
> b) why Profiler reports these dbids in the first place?|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<42299950$0$8744$db0fefd9@.news.zen.co.uk>...
> Hi
> Database ID is a default data column and should be populated for all events.
> What other columns are you capturing? Which event is displaying this value?

Have variously tried capturing just the columns I thought I needed,
and then finally capturing every column possible just in case this
altered any output!

The only event I'm capturing is the LOCK TIMEOUT event. I've since
noticed that these lock timeouts (with the dbid equal to 0 or 255) all
have the same lock type (which I can't remember right now, and I'm
nowhere that I can look it up conveniently), which essentially
translates to a "key range test for a record insert". i.e. they're
not proper locks at all, and so the timeout is almost certainly an
internal mechanism for releasing them

I was just intrigued why these were associated with a dbid at all, why
0 and 255 was chosen, and why 0 additionally translates to 'master'.

BTW - genuine lock timeouts correctly display valid database IDs.

> Not directly related to your question but these may help:
> http://support.microsoft.com/defaul...kb;en-us;832524
> http://msdn.microsoft.com/library/d...atabse_5xrn.asp
> http://support.microsoft.com/kb/271509/EN-US/
>
> John
> "Phil" <philip.yale@.gmail.com> wrote in message
> news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> > I'm currently running Profiler sessions to track down Lock Timeout
> > problems.
> > My Profiler view contains (amongst others) the dbid column.
> > Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> > 5 (my main user db). However, it also regularly displays IDs of 0 and
> > 132.
> > Using SELECT DB_NAME(), these translate as "master" and "NULL"
> > respectively.
> > Does anyone know:
> > a) why dbid = 0 translates to "master", when the actual id of this
> > database is 1, and
> > b) why Profiler reports these dbids in the first place?|||Hi

It sounds like the INDID which will can be 0 or 255.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mysterious loss of data from mssql2k

Hi All,

I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'

To explain our situation:

We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.

When that page is submitted, the form data is passed to a stored
procedure on our mssql2k server, which performs several operations,
all of which are wrapped in a transaction.

In particular, the stored procedure performs an update operation on a
record in one table (i'll call it TableA) and an insert into another
table (TableB).

If the procedure encounters a problem (ie after each update / insert
operation in the procedure we test for IF @.@.Error<>0) it performs a
rollback, performs a select similar to the one immediately below, and
then RETURNs.

SELECT '1' as error, 'Unable to update TableA' as errormsg

If the procedure doesn't fail any of the @.@.Error tests, the
transaction is committed, and a membership number is SELECTed to be
returned.

SELECT '0' as error, @.memnum as membershipnumber

The @.memnum variable is populated within the transaction.

Back in the ASP page we test both for the proc returning an empty
recordset, or for it passing an explicit value in the error field, and
push the page to an error page if either of these conditions are met.

If, on the other hand, none of these conditions are met, and the
membershipnumber field in the recordset is populated with a valid
membership number, we push to a confirmation page.

This confirmation page receives the membership number in a session
variable, performs a SELECT against TableB (the table that received
the insert during the proc) using that membership number in the WHERE
clause, and the resultant recordset is used to populate the
confirmation details on that page. That recordset is also then used to
populate the details of a confirmation email, which is automatically
sent by the confirmation page.

And now here's our problem: we've become aware of a handfull of people
who have gone through the enrollment process, have received the
confirmation email containing the information they supplied as
expected, but the data appears to be entirely missing from our tables.
By that I mean that the record in TableA does not appear to have been
updated (under normal circumstances that record should have had
several flags set, and several other fields updated with information
supplied by the person enrolling), and the record in TableB does not
appear to have been inserted.

In essence, looking at our tables, it *feels* like the transaction in
the stored procedure for that particular enrollment hit a problem and
was rolled back. However, the evidence that we have in the form of the
confirmation email argues strongly that the data must have existed in
our tables (particularly in TableB), if only for an unknown period of
time.

We're kind of at our wit's end to work out what is going wrong with
these enrollments. From my understanding of transactions (and I could
well be wrong) any changes to data (ie updates, inserts etc) contained
within are essentially 'invisible' to any other operation (ie the
SELECT that happens in the confirmation page) until the transaction is
committed, implying that the effect of the update and insert should
have been 'permanently' successful if no error code is received and if
a valid membership number was returned. I ask, because someone in our
team has suggested that maybe the operations in the transaction
'lasted long enough' in the tables to have been visible for the SELECT
on the confirmation page to have worked, but were then subsequently
rolled back, explaining why the confirmation email is appropriately
populated and why the data then appears to be missing. However, as I
said, this doesn't match my understanding of how transactions behave.

Sorry for the length of this post, but I felt it was best to explain
this as best as I could.

Does anyone have any advice they can give us on this situation? ie,
are there any known problems with operations in transactions 'bleeding
over' into tables, but then being rolled back at some later point?
Does anyone have any thoughts or suggestions on how we can further
diagnose this issue?

Truly, any help will be immensely appreciated...

Thanks in advance,

M Wells"M Wells" <planetthoughtful@.gmail.com> wrote in message
news:hmba41p1gvtsmudtbus027ad3rapgo1ghd@.4ax.com...
> Hi All,
> I'm trying to track down a mysterious problem we're experiencing in
> which updates and inserts to tables in our mssql2k server appear to be
> 'disappearing.'
> To explain our situation:
> We have a web page (written in ASP, if that's relevant) on which we
> accept enrollment information.

<snip
First, to address your question about data inside a transaction being
visible to other connections, this would only happen if the other connection
explicitly sets its transaction level to READ UNCOMMITTED, which would allow
it to see data which has been inserted/updated but not committed. See SET
TRANSACTION ISOLATION LEVEL in Books Online for more details - I suppose
your ASP connections could be setting this isolation level, but it isn't the
default, so it would be somewhat unusual.

As for tracking down what's going on with the data, you can use Profiler to
run a trace, perhaps filtered on those specific tables and any relevant
stored procedures. If this problem happens fairly often, then running it
interactively may be possible, otherwise see the sp_trace_% procs in Books
Online for details of setting up a server-side trace.

You might also want to check for any triggers on the tables, as sometimes
they can be fired at times you don't expect. And if you have a middle tier
layer, you could also see if it's initiating a transaction before calling
the procedure - it could be that the procedure itself commits correctly, and
the email is sent, but there's an additional outer transaction started by
the middle tier which is then sometimes rolled back after sending the email.
Checking @.@.TRANCOUNT inside the procedure would give you a clue.

Simon|||M Wells (planetthoughtful@.gmail.com) writes:
> If the procedure doesn't fail any of the @.@.Error tests, the
> transaction is committed, and a membership number is SELECTed to be
> returned.
> SELECT '0' as error, @.memnum as membershipnumber
> The @.memnum variable is populated within the transaction.
> Back in the ASP page we test both for the proc returning an empty
> recordset, or for it passing an explicit value in the error field, and
> push the page to an error page if either of these conditions are met.
> If, on the other hand, none of these conditions are met, and the
> membershipnumber field in the recordset is populated with a valid
> membership number, we push to a confirmation page.
> This confirmation page receives the membership number in a session
> variable, performs a SELECT against TableB (the table that received
> the insert during the proc) using that membership number in the WHERE
> clause, and the resultant recordset is used to populate the
> confirmation details on that page. That recordset is also then used to
> populate the details of a confirmation email, which is automatically
> sent by the confirmation page.
> And now here's our problem: we've become aware of a handfull of people
> who have gone through the enrollment process, have received the
> confirmation email containing the information they supplied as
> expected, but the data appears to be entirely missing from our tables.

As I understand, this is an intermittent problem, and you don't have a
reproducible scenario. This make such a problem much more difficult
to track down. And if you make changes to address, you cannot really
be sure that you fixed the right thing.

One thing that is not clear to me is whether it is the same SQL Server
process that runs the stored procedure and the gets the data to the
confirmation page, or whether they are two different. (I should butt
in that I don't know ASP or IIS, so this talk about session variables
etc, tells me little.)

If it is the same SQL Server process, here is something that could
happen:

1) The stored procedure first run unsuccessfully, and a transaction
is started, but then neither committed nor rolled back.
2) The process then runs the procedure successfully, and then gets
the data to the confirmation page. This time a nested transaction
was started and committed. However, "commit" in the case of an
inner transaction just means that transaction count is decremented.
3) The process goes on and registers and confirms more enrollments.
4) Eventually the process is logged out, still with an open transaction.
All enrollments are now rolled back.

So why in step 1, would this happen? It can be a coding error in
the stored procedure, so that a rollback is not executed when it
should. But it could also be a client-side thing. Say that the
procedure is blocked for some reason, and the client gets a command
timeout. In this case the transaction started by the stored procedure
is *not* rolled back. This a really nasty gotcha.

If the confirmation page is really a separate SQL Server connection -
and you should really use the SQL Server Profiler to verify this - then
the data has been committed, and thus it has later been removed. Well,
if the confirmation reads with NOLOCK, are back to the previous
scenario.

One thing you should investigate in either case, is whether these missing
enrollments happened at different points in time, or if they are clustered.
That could give a clue of what may have happened.

I hope this has given you some more ideas of what to look for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

mysterious changes of data

I have a simple invoice, inventory, billing program that is doing something strange but I can't track it down.

I can't figure out what triggers it but occasionally when I add an invoice to a customer all the other invoices are added to the last customer updated.

My update statement on the invoice table is

UPDATE Invoices
SET Date = @.Date, InvoiceTotal = @.InvoiceTotal, SubTotal = @.SubTotal, Tax = @.Tax, CustomerID = @.CustomerID
WHERE (CustomerID = @.CustomerID)

The only triggers on the table are to update the qty in Inventory and another trigger that fires when a payment is entered.

Any Ideas?

In your update statement, there is no need to update your customer

UPDATE Invoices
SET Date = @.Date, InvoiceTotal = @.InvoiceTotal, SubTotal = @.SubTotal, Tax = @.Tax, CustomerID = @.CustomerID
WHERE (CustomerID = @.CustomerID).

Could you post your trigger codes ?. Then only, we will find out the actual issue ?

Thanks.

Naras.

|||

Trigger on InvoiceDetails to update qty in inventory

ALTER TRIGGER trInvoiceDetails_IU_UpdateQty

ON InvoiceDetails

FOR INSERT, UPDATE

AS

IF @.@.ROWCOUNT = 0

RETURN

IF UPDATE (Qty)

UPDATE WInventory

SET WInventory.InStock = ( WInventory.InStock - i.Qty )

FROM inserted i

JOIN WInventory

ON i.ItemID = WInventory.ItemID and i.ItemNumber=WInventory.ItemNumber

Trigger on Invoices to close invoice when payment entered

ALTER TRIGGER trPaidInvoices

ON dbo.Invoices

FOR UPDATE

AS

IF UPDATE (InvoiceTotal)

insert into ClosedInvoices (InvoiceID,CustomerID,[Date])

SELECT InvoiceID, CustomerID, [Date]

FROM Invoices

WHERE (InvoiceTotal = 0.00)

Delete from invoices where invoiceTotal=0.00

select * from Invoices

|||

Hi,

shouldn′t that be:

ALTER TRIGGER trPaidInvoices

ON dbo.Invoices

FOR UPDATE

AS
BEGIN

IF UPDATE (InvoiceTotal)

insert into ClosedInvoices (InvoiceID,CustomerID,[Date])

SELECT InvoiceID, CustomerID, [Date]

FROM Invoices

WHERE (InvoiceTotal = 0.00)

NNER JOIN INSERTED I
ON I.InvoiceId = In.InvoiceId

Delete from invoices where invoiceTotal=0.00
FROM Invoices In
INNER JOIN INSERTED I
ON I.InvoiceId = In.InvoiceId

END

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||maybe but I don't see how that could be causing my original problem?|||The problem is not related to the posted trigger code. Perhpas there are some other triggers on the related tables which interfer with the invoices table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

If you want to track this thing down quickly, perhaps SQL Profiler might help you to find out what statements are being run from where on what data to see exactly what is happening here and when.

Hope that helps,

John (MSFT)

My program uses a database. Does the end-user have to have SQL server 2005?

My program uses a database. Does the end-user have to have SQL server 2005?

It uses it to keep track of members so the database is in the installation folder and does not need to be accessed remotely

http://i130.photobucket.com/albums/p247/tarkster2/screen.gif

the above shows this error on another persons computer. they do not have sql server.

It depends on your application. If it is a web application, then you only need SQL server for the application itself but the end users don't need. If it is a client server application, it will depend on how the client is implemented. If the client accesses the database directly then you will need a client access license. If only the server does, then you only need 1 SQL Server for the server application.

Read the following articles that discuss the licensing schemes:

http://www.microsoft.com/sql/howtobuy/default.mspx

http://www.microsoft.com/sql/howtobuy/faq.mspx

http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.mspx

I'm not sure if I helped but I hope I answered your question. If not, please give more details on how the application is implemented.

Best regards,

Sami Samir

|||

End users do NOT have to have SQL Server -however they MUST have a license to use SQL Server -even if their usage is through a web server. Each user MUST have either a 'Client Access License' (CAL), or if there are many users, it may be less expensive to purchase a Server based 'Processor' license -which allows unlimited users without each user needing a CAL.

However, in your situation, it seems that the user is receiving an error because the remote SQL Server is not allowing remote connections. Remote connections MUST be allowed in order for other user at other computers to access the SQL Server.