Problem with Stored Procedure
-
Hi folks! I'm fairly new to SQL Server 2K, but this is my first stab at a stored procedure, and hope someone can help point me in the right direction!
Stored Proc:
CREATE PROCEDURE [dbo].[testsp]
( @cid int,
@datechg datetime )
AS
update SomeTable
set dteDateChanged = @datechg
WHERE keyReqID = @cid
GO
[in SomeTable, dteDateChanged is datetime, keyReqID is int]
In my .ASP (NOT a .net program):
set cn=Server.CreateObject("ADODB.Connection")
cn.Open("DSN=myODBC;uid=myUID;pwd=myPW")
myDate="08/07/2004" (in prod will be from a previously selected recset or form input)
mycid=13 (ditto)
Set cmdtest= Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
cmdtest.ActiveConnection = cn
cmdtest.CommandType = adCmdStoredProc
cmdtest.CommandText = "testsp" 'my stored proc
cmdtest.CommandTimeout = 3600
cmdtest.Parameters.Append cmdtest.CreateParameter("datechg", adDBTimeStamp, adParamInput, mydate)
cmdtest.Parameters.Append cmdtest.CreateParameter("cid", adInteger, adParamInput, mycid)
Set rs = cmdtest.Execute()
When I run this in a small test prog, I get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'testsp' expects parameter '@cid', which was not supplied.
Obviously I have my syntax off somehow, 'cause it WAS supplied, but I'm just not sure how to fix it
Can anyone point me in the right direction?
Elaine Pack, Web Analyst
Charlottesville, VA
email ATAT packwebs net (you know the drill) -
Followup: it seems that if I reverse the positions of the 2 params in the sp itself (following CREATE PROCEDURE), it consistently doesn't like the FIRST one, whichever one it is. Yet it checks out syntactically. Are the passed in params (the .append things) positional to the sp? (Didn't work either way tho)
BTW, all I'm trying to accomplish here is pass in values to the sp so that the SQL statement will execute. Nothing fancy beyond that! If there's an easier way, I'm all ears
The programs work fine without an sp being involved, as does a test prog with 2 params that is only a select, but we are trying to make the update progs more efficient down the line and allow for slower connections to not timeout.elaine
Elaine Pack, Web Analyst
Charlottesville, VA
email ATAT packwebs net (you know the drill) -
Hummm.
First port of call is your procedure syntax (not part of the problem) - I assume you've run this proc and it works via Query Analyser? Just don't see where the ( ) s around the input paras (@cid etc) are needed.
Anyhow, the main thing with calling SPs from asp, you need to quote the input parameters with ' ', wrapped around whatever else you need.
For instance,
testsp ' "&[variable for @cid ] &" ' , ' " & [variable for @datechg] & " '
would be a good string to send through the ADO - no spaces between the ' and " though, that's just for clarity. And no [ s - just for clarity again.
The error is saying that when parsed, the string looks like
testsp [variable] , [variable]
which don't work!
On a parallel tack, you don't really need most of the ADODB stuff in there, unless you've got some complex cursor at work. I'm not going to re-invent the wheel, so take a look at http://www.aspfaq.com/ for some good pointers.
-
I have been fiddling with it based on your suggestions (thanks!) and it's now actually working. This is what I did:
Didn't change the sp (see orig msg) except that the () were in an example I found, but did remove them and yep, still worked fine.
In the .asp, however, now simply have this:
mycid=13 [will be a passed-in var]
tempdate="02/23/2003 " &"01:02:03" [ditto]
mydate=cdate(tempdate)
Set rs = Server.CreateObject("ADODB.Recordset")
mysql="testsp '" &mycid &"' , '" & mydate & "'"
set rs = cn.Execute(mysql)
Removed all of the ADO code relating to creating a command object, and I'm actually a LOT more comfortable with this since it's now virtually the same as I usually code my .asp progs. However I guess my question now is whether the stored proc will indeed still do what I need it to do because it is now (hopefully!) precompiled.
This whole exercise started because we have suddenly seen that on the first (and frequently only) access of the day to this very low usage table (to insert/update a rec) it seems to behave as if the db is not open, and takes SO long to execute the statement (about 48 secs) that it times out on the .asp page, and the record does not get created/updated. It takes that long in the SQL QA the first time, too, btw, then subsequent repeats of the statement work immediately. This never happened before. Note the very long timeout parameter in the original command object code--this is why.
Our system engineer insists he did nothing except install Portal Server on that server (unrelated to this particular exercise except that it's SQL server based of course). The program that updates MY db has been running uneventfully for a long time, but maybe Portal has slowed things down who knows? I have been trying to research all of this, and the concensus opinion seems to be that by having the update/insert logic in a stored proc, this will speed it up just enough that I may not get that timeout the first run thru. Plus too, having the code in an sp allows for reusability, which is always a good thing.
I'm so new to using SQL Server and stored procs that I feel like a babe in the woods even tho I'm a long time programmer
There's a lot of SQL stuff on the web but not so much in terms of concrete examples of using it in a real situation, so I'm struggling I very much appreciate your assistance!
Elaine Pack, Web Analyst
Charlottesville, VA
email ATAT packwebs net (you know the drill) -
Glad I could help - have been here and there before and learned a fair bit on Sps. The recordset you get back wil be the same as you expect, assuming the passed variables are handled properly. As you 've spotted, sps are faster, as they're (re)compiled once, rather than passing the whole proc as a query etc via ADO.
SQL is the kind of thing you really learn as you go, assuming you've got the basics together.
try running a profile trace on your sql server - if you're getting some odd delays on the rs coming back, you've either missing a fairly important index on the table(s) or the data changes so much day-to-day that you need a re-index to optimise the date. Doing a profile and then an index tune will find that bit out very quickly - I've seen a 65% performance improvement on one table from this!
best of luck...
-
Dang. It's not helping my timeout problem. I'm using the same tiny test sp that updates only 1 field, but it still times out on me. I added this to the .asp program, Server.ScriptTimeout = 180 in hopes it would supercede the server default of 90, I don't mind waiting and I can put a disclaimer on the page if necessary, but it's STILL getting the Timeout Expired error. 3 minutes is a LONG time--it only takes about 48 secs when I run it thru the SQA (the first time in the AM). Very weird. I haven't run the query thru the SQA today, because I don't want to "pop" it since it won't reset [showing the delay] until tomorrow.
<sigh>
I did pass on your suggestion about a profile trace to our systems engineer, but who knows if he will act on it, unfortunately.
elaine
Elaine Pack, Web Analyst
Charlottesville, VA
email ATAT packwebs net (you know the drill) -
<quote>I did pass on your suggestion about a profile trace to our systems engineer, but who knows if he will act on it, unfortunately.</quote>
That's not good.
You #need# to know where the bottleneck is! Try proving that everything else is ok - network speed, web server response etc.Certainly you should be getting a response in under a couple of seconds, so you could check your comms library - I don't know whether you're using a system dsn, some .NET library or something else, but there should be some diagnostic bit there to prove that the initial handshake is fast....
-
Believe me, I understand, but all I can do is ask. That said, if they are not responsive (pun fully intended!), I do have avenues to pursue it, and WILL.
Again, tho, this is NEW behavior, it did NOT happen prior to them loading Portal (HUGE red flag, you'd think, eh?) and it's ONLY the first update type of action of the day, doesn't happen on a simple select, but then, I'm not selecting large recordsets, either. Once you get past that, mainly by going into the QA and running any kind of an update/insert action, since the progs time out, it's totally normal, immediate response in the program. Just as it has been for many months now, pre-Portal.
I didn't even think that this kind of behavior (the initial lag) happened with SQL server (shows you how little I know
This happens on our mainframe programs but that's not surprising, because those systems (at least ours) don't open all data files as part of starting up. Some do, but not some of the less used ones, so the first time you encounter those, read OR write, it's SL-O-O-OW, just like my problem, but after that, whoosh!Oh well, more arm twisting to do, I guess!
elaine
Elaine Pack, Web Analyst
Charlottesville, VA
email ATAT packwebs net (you know the drill)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply