Solved

sql server connection

Posted on 2013-06-21
12
2,141 Views
Last Modified: 2013-10-16
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
0
Comment
Question by:coperations07
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
Comment Utility
Use SQL Native Client ODBC.

http://msdn.microsoft.com/en-us/library/ms131415.aspx

The SQL Server Native Client driver supports connecting to SQL Server 2000

I don't see an Advantage of OLEDB vs ODBC, especially as MS is committed to ODBC development for future SQL Server Versions and OLEDB will be discontinued.

Also see http://www.codeproject.com/Questions/296705/oledb-connection-for-powerbuilder
and of course www.connectionstrings.com

Bye, Olaf.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Thanks for correcting that Olaf.  I thought I read that it did not support SQL Server 2000.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:kotukunui
Comment Utility
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?
0
 

Author Comment

by:coperations07
Comment Utility
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
0
 

Author Comment

by:coperations07
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:kotukunui
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This video teaches viewers about errors in exception handling.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now