Wutime Posted May 14, 2003 Posted May 14, 2003 How do I view the "parsed" command text for a stored procedure before it gets sent to the database? I have a page updating a database using a stored procedure and passed arguments... ***************************************** Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As SqlCommand = new SqlCommand("sp_update_account", myConnection) myCommand.Connection = myConnection ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' setup all stored procedure parameters from text field values ......removed for brevity myConnection.Open() myCommand.ExecuteNonQuery() ***************************************** I want to view the parsed myCommand.CommandText before it reaches the database. When I view the CommandText I only get "sp_update_account", I want to see teh exact command being passed to the SQL Server Database. My procedure isn't updating the database and I'm not recieving an error, so I need to view the exact SQL text being passed. Is this possible? Quote
Madz Posted May 14, 2003 Posted May 14, 2003 You are Executing a Store procedure and asking to view command text . :mad: STORE PROCEDURES are Database Objects that are located with in Database You are just asking your command object to call that Database Object not T-SQL . Quote The one and only Dr. Madz eee-m@il
Wutime Posted May 14, 2003 Author Posted May 14, 2003 Hehe, I'm definitely not using the right terminalogy. Part of the problem of being a newbie is lacking the basic knowledge to ask questions properly; I'll try again. My paramaters are being passed to my stored procedure (@Value, @Value), and the command text has been told the stored procedure name (sp_blah_blah). I wanted to see the completed t-sql string that is being sent to the SQL Server. For instance... t-sql command = " sp_blah_blah '124312', 'asldgj' " There must be a way to capture the command being sent to the database.... My stored procedure is executing without any reported errors. However, when I check the database, no updates or inserts have been made; therefor I'm curious to see what values are being sent so that I can try to debug from the database end of things. Cheers for your help, it's much appreciated. Quote
Wutime Posted May 14, 2003 Author Posted May 14, 2003 Essentially, I would like to see what "string" has been passed when this ExecuteNonQuery() takes place. myCommand.ExecuteNonQuery() Quote
archer_coal Posted May 14, 2003 Posted May 14, 2003 re: I test the string by simply sending it to a control before it's sent to the database. Like a label or a textbox Then you can see what values are being sent as well as any other charactor issues. Quote
Wutime Posted May 14, 2003 Author Posted May 14, 2003 I would love to be able to do that, the problem is that I don't know where the string is... I'm simply supplying the name of the Procedure with this line.. Dim myCommand As SqlCommand = new SqlCommand("sp_player_vitals", myConnection) and then naming the parameters like this.... Dim parameterPlayerID As SqlParameter = New SqlParameter("@player_id", SqlDbType.Int, 4) ... I'm assuming this all gets put together by ASP.NET and then sent to the database....how do I get the completed, put together SQL statement? I'm assuming it's a property of myCommand... myCommand.ExecuteNonQuery() Quote
Madz Posted May 14, 2003 Posted May 14, 2003 Dear you are just calling a store procedure, you know you are just asking ADO.NET that it should take this parameter and execute the specified SQL Server Store procedure. you are not executing any TSQL statement . its not possible to view which query text has been sent to SQL Server. Can you check the Syntex of your Store procedure. just write it and try to convert it in a simple TQL Statements and then try executing it from your application. Quote The one and only Dr. Madz eee-m@il
Wutime Posted May 14, 2003 Author Posted May 14, 2003 I could do that, but that's not what I want. With 15 parameters it would be a waste of time if there is a property/method in the Command that would do this for me. I have a feeling this will be an invaluable peice of knowledge for me as I often use stored procedures. Quote
*Experts* Nerseus Posted May 14, 2003 *Experts* Posted May 14, 2003 The only way I know of to test this, Wutime, is to use the Sql Profiler that comes with the SQL Server tools (like Query Analyzer). Start up the profiler then: 1. File->New->Trace 2. Make your connection to the server 3. In Trace Properties go to the Events tab and add the event "SP: Completed" When this starts, you'll see a bunch of messages flow by. You're looking for the lines "SP: Completed". It shows the SQL Query, be it dynamic SQL or a stored proc call, including all params. -Nerseus Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
*Experts* Nerseus Posted May 14, 2003 *Experts* Posted May 14, 2003 Whoops - better use SP:Starting instead of SP:Completed since your proc is having errors and might not complete :p If you'd like to see EVERY line that the proc is executing (say your proc does 3 SELECTs, an INSERT, and a DELETE), use SP:StmtStarting. -Nerseus Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Wutime Posted May 14, 2003 Author Posted May 14, 2003 Thanks for the detailed message - it was VERY helpful I'm going to give it a whirl Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.