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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored procedures with parameters

Author  Topic 

kensai
Posting Yak Master

172 Posts
Posted - 2002-01-13 : 18:57:29
I'm using Recordset for dealing stored procedures but when it came to the using parameters it didn't went good. The code I'm trying is this:

objRS.Open "proc_temp 0,25",sysDB,adOpenStatic,adLockReadOnly,adCmdStoredProc

The error:

Microsoft OLE DB Provider for SQL Server (0x80040E14) Syntax error or access violation

The proc runs quite well when I run it using Query Analyzer (same: "exec proc_temp 0,25").

I searched the forum but only examples I could find was with Command object. I don't have any Command object knowledge and I will be more happy if I can do it with Recordset. Do you know a way to do it?

rrb
SQLTeam Poet Laureate

1479 Posts
Posted - 2002-01-13 : 19:18:56
kensai

Sorry - the error code doesn't help me. In any case, I'm pretty sure you won't be able to do it that way. You should create Parameter objects and append them to your command object.

I have some example code for you. It's pretty untidy - I've just hacked out a few lines from various places - but it shows the general technique.

(this is from VBScript - ASP - and hence the type definitions are documented out)

Hope it helps...:

Dim cnDB 'as ADODB.Connection
Dim cmd 'as ADODB.Command
Dim InParam 'as ADODB.Parameter
Dim OutParam 'as ADODB.Parameter
Dim strReturn 'as string

strProv = "bla bla bla"

Set cnDB = Server.CreateObject("ADODB.Connection")
cnDB.Open strProv 'provider string -

Set cmd = Server.CreateObject ("ADODB.Command")
cmd.ActiveConnection = cnDB

cmd.CommandText = "sp_Procedure"
cmd.CommandType = adCmdStoredProc

Set InParam = cmd.CreateParameter("sVal", adVarChar, adParamInput, 255, varMyValue)
cmd.Parameters.Append InParam

Set OutParam = cmd.CreateParameter("sReturn", adVarChar, adParamOutput, 255 ,"")
cmd.Parameters.Append OutParam

' execute
cmd.Execute

strReturn = Trim(cmd.Parameters ("sReturn").Value)

cnDb.Close

Set cmd = Nothing
Set cnDB = nothing


---Sorry I should add a few more comments:
* I've assumed you have the file adovbs.asp (includes all the constant definitions for stuff like "adCmdStoredProc" and "adVarChar".) Let me know if you don't and I can mail you one.
* Note also that I've defined both Params as varchar(255) for (simplicity).
* Also note that varMyValue is a variable from elsewhere in you code - ie you can either put in literals or variables.

Cheers

Edited by - rrb on 01/13/2002 19:23:40
Go to Top of Page

Lavos
Posting Yak Master

200 Posts
Posted - 2002-01-13 : 20:02:24
I don't have a testing environment to check my theory, but it looks like the problem is the adCmdStoredProc arguement. Try switching it to adCmdText. (Ahh, if only I had my network at home up and running.)

You also might have to alter the command text to match the ODBC calling conventions, or at least put EXEC before the procedure name. (I say might because it should interpret it as a stored procedure since it's the first item in a batch, but I consider it good practice to make it explicit.)


Oh yeah, and the command object would probably be more appropriate depending on your app. do some reading on it. I haven't gotten that far into it (I'm up to Replication.) but thumbing through the Books Online shows it has a section on ADO.

(In other words, consider following the advice of the previous poster rather than mine. ADO is pretty cool, but you should really try to at least understand the other main objects in it to get all that it has to offer.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"


Edited by - Lavos on 01/13/2002 20:05:40
Go to Top of Page

kensai
Posting Yak Master

172 Posts
Posted - 2002-01-13 : 20:32:18
Lavos, I tried the following variations but no luck:

objRS.Open "exec proc_temp 0,25",sysDB,adOpenStatic,adLockReadOnly,adCmdStoredProc

objRS.Open "exec proc_temp 0,25",sysDB,adOpenStatic,adLockReadOnly,adCmdText

I actually red must of the ADO SDK. I just passed unnecessary things for me, like RDS, ADOX and -unfortunately- Command. I don't like the idea to have to learn for a simple connection. All I wish is rrb is wrong :)
Go to Top of Page

Lavos
Posting Yak Master

200 Posts
Posted - 2002-01-13 : 20:53:26
Well, again I don't have a test environment to do this in right now, but I know you can call stored procedures with recorset's open method. It's just another sql statement after all.

Try this, and if it doesn't work I'll have to break down and actually try it myself, I just can't guarantee when.

objRs.Open "{ CALL dbo.proc_temp( 0, 25) }", sysDB, adOpenStatic, adLockReadOnly, adCmdText

This is the ODBC syntax IIRC, it should work though I'm realling surprised that the "exec proc_Temp 0, 25"/adCmdText didn't work.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-01-13 : 20:59:21
Hi

Here is some info that might help

http://www.devguru.com/Technologies/ado/quickref/connection_execute.html

Why not just use the connection object i.e.


Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")

Set objRS = objConn.Execute("Exec Proc_Temp 0,25")

etc (don't forget to close your objects when done)


I recommend the command object though for stored procs. Search the forums for "command object" and you will find some code examples.


Damian

Edited by - merkin on 01/13/2002 21:00:00
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts
Posted - 2002-01-13 : 21:19:01
Sorry kensai

didn't mean to waste your time - also I didn't notice the first time you wanted to return a recordset.

My apologies - for what you want - I concur with Merkin.

HOwever, just for completeness, replace the line
cmd.Execute

With
Dim rs 'as ADODB.Recordset
Set Rs = cmd.Execute



Edited by - rrb on 01/13/2002 21:31:09
Go to Top of Page

Lavos
Posting Yak Master

200 Posts
Posted - 2002-01-13 : 22:22:56
Okay, I was so intrigued by being wrong that I had to go and run through everything.

rrb and Merkin are right, the command object is usually better. Unless of course you are just executing it one time and making ad hoc sql statements on the fly, in which case Recordset.Open or Connection.Execute would be your better bets. (Unless of course, there are OUTPUT parameters, in which case you are back to using the command object.)

In any case, back to the problem at hand. I ran your original command through and got the same error. Good. I changed the sql string to "exec proc_temp 0,25" and adCmdStoredProc to adCmdText, and it worked, which is contrary to what you've already stated.

That doesn't make sense to me at all. What was the error returned when you changed it to use adCmdText? Was it still the Syntax/Access error, or something new?

You might try qualifying proc_temp with the 2 part name, though AFAIK it shouldn't require you to use dbo.proc_temp.


----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"
Go to Top of Page

kensai
Posting Yak Master

172 Posts
Posted - 2002-01-14 : 17:46:06
rrb,
no, no problem. I appreciate the help.

Merkin,
Thanks for the Command sample. I actually know the Execute but I forgot to say that I'm looking in a way to do it with Recordset. My mistake, sorry.

Lavos,
Thanks a lot for trying the code, you saved me. I tried it again after your post and "exec proc_temp 0,25"...,adCmdText worked! I don't know what I'd wrong before, maybe a typo. Now it works, thanks again.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site