View Thread

HellBound Hackers | Computer General | Webmasters Lounge

Author

Yet another SQL Question...

zumo
Member

Posts: 20
Location:
Joined: 12.11.06
Rank: Moderate

Posted on 13-06-08 17:51

i have a clr stored procedure written in c# that starts a thread to call a certain command asynchronously. the command is given as a parameter like so:

exec sp_processor [insert command]

now this works perfectly when run manually. smooth and accomplishes the asynchronous behavior i want. however when i put it into my trigger code it never runs although the trigger fires perfectly.

here's the trigger code



ALTER TRIGGER [dbo].[trig_newRow] ON [dbo].[TestTable]

AFTER INSERT

AS

DECLARE @row int

DECLARE @cmd varchar(50)

SET @row = (SELECT TestIndex FROM INSERTED)

SET @cmd = 'exec sp_initiateJob '+CONVERT(VARCHAR(20), @row)



PRINT 'Command:'+@cmd

PRINT 'Trigger fired...'

EXEC sp_processor [@cmd]

PRINT 'sp_processor shoulda fired...

the print statements are for debugging and all the print statements are called fine.. also i put in other stored procedures to execute even another written in c# and those run perfectly.

as long as i'm here i might as well post the c# code so you can tell me if there is a problem with that. here it is:



using System;

using System.Threading;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;



public class FileTrigger

{

    private static SqlConnection cnn;

    private static string command;



    [SqlProcedure]

    public static void processRow(string cmd)

    {

            try

            {

                cnn = new SqlConnection(getConnectionString());

                cnn.Open();

                command = cmd;

                Thread processor = new Thread(process);

                processor.Start();

            }

            catch

            {

                getConnectionString();

            }

    }



    private static void process()

    {

        SqlCommand cmd = new SqlCommand(command, cnn);

        cmd.ExecuteNonQuery();

        cnn.Close();

    }



    private static string getConnectionString()

    {

        return "Data Source=xxx.xxx.x.xx;Initial Catalog=xxxxxxx; User ID=xxxxxxx; Password=xxxx";

    }

}

so why will this run manually like a charm but right when put into the trigger it doesn't run.. Thanks in advance.

Jake

Edited by zumo on 13-06-08 17:53

Author

RE: Yet another SQL Question...

Zarray
Member

Posts: 96
Location:
Joined: 12.08.06
Rank: Monster

Posted on 19-06-08 18:36

have you checked the permissions?

WiFi – Part 3, Aircr...

WiFi – Part 2, Proto...

WiFi – Part 1, Inter...

WiFi – Part 0, Intro...

Info Gathering with ...

Mapping Memory...

Javascript 16 *may c...

Advanced DLL Injecti...

Tweaking Firefox Plu...

An Intoduction to Ga...

Positional Number Sy...

Dream Interpretation...

Freewebhosts and sub...

The Dunning-Kruger E...

Me and my router...

HellBound Hackers | Computer General | Webmasters Lounge

Yet another SQL Question...

RE: Yet another SQL Question...