• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

AnsiNPW Extended Property Not Working

I have a proc that was working fine in ODBC.  Now that I've switched to OLE DB it's throwing up an error.  If I put "SET ANSI_WARNINGS OFF" inside the proc it works.  If I take out that SET statement and instead put ANSINPW=No in the connection string it doesn't work.  I had a similar problem with some other procs but they were fixed with ANSINPW=No.  Why is this one the exception?
0
johns00
Asked:
johns00
  • 6
  • 5
  • 2
1 Solution
 
ispalenyCommented:
Use SQL Profiler to capture SQL commands ODBC and OLEDB drivers use.
0
 
johns00Author Commented:
That's a bit vague.  What am I looking for?
0
 
ispalenyCommented:
I tried several variants of ANSINPW option. It has no effect. But on MSDN it is documented for ODBC driver. I searched executables of OLEDB and ODBC both. It is included in ODBC driver SQLSRV32.dll only. So I think it is an ignored option in OLEDB connection.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
johns00Author Commented:
So then how can I programmatically set a global command to ignore ANSI warnings while using OLE DB?
0
 
johns00Author Commented:
I'm sorry but I don't find that helpful.  If you have a solution please present it.
0
 
ispalenyCommented:
In OLEDB world there in much responsibility on an application programmer. Why should check all registry, DSN and driver version? It creates a simple connection, then you can set by Execute method of Connection object what you want.
0
 
johns00Author Commented:
I don't execute from the Connection object.  I open a global Connection and then I reuse it throughout my ASP page until it is written to the client.

I am wondering why we are making such a long thread.  Am I not giving you the information you need to provide the answer I need?  I want to be able to specify for that connectiona single time, regardless of how I use it, that warnings should be off.  I'm doing 20 other things and I posted this here specifically so I wouldn't have to bother with figuring it out while I do other things.  Do you know of a way?  If yes, tell me.  If you need more information then tell me that.
0
 
ispalenyCommented:
And when you open the global Connection,lets say conn, you are not able to run conn.Execute "SET ANSI_WARNINGS OFF" at the same place and then reuse this modified conn on other ASP pages? Otherwise you should ask Microsoft for creating a special MSSQL OLEDB provider especially for you. Have a nice day, I am leaving my office.
0
 
Anthony PerkinsCommented:
>>I'm doing 20 other things and I posted this here specifically so I wouldn't have to bother with figuring it out while I do other things.  Do you know of a way?  If yes, tell me.  If you need more information then tell me that.<<
You really need to chill out.  I am sorry you have had to go to all the trouble of posting here so you "wouldn't have to bother with figuring it out".  I realize you are fairly new here, so I am not sure about you, but we volunteer our time here.

Now to your question:  If you did not know it already, there are subtle differences between the ODBC and OLDB drivers that making changing midstream hazardous to say the least.  I would start by posting the stored procedure and perhaps we can see what is going on.
0
 
johns00Author Commented:
I'm not new here, this account is just the most recent one that my employer pays for.  This site is pretty simple, I post a problem, people ask for clarifications and more examples if needed, then they either provide an answer, conclude an answer isn't possible under the circumstances, or bow out.  Although I appreciate all volunteers, you're not doing any of those things and it ends up a waste of both our times.

I open a connection at the start of my ASP page lifecycle.  I have various functions that return recordsets based on that connection.  This is not an unusual model.  I'm not going to change every proc to include "SET ANSI_WARNINGS OFF" because that will force a recompile at every execution and I'm not going to change all the functions that return recordsets.  I need to be able to turn the warnings off when I open the connection.  I thought I could do it in the connection string but that doesn't seem to be the case now.

Make any query that will generate a warning.  Try to open a recordset with that resultset using OLE DB and you will get an error that the object is closed.

The question is...how do I turn off ANSI_WARNINGS at the connection level prior to any execution?
0
 
Anthony PerkinsCommented:
>>Although I appreciate all volunteers, you're not doing any of those things and it ends up a waste of both our times.<<
Ok. Good luck.  I am out of here.
0
 
johns00Author Commented:
I gave you the points because I wanted to close the question.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now