Discussion Locked

Executing Dos Commands within SQL Script

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to execute a copy command from within a SQL script prior to the rest of the script running. Any assistance would be appreciated.

Thanks

4 replies

by timthetoolman

Hi,

For SQLserver the following was pulled from the help from within the query analyser...

xp_cmdshell
Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative userspermissions to execute xp_cmdshell.

Note When executing xp_cmdshell with the Microsoft? Windows? 95 or Microsoft Windows 98 operating systems, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.

Syntax
xp_cmdshell {'command_string'} [, no_output]

Arguments
'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

no_output

Is an optional parameter executing the given command_string, and does not return any output to the client.

Return Code Values
0 (success) or 1 (failure)

Result Sets
Executing this xp_cmdshell statement returns a directory listing of the current directory.

xp_cmdshell 'dir *.exe'

The rows are returned in an nvarchar(255) column.

Executing this xp_cmdshell statement returns the following result set:

xp_cmdshell 'dir *.exe', NO_OUTPUT

Here is the result:

The command(s) completed successfully.

Cheers,
Tim.

by mgonzales

BACKUP LOG eastern WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:10'
BACKUP DATABASE eastern TO eastern_fullback WITH INIT
WAITFOR DELAY '00:00:10'
DECLARE @xp_cmdshell VARCHAR(255)
xp_cmdshell 'xcopy d:\mssql7\backup\eastern_fullback.bak \\easternbackup\d$\mssql7\backup'
EXEC easternbackup.master.dbo.restore_database_backups

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'xp_cmdshell'.

This discussion is closed to new replies.