ADO Connection - How to set ANSI_NULLS OFF through the connection 
Author Message
 ADO Connection - How to set ANSI_NULLS OFF through the connection

If I run the following stored procedure in Query Analyzer I receive the
correct results but if I run it in VB using ADO 2.6 I receive a NULL value
for the Name column.  I know it has something to do with ANSI_NULLS being ON
when the OLE DB Provider is making the connection so I think I just need to
know how to shut it off.  Thank you in advance for your help.

LeeAnn



as
SET NOCOUNT ON




  Create Table #SearchFL (PersonID int)

    Insert #SearchFL
    Select  PersonID
    From    Person


    UNION
    Select  PersonID
    From    Person


   Select  P.PersonID, Name =
  CASE
   When  Deceased = 'Y' AND (AliasLastName is not Null or  AliasFirstName is
not null) then RTRIM(LastName) + RTRIM(' ' + SUFFIX) + ', ' +
RTRIM(FirstName) + ' ' + RTRIM(MiddleName) + ' ' + ' (' + AliasLastName + ',
' + AliasFirstName + ')' + ' (Deceased)'
   When (Deceased = 'N' or Deceased is Null) AND (AliasLastName is not Null
or  AliasFirstName is not null) then RTRIM(LastName) + RTRIM(' ' + SUFFIX) +
', ' + RTRIM(FirstName) +  ' ' + RTRIM(MiddleName) + ' ' + ' (' +
AliasLastName + ', ' + AliasFirstName + ')'
   When Deceased = 'Y' AND (AliasLastName is Null or  AliasFirstName is
null) then RTRIM(LastName) + RTRIM(' ' + SUFFIX) + ', ' + RTRIM(FirstName) +
' ' + RTRIM(MiddleName) + ' (Deceased)'
   ELSE RTRIM(LastName) + RTRIM(' ' + SUFFIX) + ', ' + RTRIM(FirstName) + '
' + RTRIM(MiddleName)
  End,
    'Gender' = P.Gender,
           'DOB' = Convert(Varchar,DOB,110),
    'CityState' = City + ', ' + State,
    'HistoryNumber' = H.HistoryNumber,
    SearchAgency =
  CASE
   When bAgency = 'N' then 0
   Else 1
  End,
  Address1,
  Address2
   From    Person P, #SearchFL TP, ClientHistory H
   Where   P.PersonID = TP.PersonID and P.PersonID *= H.PersonID
   Order by LastName, FirstName, MiddleName
GO



Sun, 15 Feb 2004 04:04:35 GMT  
 ADO Connection - How to set ANSI_NULLS OFF through the connection
LeeAnn,
                   There is no way to set this object on the connection.
You can set it after connection is open as such:

cn.open "SomeConnection"
cn.execute "SET ANSI_NULLS OFF"

I hope the information helps.

Thanks,
Terrell A.
Microsoft Developer Support



Mon, 16 Feb 2004 02:18:29 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Detecting live ADO recordsets hanging off connection objects

2. Connection Problem - ado failover connection

3. ADO Connection State Does Not Reflect Actual Connection State

4. Connection Errors via ADO Connection Object

5. Release ADO connection from a connection pool

6. ADO connection passed to a DLL fails to maintain valid connection

7. ADO Connection Speed Connection

8. ADO Connection Object Close and set it equal to nothing

9. How to set the connection for ADO data control

10. Setting up connection in ADO using MSDAORA

11. How to set ADO connections on user machines

12. Setting up an ADO connection during runtime

 

 
Powered by phpBB® Forum Software