Showing posts with label oledb. Show all posts
Showing posts with label oledb. Show all posts

Wednesday, March 28, 2012

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

Monday, March 26, 2012

need faster data transfer :: oledb

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are way to many reasons for it to be slow. It could be your server, their server, network, etc.

The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.

When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are so many "it depends" answers to this that its hardly worth replying.

There is a webcast on performance tuning that you may wish to watch:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

And info here about a performance tuning whitepaper:

Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)

-Jamie

|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.

Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Are there indexes in place on the destination tables?

Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?

Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?

Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?

=================

The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)

Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)

Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.

Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)

*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Only you can answer those questions. Test and measure is my advice to you.

There is some useful performance tuning info in the whitepaper that I linked to earlier.

You might also want to look at using the SQL Server Destination if at all possible.

-Jamie

|||

<<after filtering ,i.e which need to be transfered>>

This is where I would look first. That and network lag.

|||

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

|||

samssb wrote:

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.

need faster data transfer :: oledb

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are way to many reasons for it to be slow. It could be your server, their server, network, etc.

The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.

When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

There are so many "it depends" answers to this that its hardly worth replying.

There is a webcast on performance tuning that you may wish to watch:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

And info here about a performance tuning whitepaper:

Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)

-Jamie

|||

Nitin Khurana wrote:

i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),

and way to make it faster...source db in not in my control...so i cant get indexes on it..:(

Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.

Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Are there indexes in place on the destination tables?

Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?

Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?

Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?

=================

The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)

Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)

Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.

Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)

*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||

Nitin Khurana wrote:

okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..

Only you can answer those questions. Test and measure is my advice to you.

There is some useful performance tuning info in the whitepaper that I linked to earlier.

You might also want to look at using the SQL Server Destination if at all possible.

-Jamie

|||

<<after filtering ,i.e which need to be transfered>>

This is where I would look first. That and network lag.

|||

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

|||

samssb wrote:

I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.

I have no idea why it would take so much longer.

Sam

Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.sql