Native Client ignores ANSI_NULLS setting in database

Native Client ignores ANSI_NULLS setting in database

• SOAC

  I'm not sure i understand your reply.

  The SQL Native Client ODBC driver absolutely, positively, 100% supports ODBC and in the app I built using your repro code the server name was coming from an ODBC DSN and ANSI NULLs was turned off, which is what I thought you wanted. If I've misunderstood what you wanted to achieve please explain.to me again and I'll have another go.

  
  
  

• Martin00

  Thanks for trying, but that didn't solve the problem. I wish you would've waited for me to actually try your advice before marking this issue "Solved". In fact, I don't know why you marked it solved anyway. Only I would know if my problem is solved. This is the second time someone has marked one of my posts "Solved" when the advice offered hasn't solved anything.

  Anyone else have any ideas

  
  

• cookieCutter

  The following MSDN web site gives all the connection string options for OLEDB and ODBC. Hope that helps:

   

  Using Connection String Keywords with SQL Native Client 

  http://msdn2.microsoft.com/en-us/library/ms130822.aspx#

  ANSI Nulls option:

  AnsiNPW

  When "yes", the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation. When "no", ANSI defined behaviors are not exposed. For more information about ANSI NPW behaviors, see Effects of SQL-92 Options.

   Riyaz Habibbhai - MSFT

   

  
  
  

• Awais786

  As mentioned in my last post, the ODBC connection string only works when you specify the name of the server. I think that kind of defeats one of the main selling points to using ODBC (being able to specify the server name once in the DSN instead of in all connection strings), but whatever. I'll resort to using OLE DB. The OLE DB connection string, however, doesn't seem to allow me to specify whether to use ANSI_NULLS or not.

  For anyone out there who still wants to use ODBC, it seems that you'll have to specify the name of the server in the connection string. To quickly repoint the connection from the production DB to your test/development DB, you can add an entry to the windows hosts file (C:\Windows\System32\Drivers\Etc\Hosts) on your development machine.

  
  

• Ben Amor Bassem

  Once again -- my post is marked "Answered" when my question hasn't been answered at all. In fact, I'm not convinced Zoya Bashirova even read my last post. If she had, she would've noticed that my repro wasn't calling a stored procedure. So thanks for the info, Zoya, but it doesn't help here.

  I guess I'm going to have to go back to Expert's Exchange -- where no one closes your post but you.

  On the other hand, Chris Lee has been very helpful.

  
  

• Naveenkm

  I guess the SQL Native Client just doesn't 100% support ODBC (since you have to specify a server name in the connection string). That's disappointing, but at least I know how to get it working now.

  Thanks for your patience and support, Chris. You have been very helpful throughout this process! Thank you!

  
  

• durnurd

  Thanks for the repro. With VB6 and ADO you need to use MSDASQL to connect to an ODBC DSN. This is the OLE DB to ODBC bridge. ADO always uses OLE DB, so you need to use the bridge provider to work with ODBC. In the connection properties dialog this shows up as "Microsoft OLE DB Provider for ODBC Drivers

  It's simplest to use the connection properties to build the connection string. Using the 'Use Data Source Name" option you get a connection string that looks something like this if you have a look with the debugger once the connection is open:

  Provider=MSDASQL.1;Persist Security Info=False;Data Source=2K5NW;Extended Properties="DSN=2K5NW;Trusted_Connection=Yes"

  Using the connection string builder you get something like this:

  DSN=2K5NW;Trusted_Connection=Yes;APP=Visual Basic;WSID=CHRLEE-D600;DATABASE=Northwind;AnsiNPW=No;

  Both methods work equally well.

  You could use the SQL Native Client OLE DB provide directly, and this would be a bit more efficient. In this case there isn't a connection string keyword, so you'd have to execute a TSQL 'SET' statement in the application once it had connected to the database.

  
  
  

• hrubesh

  Ah! You're using an ADO.Net managed provider! And it looks as though it's System.Data.Oledb and not System.Data.Odbc. How big is the app Could you either let me know the exact configuration or (possibly better) send the source code. It may not be picking up settings from the DSN and it's possible that the AnsiNPW in the connection string isn't getting through to SQL Native Client. First step is to know exactly what's going on and if possible to reproduce it on my machine.

  
  
  

• Zerrin

  Hi, Chris. Thanks for the response.

  I'm using DSN, and I have cleared the "Use ANSI nulls, paddings, and warnings" checkbox in the setup dialogs. The previous post about the "AnsiNPW=no" in the connection string probably should've worked, but it didn't. I debugged the application to check the connection string at run-time, and I discovered that the system had automatically moved the "AnsiNPW=no" into the "Extended Properties" section of the connection string. For example:

  
  Connection String in the Designer:
  Provider=SQLNCLI.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=MyDatabase;Data Source=MyServer;AnsiNPW=no
  
  Connection String at Runtime After the Connection Has Been Opened:
  Provider=SQLNCLI.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=MyDatabase;Data Source=MyServer;Extended Properties="AnsiNPW=no";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MyWorkstation;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False

  I'm connecting to SQL Server 2005, but the database is running in compatibility level 80 right now. Could this have anything to do with it

  Thanks again.

  
  

