Moving Data Between 2 Databases (SQL)

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

anonymous
Wed Feb 04 14:41:07 CST 2004

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

Steve
Thu Feb 05 10:38:09 CST 2004

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" <rdm412_18@hotmail.com> wrote in message
news:598aef63.0402040626.27a127b8@posting.google.com...
> 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

rdm412_18
Mon Feb 09 10:44:41 CST 2004

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" <nospam@nowhere.com> wrote in message news:<e3i75ZA7DHA.2748@TK2MSFTNGP09.phx.gbl>...
> 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" <rdm412_18@hotmail.com> wrote in message
> news:598aef63.0402040626.27a127b8@posting.google.com...
> > 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