SQL Server Express 12 (local) and SQL Server 2005 (network lan)
Whe I try to connect to database I get the error:
MS SQL Server: SQL Server Native Client not installed
I’ve used the Xojo example project MSSQLServerExample.xojo_binary_project:
mDb = New MSSQLServerDatabase
mDb.Host = "localhost" // tried instance name also
mDb.UserName = "sa"
mDb.Password = myPwd
mDb.DatabaseName = "PERLA"
While I connect with success to PERLA database (and any other database both Local and Lan) using any other language VB.NET, C#, VB6.0, and Sql Sserver Management Studio, too.
On my computer there are installed both SQL Server Client and SQL Server Native Client 11
I tried to connect both local database (2012) and lan server database (2005) but I get same error only using Xojo.
P.S.
Also, I tried the ODBC sample project (using ODBC) but i get same error.
Is their any benefits in using your plugin to connect to the MS SQL database over the inbuild, I am guessing their must be otherwise you wouldnt have written it but I could not find anything on your site that really explained the benefits.
Is your app running on a 64 bit OS? If so, then you need to install the 32 bit versions of the Native Client. You can see what 32 bit drivers are installed using the 32 bit ODBC Manager at C:\\Windows\\SysWOW64\\odbcad32.exe.
Sorry maybe I wasnt specific enough, the list is generic, I was asking about MS SQL specifically also the first bullet point doesnt really say anything about what things might be faster as I am sure when developing a project people would be happy to do things differently if they though they could getter better performance but if we dont know what makes the difference then we dont know what to change or do differently.
The plugin can load records in batches. So instead of querying server on each moveNext, it can preload a 1000 records in one request. Walking through recordset is than magnitudes faster.
Of course it’s not that easy.
You’d need to change code to SQLDatabaseMBS class and use its methods. And one is the PreFetchRows option you want to set to something useful. e.g. db.Option(“PreFetchRows”) = “100”
Had this same problem about a year ago. Got the same error message you did on one of my computers but the same app connected to the database just fine on the other computer. At the time, I had just installed the 2012 SQL Server Management Studio on both computers. Looking at installed programs, it showed the SQL Native Client was present on both computers. The only difference between the two computers that I noticed was that I also had 2008 SQL Server Management Studio R2 still installed on the computer that successfully connected (I had unistalled it on the other computer that wasn’t connecting to the db when I installed the 2012 version). I went back and reinstalled the 2008 R2 version on the computer that wasn’t connecting and it began working correctly. From what I remember, finding the “right” SQL Native Client installer on the Microsoft site was not the easiest thing I ever did … of course, who ever said Microsoft support made anything easy, right?
Giorgio, I connect to a remote MSSQL 2012 database server using the native client. My approach is to use a OLEObject to do so. This is only going to work on a Windows application, which works for me at the moment. (this is from a Windows hosted web application)
[b]Dim CN2 as new OLEObject( “ADODB.Connection” )
dim RS1 as new OLEObject( “ADODB.recordset” )
Dim adoCmd as new OLEObject( “ADODB.Command” )
dim myDatabaseName as string
myDatabaseName = “MyServernameYouWillUseYours_”
myDatabaseName = mydatabasename + me.IAM.Environnement +"_2" // this is using a choice made by the user on the login page.
CN2.CursorLocation = adUseClient
This code is definitely not going to work for you as-is, since it uses a module that I am not ready to share. However, this should give you the general idea on connecting to a remote server by IP address or URL - I can use either with this code, using the native client.
Please note that there are subtle. differences between connect strings for express servers and full MSSQL . Here is a link that you may find useful.
[quote=85992:@Paul Lefebvre]Since Xojo creates 32-bit apps, you’ll need to ensure you have the 32-bit Native Client.
Also, you need the MSSQL plugin from Xojo 2014 Release 1 and later to connect to SQL Server 2012.[/quote]
Thak for your reply.
I’m sure: I have 32-bit Client because I use it to connect from Visual Basic 6.0 (which work with 32 bit only).
I don’t have 2014 rel.1.
[quote=86043:@Louis Desjardins]Giorgio, I connect to a remote MSSQL 2012 database server using the native client. My approach is to use a OLEObject to do so. This is only going to work on a Windows application, which works for me at the moment. (this is from a Windows hosted web application)
[b]Dim CN2 as new OLEObject( “ADODB.Connection” )
dim RS1 as new OLEObject( “ADODB.recordset” )
Dim adoCmd as new OLEObject( “ADODB.Command” )
dim myDatabaseName as string
myDatabaseName = “MyServernameYouWillUseYours_”
myDatabaseName = mydatabasename + me.IAM.Environnement +"_2" // this is using a choice made by the user on the login page.
CN2.CursorLocation = adUseClient
This code is definitely not going to work for you as-is, since it uses a module that I am not ready to share. However, this should give you the general idea on connecting to a remote server by IP address or URL - I can use either with this code, using the native client.
Please note that there are subtle. differences between connect strings for express servers and full MSSQL . Here is a link that you may find useful.
Thank you Louis Desjardins!
I’ve resolved using OleObject(), both for SQL Server 2012 (64bit) and SQL Server 2005 (32bit).
My code is:
SQL Server Express 2012 (64bit) (local)
[code] Dim CN As New OLEObject(“ADODB.Connection”)
Dim RS As New OLEObject(“ADODB.recordset”)
CN.open “Driver={SQL Server};Server=PC-GIORGIO\SQLEXPRESS;Database=NORTHWND;Uid=;pwd=;”
if CN.State = 1 Then
ConnectStatusLabel.Text = “Connected to MS SQL Server”
CN.CursorLocation = 3 'adUseClient
else
return
end if
’ fill listbox DataList with 4 fields:
DataList.DeleteAllRows
Dim sql As String
sql = “SELECT * FROM Customers”
RS.Open sql, CN, 0, 1, 1 ’ adOpenForwardOnly , adLockReadOnly, adCmdText
if RS <> Nil Then
While Not RS.EOF
DataList.AddRow(RS.Fields(0).Value, RS.Fields(1).Value, RS.Fields(2).Value, RS.Fields(3).Value)
RS.MoveNext
Wend
RS.Close
End If
CN.Close[/code]
SQL Server 2005 (32bit) (remote server)
Same of above, except the below two lines:
CN.open "Driver={SQL Server};Server=SERVER01\\MSSQLSRV2005;Database=PERLA;Uid=myUser;pwd=mypass"
...
sql = "SELECT * FROM Accounts"
The use of Driver={SQL Server} is client version indipendent, so no matter which version of SQL Server Client is installed on the PC.
