Can I get a Query from a Fetch_cursor in SQL2000

Hi, The main application we used uses prepared cursors when running statments against the database. This makes it very hard to debug the querys as if you get an error or lock on the database all you can see is the Fetch_curror statement and the number of the statment. If you know the lock is coming that's fine as you can run up the profiler but if you don't know it will happen then the profiler doesn't help as the cursor has already been prepared.

Is there a way of using the cursor number to query the SQL engine and find out what statment was prepared?

If your app is doing server side cursors and names them then you may be able to use sp_cursorlist, sp_describe_cursor_tables and sp_describe_cursor_columns to better understand what the query is doing. Then you can query the sysCacheObjects table in the master database and it will show you the first 128 characters of the batch (on 2005 it will show you the first 3900 characters) of cached plans. You can use the ObjType column to distinguish between ad-hoc, prepared, stored procedures etc.

See BOL for more info.

|||

Thanks fro the reply David but it still doesn't give me the query that was run (the TSQL), if all I had was the fetch_cursor id.

The sysCacheObjects is cool, I can use this for other things:)

|||True, you can't query the sysCacheObjects table just based on the cursor_id but I thought it would be the next best thing, since it gives you a place to look for cached plans and the queries that sparked them.|||

It is not possible to get the query with just the cursor handle in SQL Server 2000. You could query syscacheobjects if you know the prepare cursor call or a part of the SELECT statement etc. You will have to rely on SQL Profiler or application trace to get the calls.

In SQL Server 2005, you can use the new dynamic management function sys.dm_exec_cursors to better determine cursor handles on the server, their associated sql handles to get query text and other information.

sql

Can Add/update/drop column sql statements in one store procedure?

I have a batch sql statments to add column NewCol into TableA, then update
NewCol at this TableA, then drop column NewCol from TableA. I have no proble
m
to run them as individual sql statment with GO after each statment. But when
I tried to put these sql statements into one store procedure, each time when
compile or run the store procedure, I got error 'Invalid column name'. I
guess column NewCol is not there at beginning. So Can I put Add/update/drop
column in one store procedure?
alter procedure xash
AS
-- other sql regarding to this TableA --
--alter add column--
alter table TableA add NewCol varchar(1) null
-- update new column--
Update TableA
set NewCol= 'Y'
--other operation related this table and new column--
--drop column --
alter table TableA drop COLUMN NewCol
GOYou have to commit the work at each step before the next step can see
it. There is lots of overhead for this.
I have to ask why you are doing this. Instead of having physical
storage hold your computed values, wouldn't it be better to simply have
a VIEW with the computed column in it? Also why do you want to use
VARCHAR(1); think about it.
CREATE VIEW FilteredFoobar (..)
AS
SELECT ..., CASE WHEN <<test here>> THEN 'Y' ELSE 'N' END AS new-col
FROM Foobar, ..
WHERE ..;|||On 13 Apr 2005 16:18:15 -0700, --CELKO-- wrote:

> Also why do you want to use VARCHAR(1); think about it.
You know, it would be nice if SQL implementations could just throw an error
message if you try to create a VARCHAR(1) column. :)