SQL Server 2012 LocalDB failing to connect

[Former Member]

PB 11.5 4011 Windows 7 64 bit

I am attempting to connect to a SQL Server 2012 LocalDB through powerbuilder. I have done the following steps:

1) Install SQL Server LocalDB

2) Create a named instance Using the following CMD command (C:\> SqlLocalDb create "DirectLink")

3) Connect to my named instance through SSMS with a server name = (LocalDB)\DirectLink

3) Create a test database to confirm everything works

Now For the Powerbuilder portion. I have been trying to connect via the native client, this is what I have so far.

// Profile Test

SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"

SQLCA.LogPass = <****>

SQLCA.ServerName = "(LocalDB)\DirectLink"

SQLCA.LogId = "sa"

SQLCA.AutoCommit = False

SQLCA.DBParm = "Provider='SQLNCLI10',Database='Direct'"

When i do test connection I get the following error:

SQLSTATE = 08001

Microsoft SQL Server Native Client 10.0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Any thoughts/Ideas? Maybe I shouldn't be using Native Client?

[Former Member]

Hi ,

Can you try this ?

DBMS=OLEDB

DATABASE=

LogId=sa

LogPassword=sasa

AutoCommit=1

DBPARM="PROVIDER='SQLNCLI10',DATASOURCE='COMPUTERNAME',DelimitIdentifier='Yes',PROVIDERSTRING='database=MyDatabse'"

checkencryptedpass=no

I have the above in a start in file.

Pay attention at the provider, SQLNCLI10. For SQL server 2008 R2 I'm using the above. For SQL 2012 I think it wants SQLNCLI11.

Here you go something that works. I used oledb

thank you

[Former Member]

Yup, that's what I was talking about. 

[Former Member]

Did you find a way to connect MS Sql LocalDB?

PB 12.5 does not support SQLNCLI11 and I think LocalDB need the SQLNCLI11.

Will PB support this in the future?

Thanks

[Former Member]

Hi Guys;

  FYI: "SQLNCLI11" will not be supported until PB 15.

In the mean time - you would have to install & use the "SQLNCLI10" client. However, that would restrict you to using SS2010 DDL/DML.

Regards ... Chris

[Former Member]

As I commented in my 29-June post, if you create an OLE-DB or ODBC datasource using the SQLNCLI11 as the provider, you can connect successfully to LocalDB.

[Former Member]

I tried it before but I was not success. Maybe it was just username or password issue. However, I noticed that sqlcmd could not connect too. I have also MS SQL 2008 on the same computer maybe this caused a problem. I will try again and ley you know.

[Former Member]

Yes I did try that, although I still was unable to get it to work. We ended up deciding to wait for support for SQLNCLI11.

[Former Member]

What version of SNC are you using?

I ask because PowerBuilder 11.x (and 12.x for that matter) do not yet natively support SNC for SQL Server 2012.  You can still connect using SNC though, either by using ODBC or OLE DB using SQLNCLI11 as the provider.

[Former Member]

No expert, but creating the instance does not start it - you might try starting it before attempting the connection.  In addition, I was under the impression that creating/starting only starts the engine - you still have no database associated with the instance AFAIK.  This is something you generally do at connection time by attaching a MDF.   The sqllocaldb.exe also has an "info" option that might be helpful. 

Lastly, I'll point out that 11.5 is not supported nor (guessing here) was it officially validated against sql server 2012. It may be that it simply will not work - and you may be the only one who has/is trying to use this configuration. There is an important note in the link below about compatibility issues based on the .net framework - something to consider.

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

[Former Member]

Thanks for the reply,

I should have mentioned in the OP, but I had tried starting the DB before connecting to it. I did not however attempt to attach the MDF. Maybe I will try adding that to my DBParm. The error message seems to indicate it is unable to find the server, but I will try anyways.

The info option shows me a named pipe, I did attempt to connect to the server using that named pipe, with the same result.

I should note that I can connect to the instance through SSMS, but who knows how SSMS established the connection.

Also I am on the latest .NET.

[Former Member]

I'll highlight what seems to be an important note in the msdn link:

If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB. The Instance pipe name value is the named pipe that the instance of LocalDB is listening on. The portion of the Instance pipe name after LOCALDB# will change each time the instance of LocalDB is started. To connect to the instance of LocalDB by using SQL Server Management Studio, type the Instance pipe name in the Server name box of the Connect to Database Engine dialog box. From your custom program you can establish connection to the instance of LocalDB using a connection string similar to SqlConnection conn = new SqlConnection(@"Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query");

[Former Member]

Hi Justin;

   FWIW: For all my local SS installations, I have to use the following to connect in PB with SNC:

SQLCA.ServerName = "<Machine Name>\<SS Instance Name>"

Regards ... Chris

[Former Member]

Thank you for the reply, I just tried connecting that way and got the same error. Note this is SQL Server LocalDB, not a local version of SQL Server Express.