master..xp_cmdshell problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

master..xp_cmdshell problem

Author

Topic

warmnfuzzy
Starting Member

10 Posts

Posted - 2005-04-12 : 18:29:43

I am running this stored procedure and it executes fine all the way down to the last part where I am trying to copy the file to a different name. Any suggestions?

DECLARE @szFileName varchar(254),
@szFileName2 varchar(400),
@tsIntDate DATETIME,
@lRetCode integer,
@lFile integer,
@lFileSystemObject integer,
@tsAddDTTM datetime,
@szIntName varchar(30),
@szProcName varchar(20),
@error_con varchar(256)

EXEC @lRetCode = sp_OACreate 'Scripting.FileSystemObject',
@lFileSystemObject OUT
EXEC @lRetCode = sp_OAMethod @lFileSystemObject, 'OpenTextFile', @lFile OUT, @szFileName, 2, 1
--Execute stored procedure
EXEC IMSV7.storedproc @szFileName

--COPY THE FILE
SET @szFileName2 = 'copy "' + @szFileName + '"
"\\testdrive\Drive_N\test.txt" /y'

EXEC master..xp_cmdshell @szFileName2

I never go to sleep or I forget everything!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-12 : 18:31:32

What is the error that you are getting or in what way is it not working? Have you tried printing out @szFileName2 to make sure you are forming a valid DOS command?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-12 : 21:40:43

You're missing a space between "\.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

warmnfuzzy
Starting Member

10 Posts

Posted - 2005-04-13 : 09:19:28

I don't get an error, but it never copies the file. I have tried to PRINT @szFileName2 and it won't do that either. It will print a comment before or after, but not @szFileName2. Thanks!

I never go to sleep or I forget everything!

warmnfuzzy
Starting Member

10 Posts

Posted - 2005-04-13 : 09:20:30

Missing a space between which \ ?

I never go to sleep or I forget everything!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-13 : 12:21:45

I think you are getting a NULL then in one of your variables, so when you go to PRINT it, it doesn't show up since concatenating with a NULL yields a NULL. See the concat yields null option to help troubleshoot this.

Tara

warmnfuzzy
Starting Member

10 Posts

Posted - 2005-04-13 : 12:25:38

I finally figured this out. The date I was trying concatenate on the the file name was never being populated.

You were right. Thanks for your help!

I never go to sleep or I forget everything!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources