Can't generate script for Triggers using DMO

For some reason, when I try to generate a script for Triggers off of a View
using DMO, I get back garbage like:
?VIEW1
That's the complete text coming back, nothing more. I can see it clearly
using MSEM just fine. Any thoughts?
Lee
Can you post your code, so that we can look into it?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
For some reason, when I try to generate a script for Triggers off of a View
using DMO, I get back garbage like:
?VIEW1
That's the complete text coming back, nothing more. I can see it clearly
using MSEM just fine. Any thoughts?
Lee
|||You mean scripting an INSTEAD OF TRIGGER on a VIEW?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
> For some reason, when I try to generate a script for Triggers off of a
> View using DMO, I get back garbage like:
> ?VIEW1
> That's the complete text coming back, nothing more. I can see it clearly
> using MSEM just fine. Any thoughts?
> --
> Lee
>
|||This works for me:
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
go
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
go
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
go
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
go
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
go
using System;
using System.Runtime.InteropServices;
using SQLDMO;
namespace SDN
{
class ScriptInsteadOfTrigger
{
[MTAThread]
static void Main(string[] args)
{
try
{
SQLServer2Class server = new SQLServer2Class();
server.LoginSecure = true;
server.Connect("(local)\\sql80", null, null);
Database2 database = (Database2) server.Databases.Item("testdb", "dbo");
View2 view = (View2) database.Views.Item("Employee", "dbo");
Trigger2 trigger = (Trigger2) view.Triggers.Item("IO_Trig_INS_Employee",
"dbo");
SQLDMO_SCRIPT_TYPE scriptType = SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default;
SQLDMO_SCRIPT2_TYPE script2Type =
SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default;
string sql = trigger.Script(scriptType, null, script2Type);
server.DisConnect();
}
catch(System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex);
}
catch(System.Exception ex)
{
Console.WriteLine(ex);
}
}
}
}
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
> For some reason, when I try to generate a script for Triggers off of a
> View using DMO, I get back garbage like:
> ?VIEW1
> That's the complete text coming back, nothing more. I can see it clearly
> using MSEM just fine. Any thoughts?
> --
> Lee
>

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not show
up as one of the databases that can be selected.
Thanks.
Paul G
Software engineer.
No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the bak
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:

> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not show
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.
|||ok thanks it worked!.
Paul G
Software engineer.
"Ravi" wrote:
[vbcol=seagreen]
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not sho
w
up as one of the databases that can be selected.
Thanks.
--
Paul G
Software engineer.No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the ba
k
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:

> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not s
how
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks it worked!.
--
Paul G
Software engineer.
"Ravi" wrote:
[vbcol=seagreen]
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the
bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:
>sql

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not show
up as one of the databases that can be selected.
Thanks.
--
Paul G
Software engineer.No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the bak
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:
> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not show
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks it worked!.
--
Paul G
Software engineer.
"Ravi" wrote:
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:
> > Hi I am trying to copy a complete database and did the following.
> > backup database, saved file at location
> > C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> > on machine 1-source machine.
> > I then copied this to
> > C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> > on machine two-destination machine.
> > I was thinking I could then use the restore from EM connected to the
> > destination machine but when I select restore the database name does not show
> > up as one of the databases that can be selected.
> > Thanks.
> > --
> > Paul G
> > Software engineer.