ALTER TRIGGER without schema

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

ALTER TRIGGER without schema

Postby pil0t » Mon Jun 01, 2009 1:50 pm

ALTER TRIGGERS generates code, where trigger name used without schema name
Example:
ALTER TRIGGER [Validators_DeleteFromReferenceItems] ON [ref].[Validators]
FOR DELETE ...

this statement fails, because trigger name is [ref].[Validators_DeleteFromReferenceItems]

this bug is not repeated in SqlCompare (only in SDK), maybe I can fix it using some CompareOptions?
pil0t
 
Posts: 8
Joined: Mon Apr 27, 2009 5:43 pm

Postby Anu Deshpande » Wed Jun 03, 2009 1:18 pm

Thank you for your post!

I tried to replicate the error at our end and SQL Compare also doesnt fully qualify the trigger if the definition is without the schema.

Can I ask you which version of SQL Compare and SQL Comparison SDK are you using?
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby pil0t » Wed Jun 03, 2009 1:46 pm

C:\\Program Files\\Red Gate\\SQL Comparison SDK 8\\Assemblies\\SQL Data Compare\\RedGate.SQLCompare.Engine.dll
7.1.0.182

C:\\Program Files\\Red Gate\\SQL Comparison SDK 8\\Assemblies\\SQL Data Compare\\RedGate.SQLDataCompare.Engine.dll
7.1.0.230

C:\\Program Files\\Red Gate\\SQL Packager 6\\RedGate.SQLPackager.Engine.dll
6.0.0.107

SQLCompare 8.0.0.965
pil0t
 
Posts: 8
Joined: Mon Apr 27, 2009 5:43 pm

Postby pil0t » Wed Jun 03, 2009 3:24 pm

How to reproduce problem:

compare test1 and test2

CREATE DATABASE [test1]
GO
USE test1
GO

CREATE SCHEMA [testSchema]
GO

CREATE TABLE [testSchema].t1(i INT)
GO

CREATE TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END


CREATE DATABASE [test2]
GO
USE test2
GO

CREATE SCHEMA [testSchema]
GO

CREATE TABLE [testSchema].t1(i INT)
GO

CREATE TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a2 INT;
END



if I use SQLCompare, i got script

ALTER TRIGGER [testSchema].[trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END

if i use SDK, i got script

ALTER TRIGGER [trig1] ON [testSchema].[t1]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
declare @a INT;
END
pil0t
 
Posts: 8
Joined: Mon Apr 27, 2009 5:43 pm

Postby Anu Deshpande » Wed Jun 03, 2009 5:04 pm

Thanks for your detailed information in previous post, it helped us to recreate the issue.

Following things can be done:
1. If your project doesn't need any Data comparison you can use Compare engine 8 in your SDK project which will work similar to Compare 8 product.
2. If your project needs data comparison to be done you will have to wait for Data compare version 8 release which is sometime this month (We will publish in our Data compare forum about the release!)
3. If you are using Packager to build your project and cannot change the Comparison Engine you will have to wait for next packager release which is not yet scheduled.
4. If your project needs Schema compare and Data compare to be done you can create two different projects:
a. Schema Comparison Project : Using SDK with Compare 8 engine
b. Data Comparison Project: Using SDK with Data Compare engine.

Kindly let us know if that was helpful and if you have any queries regarding this,I'll like to help.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests

cron