console application for retrieving a large amount of data

i need to retrieve a large amount of data from the sql server database and make changes to one field and put the data back using a console application. how do i do it?Well, there are lots of ways, probably the easiest being to retrievethe data as a dataset, make the changes, and update the database.
But let's start with this. Why do you need to retrieve a large amountof data to only change one field? Do you mean one field in all or mostof the records, or one field in one record?
If you're doing an update that affects many rows and don't really needto pull the data off the server, you might be able to use a SQL UPDATEstatement that updates the data on the database server, saving thenetwork traffic of pulling it down to the client.
Tell us more about what you want to do and we'll try to help.
Don
|||well, i have to retrieve 3 million records from the sql server database. i need to encrypt one field present in all records and put that encrypted field back into the db.
thanks.|||Okay. Is this a one-time thing or will you be doing it regularly? If aone-time thing then you probably don't care too much about performanceor being inefficient with regards to memory usage. So I would probablypull down the data into a dataset, probably in ranges of data usingsome field that is reasonably well-distributed, make the changes, andthen update the database.
For example, if the data had a last name field, you could do it forlast names that begin with A to E, then F to M, and so on. Or whateverranges make sense. And bring down ONLY the data you need, presumablythe one field with the data to be encrypted, and perhaps the secondfield that is the destination for the encrypted data. Or does theencrypted data go into a different table? Then you'll need to generatethe insert statements or use a second data table in the data set.
This is going to be horribly inefficient, however, so you won't want togo this route if this is anything but a one-time thing. If it'ssomething you'll need to do regularly, I'd try to find a way to do thisentierly on the server. In that case you could write a stored procedurethat uses OLE Automation (the sp_OA* system stored procedures) to dothe encryption. Since that uses COM it's going to have its ownperformance issues, but at least you're not slepping three million rowsof data to the client across the network.
Depending on exactly how you need to do this, there are plenty of other ways to get it done.
Don

Connection Timeout Problems

Hi,
We want to connect to a remote SQL server to retrieve stock levels in real
time. If the SQL server is unavailable (it is not on a totally reliable
network connection) we want to display stock levels from a local backup
database. So, if we can't connect to the remote server in say 5 seconds, we
want the connection attempt to time out and return control to our
application (a web application on windows 2000 written in ASP). I cannot get
the connection attempt to timeout if the remote server is unavailable. I
have tried:
- Setting the ConnectionTimeout on the ADO connection object. As far as I
can see, if the server is totally unavailable, setting the Timeout property
has no effect at all
and
- Writing a VB component which opens the connection asynchronously and polls
to see whether it has opened successfully. This sort of works, except that
if I try to cancel the attempt to open the connection, this hangs for about
30 seconds before returning control to my component.
The connection string I am using is: "provider=sqloledb;Server=<Server IP
Address>;Initial Catalog=<DBName>;user Id=<userid>;Password=<password>
Is there any reliable way of setting a connection timeout for this
situation?
Thanks,
Peter
Hi Pete,
The loginTimeout issue is due to the underlying networking components.
So, before we send the login packet for SQL, we first have to establish an
underlying tcp session. (3 way handshake). If this doesn't complete, the
LoginTimeout is never even used. You may be able to reduce the timeout by
adjusting the tcp settings.
See the following kb;
176257 PRB: Client Login Does Not Time Out If the Server Is Offline
http://support.microsoft.com/?id=176257
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection Timeout Problems

Hi,
We want to connect to a remote SQL server to retrieve stock levels in real
time. If the SQL server is unavailable (it is not on a totally reliable
network connection) we want to display stock levels from a local backup
database. So, if we can't connect to the remote server in say 5 seconds, we
want the connection attempt to time out and return control to our
application (a web application on Windows 2000 written in ASP). I cannot get
the connection attempt to timeout if the remote server is unavailable. I
have tried:
- Setting the ConnectionTimeout on the ADO connection object. As far as I
can see, if the server is totally unavailable, setting the Timeout property
has no effect at all
and
- Writing a VB component which opens the connection asynchronously and polls
to see whether it has opened successfully. This sort of works, except that
if I try to cancel the attempt to open the connection, this hangs for about
30 seconds before returning control to my component.
The connection string I am using is: "provider=sqloledb;Server=<Server IP
Address>;Initial Catalog=<DBName>;user Id=<userid>;Password=<password>
Is there any reliable way of setting a connection timeout for this
situation?
Thanks,
PeterHi Pete,
The loginTimeout issue is due to the underlying networking components.
So, before we send the login packet for SQL, we first have to establish an
underlying tcp session. (3 way handshake). If this doesn't complete, the
LoginTimeout is never even used. You may be able to reduce the timeout by
adjusting the tcp settings.
See the following kb;
176257 PRB: Client Login Does Not Time Out If the Server Is Offline
http://support.microsoft.com/?id=176257
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.