trans completes N profilr but not seeing results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

trans completes N profilr but not seeing results

Author

Topic

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-11 : 15:57:47

This is a new one for me.

I'm running a .net web app that inserts a record into a database, and everything looks fine, but the data just never gets into the table.

I'm seeing a "SQL:BatchCompleted" entry for a call to a proc that does a simple insert statement, but the record it was supposed to insert didn't get inserted, like the transaction never happened.

I can take what I see in profiler to QA run it there and it works, but when it runs from the web app and I see it "completed" in profiler, I go to find the records and it's not there.

I've run the proc code by itself, in QA, it works.
I've checked web app perms to proc execute perms.
I've checked data field sizes, proc inputs, etc.

What is going on? How else can I troubleshoot this?

www.beyonder422.com

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-12 : 04:07:00

Are you running it in a transaction and it's not being committed.
Is it run with set fmtonly on?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-12 : 10:19:17

not set in transaction, nothing out of the ordinary set

create proc test
as
insert into table1
(field1)
values
('value1')

It's the d@mnedest thing I've ever seen. I've turn every event and column on in profiler to see if I was missing anyting, and nothing stands out.

How can a call show up in profiler as executed and then not happen?

www.beyonder422.com

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-12 : 11:02:13

In the sp add a xp_cmdshell call to append to a text file so that you can see if the SP is called.

exec master..xp_cmdshell 'type called > c:\trace.txt'
and after the statement
exec master..xp_cmdshell 'type complete >> c:\trace.txt'

That will show you that the statement is being executed as the text file creation won't roll back.
If that works then look at loogging parameters and error codes and to the same to log to a trace table - but that will roll back if a rollback is the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-05-12 : 11:15:48

Could it be that the insert is being tossed for a constraint violation of some sort? Such inserts would show up in Profiler BatchCompleted, but the end result would be rolled back.

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-22 : 14:09:21

I'm going to try the "log to text file" idea

thanx for the suggestion.

www.beyonder422.com

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-22 : 15:02:21

1. I tried the "log to text file" idea, tested it first in QA, and it worked.

2. I ran the proc through the web app
-- Profilr says the transaction completed
-- But the new record didn't get inserted, so it really didn't work.
-- I applyed the logging technique, and it didn't log any of my steps.

SQL:StmtStarting sp_proc .Net SqlClient Data Provider
SQL:BatchCompleted sp_proc .Net SqlClient Data Provider

3. Using the security credentials and the exact TextData I saw in Profilr and successfully run it in QA.
-- It inserted and logged.

5. NOT running with fmtonly set on

Question:
So what does all this lead you guys to deduce? How do i continue troubleshooting?

www.beyonder422.com

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-05-22 : 16:29:07

I hate to "blame the user" in this case, because you have been pretty thorough up to this point. Still, I have to wonder, is there a similar database on this machine that the app may be connecting to, rather than the one you are checking? That is the only thing I can think of that would explain everything at one swoop.

Can you add the Transactions:SQL Transactions event just to see if there is a rollback event sneaking in there?

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-22 : 16:59:23

here it is... i've recently added all the "Begin Tran" and logging stuff per advice.

what else can i do to this thing to help me?

CREATE PROCEDURE [dbo].[usp_web_psh_add_hold]
@psh_hold_name nvarchar(50),
@zonegroup_id nvarchar(32),
@psh_hold_description nvarchar(255),
@psh_user nvarchar(20)

AS
BEGIN

BEGIN TRAN
declare @cmdtxt as varchar(255)
select @cmdtxt = 'echo insert start >> c:\temp\PshIssue_c155.txt'
exec master..xp_cmdshell @cmdtxt
COMMIT TRAN

BEGIN TRAN
INSERT INTO psh_Hold (psh_hold_name, zonegroup_id, psh_hold_description, active_flag, psh_user)
VALUES (@psh_hold_name, @zonegroup_id, @psh_hold_description, 1, @psh_user)
COMMIT TRAN

BEGIN TRAN
select @cmdtxt = 'echo insert end >> c:\temp\PshIssue_c155.txt'
exec master..xp_cmdshell @cmdtxt
COMMIT TRAN

END
GO

www.beyonder422.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 17:08:40

Why are you even bothering with a transaction here? You aren't even checking the value @@ERROR to determine if you have to rollback. I don't think a rollback would work on xp_cmdshell anyway.

Tara Kizer
aka tduggan

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 11:21:55

I'm doing anything I can think of to find out why profiler says it is working, but it's not.

www.beyonder422.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 11:29:47

Have you added errors to the trace yet?

Tara Kizer
aka tduggan

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 12:05:25

yes,

I've run it with EVERYTHING turned on.

Every event, every column, no filters.

I'm used to handling problems on the web side, but the kicker to me is that I see it in profiler. I've never seen a situation where I see the proc "getting" to the db and not executing.

so am i right to be focusing on the db side if I see it in profiler?

again, I take the textdata representation of the call and can execute it in QA.

i've dropped and recreated the object and am going to have to just start simplifying the equation even more, i'm just not sure how much more straight forward I can get it.

I dread knowing that it is going to be a Homer (DOOOHHH) moment when I figure it out, but right now that would be great...

other iteration of proc:

CREATE PROCEDURE [dbo].[usp_web_psh_add_hold]
@psh_hold_name nvarchar(50),
@zonegroup_id nvarchar(32),
@psh_hold_description nvarchar(255),
@psh_user nvarchar(20)

AS
BEGIN

declare @error int,
@cmdtxt varchar(500)

select @cmdtxt = 'echo insert start >> c:\temp\PshIssue_c155.txt'
exec master..xp_cmdshell @cmdtxt

BEGIN TRAN
INSERT INTO psh_Hold (psh_hold_name, zonegroup_id, psh_hold_description, active_flag, psh_user)
VALUES (@psh_hold_name, @zonegroup_id, @psh_hold_description, 1, @psh_user)
COMMIT TRAN

set @error = @@error

IF @error > 0
BEGIN
select @cmdtxt = 'echo error:' + convert(varchar(20),@error) + ' >> c:\temp\PshIssue_c155.txt'
exec master..xp_cmdshell @cmdtxt
print 'error -> ' + convert(varchar(20),@error)
END

select @cmdtxt = 'echo insert end >> c:\temp\PshIssue_c155.txt'
exec master..xp_cmdshell @cmdtxt

END
GO

www.beyonder422.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 12:26:16

If the stored procedure works fine in Query Analyzer, then the problem isn't with the stored procedure.

Could you post the application code that is associated with executing the stored procedure?

Here's how I would have my trace configured:

Start with the default trace (SQLProfilerStandard).

Event classes:
Stored Procedures -- RPC:Completed and SP:StmtCompleted
TSQL -- SQL:BatchCompleted and SQL:StmtCompleted
Errors and Warnings -- Exception
Security Audit -- Audit Object Permission Event event classes to your trace.
Remove everything else from event classes

Data columns:
All default data columns
Add Success

Don't filter anything.

If the row isn't being inserted, then you should either have a permission issue in the trace or an exception in the trace.

Have you verified that there aren't any triggers on the psh_Hold table? Could you post the DDL for that table including constraints, indexes, and triggers?

Tara Kizer
aka tduggan

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 15:11:46

web app code call:

Dim cmd As SqlDataAdapter
Dim conn As SqlConnection = New SqlConnection(CONN_STRING)
Dim ds As DataSet = New DataSet()
Try
' Add new hold
cmd = New SqlDataAdapter("usp_web_psh_add_hold '" & name.Text & "', '" & _
locationList.SelectedItem.Text & "', '" & description.Text.Trim & _
"', '" & getUser() & "'", conn)

cmd.Fill(ds)
ErrorHandler.LogTrace("SUCCESS", ClassName, FuncName, "Web", "Added new hold")
Catch
LogError(Err.Description, XXXX.ErrorHandler.ErrorType.FATAL, True, False)
ErrorHandler.LogTrace("FAILURE", ClassName, FuncName, "Web", Err.Description)
Finally
conn.Close()
cmd.Dispose()
conn.Dispose()
ds.Dispose()
End Try

www.beyonder422.com

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 15:20:38

trace info:
----------------------------------------------------------------------
SQL:BatchStarting usp_web_psh_add_hold 'xxx', 'P1_PAINT_OK', 'xxx', 'xxx\xxx
Audit Object Permission Event 1 -- usp_web_psh_add_hold usp_web_psh_add_hold 'xxx', 'P1_PAINT_OK', 'xxx', 'xxx\xxx
SP:StmtCompleted -- usp_web_psh_add_hold select @cmdtxt = 'echo insert start >> c:\temp\PshIssue_c155.txt'
Audit Object Permission Event 1 exec master..xp_cmdshell @cmdtxt
SP:StmtCompleted -- usp_web_psh_add_hold exec master..xp_cmdshell @cmdtxt
SP:StmtCompleted -- usp_web_psh_add_hold BEGIN TRAN
SP:StmtCompleted -- usp_web_psh_add_hold INSERT INTO psh_Hold (psh_hold_name, zonegroup_id, psh_hold_description,
VALUES (@psh_hold_name, @zonegroup_id, @psh_hold_description, 1, @psh_us

SP:StmtCompleted -- usp_web_psh_add_hold COMMIT TRAN
SP:StmtCompleted -- usp_web_psh_add_hold set @error = @@error
SP:StmtCompleted -- usp_web_psh_add_hold IF @error > 0
SP:StmtCompleted -- usp_web_psh_add_hold select @cmdtxt = 'echo insert end >> c:\temp\PshIssue_c155.txt'
Audit Object Permission Event 1 exec master..xp_cmdshell @cmdtxt
SP:StmtCompleted -- usp_web_psh_add_hold exec master..xp_cmdshell @cmdtxt
SQL:StmtCompleted usp_web_psh_add_hold 'xxx', 'P1_PAINT_OK', 'xxx', 'xxx\xxx
SQL:BatchCompleted usp_web_psh_add_hold 'xxx', 'P1_PAINT_OK', 'xxx', 'xxx\xxx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 15:26:20

For the permission events, what does the Success column show? 1 or 0?

Tara Kizer
aka tduggan

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 15:27:38

table data insert into schema:
----------------------------------------------------------------------

CREATE TABLE [dbo].[psh_Hold] (
[psh_hold_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zonegroup_id] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[psh_hold_description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[psh_create_time] [datetime] NOT NULL ,
[psh_release_time] [datetime] NULL ,
[active_flag] [bit] NOT NULL ,
[psh_user] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[psh_Hold] WITH NOCHECK ADD
CONSTRAINT [PK_psh_Hold] PRIMARY KEY CLUSTERED
(
[psh_hold_name]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[psh_Hold] ADD
CONSTRAINT [DF_psh_Hold_psh_create_time] DEFAULT (getdate()) FOR [psh_create_time],
CONSTRAINT [DF_psh_Hold_active_flag] DEFAULT (0) FOR [active_flag],
CONSTRAINT [DF_psh_Hold_psh_user] DEFAULT (suser_sname()) FOR [psh_user]
GO

www.beyonder422.com

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 15:29:28

quote:
Originally posted by tkizer

For the permission events, what does the Success column show? 1 or 0?

Tara Kizer
aka tduggan

First thing I thought of as well, perms issue.

1's across the board...

I noticed a "0" on the xp_cmdshell call, but I fixed that so now the text file loggint part is working.

so now it's just the insert.

www.beyonder422.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 15:32:00

So there are no triggers on your table? I just want to be sure about this.

Tara Kizer
aka tduggan

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-05-23 : 15:35:57

no triggers.

i'm going to add some more testing code to the proc.

do an insert into a test table, before and after.

I'm starting to see that there isn't a silver bullet for this.

and am waiting for the "DOOOOHHHHH" to happen....

I'll let you know as soon as I figure it out.

www.beyonder422.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources