MQ Series Broker Service vs SQL Server Trigger

Hello

A rather frustrating trigger problem.
When I insert a record manually or even with an insert command, my trigger works fine and executes a job.

The trigger is supposed to work after a MQ Series broker service has added a row to the table but nothing happens.

Does this sound familiar?

The trigger:

CREATE TRIGGER CHECKFORNEWFILES
ON dbo.MQLog
AFTER INSERT
AS

SELECT 1

SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @.Job_Name AS VARCHAR(50)
DECLARE @.Division AS VARCHAR(50)
DECLARE @.filename AS VARCHAR(50)

-- The bitmask is: power(2,(4-1)) = 8 => ((COLUMNS_UPDATED( )& 8 )> 0)
IF (COLUMNS_UPDATED() & 2 > 0)
BEGIN
UPDATE MQLOG
SET READY = GETDATE() WHERE Updated IS NULL

SELECT @.Division = CASE RTRIM(LTRIM(UPPER(LIBRARY)))
WHEN 'AEB' THEN 'SSS'
WHEN 'AIB' THEN 'ZZZ'
ELSE 'UNKNOWN'
END, @.filename = filename
FROM INSERTED

SELECT @.Job_Name= @.Division + '_' + @.filename
FROM INSERTED

-- Let's execute the job according to the file name which is ready for download.
EXEC msdb..sp_start_job_mq @.Job_Name

UPDATE MQLOG
SET PROCESSED = GETDATE(), UPDATED = 1 WHERE UPDATED IS NULL

END

Many thanks!!

Worf

Changing the AFTER INSERT into FOR INSERT did the trick. Whilst looking in SQL Profiler, we saw an exec sp_execute but not quite an insert, that's why an AFTER INSERT did not work.

regards,

Steve

MQ Series Broker Service vs SQL Server Trigger

Hello

A rather frustrating trigger problem.
When I insert a record manually or even with an insert command, my trigger works fine and executes a job.

The trigger is supposed to work after a MQ Series broker service has added a row to the table but nothing happens.

Does this sound familiar?

The trigger:

CREATE TRIGGER CHECKFORNEWFILES
ON dbo.MQLog
AFTER INSERT
AS

SELECT 1

SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @.Job_Name AS VARCHAR(50)
DECLARE @.Division AS VARCHAR(50)
DECLARE @.filename AS VARCHAR(50)

-- The bitmask is: power(2,(4-1)) = 8 => ((COLUMNS_UPDATED( )& 8 )> 0)
IF (COLUMNS_UPDATED() & 2 > 0)
BEGIN
UPDATE MQLOG
SET READY = GETDATE() WHERE Updated IS NULL

SELECT @.Division = CASE RTRIM(LTRIM(UPPER(LIBRARY)))
WHEN 'AEB' THEN 'SSS'
WHEN 'AIB' THEN 'ZZZ'
ELSE 'UNKNOWN'
END, @.filename = filename
FROM INSERTED

SELECT @.Job_Name= @.Division + '_' + @.filename
FROM INSERTED

-- Let's execute the job according to the file name which is ready for download.
EXEC msdb..sp_start_job_mq @.Job_Name

UPDATE MQLOG
SET PROCESSED = GETDATE(), UPDATED = 1 WHERE UPDATED IS NULL

END

Many thanks!!

Worf

Changing the AFTER INSERT into FOR INSERT did the trick. Whilst looking in SQL Profiler, we saw an exec sp_execute but not quite an insert, that's why an AFTER INSERT did not work.

regards,

Steve

Moving RDF feeds to sql2000

I am trying to grab feeds from craigslist and insert them into a
sql2000 table. I figured bulkload in sqlxml would work. But craislist
uses rss v.1 (aka RDF) instead of rss v2(which I have no problem
inserting). But it looks like RDF is too complex a structure to create
a schema. I scoured the net for most the day trying to figure out how
to import RDF into SQL without any luck. I would like a solution that
can run in a DTS perhaps with ActiveX. Really all I'm looking for is
examples of parsing RSS v.1 (RDF). I've found dozens for RSS v.2. Many
thanks in advance.
You could write your custom shredder using OpenXML in a stored proc.
Best regards
Michael
"MrKnital" <knital@.geocities.com> wrote in message
news:1119137306.602662.108860@.g44g2000cwa.googlegr oups.com...
>I am trying to grab feeds from craigslist and insert them into a
> sql2000 table. I figured bulkload in sqlxml would work. But craislist
> uses rss v.1 (aka RDF) instead of rss v2(which I have no problem
> inserting). But it looks like RDF is too complex a structure to create
> a schema. I scoured the net for most the day trying to figure out how
> to import RDF into SQL without any luck. I would like a solution that
> can run in a DTS perhaps with ActiveX. Really all I'm looking for is
> examples of parsing RSS v.1 (RDF). I've found dozens for RSS v.2. Many
> thanks in advance.
>