QueryDef with GUID parameters - ODBC Call Failed Errors

[VBSponge]

Hi all. Need help here as I cant see whats wrong...

I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the life of
me:

"Error 3146: ODBC Call Failed"

Now, the stored proc uses @RETURN_VALUE to pass back an integer result
to the calling process (in this case Access), so I am using
ReturnsRecords = true and qdf.openrecordset to run it.

When it runs from Access i get the errors, when i run it from SQL
Query analyaer it runs fine. I have another querydef in another db
that does the same thing and works fine - the only difference is the
OK one does not use GUIDs as parameters. I was wondering (maybe due to
MS Access' dislike of GUIDs) if this could be the problem causing the
ODBC call failed errors, or if there is something else I have missed
..... here is my code:

Dim strItemGUID As String
dim strSourceGUID as string
Dim Qdf As QueryDef
Dim rst As DAO.Recordset
Set Qdf = CurrentDb.CreateQueryDef("")
Qdf.Connect = CurrentDb.TableDefs(0).Connect 'same as linked tables in
db
Qdf.ReturnsRecords = True
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" & strItemGUID & "', '" &
strSourceGUID & "'"
Set rst = Qdf.OpenRecordset()

.... in the last line also tried assigning return to an integer instead
of a recordset, but same errors.

(note that also, - Access hates GUIDs, so you have to refer to them in
their string context as Access cant deal with Byte arrays or something
like that).

In SQL Server, varchar/char > UniqueIdentifier conversions are
implicit, but I wonder if this does not happen when using a QDF in
Access and feeding strings in for GUIDs??

I need some clarification, help, and ideas on how to get this working.
I DO NOT want to use string params and convert them within the stored
proc (if i can avoid it).
ideas? suggestions? help?

Many thanks in advance
Karl

[Tom van Stiphout]

On 29 Sep 2004 15:30:29 -0700, ka*****@hotmail.com (VBSponge) wrote:

Try this:
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" &
StringFromGUID(strItemGUID) & "', '" &
StringFromGUID(strSourceGUID) & "'"

-Tom.

Hi all. Need help here as I cant see whats wrong...

I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the life of
me:

"Error 3146: ODBC Call Failed"

Now, the stored proc uses @RETURN_VALUE to pass back an integer result
to the calling process (in this case Access), so I am using
ReturnsRecords = true and qdf.openrecordset to run it.

When it runs from Access i get the errors, when i run it from SQL
Query analyaer it runs fine. I have another querydef in another db
that does the same thing and works fine - the only difference is the
OK one does not use GUIDs as parameters. I was wondering (maybe due to
MS Access' dislike of GUIDs) if this could be the problem causing the
ODBC call failed errors, or if there is something else I have missed
.... here is my code:

Dim strItemGUID As String
dim strSourceGUID as string
Dim Qdf As QueryDef
Dim rst As DAO.Recordset
Set Qdf = CurrentDb.CreateQueryDef("")
Qdf.Connect = CurrentDb.TableDefs(0).Connect 'same as linked tables in
db
Qdf.ReturnsRecords = True
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" & strItemGUID & "', '" &
strSourceGUID & "'"
Set rst = Qdf.OpenRecordset()

... in the last line also tried assigning return to an integer instead
of a recordset, but same errors.

(note that also, - Access hates GUIDs, so you have to refer to them in
their string context as Access cant deal with Byte arrays or something
like that).

In SQL Server, varchar/char > UniqueIdentifier conversions are
implicit, but I wonder if this does not happen when using a QDF in
Access and feeding strings in for GUIDs??

I need some clarification, help, and ideas on how to get this working.
I DO NOT want to use string params and convert them within the stored
proc (if i can avoid it).
ideas? suggestions? help?

Many thanks in advance
Karl