Link to home
Start Free TrialLog in
Avatar of coperations07
coperations07Flag for United States of America

asked on

sql server connection

Hi,
 I have a powerbuilder app that currently uses odbc to connect to sql server 2008 r2  and sql server 2000 databases.  I would like to do away with the requirement for odbc and use something like SQL Native Client to connect or whatever the best connection method is. With VB.Net apps I import the system.data.sqlclient namespace and then I can just pass a connection string. I haven't figured out exactly how to do this yet with powerbuilder. Can someone give me some direction please?  Here's what I've tried passing through the config file:

[DATABASE]
DBMS = "SNC SQL Native Client(OLE DB)"
ServerName = "usoscs407"
AutoCommit = False
DBParm = "Provider='SQLNCLI10',Database='db_qc00',TrustedConnection=1"

Thanks,
Dave
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

SQL Native Client is the current ODBC provider as well as several other protocols and in one DLL.  http://msdn.microsoft.com/en-us/sqlserver/aa937707

Connection strings for SQL Server 2008: http://www.connectionstrings.com/sql-server-2008

If you really mean "sql server 2000", you will have to keep your old ODBC driver because the newest SQL Native Clients do not support it.
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for correcting that Olaf.  I thought I read that it did not support SQL Server 2000.
Well, the msdn page can an also be wrong, but if Microsoft says, it's worth a try. I haven't tried, because there is no sql server 200 around here.

It should not be hard to work with two different drivers, the connection string hopefully is configurable.

Good Luck.

Bye, Olaf.
Avatar of kotukunui
kotukunui

Those parameters look OK on first inspection. Servername, DBMS and DBParm are the crucial ones there.
Are you getting an error when you try to CONNECT your Transaction Object to the database once its matching attributes have been set to those values?
Avatar of coperations07

ASKER

When I run the app on my own pc it connections and runs fine. But when I try to install it on a user machine it doesn't connect.

The message I get when I open the app states: DBMS SNC SQL Native Client(OLE DB) is not supported in your current installation.

I ran the windows installer sqlncli that I downloaded from Microsoft and it seemed to install fine.

From what I gather the provider for 2008r2 would be SQLNCLI10 and the provider for 2000 would be SQLNCLI1.

I'm just trying to get rid of the need for all the ODBCs on every install. This app connects to 6 different databases on 5 servers so it requires 6 ODBCs. The Native Client may not be the best approach so feel free to let me know if there is something else I should be doing.

Thanks,
Dave
I'll take you one step back:
http://msdn.microsoft.com/en-us/library/ms130892.aspx

This has some child nodes, among them are two nodes for SQL Server Native Client (ODBC) and SQL Server Native Client (OLE DB).

The quote I did was from the ODBC version. The OLE DB provider doesn't state anything about SQL2000.

Aside of that, the native client download contains both ODBC driver and OLE DB Provider, see http://msdn.microsoft.com/en-us/sqlserver/ff658532.aspx

Googling the error you gave I find: http://www.sybase.com/detail?id=44510
And that document has some things you should check
http://www.sybase.com/detail?id=47750

For one, the bitness of your powerbuilder version and the native client driver. Even on a 64bit Windows you most probably need the 32bit drivers.

Read on there, I'm sure you'll find the difference making it work on one but not on another client.

Bye, Olaf.
You only need the newest SQL Native Client.  As Olaf point out to me, the newest one supports all SQL Servers back to SQL Server 2000.  And oddly enough, Microsoft changed their minds again and after SQL Server 2012, SQL native client will not be included with the Server install.  However, ODBC drivers will be.  Here is a (confusing) page for Microsoft's Data Access Technologies Road Map.

http://msdn.microsoft.com/en-us/library/ms810810.aspx
Thanks for the feedback guys.  After reading through some articles I'm thinking I may just stick with ODBC. I'd still have to include dll files for a OLE DB connection if I used it, so I wouldn't be gaining much.  

I did read where .NET apps can use the .NET Framework to help set connections. I'm guessing if I were trying to connect to a Sybase db from PowerBuilder then it might have the same type of setup.
Hang on a second. Don't give up on Native client just yet.

If it works on your machine (which I assume has the full PB development environment) but not on others then it may just be that their Powerbuilder runtime DLL set does not include the Powerbuilder SNC connection library. Check for the presence of PBSNCxxx.DLL in the app directory (or somewhere in their path if they use a shared PB runtime folder approach). The "xxx" represents the version number of Powerbuilder in use. You don't say what version of Powerbuilder you have so the "xxx" could be "110" or "120" or something else similar.

In simplified terms, the DBMS entry in the config file uses the first three letters to look for the DB connection library. If those three letters are ODB (as in ODBC) then it looks for PBODBxxx.DLL. Again "xxx" represents the version. In this case the first three letters are SNC so it looks for PBSNCxxx.DLL.

Find that file on your machine and copy it to the app folder or PB runtime folder in their path and you might find it just starts working for everyone else.

The error you are getting looks like it is probably coming from Powerbuilder, not SQL Server itself.
Overall, you will in most seldom cases just deploy your final EXE and nothing else.
Using a database always requires some dlls, in this case both client DLLs from the database side and from powerbuilder.

The only reason a dotnet exe runs on it's own is the .NET framework is widespread. The only reason many C++ DLLs work is The C++ runtime is preinstalled by Windows itself. No programming language runs self contained.

If you don't want to install anything but your exe, you have to be really minimalistic. On the database access level the MSDAC components preinstalled only cover a few things, mostly the Jet database engine.

Bye, Olaf.
SQL Native client should perform better than ODBC does, so if it were me I would try to make it work. ODBC is fine if you only have a minimal amount of database activity and not much writing. ODBC is also useful if you need to support multiple database vendors in the same application. But if you know for sure the database is always MS-SQL then I would go with SQL Native.

The fact that it is running on your machine and not the other machine indicates a missing DLL. Either the SNC install isn't right, or there are missing PB dll's as kotukunui recommended. If there are other apps on the machine successfully using SNC, then that definitely points to missing PB dll's.