Strange Behaviour in ADO, any ideas why this happens?

[malleyo]

I have a program that gets data from one table using a Stored Procedure (say sp_StoredProc1) which is working fine. I substituted a different Stored procedure (say sp_StoredProc2) in place of sp_StoredProc1. I got Error: Line 1: Incorrect syntax near 'N'. The Stored Procedures are exactly the same with the exception that they Select from different tables (tables are identitical, just different data). I ran both Queries through QueryAnalyzer, so I know that they both work and return the correct Data. The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!

I was wondeing if anyone has come across this before? I have already fixed it by removing the parentheses, I'm just interested in finding out why one works and the other doesn't.

Here's a sample of my code (I use ADO 2.5):

strQuery = "sp_StoredProc1('N')"
            
Set rsData = Nothing

If rsData Is Nothing Then
     Set rsData = cnConnection.Execute(strQuery, 0, adAsyncExecute)
Else
     rsData.Requery
End If

If rsData.State <> adStateExecuting Then  '<-- Breaks here when I replace sp_StoredProc1 with sp_StoredProc2
     'Code Here
End If

[M Owen]

I have a program that gets data from one table using a Stored Procedure (say sp_StoredProc1) which is working fine. I substituted a different Stored procedure (say sp_StoredProc2) in place of sp_StoredProc1. I got Error: Line 1: Incorrect syntax near 'N'. The Stored Procedures are exactly the same with the exception that they Select from different tables (tables are identitical, just different data). I ran both Queries through QueryAnalyzer, so I know that they both work and return the correct Data. The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!

I was wondeing if anyone has come across this before? I have already fixed it by removing the parentheses, I'm just interested in finding out why one works and the other doesn't.

Here's a sample of my code (I use ADO 2.5):

strQuery = "sp_StoredProc1('N')"
            
Set rsData = Nothing

If rsData Is Nothing Then
     Set rsData = cnConnection.Execute(strQuery, 0, adAsyncExecute)
Else
     rsData.Requery
End If

If rsData.State <> adStateExecuting Then  '<-- Breaks here when I replace sp_StoredProc1 with sp_StoredProc2
     'Code Here
End If

If you think about this: "The funny part is that if I remove the parantheses (like sp_StoredProc2 'N'), then the second Query works fine!" the answer should be apparent - Do you have any kind of return value on 1st procedure? The procedure working like that after making the change you did indicates there is not return value ...

[malleyo]

There is no Return Value. Both are Select Statements that return 1000 rows (or less) in a recordset.

[Cimperiali]

you might try:

Dim rsData as adodb.recordset
set rsData=new adodb.recordset

Dim cmd as adodb.command
Set cmd = new ADODB.Command

cmd.CommandText = "sp_StoredProc1"

cmd.Parameters.Add cmd.CreateParameter("@ParameterName", adVarchar, adParamInput, 1, "N")

cmd.activeconnection=YourConnectionObject
rsData.Open cmd

or, if you do not want the command object:

Set rsData = Connection.Execute("EXEC sp_StoredProc1 @TheParmName ='N'")

[malleyo]

Cimperiali: Thanks for your suggestion. Your first example is how I normally write my ADO stuff. The code in my original post was written by someone else. All I had to do was remove the parenteses and it worked fine. I was just curious if anyone knew why it broke with one Stored Proc and not the other.

Thanks to everyone who tried to help!