Severe SQL Exception from .NET

[Becks25Not]

Hello,

I hava trigger on a table (settings table) that creates a text file after any data changes are made to the table. (The table is not updated requently but several things need to happen when it is). The trigger and everything works if I mahe changes in SQL Manager. But when I execute changes from .NET using a command, I get this error: "A severe error occurred on the current command. The results, if any, should be discarded." It inserts / updates the records so I am guessing it is happening on the trigger. There is nothing in the SQL logs. I have searched but all of the reasons for this did not seem to fit my scenario.

My Trigger:

CREATE TRIGGER [Updated] ON [dbo].[StApplicationSettings] 
FOR INSERT, UPDATE, DELETE 
AS

DECLARE @cmd varchar(200)
SELECT @cmd = 'echo' + Cast(getDATE() as Varchar(50)) +
' > \\Path\SettingsFiles\SettingsUpdate.txt'
EXEC master..xp_cmdshell @cmd, no_output

Any help is greatly appreciated!!
~Becca

 

[andrea96]

Are you using the same ID in .NET that you are using in SQL? If not, does the ID you are using in .NET have permission to execute xp_cmdshell?
Andrea

 

[Becks25Not]

Hi Andrea,

The first time I ran it, I forgot to give execute permissions on xp_cmdshell and it gave me an access denied error. I fixed that and got this error.

Thanks,
Becca

 

[andrea96]

How about permissions to the text file?
Andrea

 

[Becks25Not]

Hi Andrea,

Good idea -- I am admin on both but the application uses a SQL Log On -- which wouldn't have perms to the file.

Thanks!!
Becca

 

[ca8msm]

so I am guessing it is happening on the trigger

First of all, I'd try turning the trigger off just so you can determine where the problem really is. If it works fine then at least you know the problem is in the trigger and you'll have to step through the trigger (I haven't done it before but I believe this is possible in the IDE).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

I think it's only possible in VS 2005.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[Becks25Not]

Hi All,

The trigger is a revision -- it was all working then we added the trigger. I granted the directory / file full perms to everyone to test it and its still failing.

~ Becca

 

[Becks25Not]

I did a new test changing my connection string to Windows NT Security and it worked so the problem must be that the SQL Account cannot authenticate against the network. My problem now is the work around because I have to use a SQL account!!

 

[Becks25Not]

Okay -- I got the service account with the required perms in SQL. I get log in fails using the SQL Client Connection in NET and providing the username and password of the service account. a co-work was able to create it using the MSDASQL Provider but .NET is not letting me use that provider. any ideas?