I just finished writing a small data update utility to copy a single row of data from a custom written application into a piece of packaged software. Usually when I do this I turn on Profiler, watch the packaged software insert a row and then copy what they did. This helps me find all the odd little status fields and related tables that aren't obvious from looking at the table. And yes, if the developer provided documentation it would help. They didn't.
A simple login, adding one row and logging out generated over 10,000 SQL:BatchCompleted events since this application uses ODBC cursors for all its data access. This meant I had to wade through 10,000 sp_cursor* statements to find the dozen actual SQL statements that were issued.
Last year I wrote an article on examining SQL Server Trace files. In that article I show examples of what a server-side cursor or SQL statements issued through ODBC look like:
declare @P1 int
set @P1=180150001
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen
@P1 output,
N'select SomeColumn FROM TableName',
@P2 output,
@P3 output,
@P4 output
select @P1, @P2, @P3, @P4
GO
exec sp_cursorfetch 180150001, 2, 1, 1
GO
exec sp_cursorclose 180150001
GO
Today I got the "opportunity" to see how updates worked in this case. Using a similar example an update would look like this:
declare @p1 int
set @p1=1073741854
declare @p2 int
set @p2=180150297
declare @p5 int
set @p5=1
declare @p6 int
set @p6=4
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,
N'SELECT Column1, Column2 FROM TableName WHERE PkColumn = 37',
@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7
GO
exec sp_cursorfetch 180150297,2,1,1
GO
exec sp_cursor 180150297,4,1,N'',@Column1='2007-0660',@Column2='Accident'
GO
exec sp_cursorfetch 180150297,8,-1,1
GO
exec sp_cursorclose 180150297
GO
The sp_cursor statement declares the new values for the columns that are updated. I found some third-party documentation on sp_cursor but didn't find much from Microsoft in very limited searching. Apparently the second parameter indicates whether this is an update or insert. The sp_cursorfetch with the -1 appears to commit the row back to the database.
The sp_cursor statement can be separated from the corresponding statement to create the cursor by hundreds of rows in Profiler. I found myself searching for sp_cursor, finding the cursor number and then searching backwards in the trace to find the declaration of the cursor.