September 12, 2008 at 11:47 am
any one knows what is worng with this statement
DECLARE @cursor INT,@rows INT
EXEC sp_cursoropen @cursor OUTPUT,N'SELECT * FROM sys.objects where objectid>@y',8193, 8193,
@rows OUTPUT,N'@y int',12
am getting the following error:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@y".
Msg 16909, Level 16, State 1, Procedure sp_cursorclose, Line 1
..>>..
MobashA
September 12, 2008 at 12:15 pm
Based on the error message, you have not declared the variable/parameter @y correctly. Are you directly calling this stored procedure? According to BOL:
These system stored procedures will show up in SQL Profiler traces of ADO, OLE DB,, ODBC, and DB-Library applications that are using API server cursors. They are intended only for the internal use of the SQL Server Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2008 at 12:22 pm
am executing this statement from the management studio.
..>>..
MobashA
September 12, 2008 at 12:26 pm
do u know a way to save the trace results to a table just like the profiler dose..(on line)
..>>..
MobashA
September 12, 2008 at 12:58 pm
If you start a server side trace to a file you can use ::fn_trace_gettable([path], [# of files]) to return the data in tabular format and insert them into a table. You can do this while the trace is writing to the file as well. If you return the EVentSequence column in the trace you could use that in a join in order to only insert new data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2008 at 1:19 pm
but if i need the trace data to be online in a table i couldnt use this way rigth..
thats so i need to find who SQL profiler dose it..
..>>..
MobashA
September 12, 2008 at 1:42 pm
Can you please explain what you are trying to do, then someone may be able to help you. Right now all I know is that you were trying to use a stored procedure that is designed for internal use by SQL Server to return all the rows in sysobjects where the object_id > a variable. Then you asked about how to save trace data to a table while the trace is running.
There is not a way to save server-side trace data to a table while the trace is running, other than the method I already shared. You just need to create the destination table then you can create a query that inserts the data from the trace file into the table. You can put the query in a job and then have a minimal delay which you can define. The query would have to be something like this:
Insert Into trace_data
-- you may want to provide the column list here
Select
GT.* -- you may actually want to list columns
From
-- [# of files should be the max # of files as defined in the trace
::fn_trace_gettable([file path], [# of files] GT Left Join
trace_data TD ON
GT.EventSequence = TD.EventSequence And
GT.StartTime = TD.StartTime -- you need this for reboots
WHere
TD.EventSequence Is Null
If you are interested in using the Rowset provider (like Profiler does) you could use the SMO library in .NET to write your own Profiler-Type Client that runs a a windows service and writes the events to the table, but this will have a bigger performance impact than a server side trace.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply