Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-01-25 : 10:01:04
|
| If I use ADO to execute an SQL command:SET cmdSP = Server.CreateObject("ADODB.Command")WITH cmdSP .ActiveConnection = application("DBaddr") .CommandText = "dbo.AD_UserIDSelect" .CommandType = adcmdstoredproc .Parameters.Append .CreateParameter ("@CallerAdminID", adInteger, adParamInput, , session("AdminID")) .Parameters.Append .CreateParameter ("@UserID", adInteger, adParamInput, , useridsearch) .Parameters.Append .CreateParameter ("@CourseID", adInteger, adParamInput, , session("CourseID")) SET rsRecordSet = .Execute Is there a command string that's built by ADO that could be retrieved and printed for diagnostic support?Sam |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-25 : 11:27:42
|
| You can use sql profiler on sql server and do a trace to see exactly which commands are being executed. You can also debug stored procedures.Alternatively you could do a watch on a Command object in vb and see all it's properties then in your asp write out all those parameters.Another thing to try that I've never tried before would be the prepared statement.objcommand.prepared = trueResponse.Write objCommand.preparedResponse.Write objCommand.TextThis is supposed to prepare/compile the command before execution don't know if it changes the Text but you can try and let us know.Edited by - ValterBorges on 01/25/2003 11:42:01 |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-25 : 12:48:15
|
| Valter,This looks promising, but using the following .CommandText = "dbo.AD_UserIDSelect" .CommandType = adcmdstoredproc .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter ("@CallerAdminID", adInteger, adParamInput, , session("AdminID")) .Parameters.Append .CreateParameter ("@UserID", adInteger, adParamInput, , useridsearch) .Parameters.Append .CreateParameter ("@CourseID", adInteger, adParamInput, , session("CourseID")) .Prepared = True strcmdSP = .CommandText SET rsRecordSet = .Execute response.write strcmdSPI get:{ ? = call dbo.AD_UserIDSelect(?, ?, ?) }Parameters are not filled in.. Works the ame if .CommandText is read after the .Execute.--------------Aside - I found my problem, I'd declared two SP parameters in one order, and placed them in another order in the ADO. No error given other than no record found. Seems ADO doesn't check the SQL parameter names?Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-25 : 13:03:49
|
It could be, but I do know that it's very sensitive to parameter order even if it does check the parameter names. That was one of the things I kinda liked about passing everything as a string:sql = "EXEC mySP "if response("name")<>"" then sql = sql & " @name='" & response("name") & "', "if response("age")<>"" then sql = sql & " @age=" & response("name") & ", "if response("height")<>"" then sql = sql & " @height=" & response("name") & ", "sql = sql & " @deadVar=Null " 'this is the ultimate in laziness  connObj.Execute sqlThis way all of the optional parameters would be added or not depending on whether they were filled in on the form. I would even sometimes add a @deadVar to the sproc just so I wouldn't have to write code to clean up the trailing comma  As you can guess it's a really lazy, inefficient, and bad way to program, and I try not to do it anymore. But it did not suffer from parameter ordering problems in ADO, that's about the only good thing I can say about it. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-25 : 14:17:29
|
| Yeah, well I thought there might be an ADO option somewhere to make it aware of the parameter names. It's so fussy about the parameter type and length, then it goes and doesn't check the name. Go figure (or not).I've been 90% done converting all my queries to ADO for months now. I don't know when I'll get a chance to finish the job. I think it's a little more readable. Maybe one day I'll use a Public user role from the web.Nice technique to avoid the last comma problem.Sam |
|
|
|
rharmon
Starting Member
41 Posts |
Posted - 2003-01-25 : 15:10:05
|
| If you're truely lazy and converting all your sproc calls to ado command objects, download sscodegen from Alpine Consultants (shareware try download.com) If you don't like that one, there are plenty of others available on the net.Rob |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-25 : 15:36:40
|
| Actually, though I can be very lazy, it's not lazyness that holds me back.I pass the entire SQL command string today to a procedure to send email if the user requests. I'll need to change all that so it passes the parameters instead.Interesting tool though..Argh.Sam |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-01-27 : 13:02:47
|
| Sam,The behavior of the ADO command object changed substantially with MDAC 2.6. I don't recommend using command objects with any MDAC prior to 2.6 because their functionality is very limited and idiosyncratic. In particular your ability to call sp's with optional parameters is quite limited, and parameter order matters because the parameter values are passed to SQL Server inline rather than with the @variable1=value, @variable2=value, etc. format.Jonathan{0} |
|
|
|
|
|
|
|
|
Can SQL Command be retrieved from ADO