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 the ReportServer database

I want to move the ReportServer database .mdf and .ldf files from the default directory to another drive (E:)

The ReportServer service is started, but when I run a sp_help_db from SQL Server management studio the ReportServer database is not listed. When I tried to detach it using sp_detach_db , the command reported succes but I'm wondering how this worked when the database is not in the sysdatabases table.

I haven't tried moving the ReportServerTempdb but I imagine that this would be ok, because this is in sysdatabases.

I can't seem to find anything in BOL about moving just the ReportServer database itself , anyone else come across this?

Thanks!

sp_helpdb will only return databases you have access to, so that may be part of it...also won't return databases that are offline and some other things, so not sure if that might be what you are seeing....

As for moving the database files, the RS catalog DB and RSTempDB's are just like any other user database, you can move the data files wherever you like, so long as SQL Server can see them. Note that the ReportServer service does not necessarily have to be running in order to see/move/modify/etc. the RS databases in SQL Server. The ReportServer service is simply a consumer of the SQL Server services to provide data storage in the RS and RSTempDB databases, that's all.

HTH,

|||

Thanks.

I'm using the sa login, so I would expect to be able to see it. There is nothing in sys.master_files for the ReportServer database and it does not show up in the list of databases in the management studio.

I haven't actually configured Reporting Services yet, only installed it. Is it possible that the database is not listed or attached until reporting services is up and running?

Cheers

moving reports to another server

I had plan to move reports from one server (reporting service) to another
server (another reporting service).
Is there any ways to move reports instead of uploading all reports (rdl)
again into another server?
As far as I know, rdl is the xml file.
after uploading to reporting what is really happen on reporting service?
where does the rdl file go after uploading (publishing) ?
ThanksSimple method is to use the deployment configuration screen (ie right click
on the project and properties) change the server name in the URL,it will get
deployed on to the other server. You can have scripts as well. basically when
you upload or deploy it builds and encrypts and stores in the database.
Amarnath
"MS newsgroup" wrote:
> I had plan to move reports from one server (reporting service) to another
> server (another reporting service).
> Is there any ways to move reports instead of uploading all reports (rdl)
> again into another server?
> As far as I know, rdl is the xml file.
> after uploading to reporting what is really happen on reporting service?
> where does the rdl file go after uploading (publishing) ?
> Thanks
>
>|||so, then
I need at least upload or deploy rdl files to reporting service.'
Is it possible to export data (ie data in dbo.Catalog or something
else)from current sql server used for reporting service to the other sql
server which use another reporting service ?
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A0F43E3-90FB-4C2D-A8C9-72EF10F03E89@.microsoft.com...
> Simple method is to use the deployment configuration screen (ie right
> click
> on the project and properties) change the server name in the URL,it will
> get
> deployed on to the other server. You can have scripts as well. basically
> when
> you upload or deploy it builds and encrypts and stores in the database.
> Amarnath
>
> "MS newsgroup" wrote:
>> I had plan to move reports from one server (reporting service) to another
>> server (another reporting service).
>> Is there any ways to move reports instead of uploading all reports (rdl)
>> again into another server?
>> As far as I know, rdl is the xml file.
>> after uploading to reporting what is really happen on reporting service?
>> where does the rdl file go after uploading (publishing) ?
>> Thanks
>>|||Detailed instructions:
http://support.microsoft.com/default.aspx?scid=842425
If you don't have subscriptions and extensive security customization (for
instance certain reports or folders have different rights) then redeploying
is the easiest. Otherwise follow the instructions in the above link.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MS newsgroup" <klee@.jeromegroup.com> wrote in message
news:uMu9xEBBHHA.1224@.TK2MSFTNGP04.phx.gbl...
> so, then
> I need at least upload or deploy rdl files to reporting service.'
> Is it possible to export data (ie data in dbo.Catalog or something
> else)from current sql server used for reporting service to the other sql
> server which use another reporting service ?
>
>
>
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:9A0F43E3-90FB-4C2D-A8C9-72EF10F03E89@.microsoft.com...
>> Simple method is to use the deployment configuration screen (ie right
>> click
>> on the project and properties) change the server name in the URL,it will
>> get
>> deployed on to the other server. You can have scripts as well. basically
>> when
>> you upload or deploy it builds and encrypts and stores in the database.
>> Amarnath
>>
>> "MS newsgroup" wrote:
>> I had plan to move reports from one server (reporting service) to
>> another
>> server (another reporting service).
>> Is there any ways to move reports instead of uploading all reports (rdl)
>> again into another server?
>> As far as I know, rdl is the xml file.
>> after uploading to reporting what is really happen on reporting service?
>> where does the rdl file go after uploading (publishing) ?
>> Thanks
>>
>

Moving Reporting Service Database

Hi, I need to move the both Reporting Service Databases "ReportServer" and
"ReportServerTempDB" to another SQL Server, only the both DB are moved, the
Server with installed RServices remains the same. Is there some dokument
about? Thnakssomething like this?
http://support.microsoft.com/kb/842425
"MV" <MV@.MV.MV> wrote in message
news:OeURCYVRHHA.3316@.TK2MSFTNGP02.phx.gbl...
> Hi, I need to move the both Reporting Service Databases "ReportServer" and
> "ReportServerTempDB" to another SQL Server, only the both DB are moved,
> the Server with installed RServices remains the same. Is there some
> dokument about? Thnaks
>|||Thank you a lot ! I needed exactly this, but how can I find out the password
from the user DOMAIN\MACHINE$ '
this is the user that have RSExecRole on the Database, this user must I give
as parameter to the RSConfig command line tool
Thanks
"Immy" <therealasianbabe@.hotmail.com> schrieb im Newsbeitrag
news:exaCzfVRHHA.3996@.TK2MSFTNGP04.phx.gbl...
> something like this?
> http://support.microsoft.com/kb/842425
> "MV" <MV@.MV.MV> wrote in message
> news:OeURCYVRHHA.3316@.TK2MSFTNGP02.phx.gbl...
>> Hi, I need to move the both Reporting Service Databases "ReportServer"
>> and "ReportServerTempDB" to another SQL Server, only the both DB are
>> moved, the Server with installed RServices remains the same. Is there
>> some dokument about? Thnaks
>|||You will need to either use a domain account or sql account.
If you use a domain account, you need to see your system administrator who
should be able to give you the password.
Sorry - no trick to obtain the password (legally that I know of anyway) :-)
"MV" <MV@.MV.MV> wrote in message
news:Oo81aVeRHHA.2076@.TK2MSFTNGP05.phx.gbl...
> Thank you a lot ! I needed exactly this, but how can I find out the
> password from the user DOMAIN\MACHINE$ '
> this is the user that have RSExecRole on the Database, this user must I
> give as parameter to the RSConfig command line tool
> Thanks
>
> "Immy" <therealasianbabe@.hotmail.com> schrieb im Newsbeitrag
> news:exaCzfVRHHA.3996@.TK2MSFTNGP04.phx.gbl...
>> something like this?
>> http://support.microsoft.com/kb/842425
>> "MV" <MV@.MV.MV> wrote in message
>> news:OeURCYVRHHA.3316@.TK2MSFTNGP02.phx.gbl...
>> Hi, I need to move the both Reporting Service Databases "ReportServer"
>> and "ReportServerTempDB" to another SQL Server, only the both DB are
>> moved, the Server with installed RServices remains the same. Is there
>> some dokument about? Thnaks
>>
>