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

Need help - Converting OLEDB connections to SQL connections in asp.net

Hi there,

Here we have got aasp.net application that was developed when database wassitting on SQL server 6.5. Now client has moved all of their databasesto SQL server 2000. When the database was on 6.5 the previousdevelopment team has used oledb connections all over. As the databaseshave been moved to SQL server 2000 now i am in process of changing thedatabase connection part. As part of the process i have a loginauthorization code.

PrivateFunction Authenticate(ByVal usernameAsString,ByVal passwordAsString,ByRef resultsAs NorisSetupLib.AuthorizationResult)AsBoolean

Dim connAs IDbConnection = GetConnection()

Try

Dim cmdAs IDbCommand = conn.CreateCommand()

Dim sqlAsString = "EDSConfirmUpdate"'"EDSConfirmUpdate""PswdConfirmation"

'Dim cmd As SqlCommand = New SqlCommand("sql", conn)

cmd.CommandText = sql

cmd.CommandType = CommandType.StoredProcedure

NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)

NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)

conn.Open()

'Get string for return values

Dim ReturnValueAsString = cmd.ExecuteScalar.ToString

'Split string into array

Dim Values()AsString = ReturnValue.Split(";~".ToCharArray)

'If the return code is CONTINUE, all is well. Otherwise, collect the

'reason why the result failed and let the user know

If Values(0) = "CONTINUE"Then

ReturnTrue

Else

results.Result = Values(0)

'Make sure there is a message being returned

If Values.Length > 1Then

results.Message = Values(2)

EndIf

ReturnFalse

EndIf

Catch exAs Exception

Throw ex

Finally

If (Not connIsNothingAndAlso conn.State = ConnectionState.Open)Then

conn.Close()

EndIf

EndTry

EndFunction

''' ------------------------

''' <summary>

''' Getting the Connection from the config file

''' </summary>

''' <returns>A connection object</returns>

''' <remarks>

''' This is the same for all of the data classes.

''' Reads a specificconnection string from the web.config file for the service, creates aconnection object and returns it as an IDbConnection.

''' </remarks>

''' ------------------------

PrivateFunction GetConnection()As IDbConnection

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

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

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

Return conn

EndFunction

in the above GetConnection() method ihave commented out the .net dataprovider for oledb and changed it to.net dataprovider for SQLconnection. this function works fine. But inthe authenticate method above at the line

Dim ReturnValueAsString = cmd.ExecuteScalar.ToString

for some reason its throwing the below error.

Run-time exception thrown : System.Data.SqlClient.SqlException - @.password is not a parameter for procedure EDSConfirmUpdate.

If i comment out the

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

and uncomment the .net oledb provider,

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

then it works fine.

I also have changed the webconfig file as below.

<!--<addkey="Common" value='User ID=**secret**;pwd=**secret**;DataSource="ESMALLDB2K";Initial Catalog=cj_common;AutoTranslate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'/>-->

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

Please help. Thanks in advance.

what are the parameters your stored proc is defined with ?
NEVERgive out your userid/pwd in the connection string when pasting code over on a public forum.Mask your pwd.
|||

Thanks for the quick response Dinakar.

Here are the parameters the stored proc is expecting

*******************************

CREATE PROCEDURE EDSConfirmUpdate
@.logon char(10), @.curpswdclr varchar(255), @.newpswdclr varchar(255) = null
AS

***********************

But in the it is sending the parameter

NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)

NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)

obviously @.password is not a parameter in the sotred proc. But why it works when i use oledb... i am not sure about it.

Hope this will help you to understand further.

Thanks and i just pasted it like that. I won't do it again.

-D

|||well I dont know how it worked in OLEDB. But for SQL you need todeclare the same parameters that you used in the stored procedure.
|||

yeah you are right. i couldn't figure out why it was working with oledb before with a wrong parameter. I have changed it to the right parameter now its working.

I have one more problem with the tree control that is defined. The client main concern is that they are not able to use their tree view control. before move it was rendering well but after the databases to 2000 its got messed up. I have changed the connections to SQL server and thought it would render automatically(off course there is not connection between rendering a treeveiw and the type of the provider thats being used ) but i thought some thing messed up with the move and the tree view was unable to find the data needed to build it. But no luck yet! i have posted it at

http://forums.asp.net/930668/ShowPost.aspx

Please take a look at it if you can think of any thing.

Thank you

-D