ADO, SQL Server and UniqueIdentifier Column 
Author Message
 ADO, SQL Server and UniqueIdentifier Column

Below is my query that is in a Store Procedure

SELECT ObjectId,
        ManifestId,
           ISNULL(CAST(ParentId As VARCHAR(39)),'') As ParentId,
        ISNULL(CAST(LinkId As VARCHAR(39)),'') As LinkId,
     FROM manifest_details

NOTE: ManifestId, ParentId and LinkId are all UniqueIdentifier Fields

If I run this from SQL Query Analyzer it runs fine.  It returns the values
expected.

If I run it from an application such as VB or C++ I get this error

Error #-2147217900: Syntax error converting from a character string to
uniqueidentifier.

All I am doing at this time is a Query.  One more item -- this error only
occurs when using a Command Object.

What I don't understand is
    1. if it works and is legal in SQL Server, then why is it not legal in
ADO
    2. SQL Query Analyzer seems to use OLE DB and it works in that
application what do I have to do to make it work in my application!!!!
    3. The query works if I send it as SQL query and it does not work if I
put in a Stored Proc  So does that mean the Connection Object Team had a
clue
and the Command Object Team did not??
    4. If I am doing a CAST to VARCHAR, why does in not return a VARCHAR
thus eliminating the error
    5. What is my alternative?  And no, I don't what to do the check at the
client because this information may change over time



Mon, 17 May 2004 12:15:23 GMT  
 ADO, SQL Server and UniqueIdentifier Column
Just a guess, but have you tried:

           CAST(ISNULL(ParentId,'') As VARCHAR(39) As ParentId,

Instead?

Quote:

> Below is my query that is in a Store Procedure

> SELECT ObjectId,
>         ManifestId,
>            ISNULL(CAST(ParentId As VARCHAR(39)),'') As ParentId,
>         ISNULL(CAST(LinkId As VARCHAR(39)),'') As LinkId,
>      FROM manifest_details

> NOTE: ManifestId, ParentId and LinkId are all UniqueIdentifier Fields

> If I run this from SQL Query Analyzer it runs fine.  It returns the values
> expected.

> If I run it from an application such as VB or C++ I get this error

> Error #-2147217900: Syntax error converting from a character string to
> uniqueidentifier.

> All I am doing at this time is a Query.  One more item -- this error only
> occurs when using a Command Object.

> What I don't understand is
>     1. if it works and is legal in SQL Server, then why is it not legal in
> ADO
>     2. SQL Query Analyzer seems to use OLE DB and it works in that
> application what do I have to do to make it work in my application!!!!
>     3. The query works if I send it as SQL query and it does not work if I
> put in a Stored Proc  So does that mean the Connection Object Team had a
> clue
> and the Command Object Team did not??
>     4. If I am doing a CAST to VARCHAR, why does in not return a VARCHAR
> thus eliminating the error
>     5. What is my alternative?  And no, I don't what to do the check at the
> client because this information may change over time



Tue, 18 May 2004 06:35:07 GMT  
 ADO, SQL Server and UniqueIdentifier Column
Yes, I tried what you suggested.  I get the same error.

Enzo


Quote:
> Just a guess, but have you tried:

>            CAST(ISNULL(ParentId,'') As VARCHAR(39) As ParentId,

> Instead?




Quote:
> > Below is my query that is in a Store Procedure

> > SELECT ObjectId,
> >         ManifestId,
> >            ISNULL(CAST(ParentId As VARCHAR(39)),'') As ParentId,
> >         ISNULL(CAST(LinkId As VARCHAR(39)),'') As LinkId,
> >      FROM manifest_details

> > NOTE: ManifestId, ParentId and LinkId are all UniqueIdentifier Fields

> > If I run this from SQL Query Analyzer it runs fine.  It returns the
values
> > expected.

> > If I run it from an application such as VB or C++ I get this error

> > Error #-2147217900: Syntax error converting from a character string to
> > uniqueidentifier.

> > All I am doing at this time is a Query.  One more item -- this error
only
> > occurs when using a Command Object.

> > What I don't understand is
> >     1. if it works and is legal in SQL Server, then why is it not legal
in
> > ADO
> >     2. SQL Query Analyzer seems to use OLE DB and it works in that
> > application what do I have to do to make it work in my application!!!!
> >     3. The query works if I send it as SQL query and it does not work if
I
> > put in a Stored Proc  So does that mean the Connection Object Team had a
> > clue
> > and the Command Object Team did not??
> >     4. If I am doing a CAST to VARCHAR, why does in not return a VARCHAR
> > thus eliminating the error
> >     5. What is my alternative?  And no, I don't what to do the check at
the
> > client because this information may change over time



Tue, 18 May 2004 11:01:29 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Server 2000 uniqueidentifier, RowGuid

2. SQL Server CE: UNIQUEIDENTIFIER

3. VB 6.0 / ADO 2.0 / SQL 7.0 Beta 3 (UniqueIdentifier)

4. ADO 2.0, SQL Server 7.0, ServerSide cursors, IDENTITY columns after rs.AddNew

5. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

6. adGUID to UniqueIdentifier mapping in ADO and VB

7. t-SQL & uniqueidentifier problem

8. t-SQL & uniqueidentifier problem

9. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

10. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

11. sql-problem ADO - MS Sql Server

12. ADO, SQL Server, VB problem posted to the microsoft.public.data.ado group as well

 

 
Powered by phpBB® Forum Software