Here I go again

Again am in a kinda mess ... am shifting from an old server to a new server with lots of disk space ... have migrated the jobs ... backup and restored the db's ... everything seems going fine until I come up to the Linked Servers ... there is one linked server that has been there for a long long time and nobody seems to know the remote login and password for that ...

Can I simply move over the records for that server from sysservers and sysxlogins ...

Is it advisable ?

Are there any more tables I need to look into ...I don't know if I'd mess with the system tables...

How long does it take to build the new box...everything all scripted out?

Doing a dump and restore ?

How many linked servers do you have?

Is any process even using the server?

As part of this migration did you do any housekeeping (like getting rid of unused links)?|||And are you in the office?

What the hell time is it over there?|||Can you clone the user on the remote system, with a new password? To me that would be easier. I have not played with linked servers, since they are such a pain to use. Especially when you try to move code from a QA server to a prod server. Too easy to hard code names in there.|||Yup .. I am in the office .. time is just 3:21 AM ...

And yes .. the linked server is being used for a table that is being transferred daily at 7 AM ...

Right now ... nobody is around who can provide me with the password ...

so any suggestions ?|||-Usa -Pblank?

-Usa -PServernme

-Usa -PBoss's daughters name

I'm suprised they're not all the same....

Do you know the other ones? Di d you try any of those?|||The Linked server is at a different location ... and its a oracle database ...

The userid is system as i can make out from sysxlogins ...
password :(|||Wake them up!|||Originally posted by blindman
Wake them up!

Certainly more helpful then my suggestions...

Is this new box have to ready for the 7:00am dump?

Or is the old box still handling the load?

In other words did you cut over already?|||There may be a way, but it is very dangerous. I hope you have admin access to the SQL Server you are dealing with?

Step one

select password from sysxlogins where name = 'remote user's name'
copy this result to the next query

On the server you want to work

exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysxlogins
set password = 0x130270E06743495DE22414DAFA0CAA12
where name = 'Remote User's name' -- <-- This is EXTREMELY IMPORTANT
go
exec sp_configure 'allow updates', 0
reconfigure with override

Forgive me for being a little emphatic about the where clause on that update query, but I know how I get at 3:00 in the morning.

This depends on you being able to add the login in the first place, of course.|||I'm LOL...mostly out of fear...

TIMBER

I'm going to go home and slip in to a margaritta...

Good Luck Enigma...I'll logon from there to see how it's going...|||I agree, Brett. I would be nervous doing this myself, but it may be that Enigma has little to no choice. Unless he is able to wake up one of the guys he is workinig with. Hmm. 3 AM...Hong Kong, maybe?|||Hong Kong? No, Enigma is what we Yanks call a "Red Coat".|||Did the following :
Created a linked server from newserver to oldserver ...

exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysxlogins
set password = (select distinct password from sysxlogins where lname ='SYSTEM')
where name = 'System'
go
exec sp_configure 'allow updates', 0
reconfigure with override

Didnt work out :(

OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].|||By the way, Enigma. Did you take your username from the famous code machine deciphered by Turing at Bletchley Park during WWII? Or are you just hard to figure out?|||Yup ... saw the movie once ...

Have been using this nick for a long time though ... now i dont even remember where i got this from :D|||Sorry to hear it did not work out. So you must have missed the 7 AM load. Are any of the regulars there able to help you clone the user with a known password, or get the old password, then?|||Will get the password only on monday ... when I get to talk to the client IT guy ...

Till then .. Only thing I can do is Enjoy !!! :)

Bring on the Whiskeys :D|||You can try to restore MASTER database from the old server to your new one under a different name of course, and then instead of creating a linked server/remote login on the new one - just insert...select from master_old.dbo...|||...make sure you do that for sysservers as well as sysxlogins. Of course the local login should exist before you do that.|||I have not tried that in a while, rdjabarov, but I remember it complained that master can only be restored as "master". I guess that keeps passwords safer, in case someone happens to steal a backup of your master database. But, like I say, it has been a long time since I tried that.|||Originally posted by blindman
(...) famous code machine deciphered by Turing at Bletchley Park during WWII? (...)
Just to be clear - historicians have problems to agree who broke Enigma code. Some historians say three Polish mathematicians - Marian Rejewski, Jerzy Rozycki and Henryk Zygalski - broke the Enigma code in 1933. In July 1939, just before the war, Poland gave Britain and France replicas of the Enigma encoding machine, helping the Allies decipher secret Nazi messages. According to British accounts, British cryptographers cracked the code a year before war broke out.

