Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error....While creating Folder through SQL Expand / Collapse
Author
Message
Posted Thursday, June 04, 2009 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #728706
Posted Thursday, June 04, 2009 5:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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...



Post #728817
Posted Thursday, June 04, 2009 5:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #728824
Posted Thursday, June 04, 2009 5:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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.



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #728828
Posted Thursday, June 04, 2009 6:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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...




Post #728857
Posted Thursday, June 04, 2009 6:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #728871
Posted Thursday, June 04, 2009 6:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
um....could you also let us know what error you see if you execute your xp_cmdshell script without the no_output parameter?


Post #728878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse