March 5, 2010 at 7:38 am
Good Morning to All! ... I have an application that inserts data into a table. When monitoring through the SQL Server Profiler shows me that the insertion does StoreProcedure runs but when I try to do a SELECT on the table do not show me any records. What can be happening?
March 5, 2010 at 7:51 am
Usually when a developer asks me this question, I find the data in the table in a different database (usually master).
You may want to check other databases.
March 5, 2010 at 11:32 am
Assuming you have identified the locations and they are correct there are a couple of possibilities. The most likely is that the records from the insert statement in the stored procedure were rolled back. If the table has an identity column you can check what the current value is by using dbcc checkident. You can also trace for exceptions, or look at the activity as it is happening through a query like the following:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Also, if you are looking at the records as they are bing inserted you might not be able to see them because the insert probably has an exclusive key/page/table lock during the process.
Regards,
Toby
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply