A trigger that has been added by someone else before, also I've done on, modify, I get the following error.

Question

A trigger that has been added by someone else before, also I've done on, modify, I get the following error.

Lsuersoy

SSChasing Mays

Points: 632
More actions
April 9, 2012 at 3:18 am

#253757

Hi ,
A trigger that has been added by someone else before, also I've done on, modify, I get the following error.
The error is as follows:
Attempt to retrieve data for object failed for Server (name of server) (Microsoft.SQLServer.SMo)
Additional Information:
The database (database name) does not exist on the server. (Microsoft.SQLServer.SMo)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well it sounds like there is a few things going wrong here; is this a CLR trigger or a regular TSQL trigger? a TSQL trigger should not be going to the command shell and running an SMO script; if something needs to occur based on a insert/update/delete event, that really needs to be put into something asyncronous, like a service broker call.

a database name not found? you have the code, is it looking for a specific database, or looping thru all databases?

if it's looping, then a database names might need to be quoted; [Prod-Copy] for example instead of Prod-copy

I cannot think of a reason offhand that a trigger needs to go to MSO, if you can show us the code, we can offer some suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

lsuersoy (4/9/2012)
Hi ,
A trigger that has been added by someone else before, also I've done on, modify, I get the following error.
The error is as follows:
Attempt to retrieve data for object failed for Server (name of server) (Microsoft.SQLServer.SMo)
Additional Information:
The database (database name) does not exist on the server. (Microsoft.SQLServer.SMo)

It sounds like you are getting the error when you are trying to script out the trigger from SSMS (right-clicking on it and selecting Modify). If this is true what happens when you do:

SELECT

SM.definition

FROM

sys.triggers AS T

JOIN sys.sql_modules AS SM

ON T.object_id = SM.object_id

WHERE

T.name = 'trigger_name'

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question