wrong sentence?! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

wrong sentence?!

Author

Topic

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 09:15:26

Is there something abnormal:

declare @rmdircmd nvarchar(280);
declare @rmdircmd1 nvarchar(280);
set @rmdircmd = N'del \\cgcapp\Report\presence.dbf /q'
set @rmdircmd1 = N'del \\cgcapp\Report\presence.rdf /q'
exec master.dbo.xp_cmdshell @rmdircmd, no_output
exec master.dbo.xp_cmdshell @rmdircmd1, no_output

because the file do not delete.
Thank you

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-02 : 09:20:49

does the file delete correctly if you run the "del" command normally, from a command prompt, on the SQL Server's PC using the account that is running the SQL service ?

- Jeff

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 10:06:37

Yes it works.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 10:27:13

I mean at the command prompt it works fine.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 12:15:40

Someone knowing what's wrong?!

Thanx
Benj

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-02 : 13:51:28

Are you running the SQL script under a login that is not in the SQL Server system administrator role? If so, maybe the proxy account does not have access to that share.

quote:
Originally posted by scrap

Someone knowing what's wrong?!

Thanx
Benj

Codo Ergo Sum

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 14:13:35

Thank you but no, im using sa.
Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-02 : 14:32:50

When you say it works from the command line, is that from the actual SqlServer box's command line? Or your workstation's command line. Is it possible your sql server box can't see \\cgcapp?

Be One with the Optimizer
TG

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 15:23:32

It is ok from the sqls command prompt and well parse in the query analyser. The file can be reach from sql for sure because "presence.dbf" is a table i import every day in sql.
I'll try to troubleshhot again and maybe come back with more information.

Thank you
Benjamin Viel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-02 : 15:57:01

whenever I have issues with xm_cmdshell (or jobs that need access to external resources) the problem always turns out to be permission based. Even when I think I've finally got all the proxy account stuff figured out.

Michael, isn't it possible to set up a sql server so that xp_cmdshell uses a non-sysadmin system account even though you're logged in as sa?

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 18:36:25

Remove the no_output, it might give You hints...

exec master.dbo.xp_cmdshell @rmdircmd~~, no_output~~

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-02 : 23:18:48

Maybe the problem is that the SQL Server service account has access to read data from that directory, but not to delete files.

Another possibility is that someone else has the file open, which would prevent you from deleteing it.

As rockmoose suggested, you should look at the output from the command to see what it is doing and what error message you are getting.

quote:
Originally posted by scrap

It is ok from the sqls command prompt and well parse in the query analyser. The file can be reach from sql for sure because "presence.dbf" is a table i import every day in sql.
I'll try to troubleshhot again and maybe come back with more information.

Thank you
Benjamin Viel

Codo Ergo Sum

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-03 : 07:58:31

Thank you.
I'll have a look and give you a feed back

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-03 : 08:10:50

Here is something interesting:
I removed the no_output and here is the result in the query analyser

****\\cgcapp\Report\presence.dbf********
****Access is denied.*******************
****NULL********************************

Is this access denied to the file?

Thank you all.
Benjamin Viel

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-03 : 15:06:27

So TG how do you set it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-03 : 15:24:03

Ok lets see if I've got this right...

If the account that the SQL Server service is running under cannot access your external resource than xp_cmdshell won't have access to that resource when executed by even by a sysAdmin fixed server role user (sa).

If the user logged in to sql server is NOT a sysAdmin fixed server role, then the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account, is the account that would need access to the remote resource.

If the SQL Server Agent proxy account has not been set, then the call to xp_cmdshell will fail when executed by a role other than sysAdmin fixed server role.

So I guess to make this work for sa, you would need to determine the account Sql Server service runs as, and grant sufficient network rights for that account to delete your file.

To make it work for a non sa, you would need to use xp_sqlagent_proxy_account to establish an account that xp_cmdshell would run under. Then grant appropriate rights for that account to delete your file.

edit:
xp_sqlagent_proxy_account info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-03 : 15:56:56

quote:
Originally posted by jsmith8858

does the file delete correctly if you run the "del" command normally, from a command prompt, on the SQL Server's PC using the account that is running the SQL service ?

- Jeff

quote:
Originally posted by scrap

Yes it works.

quote:
Originally posted by scrap
Here is something interesting:
I removed the no_output and here is the result in the query analyser

****\\cgcapp\Report\presence.dbf********
****Access is denied.*******************
****NULL********************************

Is this access denied to the file?

Thank you all.
Benjamin Viel

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-03 : 16:08:32

I submit to the jury that the defendant has Perjured himself !!! The prosecution will now down a cold one.

Be One with the Optimizer
TG

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-03 : 16:23:25

I'm on it!