SQL Server uses xp_cmdshell to prompt "not an internal or external command, or a program or batch file that can be run"

After data migration, using SQL Server 2012 to back up the database, after the completion of the backup is automatically compressed into RAR files, the previous execution of the statement can only do a backup, but not automatically compress and delete the backup, indicating that the problem occurs after performing a backup compression. Copy the statement to Query Analyzer to perform an error, prompting "RAR." EXE is not an internal or external command, or a program or batch file that can be run. "

The SQL statements are as follows:

/* Automatically compress the file and delete the original file after compression is complete */

DECLARE @sqlPathB varchar (---) Data path

Set @sqlPathB = ' RAR. EXE a-dw-ep E:\EFBackup\EFNETSYS\EFNETSYS ' +rtrim (CONVERT (varchar), GETDATE (),) + '. rar '--Create compressed files and store paths,- DF or-DW means that the original file is deleted after compression, and the-EP table is compressed without a path inside the package

+ ' E:\EFBackup\EFNETSYS\EFNETSYS ' +rtrim (CONVERT (varchar), GETDATE (), ()) + ' 0000.bak '--Take compressed files and paths

EXEC xp_cmdshell @sqlPathB

First, check the environment variables.

Automatic compression calls WinRAR's Rar.exe, my winrar is installed on the D drive, the above SQL statement is executed using Rar.exe in the WinRAR installation directory, so the Winrra installation path must be added to the environment variable. The check found that environment variables were forgotten because of the database migration. Add in the following order (for example, Win Server 2008):

1, right-click on the desktop "computer", find "Properties", click "Advanced System Settings", bring Up the "System Properties" dialog box, and find "Environment variables"

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/89/CF/wKiom1gdZ5vSY6JdAAAj1uoWpVI792.png-wh_500x0-wm_3 -wmp_4-s_3773318046.png "title=" 1.png "alt=" Wkiom1gdz5vsy6jdaaaj1uowpvi792.png-wh_50 "/>

2, click "Environment variable", in "system variables", find and select the "Path" variable, click "Edit" to open the "Edit System Variables" dialog box

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/89/CF/wKiom1gdZ-aDqq0VAAAjxH0sQqM835.png "title=" 2.png " alt= "Wkiom1gdz-adqq0vaaajxh0sqqm835.png"/>

3, in the Open dialog box, put WinRAR installation path at the end of the "variable value", I here is D:\Program Files\winrar, here to note, the new placement of the path, and other paths to be separated by half-width semicolon.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/89/CC/wKioL1gdaDngSNYMAAAKryGEFg8345.png "title=" 3.png " alt= "Wkiol1gdadngsnymaaakrygefg8345.png"/>

4, after the addition, all the way to determine the return can be.

5, test is normal, in cmd, execute "rar.exe", enter, window List command usage, description set success.

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/89/CC/wKioL1gdam6B7aFOAAA8tRP7fbs548.png-wh_500x0-wm_3 -wmp_4-s_4039818638.png "title=" 4.png "alt=" Wkiol1gdam6b7afoaaa8trp7fbs548.png-wh_50 "/>

After the setup is complete, should be able to use normally, who knows, after executing the SQL statement still can not compress normally, the problem is still not resolved, since the DOS command window Rar.exe can execute normally, the problem is not related to the compression command, continue to find the reason.

Second, check the SQL Server settings.

Because just calling the xp_cmdshell component in SQL, now Rar.exe is working in the DOS command window, now we have to start looking for the reason from SQL Server. Because database compression backups are performed automatically using SQL Server Agent, here's where to start:

1. Open SQL Server Configuration Manager, locate the SQL Server service, and view the SQL Server Agent service

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/89/CD/wKioL1gdbh6AqMfXAABROUvx-1k026.png "title=" 5.png " alt= "Wkiol1gdbh6aqmfxaabrouvx-1k026.png"/>2, suddenly discovers that the login identity of SQL Server Agent is LocalService.

Think of a time before also because the login identity causes SQL use problem, later changed to Localsyste can be used normally, suspect that the login identity is insufficient, resulting in the inability to call rar.exe caused by normal.

3. Modify the login identity of SQL Server Agent to LocalSystem.

Right-click on the SQL Agent service, select Properties, open the SQL Agent Properties dialog box, and in the login as a built-in account, next to the menu, the local service will be more local System, after clicking OK.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/89/CD/wKioL1gdb27CmLtXAAAidpGUdXY880.png-wh_500x0-wm_3 -wmp_4-s_549378215.png "title=" 6.png "alt=" Wkiol1gdb27cmltxaaaidpgudxy880.png-wh_50 "/>

The system prompts for the account change, the SQL Agent service restarts, the click is restarts the service.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/89/CF/wKiom1gdcDHwRpZyAAAOire3fmE429.png "title=" 8.png " alt= "Wkiom1gdcdhwrpzyaaaoire3fme429.png"/>

Once the settings are complete, execute the SQL statement again to find that the compression was successful.

Summarize:

xp_cmdshell when executing the cmd command, in addition to properly configuring the necessary environment variables, you also give the required permissions that are involved in the execution of the command.

SQL Server uses xp_cmdshell to prompt "not an internal or external command, or a program or batch file that can be run"