VB6 Connection (going from 2000 to 2008)

Question

VB6 Connection (going from 2000 to 2008)

texpic

SSCertifiable

Points: 5885
More actions
February 11, 2012 at 7:28 pm

#264376

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 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")

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 1

I don’t think it’s an issue with application (however it can be). Most of the times database upgrade 2000 -> 2008 performs slow if not properly done.

Can you please provide more information on database upgrade / migration? Who did it & what all steps you followed? How big the database is? etc.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 2

This article would be helpful to you.

SQL Server 2000 to 2008 Upgrade - Microsoft

http://www.google.co.in/url?sa=t&rct=j&q=sql%20server%20upgrade%20from%202000%20to%202008&source=web&cd=1&ved=0CDIQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F2%2F0%2FB%2F20B90384-F3FE-4331-AA12-FD58E6AB66C2%2FSQL%2520Server%25202000%2520to%25202008%2520Upgrade%2520White%2520Paper.docx&ei=Uok3T8c1i-qtB6Xf6NUF&usg=AFQjCNHREhaRPPZE_tjdjNENilWNKPNpIw&cad=rja

paul.knibbs SSCoach Points: 15320 More actions · Answer 3

I would try running the queries you've got in your application in the database directly to see how fast they go--chances are this is a query optimisation issue thanks to changes in the optimiser between SQL 2000 and 2008, not a problem with VB6 itself; you'll need to rewrite the queries.

texpic SSCertifiable Points: 5885 More actions · Answer 4

I'm sure the queries are running slow. Seems like they all are. I read if I change the driver, it would run faster.

I'm not much of a VB person, all I know is I have this connection m_con that gets opened and closed all over the place in the VB application so I have to keep it.

Old:

Set m_con = New ADODB.Connection

Call m_con.Open("DRIVER={SQL Server};SERVER=" & _

MyValue & ";UID=CustServ;PWD=xxxxxxxx")

Trying to get to:

Set m_con = New ADODB.Connection

Call m_con.Open("DRIVER={SQL Server Native Client};SERVER=" & _

MyValue & ";UID=CustServ;PWD=xxxxxxxx")

I get an error that the driver is not found; think that is the 2008 driver? Also a lot of threads say going to that driver cured slowed stored procs, actually they got faster.

texpic SSCertifiable Points: 5885 More actions · Answer 5

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.