August 8, 2008 at 9:08 am
Hi All
I am using a cmdshell statement to execute a string statement inside a trigger, when i execute the trigger, it says access denied, i am using sa login and XP_cmdshell is enabled on SQL 2005, Have any one came across this issue?? please help me on this??
Cheers
August 8, 2008 at 10:12 am
What did you do in using xp_cmdshell?
I would like to recommend that you separate your trigger and xp_cmdshell to test where the problem is.
Run your xp_cmdshell command and see whether or not you get the error.
August 8, 2008 at 10:18 am
Thanks, i tried doing that, it works perfectly, it only gives error once it is inside a trigger, thats what it confusing me 🙂
August 8, 2008 at 10:23 am
Just a guess, but the service account running SQL Server may not have sufficent priviledges to run xp_cmdshell.
😎
August 8, 2008 at 10:26 am
Can you post the trigger please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 10:48 am
Create TRIGGER BlTri
on Table1
AFTER INSERT, UPDATE, DELETE
AS
Declare @Name varchar(15)
Declare @Age varchar(15)
Declare @sql varchar(255)
select @Name=Name,@Age=age from deleted
union
select @Name=Name,@Age=age from inserted
set @sql ='c:\Bond\Bondcli "tablePKDAO{phone=''' + rtrim(@Name) + ''', age=' + @age + '}"'
exec xp_cmdshell @sql
GO
Cheers
🙂
August 8, 2008 at 10:53 am
you do know that @name and @age are going to be the values from the insert?
August 8, 2008 at 11:02 am
yeap i know, thats the whole point of doing this trigger 🙂
August 8, 2008 at 11:10 am
this might be outside the scope of your question, but what does Bondcli do? can the trigger be written into a CLR instead? i ask because the trigger is likely executing as the user who's doing the update, and if that user does not have rights to the file system, then that could be your problem. the quick fix is to either give rights to all users to the file system (not the best idea) or to execute the trigger as someone else (also not the best idea).
so basically, the question is... what does the executable do that it needs to be fired from within a trigger?
August 8, 2008 at 1:28 pm
Does the SQL server service account have rights to the directory c:\Bond\Bondcli?
Does it have rights to execute tablePKDAO and do whatever that's supposed to do?
What's going to happen if more than one row gets inserted/updated/deleted from this table?
Generally, for this kind of thing I recommend adding a record to a tasks table (or sending a service broker message) and having a job (or trigger stored proc for service broker) pick up and process the record asynchronously. It keeps transactions shorter and reduces blocking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2008 at 12:13 am
HI Lenny
Its a java exe file that clears the cache, i saw permission, i have done this before, i have done this on command prompt before, i need to check my rights, on the server, but sa should have permissions to execute this command, i am sure that the folder is on the local computer where i execute the command and it is not blocked with any previliages
🙂
August 9, 2008 at 12:16 am
Thanks Gial
I tought of this as well, since this is been updated or deleted by applicaitons, it is only done one at a time, but with several threads, so i decided to write a trigger, will this have more load on the database ??, sine i never tried this type of triggers for applications, what do you recon Gial???:)
August 9, 2008 at 1:42 am
CrazyMan (8/9/2008)
HI LennyIts a java exe file that clears the cache, i saw permission, i have done this before, i have done this on command prompt before, i need to check my rights, on the server, but sa should have permissions to execute this command, i am sure that the folder is on the local computer where i execute the command and it is not blocked with any previliages
sa will have privilidges to run xp_cmdshell, but it's not sa's permissions that are the question. (aside, why on earth is an application running as sa in the first place?)
sa is a database account and its rights are solely within SQL Server. When xp_cmdshell runs, it needs a windows account and permissions to the underlying operating system. It uses the SQL Server service account, the account that SQL Server itself runs as. You can see that account if you check the SQL Server configuration manager. It's that account that needs rights to the dir and to run the exe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2008 at 1:55 am
CrazyMan (8/9/2008)
Thanks GialI tought of this as well, since this is been updated or deleted by applicaitons, it is only done one at a time, but with several threads, so i decided to write a trigger, will this have more load on the database ??, sine i never tried this type of triggers for applications, what do you recon Gial???:)
How long does the cache clear take? Does it really have to be flushed after every single insert/update/delete? How often do you insert/update/delete rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2008 at 11:00 am
Thanks Gial
i tried on test server with login that are used by application, it dint work, then i tried to see if sa works, this dosent as well, the cache clearing just takes less than a second, and it really depends, say some 1000 records a day.the problem is this has to be cleared on when the appliction is processing it, else we will have a wrong data. 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply