Post reply

XP_CMDSHELL Syntax Problem

  • November 13, 2009 at 12:29 pm

    #206462

    I'm trying to put together a procedure to script out SPs and Table definitions using the following code:

    set @code = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\scptxfr.exe" /s MyServer ' + ' /d MyDatabase /I /F '

    + '\\Server\Users\MyFolder\Object_Scripts\SP_Table_structure_'+ convert(varchar(8),getdate(),112) + '' + ' /q /A /r'

    EXEC master..xp_cmdshell @code

    If I paste the above command "C:\Program Files\Microsoft SQL Server....(varchar(8),getdate(),112) + '' + ' /q /A /r'

    into the command prompt on the server and run it manually, it works fine and creates the folder: SP_Table_structure_YYYYMMDD and the files I'm expecting.

    If I run the code in SQL, I get the error:

    Description: [SQL-DMO]Cannot set \\Server\Users\MyFolder\Object_Scripts\SP_Table_structure_20091113 as the current directory.

    Any idea what I'm doing wrong in the SQL code ?

  • November 13, 2009 at 1:28 pm

    #1079237

    xp_cmdshell opens a new console using the Windows account used by SQL Server service. Your Windows account has access to that remote folder; the SQL Server service account may not.

  • November 13, 2009 at 1:33 pm

    #1079242

    I would agree, this kind of feels like a security context problem..

    CEWII

  • November 13, 2009 at 3:24 pm

    #1079288

    Thanks ! That was it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply