dts isqlw slow
-
Hi everyone,
I try to export data from our current server 2000 and import thoses data into another server sql server 7.0. I use the task execute process in dts to export and import the data. This task use isqlw to execute a file for exporting and importing using bcp utility.
I find out that using dts are 3 times slower than using query analyzer (utility) executing the same script for exporting and importing the same data.What i noticed in Entreprise Manager is that dts use the library tcp/ip while query analyzer use name pipe.
My question here is how can we force dts to use name pipe instead ?
Here are the detail of the execute process task :
win32 process: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe
Parameters : -Sserver -Usa -PsaPassword -dmaster -i"c:\acdv_bcp.sql" -o"c:\acdv_bcp.out"
And here are the code of the script acdv_bcp.sql :
use test
go
DECLARE c_table insensitive CURSOR FOR
SELECT name, user_name(uid)
FROM sysobjects
WHERE type IN ('U') AND name != 'dtproperties' AND id IN
(SELECT id FROM sysindexes
WHERE indid IN (1, 0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
AND rowcnt != 0)
DECLARE @Tname varchar(60)
DECLARE @Uname varchar(60)
DECLARE @stmt varchar(255)
OPEN c_table FETCH c_table INTO @Tname, @Uname
WHILE (@@fetch_status != - 1)
BEGIN
IF (@Uname = 'dbo')
BEGIN
SELECT @stmt = 'bcp.exe test.' + @Uname + '.' + @Tname + ' out c:\sqsdata\' + @Uname + '_' + @Tname + '.bcp -n -b1000 -Sserver -U test -P test'
EXEC master..xp_cmdshell @stmt
SELECT @stmt = 'bcp.exe acdv.' + @Uname + '.' + @Tname + ' in c:\sqsdata\' + @Uname + '_' + @Tname + '.bcp -n -b1000 -Sserver3 -U acdv -P acdv'
EXEC master..xp_cmdshell @stmt
END
ELSE
BEGIN
SELECT @stmt = 'bcp.exe test.' + @Uname + '.' + @Tname + ' out c:\sqsdata\' + @Uname + '_' + @Tname + '.bcp -n -b1000 -Sserver -U' + @Uname + ' -P' + @Uname
EXEC master..xp_cmdshell @stmt
SELECT @stmt = 'bcp.exe acdv.' + @Uname + '.' + @Tname + ' in c:\sqsdata\' + @Uname + '_' + @Tname + '.bcp -n -b1000 -Sserver3 -U' + @Uname + ' -P' + @Uname
EXEC master..xp_cmdshell @stmt
END
FETCH c_table INTO @Tname, @Uname
END
CLOSE c_table
DEALLOCATE c_table
go
Hope that somebody can give me a hint or suggestion on this. Best regards, Tran
-
[Edit] ahh, nevermind, just noticed the cross-server requirement. Mental note to self, read entire post before replying.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply