SQL pass thru connection problem after Office 2010 install

[glamb]

I have been using SQL passthru to connect to my SQL Server tables for quite a while with no problem. I have a connection string encryped in a table and use it decrypted to get the connection which is stored in a variable.
Since installing Office 2010 that same connection string does not work always returning -1.
I am able to access SQL Server through SSMS no problem. I can retrieve SQL table data into Excel, no problem, however anything that I have recompiled with the SQL passthru code fails to connect. I've checked the foxuser table, nothing there interfering.
So, has anyone else had this problem and found a solution? I'm thinking some DLL or driver has been overwritten or a path changed to it, but the Office installation added thousands of files to my machine and haven't been able to pin point it.

 

[Olaf Doschke]

What do/did you use for encryption/decryption? The only thing I can imagine having an influence, when you install Office, which should be completely independant of both Foxpro and SQL Server, is an update of some cryptographic DLLs, cryptographic providers, that may come with Office.

Bye, Olaf.

 

[Olaf Doschke]

You would of course detect that quite obviously, if the decrypted connection string is unreadable and the error message should point that out, too. So it may be rather unlikely, but have you inspected via debugger, what connection string is used? Have you checked, what additional error description AERROR() gives you?

Using AERROR after any SQL- -Connect(),-Exec() function failing is always a good start.

Bye, Olaf.

 

[glamb]

The encryption of a function I wrote. I have stepped through the code and it errs on the SQLCONNECT statement. The message I receive is that it is not able to connect.

 

[Dan Freeman]

As asked previously, what does AERRROR() tell you after the failure?

What is your connect string?

 

[glamb]

There is no error, the connection handle is never created. It always stays -1.
LPARAMETERS lpconnstring

LOCAL lconn
set STEP on
lconn = -1
*!* -----------------------------------------------------------------------------------------------------------
*!* Connection string has been passed as parameter from edsloc.pmergstr or edsloc.pedsstr
*!* -----------------------------------------------------------------------------------------------------------
TRY
*!* -----------------------------------------------------------------------------------------------------------
*!* Now get the connection statement
*!* -----------------------------------------------------------------------------------------------------------
IF !EMPTY(lpconnstring)
lconn = SQLSTRINGCONNECT(lpconnstring, .T.)
ENDIF
I can loop 100 times and keep trying but it always returns -1, ie, It doesn't cause an error, just no connection handle.
The connection string has not changed. It worked before I installed Office 2010. Ever since it doesn't work.

 

[Olaf Doschke]

Read about SQLSTRINGCONNECT in the help or simply trust us: Though a failed connection does not trigger an error, AERROR() will give you additionla info on the problem the SQL Server had with the SQL Passthrough call. This is valid for SQLStringConnect and other SQL...() functions.

So AERROR() is not only useful after a system error happened.

Besides: AERROR() also is giving info, if Tableupdate() returns .F., and that also does not trigger an error.

Bye, Olaf.

 

[Olaf Doschke]

Actually the help on SQLStringConnect and SQLConnect only hint AERROR() is of relevance, as these two topics lists AERROR() in the "See Also" section...

You could check, if there is any irregularity with the value of lpconnstring (check out it's value in the debugger Locals Window eg) and what AERROR() tells you, after getting lconn<0:

...
lconn = SQLSTRINGCONNECT(lpconnstring, .T.)
If lconn<0
AERROR(laError)
set step on
Endif

Bye, Olaf.

 

[glamb]

will try ....

 

[glamb]

OK, here it is:

LAERR Priv A getsqlconn
( 1, 1) N 1526 ( 1526.00000000)
( 1, 2) C "Connectivity error: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandl
e on SQL_HANDLE_ENV failed"
( 1, 3) C "[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV
failed"
( 1, 4) C "IM004"
( 1, 5) N 0 ( 0.00000000)
( 1, 6) N 1 ( 1.00000000)
( 1, 7) C .NULL.

 

[Olaf Doschke]

Hm, googling the Sql State IM004 I only find this in conjunction with Oracle Databases, not MS SQL Server. Maybe an added ODBC driver is now adressed via your unchanged connection string. What does your connection string specify as Driver? If you're too unspecific the ODBC Driver Manager choose something, you didn't have in mind.

Bye, Olaf.

 

[Olaf Doschke]

By the way: The error being reported from the ODBC Driver Manager is alreay a hint the SQL Server itself has nothing to do with that. If SQL Server itself would deny the connection, the error would come from the SQL Server Driver itself.

Office may have updated the ODBC driver you use and the new version seems incompatible. That would be my guess for now.

Bye, Olaf.

 

[Dan Freeman]

And the second question I asked?

I'm done shooting in the dark.

 

[glamb]

connect string is
Driver={SQL Server};Server=servername;UID=myuid;PWD=mypasswrd;Database=mydb

But I do think that the driver was overwritten. How would I find out? Do you know the actual file name of the SQL Server driver?

 

[Olaf Doschke]

Uless you haven't noted what Driver Version you initially used, you'll need another computer on which your connection still works to see what driver version works.

To find out the DLL File, simply start odbcad32.exe (on 64 bit systems this 32 bit ODBC Administrator version is in a SysWOW64 subfolder of Windows) and see what drivers it lists. The human readable name you specify in the connection string is listed in the Drivers tab there besides version and file.

I'm on a Vista 32bit machine right now and have Version 6.00.6002.18005 of the SQL Server Driver, which is the file SQLSRV32.DLL. I can confirm this version of the driver works.

You might look out for another entry in the list of drivers also being named SQL Server pointing to another DLL file.

You might also try to temporarily use the "SQL Native Client" driver instead. While you may have some problems with it in detail and want to revert to the old version of SQL Server you had before, you should still be able to connect and see, if there is no other problem in SQL Server itself, perhaps.

By, Olaf.

 

[glamb]

Thank you so much. I'm on it and will let you know what I find.