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

Considerations... Backing up IIS 6 web files and SQL Server 2005 Express database

Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database?

I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors.

But I have heard a lot of discussion that you can't just simply backup SQL Server data files?

I'm wondering how sound the backup I've created is...

Any suggestions?

Try the link below to dowload the eval version of the full SQL Server 2005 it is good for 180 days so you have to buy the developer edition which is $60 or less later. Install it as a named instance and register the Express so you can use the backup and restore wizard to backup your databases. You don't need any third party tool to backup SQL Server if your databases are less than 1000. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

Connection string security.

Howdy all.
Currently on 2000, but will be going to 2005 in the next couple of months.
We recently ran into a scenario where a developer built a back door into our
Production SQL box, and we are not too happy about it. Here is the scenario:
Since a lot of our apps are for the internet, we mostly use SQL Server
authentication for the app to talk to the DB. In our environment, developers
are NOT allowed to connect directly to our OLTP box, and as such dont have
the username or password for this connection. But one of them built in
funcationality to display the connection string (username and password
included) into his app. The DB connection string is stored in a web config
file, and he is simply querying that file. Is there anything I can do to
prevent this? Apps are written in both .NET and Java.
TIA, ChrisRIf your developer programmed some back door into the app, there's not much
you can do from that end, other than changing the app to use windows
authentication. You can do this without requiring users to log in
explicitly by setting the web site to anonymous authentication, but setting
the identity to a domain user that has been granted limited access to SQL
Server (ideally, the login will only have permissions to execute stored
procedures in certain database(s)).
However, what you should ensure is that the apps that talk to SQL Server do
so only through the internal network. The web app is visible to the world,
but the SQL Server box is not. You can use a mix of hardware/software
firewall appliances to accomplish this. If the developer is not inside your
network, then he/she cannot connect to SQL Server regardless of the
credentials they are trying to use.
A
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> Howdy all.
> Currently on 2000, but will be going to 2005 in the next couple of months.
> We recently ran into a scenario where a developer built a back door into
> our
> Production SQL box, and we are not too happy about it. Here is the
> scenario:
> Since a lot of our apps are for the internet, we mostly use SQL Server
> authentication for the app to talk to the DB. In our environment,
> developers
> are NOT allowed to connect directly to our OLTP box, and as such dont have
> the username or password for this connection. But one of them built in
> funcationality to display the connection string (username and password
> included) into his app. The DB connection string is stored in a web config
> file, and he is simply querying that file. Is there anything I can do to
> prevent this? Apps are written in both .NET and Java.
> TIA, ChrisR|||Thanks Aaron!
"Aaron Bertrand [SQL Server MVP]" wrote:
> If your developer programmed some back door into the app, there's not much
> you can do from that end, other than changing the app to use windows
> authentication. You can do this without requiring users to log in
> explicitly by setting the web site to anonymous authentication, but setting
> the identity to a domain user that has been granted limited access to SQL
> Server (ideally, the login will only have permissions to execute stored
> procedures in certain database(s)).
> However, what you should ensure is that the apps that talk to SQL Server do
> so only through the internal network. The web app is visible to the world,
> but the SQL Server box is not. You can use a mix of hardware/software
> firewall appliances to accomplish this. If the developer is not inside your
> network, then he/she cannot connect to SQL Server regardless of the
> credentials they are trying to use.
> A
>
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> > Howdy all.
> >
> > Currently on 2000, but will be going to 2005 in the next couple of months.
> > We recently ran into a scenario where a developer built a back door into
> > our
> > Production SQL box, and we are not too happy about it. Here is the
> > scenario:
> >
> > Since a lot of our apps are for the internet, we mostly use SQL Server
> > authentication for the app to talk to the DB. In our environment,
> > developers
> > are NOT allowed to connect directly to our OLTP box, and as such dont have
> > the username or password for this connection. But one of them built in
> > funcationality to display the connection string (username and password
> > included) into his app. The DB connection string is stored in a web config
> > file, and he is simply querying that file. Is there anything I can do to
> > prevent this? Apps are written in both .NET and Java.
> >
> > TIA, ChrisR
>
>

Connection Problem with Remote Server

If I do this:
select * from tblAmcareDetail
I get my records back but as soon as try to specify column names I get:
Server: Msg 207, Level 16, State 3, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'apexorderid'.
I think it must be a connection problem because I don't have this problem on
local servers. The server is registered by it's ip address and I'm running
the query under the servers sa account.
Can anyone help?Doesn't sound like a connection problem.
Are you sure the table on the server.database you're connecting to has
this column?
Is the server/db/table you're connecting to perhaps setup with a
case-sensitive collation? If so, you'll need to query using the case of
column names as in the table definition.
Spike wrote:
> If I do this:
> select * from tblAmcareDetail
> I get my records back but as soon as try to specify column names I get:
> Server: Msg 207, Level 16, State 3, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
> 'apexorderid'.
> I think it must be a connection problem because I don't have this problem
on
> local servers. The server is registered by it's ip address and I'm runnin
g
> the query under the servers sa account.
> Can anyone help?
>