need help - Database has been moved to SQL server 2000 from SQL servere 6.5

Hi there,

I am not sure if this is the right place to seek help for the problem i have but as i don't see any other link to discuss the situation i have i am just posting it here. To explain a little bit about the project i am on.... Originally the appication(developed in asp.net and vb) i am assigned to was developed by a different team and most of the databases were on SQL server 6.5 servers. So they have used oledb connections where ever they had to connect SQL server 6.5 data sources. Lately the client has moved all of the sql server 6.5 data bases to SQL server 2000 and now the application is kinda not working as it is suppose to as it was before. So i am hired to fix the problem.

So as a first step what i am doing is finding out all of the .net oledb proivder part of the database connections code to SQL server 6.5 data sources and changing them to .NET provider for SQL server.

Say for example in the below GetConnection function i am commenting out OledbConnection and defining a SqlConnection instead.

PrivateFunction GetConnection()As IDbConnection

'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection

Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection

conn.ConnectionString = Helpers.DBHelpers.GetConnectionString(Helpers.DBHelpers.COMMON)

Return conn

EndFunction

and also commenting out OledbDataAdapter line of code and defining a SqlDataAdapter instead. You can see it below

'Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(CType(cmd, OleDb.OleDbCommand))

Dim adapterAs SqlClient.SqlDataAdapter =New SqlClient.SqlDataAdapter(CType(cmd, SqlClient.SqlCommand))

And connection strings are defined in web.config file and also i am changing those as well . See below.

<!--<add key="Common" value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";" />-->

<addkey="Common"value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;"/>

So i hope i am in the right direction as far as the first step.But please throw in any kind of suggestions on this.

One more thing. I have a search screen and T-sql query thats built for this purpose searches 4,5 tables and brings the data back.

When i make a search from the web browser it doesn't return the data for the first couple of times but it brings the data 3rd time but even its taking as long as 60 seconds to bring the data back. when i close the browser and debug and paste the SQL query in the query analyzer it returns the data in the query analyzer and when i complete the remaining part of debugging and bind the data to the gird i also see the data on the broswer for the first time itself.

Question : Why i don't get the data for the first time when i search it from the front(web browser)?

But like i said the executing time to the query in the query analyzer itself takes considerably long time( i would say around 60 seconds just to return 3,4 records)) in the query analyzer. When i talked to the database guys why sql queries are a little slow they say they have a lot of datat out there around 180 thousand records in it and thats why its taking that much time to search agains all of the rows.

Question - Do you think it could be some thing to do with dropping and recreating the indexes should solve our problem? May be its some thing to do with the indexes but i am sure they have not dropped out the indexes of all of the table objects and recreated yet after the databases are moved to SQL servere 2000.

Hope i am able to explaing what i am looking for and what i am doing.

Please help me understand in solving these problems. Thanks in advance

-D

I can write 200 pages and I have not finished the differences between SQL Server 6.5 and 2000, SQL6.5 is linked list based 2000 is on disk, page in 6.5 is 256k per page, 2000 8000k,6.5 no DRI(declarative referential integrity), 2000 DRI. Try the link below and download Neil Pike's FAQ. My advice get a copy of the database from your client and manually clean all the files to SQL Server 2000 as you could, then backup and restore about three increaments note all the changes so you don't loose any data and then change your data provider. BTW there is no direct upgrade from 6.5 to 2000 that may be your problem. Hope this helps.

http://www.mssqlserver.com/faq