VB6 SQL 2008 (change driver?)

I'm moving a VB6 app that has been used with SQL 2000 to SQL 2008. It connect, is functional, but very slow. Things that would take less than a second take a minute. Reading various posts it looks like a different connection string (driver) would help but I havent been able to get anything else to work. Appreciate the help, thanks.

SQL 2000:

Private Sub Form_Load()
IniFileName = App.Path & "\" & "SqlServer.ini"
Dim MyServer As String
Dim MyValue As String * 20
MyServer = GetPrivateProfileString("Database", "Server", _
"No INI File Located", MyValue, _
Len(MyValue), IniFileName)


Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")

Welcome to the forum!

Not sure why a driver would make a difference here - what type of SQL statements are running slow in 2008 that ran fast in 2000??

Here is code I use in an old VB6 app to connect to versions of SQL from 2000 to 2008

Code:

---

    If strDriver = "" Then
        strDriver = "SQLOLEDB"
    End If
   
    'Create a connection to the database
    Set gCn = New ADODB.Connection
   
    gCn.Provider = strDriver
    gCn.Properties("Data Source").value = strServer
    gCn.Properties("Initial Catalog").value = strDatabase
    If gstrUser <> "" Then
        gCn.Properties("User Id").value = gstrUser
        gCn.Properties("Password").value = gstrPassWord
    Else
        gCn.Properties("Integrated Security").value = "SSPI"
    End If
   
    gCn.CommandTimeout = 300
    gCn.Open

---

m_con is used all over the place in the app to open/close connection so I can't eliminate it. I'm trying to modify it but can get it working with the SQL 2008 driver.

Old:
Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")

New:
Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server Native Client};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")

gives error driver cant be found, tried variations of naming the native driver

Quote:

---

Originally Posted by szlamany

Not sure why a driver would make a difference here - what type of SQL statements are running slow in 2008 that ran fast in 2000??

---

I always wonder why I asked questions and they don't get answered?

Please - answer this question.

I have not determined which stored procedure is running slowly. I'm guessing, only guessing, if I ran the store procs from sql itself they would run fast. There is nothing dramatic happening in the queries.

The problem seems to occur when the VB app is used to execute the queries.

Waht got me on the driver thing was when I googled VB6 slow in SQL 2008, there are numerous mentions on the internet that when a driver was changed to the 2008, things went faster.

I'll dig into trying to answer you question about which queries run slow tonight.

I figured it out. I haven't used this app in years but it is being used still today. I haven't updated anything either. I installed the latest service pack for VB6 and added the ADO Data Control 6 to the app. Presto, runs fast.