vb6到SQL Server 2008的ADODB连接字符串 [英] vb6 ADODB connection string to sql server 2008

问题描述

我最近将数据库从sql server 2005迁移到Windows Server 2008上的2008。客户端从XP计算机连接正常，SQL Management Studio 2008也是如此。我还测试了使用LINQPad的远程连接，该连接工作正常。

I recently migrated a database from sql server 2005 to 2008 on windows server 2008. Clients connect fine from their XP machines and so does the SQL Management Studio 2008. I have also tested a remote connection using LINQPad which worked fine.

但是在我的VB6应用程序上，连接字符串似乎给我带来了问题。有任何想法我做错了吗？

However on my VB6 app, the connection string seems to give me problems. Any ideas what I'm doing wrong?

    Dim strUserName As String
     Dim strPassword As String
     Dim sProc As String

     sProc = "Class_clsAdoFnx_Initialize"

        Me.DatabaseName = "db_app"




 'Connect to SQL Server

    strUserName = "admin"
    strPassword = "mudslinger"

    Set cSQLConn = New ADODB.Connection
    '**Original connection String
    'cSQLConn.CommandTimeout = 0
    'cSQLConn.ConnectionString = " PROVIDER=SQLOLEDB" & _
    '    ";SERVER=NET-BRAIN" & _
    '    ";UID=" & strUserName & _
    '    ";PWD=" & strPassword & _
    '    ";DATABASE=" & Me.DatabaseName

    '***First attempt, no dice
    'cSQLConn.ConnectionString = "Provider=sqloledb;" & _
    '       "Data Source=NET-BRAIN;" & _
    '       "Initial Catalog=DB_APP;" & _
    '       "User Id=admin;" & _
    '       "Password=mudslinger"
    'cSQLConn.Open

    '***3rd attempt, no dice 
    cSQLConn.Open "Provider=sqloledb;" & _
           "Data Source=NET-BRAIN;" & _
           "Initial Catalog=db_app;" & _
           "User Id=admin;" & _
           "Password=mudslinger", "admin", "mudslinger"

请提前谢谢。

更新：这是我使用test.UL文件生成的字符串

UPDATE: Here is the string I generated using my test.UL file

[ODBC ]提供商

Provider = MSDASQL.1;密码=登录名；持久安全信息=真；用户ID = sa；扩展属性= DSN = NET- BRAIN; UID = admin; PWD = mudslinger; APP =Microsoft®Windows®操作系统; WSID = BPOOR-16D68FBC7D; DATABASE = DB_App; Network = DBMSSOCN;初始目录= DB_App

Provider=MSDASQL.1;Password=logmein;Persist Security Info=True;User ID=sa;Extended Properties="DSN=NET-BRAIN;UID=admin;PWD=mudslinger;APP=Microsoft® Windows® Operating System;WSID=BPOOR-16D68FBC7D;DATABASE=DB_App;Network=DBMSSOCN";Initial Catalog=DB_App

以下是使用 SQL本机提供程序的UL文件：

Here is the same UL file using the SQL Native provider:

Provider = SQLNCLI10.1; Integrated Security =;持久安全信息= False；用户ID = admin；初始目录= DB_APP；数据源= NET-BRAIN；初始文件名=；服务器SPN =

"Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=admin;Initial Catalog=DB_APP;Data Source=NET-BRAIN;Initial File Name="";Server SPN="""

-收到错误：Ado调用中的错误... Class_clasAdoFnx_initialize 3001中有错误参数类型错误，超出可接受范围或与on冲突另一个。
-错误：Class_clsAdoFnx_Initialize
3709请求的操作需要一个OLE DB Session对象，当前提供程序不支持该对象。

--received the error: Error in Ado Call... There was an error in Class_clasAdoFnx_initialize 3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. --Error: Class_clsAdoFnx_Initialize 3709 Requested operation requires an OLE DB Session object, which is not supported by current provider.

尝试了[oledb] for sql server provider option
Provider = SQLOLEDB.1; Password = mudslinger; Persist Security Info = True;用户ID = admin;初始目录= db_app;数据源= net-brain

tried [oledb] for sql server provider option "Provider=SQLOLEDB.1;Password=mudslinger;Persist Security Info=True;User ID=admin;Initial Catalog=db_app;Data Source=net-brain"

错误：-2147217900用户'admin'的登录失败

error: -2147217900 Login failed for user 'admin'

UPDATE2：隔离打开的连接字符串后，事实证明连接正在打开，而我用来测试的存储过程失败了。

UPDATE2 : After isolating the open connection string, it turns out the connection is opening and the stored procedure I was using to test with was failing.

推荐答案

跟随将ADO与SQL Server Native Client配合使用以启用SQL Server Native Client的使用，ADO应用程序将需要在其连接字符串中实现以下关键字：

Following Using ADO with SQL Server Native Client to enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

Provider = SQLNCLI10

DataTypeCompatibility = 80

Provider=SQLNCLI10
DataTypeCompatibility=80

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI10;" _
         & "SERVER=NET-BRAIN;" _
         & "Database=DB_APP;" _ 
         & "DataTypeCompatibility=80;" _
         & "User Id=admin;" _
         & "Password=mudslinger;"

con.Open

这篇关于vb6到SQL Server 2008的ADODB连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！