Moving Data Between 2 Databases (SQL)

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have created a web form that enables a user to pass a large amount
of data between 2 different SQL 2000 Databases. The current
methodology is to extract the source data, store it in a .Net dataset
and then using the CmdBuilder, pass each line of data out to the
destination database.

I'm having some issues with the size (1000s of lines) and time of the
data transfer that invariably ends up with either a SQL timeout (I
have set all Command.Timeouts=0) or the aspnet_wp.exe process cycles
during inactivity. I have attempted to use SQLServer session state
management, but am not very familiar with exactly how that works
(authentication is also an issue here).

My question is, can I use a different method of moving the data from
the dataset to the database? I feel as though there must be a more
efficienct, less time consuming way. Is it possible to move an entire
dataset at once? Can SQL and .Net perform this?

Thanks in advance for your help.

Rob
 
Rob

Perhaps it's not an elegant solution but have you thought of using bcp to copy the db to a file then either bcp or BULK INSERT into the other db. There may be limitations with this but it is faster than trying to use sql statements within ADO.NE

Good Luck
 
Rob,

For the aspnet_wp.exe cycling, are you aware of the responseDeadlockInterval
setting in machine.config?

Do your two SQL Servers have a security relationship between them? In other
words, could you simply send an insert command (via ExecuteNonQuery) of the
form:

INSERT INTO Server1.MyDatabase.dbo.MyTable
SELECT [Fields] FROM Server2.MyDataBase.dbo.MyTable

-Steve
 
Steve,

Thanks for the advice...the deadlock interval has resolved some of the
problem. I can now run successfully with a 'local' installation, but
my production server installation results in a Timeout. I've read
many articles and comments from users about the CommandTimeout
property and have set it to 0 throughout my code. My connection
string timeouts are all set beyond the timeout I actually recieve.
So, I can run this from my desktop, but I'd obviously prefer it on our
production server. The strange thing is, my desktop does this
successfully across a WAN, while the production environment has all
the database and web server infrastructure, yet times out!

Rob



Steve said:
Rob,

For the aspnet_wp.exe cycling, are you aware of the responseDeadlockInterval
setting in machine.config?

Do your two SQL Servers have a security relationship between them? In other
words, could you simply send an insert command (via ExecuteNonQuery) of the
form:

INSERT INTO Server1.MyDatabase.dbo.MyTable
SELECT [Fields] FROM Server2.MyDataBase.dbo.MyTable

-Steve


Rob said:
I have created a web form that enables a user to pass a large amount
of data between 2 different SQL 2000 Databases. The current
methodology is to extract the source data, store it in a .Net dataset
and then using the CmdBuilder, pass each line of data out to the
destination database.

I'm having some issues with the size (1000s of lines) and time of the
data transfer that invariably ends up with either a SQL timeout (I
have set all Command.Timeouts=0) or the aspnet_wp.exe process cycles
during inactivity. I have attempted to use SQLServer session state
management, but am not very familiar with exactly how that works
(authentication is also an issue here).

My question is, can I use a different method of moving the data from
the dataset to the database? I feel as though there must be a more
efficienct, less time consuming way. Is it possible to move an entire
dataset at once? Can SQL and .Net perform this?

Thanks in advance for your help.

Rob
Click to expand...
Click to expand...
 
Back
Top