June 4, 2009 at 12:56 am
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;-)
June 4, 2009 at 5:12 am
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...
June 4, 2009 at 5:21 am
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;-)
June 4, 2009 at 5:32 am
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.
June 4, 2009 at 6:05 am
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...
June 4, 2009 at 6:17 am
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;-)
June 4, 2009 at 6:27 am
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