Error....While creating Folder through SQL

  • hi,

    Below is the script used to create a folder at shared drive.

    When i run the script ( md \\T2ADM01.cvent.net\Output\test) , creating folder

    but when i am doing same task through SQL , its not working neither giving any error

    i am using right credentials.

    ----------------------------------SCRIPT----------------------------------------

    DECLARE @SQLCmd varchar(2000),@acct_num nvarchar(10),

    @share_unc ut_long_description,

    @acct_id int

    SELECT @acct_num = info_value FROM dmp_event_info

    WHERE info_name = 'Admin name'

    --print @acct_num

    set @share_unc = '\\T2ADM01.cvent.net\Output'

    SET @share_unc = @share_unc + '\' + @acct_num

    SET @SQLCmd = 'md ' + @share_unc

    --print @SQLCmd

    EXEC master..xp_cmdshell @SQLCmd , no_output

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Do you get any error messages if you don't use the "no_output" parameter with xp_cmdshell? i.e. run the xp_cmdshell by omitting the no_output parameter.

    It could be that the account the SQL Server service is running under does not have access rights to create the folder...

    Forgot to add - is there any reason to use xp_cmdshell and do this task via SQL? I would prefer to use .SQL CLR instead of xp_cmdshell...

  • Thanks

    Plesse EDIt my above code with your SQL CLR approach

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • The answer for the correct technology depends on the executing process.

    If this should be done by a DBA/Maintenance job on server I would advice xp_cmdshell because it is an available feature in SQL Server. As winash already asked; which error do you get if you call without "no_ouput" option?

    If this shall be done by client-applications I would prefer CLR because xp_cmdshell requires sysadmin privileges.

  • I agree with Florian - xp_cmdshell is useful in situations (like the example given)...

    Bhuvnesh - I only suggested that you could use SQL CLR instead...samples on SQL CLR are available at http://www.codeplex.com/MSFTEngProdSamples/ and that would give you a good starting point from where you could write your own implementation...

  • Thanks for you ALL .. i will try

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • um....could you also let us know what error you see if you execute your xp_cmdshell script without the no_output parameter?

Viewing 7 posts - 1 through 6 (of 6 total)

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