Movies aren't best source for infos like this.|||http://www.pbs.org/wgbh/nova/decoding/enigma.html|||Originally posted by Brett Kaiser
http://www.pbs.org/wgbh/nova/decoding/enigma.html

http://www.pan.net/history/enigma/|||Originally posted by MCrowley
I have not tried that in a while, rdjabarov, but I remember it complained that master can only be restored as "master". I guess that keeps passwords safer, in case someone happens to steal a backup of your master database. But, like I say, it has been a long time since I tried that.

You must have been trying to do something...Enigmatic...because it works like a charm. Do yourself a favor and try it :cool:|||Originally posted by MST78
Just to be clear - historicians have problems to agree who broke Enigma code. Some historians say three Polish mathematicians - Marian Rejewski, Jerzy Rozycki and Henryk Zygalski - broke the Enigma code in 1933. In July 1939, just before the war, Poland gave Britain and France replicas of the Enigma encoding machine, helping the Allies decipher secret Nazi messages. According to British accounts, British cryptographers cracked the code a year before war broke out.

Movies aren't best source for infos like this.

well ... now i remember ... i kept the nick when I broke the Enigma code ... how could I forget that !!! :D|||Score one for rdjabarov. You can restore a clone of the master database. I wonder what version I tried that on last...|||Originally posted by MCrowley
Score one for rdjabarov. You can restore a clone of the master database. I wonder what version I tried that on last...

Well ... Its been a long long day again and what i find is that even that does not work ... I mean .. I can restore master backup as another database ... but it seems that the passwords for the linked server are encrypted using some algo that takes the machine config... or something like that into account ..

Tara (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6645) at www.sqlteam.com provided me with this code to transfer logins from one server to another .. but ... this code does not work for linked servers .. probably because sp_addlinkedsrvlogin uses the function encrypt to encrypt the passwords and sp_addlogin uses pwdencrypt ... and there seems to be a lot of diff between the two ...



CREATE PROCEDURE isp_Transfer_Logins
AS

SET NOCOUNT ON

DECLARE @.login sysname
DECLARE @.pwd sysname
DECLARE @.new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN DTS.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @.login, @.pwd

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- If the login does not exist on the destination server, then add it.
IF ((SELECT count(*) FROM SDDEVSQL1.master.dbo.syslogins WHERE name = @.login) = 0)
BEGIN
EXEC SDDEVSQL1.master.dbo.sp_addlogin @.loginame = @.login, @.passwd = @.pwd, @.encryptopt = skip_encryption, @.defdb = 'QTRACS'
END

-- If the login does exist on the destination server, then synchronize the password.
ELSE
BEGIN
EXEC SDDEVSQL1.master.dbo.sp_droplogin @.login
EXEC SDDEVSQL1.master.dbo.sp_addlogin @.loginame = @.login, @.passwd = @.pwd, @.encryptopt = skip_encryption, @.defdb = 'QTRACS'
END

FETCH cur_Users INTO @.login, @.pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

RETURN
GO|||Tara?

Who's she?|||Originally posted by Brett Kaiser
Tara?

Who's she?

I dont know ... maybe someone else would be able to answer ...maybe some body called X002548 (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198)|||Originally posted by Enigma
well ... now i remember ... i kept the nick when I broke the Enigma code ... how could I forget that !!! :D
Daddy?? ;)|||My Son !!!! where were you till now ;D|||You guys are WEIRDING ME OUT!

Help: CHECKDB gives errors I can't fix

Hi,
I've got what appears to be a corrupt database, (SQL 7 SP4) running CHECKDB on it returns lots of errors, the first being:
Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch page
(1:177936) with latch type SH. PFS failed.
I switched the database to single user, and tried various repair flags on CHECKDB, and I always seem to get the same errors, and it returns lots of:
'The repair level on the DBCC statement caused this repair to be bypassed.'
Any ideas on where to start on this.. Its a customers database, I don't have information on what backups they have etc.. and its probably problems in the only large table in the database (some 2.5 million records, and 900Mb on disk, with 400Mb index).. Could this be a resource related problem somehow 'Hopefully you get some specific suggestions about your message. Below you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a case with
MS might be a better option than start with the restore route (also search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch
page
> (1:177936) with latch type SH. PFS failed.
> I switched the database to single user, and tried various repair flags on
CHECKDB, and I always seem to get the same errors, and it returns lots of:
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
> Any ideas on where to start on this.. Its a customers database, I don't
have information on what backups they have etc.. and its probably problems
in the only large table in the database (some 2.5 million records, and 900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hi Kevin,
I think that this is probably a corruption in the database. This error =message indicates that DBCC was not able to read (or latch) page 177936 =in your customer's database. The page type in question (PFS) is a =system allocation page that tracks database free space and some other =state information. This page is actually used to drive the DBCC scan, =so an error here is pretty severe. This is made worse by the fact that =DBCC can't repair this type of page if there is a problem.
Without more error context it's hard for me to say why we can't read =this page: it could be hardware causing a bad read from disk, a bad =page header that resulted from a page corruption, etc.
I completely agree with Tibor that you should restore the database, =perhaps pairing that with a case with PSS to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:einCq2YvDHA.2712@.tk2msftngp13.phx.gbl...
Hopefully you get some specific suggestions about your message. Below =you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a =case with
MS might be a better option than start with the restore route (also =search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books =Online and
KB for the error numbers that CHECKDB gives you. There might be =specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to =happen
again! If the database is suspect, the file might have been in use by =for
instance an anti-virus program and restarting SQL Server might be all =that
is needed - but you still want to read logs etc to find out what =happened.
3. If there is a hardware problem, ensure the faulty hardware is =replaced.
4. Backup the log. This assumes that log backup schedule is in place, =of
course. If the database is suspect, then the NO_TRUNCATE option for =the
RESTORE command must be used. Also, you might want to do a file backup =of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log =as per
step 4, then you will most probably have zero dataloss. You should =restore
the latest clean database backup and the subsequent log backups =including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be =a
secondary option but this will often result in loss of data. =Additional
solutions, depending on the errors, may be to manually rebuild =non-clustered
indexes, manually drop and reload a table if the data is static, and =so on.
If the database is suspect, a secondary option can be to try to ="un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, =google
etc). It might help but if the database is too damaged, it might just =pop
back to suspect again. There's also something called "emergency mode" =which
is a "panic" status you can set in order to try to get data out of a =damaged
database. I think the name of that option speaks for itself. Again =search
the net for info.
If you feel uncertain with above steps, I recommend letting MS =hand-hold you
through the steps appropriate for your particular situation.
-- Tibor Karaszi, SQL Server MVP
Archive at:
=http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
>
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
>
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and =latch
page
> (1:177936) with latch type SH. PFS failed.
>
> I switched the database to single user, and tried various repair =flags on
CHECKDB, and I always seem to get the same errors, and it returns lots =of:
>
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
>
> Any ideas on where to start on this.. Its a customers database, I =don't
have information on what backups they have etc.. and its probably =problems
in the only large table in the database (some 2.5 million records, and =900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>
>
>
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Kevin,
I think that this is probably a =corruption in the database. This error message indicates that DBCC was not =able to read (or latch) page 177936 in your customer's database. The page =type in question (PFS) is a system allocation page that tracks database free =space and some other state information. This page is actually used to drive =the DBCC scan, so an error here is pretty severe. This is made worse by the =fact that DBCC can't repair this type of page if there is a =problem.
Without more error context it's =hard for me to say why we can't read this page: it could be hardware causing a =bad read from disk, a bad page header that resulted from a page corruption, etc.
I completely agree with Tibor =that you should restore the database, perhaps pairing that with a case with PSS =to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" wrote in message news:einCq2YvDHA.2712=@.tk2msftngp13.phx.gbl...Hopefully you get some specific suggestions about your message. Below =youfind my general recommendations. Note, however, that this *might* =notactually be a corrupt database (I can't say for sure) so opening a case withMS =might be a better option than start with the restore route (also searchKb =etc first):Here are the general recommendations for handling a =suspect or corruptdatabase:0. Ensure you have a backup strategy that =you can use to recover fromhardware failures (including corruption). I =recommend performing bothdatabase and log backup in most =situations.1. If you can run DBCC CHECKDB against the database: Search Books Online =andKB for the error numbers that CHECKDB gives you. There might be =specificinfo for that type of error.2. Find out why this happened. Check =eventlog, do HW diagnostics etc.;search Books Online and KB for those =errors. You don't want this to happenagain! If the database is suspect, the =file might have been in use by forinstance an anti-virus program and =restarting SQL Server might be all thatis needed - but you still want to read =logs etc to find out what happened.3. If there is a hardware problem, =ensure the faulty hardware is replaced.4. Backup the log. This assumes =that log backup schedule is in place, ofcourse. If the database is suspect, =then the NO_TRUNCATE option for theRESTORE command must be used. Also, =you might want to do a file backup ofthe mdf and ldf files, for extra safety.5. Restore is the best thing to do now. If you managed =to backup log as perstep 4, then you will most probably have zero =dataloss. You should restorethe latest clean database backup and the =subsequent log backups includingthe one taken in above step.If the database =isn't suspect, then DBCC with a REPAIR option might be asecondary option =but this will often result in loss of data. Additionalsolutions, =depending on the errors, may be to manually rebuild non-clusteredindexes, =manually drop and reload a table if the data is static, and so on.If the =database is suspect, a secondary option can be to try to "un-suspect"the =database using sp_resetstatus. Read about it (books online, KB, googleetc). =It might help but if the database is too damaged, it might just =popback to suspect again. There's also something called "emergency mode" =whichis a "panic" status you can set in order to try to get data out of a damageddatabase. I think the name of that option speaks for =itself. Again searchthe net for info.If you feel uncertain with =above steps, I recommend letting MS hand-hold youthrough the steps appropriate =for your particular situation.-- Tibor Karaszi, SQL Server =MVPArchive at:http://groups.google.com/groups?oi=3Ddjq&as_ugrou=p=3Dmicrosoft.public.sqlserver"Kevin" wrote in messagenews:BC4=EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...> Hi,>> I've got what appears to be a corrupt database, =(SQL 7 SP4) runningCHECKDB on it returns lots of errors, the first being:>> Msg 8966, Level 16, State 5, Server 01AW01, =Procedure , Line 3> [Microsoft][ODBC SQL Server Driver][SQL Server]Could =not read and latchpage> (1:177936) with latch type SH. PFS failed.>> I switched the database to single user, and =tried various repair flags onCHECKDB, and I always seem to get the same =errors, and it returns lots of:>> 'The repair level on the DBCC statement caused this repair to bebypassed.'>> Any =ideas on where to start on this.. Its a customers database, I =don'thave information on what backups they have etc.. and its probably problemsin the only large table in the database (some 2.5 million =records, and 900Mbon disk, with 400Mb index).. Could this be a =resource related problemsomehow '>>>>

---

--=_NextPart_000_0058_01C3BD8A.EDA4E890--

Help: Bulk insert with ADO.NET from outside database

I am programmatically importing lots of rows from dBase 5 into SQL and
want to get an idea what the optimal approach would be for this
scenario.
Currently I'm doing the following:
1) Using OleDbConnection, OleDbCommand and OleDbDataReader to read the
rows from dBase tables;
2) Putting the extracted data into a DataSet;
3) Once the number of rows in the DataSet reaches, say, 1000, I'm
using SqlDataAdapter to submit the data to the database. This step
creates a transaction, issues an INSERT statement for each row in the
DataSet, then commits the transaction.
4) Go to Step 1 and repeat until all data is processes.
The above is inefficient and slow even if the both database
connections are kept open during the whole batch process. For example,
50,000 rows get processed in around 30 seconds.
On the other hand, if I use Import and Export Wizard, the same task
(dBase -> SQL) takes less than 5 seconds.
Out of curiosity, I used SQL Profiler to trace the Import and Export
Wizard to see what it's doing. The last statement in the log is as
follows:
insert bulk [DatabaseName].[dbo].[TableName]
(
"Field_1" float,
"Field_2" float,
"Field_3" nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
..
(other table fields here)
..
)
with
(
check_constraints
)
As you can see there is no FROM clause. Also, it's "insert bulk", not
widely-known "bulk insert". (?)
My goal is to achieve the same (or similar speed) with ADO.NET, if
possible. But how?
Also, DTS is not an option, because the tables I'm dealing with change
from batch to batch (different table names, not schema)
Thanks!That performance is not achievable in ADO.NET. The problem is that the 1000
rows that you are updating are still updating 1 row at a time. So 1000
individual calls to the database.
The only way to increase that is to use DTS (or you could query the dBase
files, store as text file, and use BCP, Bulk Insert, Etc.). If you need thi
s
to happen in /Net code, you can create the DTS package and then call it from
your .Net application via COM Interop.
HTH,
John Scragg
"Usenet User" wrote:

> I am programmatically importing lots of rows from dBase 5 into SQL and
> want to get an idea what the optimal approach would be for this
> scenario.
> Currently I'm doing the following:
> 1) Using OleDbConnection, OleDbCommand and OleDbDataReader to read the
> rows from dBase tables;
> 2) Putting the extracted data into a DataSet;
> 3) Once the number of rows in the DataSet reaches, say, 1000, I'm
> using SqlDataAdapter to submit the data to the database. This step
> creates a transaction, issues an INSERT statement for each row in the
> DataSet, then commits the transaction.
> 4) Go to Step 1 and repeat until all data is processes.
> The above is inefficient and slow even if the both database
> connections are kept open during the whole batch process. For example,
> 50,000 rows get processed in around 30 seconds.
> On the other hand, if I use Import and Export Wizard, the same task
> (dBase -> SQL) takes less than 5 seconds.
> Out of curiosity, I used SQL Profiler to trace the Import and Export
> Wizard to see what it's doing. The last statement in the log is as
> follows:
> insert bulk [DatabaseName].[dbo].[TableName]
> (
> "Field_1" float,
> "Field_2" float,
> "Field_3" nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
> ...
> (other table fields here)
> ...
> )
> with
> (
> check_constraints
> )
> As you can see there is no FROM clause. Also, it's "insert bulk", not
> widely-known "bulk insert". (?)
> My goal is to achieve the same (or similar speed) with ADO.NET, if
> possible. But how?
> Also, DTS is not an option, because the tables I'm dealing with change
> from batch to batch (different table names, not schema)
> Thanks!
>|||John,
Thanks for responding.
Calling a saved DTS from within .NET is not an option, because, as I
mentioned, it must be dynamic because table names differ for every
batch. I suppose, I could re-create the inner guts of the package in
my code using DTS object model but then the program will have to run
on the SQL server, otherwise that model won't be available. In
addition, this would be mean stepping away from ADO.NET completely.
Instead I tried yesterday the same thing you mentioned: export the
data into a temp file with OleDbDataReader and then use SqlCommand to
import the file with BULK INSERT. Worked like a charm: 3.5 sec to
export and 3.5 sec to import - 7 seconds total.
:)
On Mon, 17 Oct 2005 18:37:02 -0700, "John Scragg"
<JohnScragg@.discussions.microsoft.com> wrote:
>That performance is not achievable in ADO.NET. The problem is that the 100
0
>rows that you are updating are still updating 1 row at a time. So 1000
>individual calls to the database.
>The only way to increase that is to use DTS (or you could query the dBase
>files, store as text file, and use BCP, Bulk Insert, Etc.). If you need th
is
>to happen in /Net code, you can create the DTS package and then call it fro
m
>your .Net application via COM Interop.
>HTH,
>John Scragg
>"Usenet User" wrote:
>