• Enkht

  Alright... after reading your post, I tested something that revealed the true nature of the problem -- I'm an idiot. I was using a non-ODBC connection string and expecting it to use the system DSN I had created. The name of the DSN was the same as the name of the server, which is why it connected. Of course, none of the settings in the DSN were being used because the connection wasn't being made via ODBC! So now I've changed my connection string, but I'm encountering another problem. When I try to connect, I keep getting the error message, "[Microsoft][SQL Native Client]Neither DSN nor SERVER keyword supplied" even though I'm supplying the DSN keyword. My new connection string is:

  Driver={SQL Native Client};DSN=MyDSN;PWD=xxxx;UID=xxxx;Database=MyDatabase

  If I go ahead and specify a value for the Server attribute, everything works. For example:

  Driver={SQL Native Client};DSN=MyDSN;PWD=xxxx;UID=xxxx;Database=MyDatabase;Server=MyServer

  However, after connecting, if I examine the connection string on the ADODB.Connection object, it reads as follows:

  Provider=MSDASQL.1;Password=xxxx;User ID=xxxx;Extended Properties="DRIVER=SQL Native Client;SERVER=MyServer;UID=xxxx;PWD=xxxx;APP=Visual Basic;WSID=MyWorkstation;DATABASE=MyDatabase;"

  What is going on here

  
  

• Maaloul

  ANSI_NULLS on is the default for ODBC, so you need to explicitly turn it off even when the database has it turned off. If you're using a DSN, then the DSN setup dialogs will let you turn it off, but again the default is on. Having a fixed default in the driver gives more predictable behavior for applications that don't make an explicit choice. Without this applications would see different default behavior from different databases.

  In what way did the previous answer not solve your problem With more to go on we can try a bit harder.

  
  
  

• Tomorrow Shi

  MSDASQL is the OLE DB to ODBC bridge, so you're getting to the ODBC driver via OLE DB, which is probably why you're connection string isn't working as expected. I'm a bit confused how this is happening. Could you send the code you're using to connect. What language and API are you using Is this a native or managed application If it's managed are you using System.Data.Oledb or System.Data.Odbc It should be possible to get the behavior you want once I understand better what your application is doing.
  
  

• JeffK_

  Steps to reproduce the issue:

  1. Create a new system DSN named MyDSN for the SQL Server 2005 you are trying to connect to. Be sure to use the SQL Native Client and turn off ANSI NULLS.
  2. Launch Visual Studio 6 and create a new VB6 project. This will be a purely VB6 application -- we will not be adding any managed components to this VB6 project. .NET is not a player in this scenario.
  3. Add a new DataEnvironment named deData to the project.
  4. Add a new Connection to deData named cnMyDB. Use the following connection string: Driver={SQL Native Client};DSN=MyDSN;PWD=xxxx;UID=xxxx;Database=MyDatabase
  5. Create a form with a single button named btnTestDB. In the Click event of the button, write code to execute a query against the database using deData.cnMyDB. For example:
     Private Sub btnTestDB_Click()
     On Error GoTo DisplayError
     deData.cnMyDB.Open
     Dim rsCountOfUsers As New ADODB.Recordset
     Set rsCountOfUsers = deData.cnMyDB.Execute("SELECT COUNT(1) FROM Users")
     MsgBox "There are " & CLng(rsCountOfUsers(0)) & " users in the Users table."
     deData.cnMyDB.Close
     Exit Sub
     DisplayError:
     If deData.cnMyDB.State <> adStateClosed Then deData.cnMyDB.Close
     MsgBox "Error: " & Err.Description
     End Sub
  6. The code above should cause a "Server not found" error message to be displayed. If you add the Server=MyServer attribute to your connection string, the code should work.

  I would like to know why the creators of this driver require you to specify the name of the server in the connection string, even though the name of the server has already been specified in the DSN that you created. The link posted by Riyaz Habibbhai (http://msdn2.microsoft.com/en-us/library/ms130822.aspx) explains that the Server attribute on the connection string is required if the Driver attribute is specified and DriverCompletion is set to SQL_DRIVER_NOPROMPT (which is the default, I guess).

  Thanks for the continued interest in this issue.

  
  

• laja

  I guess I misunderstood your reply and/or confused it with the documentation. The documentation says that you can use ODBC with the native client by specifying the attribute DRIVER={SQL Native Client} in the connection string. This doesn't seem to work unless you also specify the Server=MyServer attribute in the connection string. Using your advice (omitting the DRIVER={SQL Native Client} and the Server=MyServer attributes, and using the Provider=MSDAQ attribute) will work. I was just confused about why you are required to specify the Server attribute if you use the DRIVER={SQL Native Client} attribute. I was under the impression that the Provider attribute was not a valid ODBC attribute.

  Thanks

  
  
• Native Client ignores ANSI_NULLS setting in database