duplicate rows in 1 csv file

Hi, I am trying to import data from a csv files to a OLE DB Destination. The csv files contains all transactional changes . For example for a particular record the firstname, lastname, email address records change within the same csv file. I need to save only the last updated record from the csv files. I have tried "slowly changing dimensions" but these dont work when there is duplictes within the same csv file. Also have tried 'Sort' but this only stores the first occurance.
Any ideas how i can store the latest changed data within 1 csv file.

How does the process distinguish between the latest data and duplicate data?

Is there a date as part of the row or is it just the last row wins?

Kirk Haselden
Author "SQL Server Integration Services"

|||the last row wins. there is no timestamp field . This is the way designed by the third party developers. Records are saved in csv files as changes are made. I have to put all these rows of data into a datawarehouse without duplicating the primary key.

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECTi.myID, i.Type

FROMINSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID

GROUP BY i.myID, i.Type

HAVING(COUNT(*) > 1) AND (i.Type = 'In')

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s.rollback is done.', 16, 1, @.step)

Return

END

What is problem?

Moving to the T-SQL forum.|||
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
ENDsql

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECT i.myID, i.Type

FROM INSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID

GROUP BY i.myID, i.Type

HAVING (COUNT(*) > 1) AND (i.Type = 'In')

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)

Return

END

What is problem?

You may not use the SET statement and I also did not see an aggregate function with your GROUP BY which means it is not a GROUP BY but a standard DISTINCT. The link below covers what you need to know and a sample of what you can do in SQL Server. Hope this helps.

http://www.sommarskog.se/error-handling-I.html

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records
inserted to T1 is available in myDB.dbo.myTable or not (destination table).
If it is available rollback T1. It does not do that although I insert the
same records twice.
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.Type
FROM INSERTED i INNER JOIN
myDB.dbo.myTable c ON i.myID = c.myID
GROUP BY i.myID, i.Type
HAVING (COUNT(*) > 1) AND (i.Type = 'In')
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
Return
END
What is problem?
JIM. H. wrote:
> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
Why not just add a unique constraint to the table instead?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Jim,
It seems like if your goal is to prohibit the addition of rows having
duplicate values for columns MyID and [Type], then a UNIQUE constraint would
have less performance impact than a trigger.
Preventing the duplicate row from being added to the table is far better
than trying to determine if a duplicate row HAS been added, and then
removing it after the fact.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:876403CD-C4A3-4FB9-B278-CD792B39D1B2@.microsoft.com...
> This is part of my trigger on table T1. I am trying to check if the
> records
> inserted to T1 is available in myDB.dbo.myTable or not (destination
> table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>
|||Hi Jim
I asked in your previous thread that you posted example DDL and data along
with statements you expected to work and those that you don't want to work.
Without doing so you have got the same replies as before a UNIQUE constraint
would be the best solution. For example:
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE myTable ( MyId int NOT NULL,
myType char(2) NOT NULL,
CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
/* If this the above statement should have been allowed then you can not use
a unique constraint, but can make the exception in the trigger */
ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
GO
CREATE TRIGGER trg_myTable ON myTable
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.myType
FROM INSERTED i
JOINdbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
= 'In'
GROUP BY i.myID, i.myType
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
Return
END
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* But this still works */
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
UPDATE myTable
SET myType = 'In'
WHERE MyId = 2
AND myType = 'Ot'
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
John
"JIM.H." wrote:

> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>
|||Hi John,
Thank you very much for your help. Here is a little bit more detail. The
trigger is on T1. T1 is inserted records by an external system. T1.Type can
be “In’, or ‘NotIn’ If it is In I need to insert this to a remote database
too which is myDB.dbo.myTable. There is no myTable.Type field in remote
database.
1. So if T1.myID=1 and T1.Type=’In’ comes more than one and myID=1 is
already available in remote database, return error to the external system and
do not accept insert into T1.
2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue without
error.
How should I do this?
So in the example you gave
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'In' )
This should not let another (1,’In’) inserted.
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'Ot' )
This can be many times, there should not be any restriction on this, because
of this I could not use unique constraints on T1.
It seem your trg_myTable example is the same as my example (am I missing
anything) but it does not seem that is working, I am able insert (1,’In’)
more than one.
Any idea would be greatly appreciated.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> I asked in your previous thread that you posted example DDL and data along
> with statements you expected to work and those that you don't want to work.
> Without doing so you have got the same replies as before a UNIQUE constraint
> would be the best solution. For example:
> CREATE DATABASE TESTDB
> GO
> USE TESTDB
> GO
>
> CREATE TABLE myTable ( MyId int NOT NULL,
> myType char(2) NOT NULL,
> CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> /* If this the above statement should have been allowed then you can not use
> a unique constraint, but can make the exception in the trigger */
> ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> GO
> CREATE TRIGGER trg_myTable ON myTable
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.myType
> FROM INSERTED i
> JOINdbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> = 'In'
> GROUP BY i.myID, i.myType
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> Return
> END
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
> /* But this still works */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> UPDATE myTable
> SET myType = 'In'
> WHERE MyId = 2
> AND myType = 'Ot'
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
>
> John
> "JIM.H." wrote:
|||Hi Jim
The trigger is not the same as your code. As T1 it would be
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOINdbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
RETURN
END
This would allow duplicates for any value of MyId and a Type of 'NotIn', but
only one occurance of a value of Type of 'In' with any single value of MyId.
If the table in your remote database can only be populated by this procedure
all you need to do is
insert the values into that database through the trigger
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOINdbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
ELSE
BEGIN
INSERT INTO myDB.dbo.myTable ( myId )
SELECT MyId
FROM inserted i
LEFT JOIN myDB.dbo.myTable c ON i.myID =
c.myID
WHERE c.MyId IS NULL
AND i.[Type] = 'In'
END
RETURN
END
If records can be inserted into the remote database independent of this
process then would not be sufficient.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Hi John,
> Thank you very much for your help. Here is a little bit more detail. The
> trigger is on T1. T1 is inserted records by an external system. T1.Type can
> be “In’, or ‘NotIn’ If it is In I need to insert this to a remote database
> too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> database.
> 1. So if T1.myID=1 and T1.Type=’In’ comes more than one and myID=1 is
> already available in remote database, return error to the external system and
> do not accept insert into T1.
> 2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue without
> error.
> How should I do this?
> So in the example you gave
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'In' )
> This should not let another (1,’In’) inserted.
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'Ot' )
> This can be many times, there should not be any restriction on this, because
> of this I could not use unique constraints on T1.
> It seem your trg_myTable example is the same as my example (am I missing
> anything) but it does not seem that is working, I am able insert (1,’In’)
> more than one.
> Any idea would be greatly appreciated.
>
> "John Bell" wrote:
|||Thanks John. I will try to implement this.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> The trigger is not the same as your code. As T1 it would be
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOINdbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> RETURN
> END
> This would allow duplicates for any value of MyId and a Type of 'NotIn', but
> only one occurance of a value of Type of 'In' with any single value of MyId.
> If the table in your remote database can only be populated by this procedure
> all you need to do is
> insert the values into that database through the trigger
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOINdbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> ELSE
> BEGIN
> INSERT INTO myDB.dbo.myTable ( myId )
> SELECT MyId
> FROM inserted i
> LEFT JOIN myDB.dbo.myTable c ON i.myID =
> c.myID
> WHERE c.MyId IS NULL
> AND i.[Type] = 'In'
> END
> RETURN
> END
> If records can be inserted into the remote database independent of this
> process then would not be sufficient.
> John
>
> "JIM.H." wrote:

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records
inserted to T1 is available in myDB.dbo.myTable or not (destination table).
If it is available rollback T1. It does not do that although I insert the
same records twice.
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.Type
FROM INSERTED i INNER JOIN
myDB.dbo.myTable c ON i.myID = c.myID
GROUP BY i.myID, i.Type
HAVING (COUNT(*) > 1) AND (i.Type = 'In')
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
Return
END
What is problem?JIM. H. wrote:
> This is part of my trigger on table T1. I am trying to check if the record
s
> inserted to T1 is available in myDB.dbo.myTable or not (destination table)
.
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
Why not just add a unique constraint to the table instead?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jim,
It seems like if your goal is to prohibit the addition of rows having
duplicate values for columns MyID and [Type], then a UNIQUE constraint w
ould
have less performance impact than a trigger.
Preventing the duplicate row from being added to the table is far better
than trying to determine if a duplicate row HAS been added, and then
removing it after the fact.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:876403CD-C4A3-4FB9-B278-CD792B39D1B2@.microsoft.com...
> This is part of my trigger on table T1. I am trying to check if the
> records
> inserted to T1 is available in myDB.dbo.myTable or not (destination
> table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi Jim
I asked in your previous thread that you posted example DDL and data along
with statements you expected to work and those that you don't want to work.
Without doing so you have got the same replies as before a UNIQUE constraint
would be the best solution. For example:
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE myTable ( MyId int NOT NULL,
myType char(2) NOT NULL,
CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
/* If this the above statement should have been allowed then you can not use
a unique constraint, but can make the exception in the trigger */
ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
GO
CREATE TRIGGER trg_myTable ON myTable
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.myType
FROM INSERTED i
JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
= 'In'
GROUP BY i.myID, i.myType
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
Return
END
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* But this still works */
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
UPDATE myTable
SET myType = 'In'
WHERE MyId = 2
AND myType = 'Ot'
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
John
"JIM.H." wrote:

> This is part of my trigger on table T1. I am trying to check if the record
s
> inserted to T1 is available in myDB.dbo.myTable or not (destination table)
.
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi John,
Thank you very much for your help. Here is a little bit more detail. The
trigger is on T1. T1 is inserted records by an external system. T1.Type can
be “In’, or ‘NotIn’ If it is In I need to insert this to a remote da
tabase
too which is myDB.dbo.myTable. There is no myTable.Type field in remote
database.
1. So if T1.myID=1 and T1.Type=’In’ comes more than one and myID=1 is
already available in remote database, return error to the external system an
d
do not accept insert into T1.
2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue with
out
error.
How should I do this?
So in the example you gave
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'In' )
This should not let another (1,’In’) inserted.
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'Ot' )
This can be many times, there should not be any restriction on this, because
of this I could not use unique constraints on T1.
It seem your trg_myTable example is the same as my example (am I missing
anything) but it does not seem that is working, I am able insert (1,’In’
)
more than one.
Any idea would be greatly appreciated.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> I asked in your previous thread that you posted example DDL and data along
> with statements you expected to work and those that you don't want to work
.
> Without doing so you have got the same replies as before a UNIQUE constrai
nt
> would be the best solution. For example:
> CREATE DATABASE TESTDB
> GO
> USE TESTDB
> GO
>
> CREATE TABLE myTable ( MyId int NOT NULL,
> myType char(2) NOT NULL,
> CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> /* If this the above statement should have been allowed then you can not u
se
> a unique constraint, but can make the exception in the trigger */
> ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> GO
> CREATE TRIGGER trg_myTable ON myTable
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.myType
> FROM INSERTED i
> JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myTy
pe
> = 'In'
> GROUP BY i.myID, i.myType
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> Return
> END
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
> /* But this still works */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> UPDATE myTable
> SET myType = 'In'
> WHERE MyId = 2
> AND myType = 'Ot'
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
>
> John
> "JIM.H." wrote:
>|||Hi Jim
The trigger is not the same as your code. As T1 it would be
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[
Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
RETURN
END
This would allow duplicates for any value of MyId and a Type of 'NotIn', but
only one occurance of a value of Type of 'In' with any single value of MyId.
If the table in your remote database can only be populated by this procedure
all you need to do is
insert the values into that database through the trigger
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[
Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
ELSE
BEGIN
INSERT INTO myDB.dbo.myTable ( myId )
SELECT MyId
FROM inserted i
LEFT JOIN myDB.dbo.myTable c ON i.myID =
c.myID
WHERE c.MyId IS NULL
AND i.[Type] = 'In'
END
RETURN
END
If records can be inserted into the remote database independent of this
process then would not be sufficient.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Hi John,
> Thank you very much for your help. Here is a little bit more detail. The
> trigger is on T1. T1 is inserted records by an external system. T1.Type ca
n
> be “In’, or ‘NotIn’ If it is In I need to insert this to a remote
database
> too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> database.
> 1. So if T1.myID=1 and T1.Type=’In’ comes more than one and myID=1 is
> already available in remote database, return error to the external system
and
> do not accept insert into T1.
> 2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue wi
thout
> error.
> How should I do this?
> So in the example you gave
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'In' )
> This should not let another (1,’In’) inserted.
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'Ot' )
> This can be many times, there should not be any restriction on this, becau
se
> of this I could not use unique constraints on T1.
> It seem your trg_myTable example is the same as my example (am I missing
> anything) but it does not seem that is working, I am able insert (1,’In
)
> more than one.
> Any idea would be greatly appreciated.
>
> "John Bell" wrote:
>|||Thanks John. I will try to implement this.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> The trigger is not the same as your code. As T1 it would be
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.&
#91;Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> RETURN
> END
> This would allow duplicates for any value of MyId and a Type of 'NotIn', b
ut
> only one occurance of a value of Type of 'In' with any single value of MyI
d.
> If the table in your remote database can only be populated by this procedu
re
> all you need to do is
> insert the values into that database through the trigger
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.&
#91;Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> ELSE
> BEGIN
> INSERT INTO myDB.dbo.myTable ( myId )
> SELECT MyId
> FROM inserted i
> LEFT JOIN myDB.dbo.myTable c ON i.myID =
> c.myID
> WHERE c.MyId IS NULL
> AND i.[Type] = 'In'
> END
> RETURN
> END
> If records can be inserted into the remote database independent of this
> process then would not be sufficient.
> John
>
> "JIM.H." wrote:
>

Duplicate record trigger

This is part of my trigger on table T1. I am trying to check if the records
inserted to T1 is available in myDB.dbo.myTable or not (destination table).
If it is available rollback T1. It does not do that although I insert the
same records twice.
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.Type
FROM INSERTED i INNER JOIN
myDB.dbo.myTable c ON i.myID = c.myID
GROUP BY i.myID, i.Type
HAVING (COUNT(*) > 1) AND (i.Type = 'In')
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
Return
END
What is problem?JIM. H. wrote:
> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
Why not just add a unique constraint to the table instead?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jim,
It seems like if your goal is to prohibit the addition of rows having
duplicate values for columns MyID and [Type], then a UNIQUE constraint would
have less performance impact than a trigger.
Preventing the duplicate row from being added to the table is far better
than trying to determine if a duplicate row HAS been added, and then
removing it after the fact.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:876403CD-C4A3-4FB9-B278-CD792B39D1B2@.microsoft.com...
> This is part of my trigger on table T1. I am trying to check if the
> records
> inserted to T1 is available in myDB.dbo.myTable or not (destination
> table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi Jim
I asked in your previous thread that you posted example DDL and data along
with statements you expected to work and those that you don't want to work.
Without doing so you have got the same replies as before a UNIQUE constraint
would be the best solution. For example:
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE myTable ( MyId int NOT NULL,
myType char(2) NOT NULL,
CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
/* If this the above statement should have been allowed then you can not use
a unique constraint, but can make the exception in the trigger */
ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
GO
CREATE TRIGGER trg_myTable ON myTable
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.myType
FROM INSERTED i
JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
= 'In'
GROUP BY i.myID, i.myType
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
Return
END
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* But this still works */
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
UPDATE myTable
SET myType = 'In'
WHERE MyId = 2
AND myType = 'Ot'
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
John
"JIM.H." wrote:
> This is part of my trigger on table T1. I am trying to check if the records
> inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> If it is available rollback T1. It does not do that although I insert the
> same records twice.
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.Type
> FROM INSERTED i INNER JOIN
> myDB.dbo.myTable c ON i.myID = c.myID
> GROUP BY i.myID, i.Type
> HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> Return
> END
> What is problem?
>|||Hi John,
Thank you very much for your help. Here is a little bit more detail. The
trigger is on T1. T1 is inserted records by an external system. T1.Type can
be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
too which is myDB.dbo.myTable. There is no myTable.Type field in remote
database.
1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
already available in remote database, return error to the external system and
do not accept insert into T1.
2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
error.
How should I do this?
So in the example you gave
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'In' )
This should not let another (1,â'Inâ') inserted.
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'Ot' )
This can be many times, there should not be any restriction on this, because
of this I could not use unique constraints on T1.
It seem your trg_myTable example is the same as my example (am I missing
anything) but it does not seem that is working, I am able insert (1,â'Inâ')
more than one.
Any idea would be greatly appreciated.
"John Bell" wrote:
> Hi Jim
> I asked in your previous thread that you posted example DDL and data along
> with statements you expected to work and those that you don't want to work.
> Without doing so you have got the same replies as before a UNIQUE constraint
> would be the best solution. For example:
> CREATE DATABASE TESTDB
> GO
> USE TESTDB
> GO
>
> CREATE TABLE myTable ( MyId int NOT NULL,
> myType char(2) NOT NULL,
> CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> /* If this the above statement should have been allowed then you can not use
> a unique constraint, but can make the exception in the trigger */
> ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> GO
> CREATE TRIGGER trg_myTable ON myTable
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.myType
> FROM INSERTED i
> JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> = 'In'
> GROUP BY i.myID, i.myType
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> Return
> END
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
> /* But this still works */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> UPDATE myTable
> SET myType = 'In'
> WHERE MyId = 2
> AND myType = 'Ot'
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
>
> John
> "JIM.H." wrote:
> > This is part of my trigger on table T1. I am trying to check if the records
> > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > If it is available rollback T1. It does not do that although I insert the
> > same records twice.
> >
> > -- duplicate record check
> > SET @.step = 'Duplicate record'
> > IF EXISTS (
> > SELECT i.myID, i.Type
> > FROM INSERTED i INNER JOIN
> > myDB.dbo.myTable c ON i.myID = c.myID
> > GROUP BY i.myID, i.Type
> > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > )
> > BEGIN
> > ROLLBACK transaction
> > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > Return
> > END
> >
> > What is problem?
> >
> >|||Hi Jim
The trigger is not the same as your code. As T1 it would be
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
RETURN
END
This would allow duplicates for any value of MyId and a Type of 'NotIn', but
only one occurance of a value of Type of 'In' with any single value of MyId.
If the table in your remote database can only be populated by this procedure
all you need to do is
insert the values into that database through the trigger
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
ELSE
BEGIN
INSERT INTO myDB.dbo.myTable ( myId )
SELECT MyId
FROM inserted i
LEFT JOIN myDB.dbo.myTable c ON i.myID =c.myID
WHERE c.MyId IS NULL
AND i.[Type] = 'In'
END
RETURN
END
If records can be inserted into the remote database independent of this
process then would not be sufficient.
John
"JIM.H." wrote:
> Hi John,
> Thank you very much for your help. Here is a little bit more detail. The
> trigger is on T1. T1 is inserted records by an external system. T1.Type can
> be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
> too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> database.
> 1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
> already available in remote database, return error to the external system and
> do not accept insert into T1.
> 2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
> error.
> How should I do this?
> So in the example you gave
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'In' )
> This should not let another (1,â'Inâ') inserted.
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'Ot' )
> This can be many times, there should not be any restriction on this, because
> of this I could not use unique constraints on T1.
> It seem your trg_myTable example is the same as my example (am I missing
> anything) but it does not seem that is working, I am able insert (1,â'Inâ')
> more than one.
> Any idea would be greatly appreciated.
>
> "John Bell" wrote:
> > Hi Jim
> >
> > I asked in your previous thread that you posted example DDL and data along
> > with statements you expected to work and those that you don't want to work.
> > Without doing so you have got the same replies as before a UNIQUE constraint
> > would be the best solution. For example:
> >
> > CREATE DATABASE TESTDB
> > GO
> >
> > USE TESTDB
> > GO
> >
> >
> > CREATE TABLE myTable ( MyId int NOT NULL,
> > myType char(2) NOT NULL,
> > CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 2, 'In' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 2, 'Ot' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > /*
> > Msg 2627, Level 14, State 1, Line 1
> > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > duplicate key in object 'dbo.myTable'.
> > The statement has been terminated.
> > */
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> > /*
> > Msg 2627, Level 14, State 1, Line 1
> > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > duplicate key in object 'dbo.myTable'.
> > The statement has been terminated.
> > */
> >
> > /* If this the above statement should have been allowed then you can not use
> > a unique constraint, but can make the exception in the trigger */
> > ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> > GO
> >
> > CREATE TRIGGER trg_myTable ON myTable
> > FOR INSERT,UPDATE
> > AS
> > BEGIN
> > DECLARE @.step varchar(30)
> > -- duplicate record check
> > SET @.step = 'Duplicate record'
> > IF EXISTS (
> > SELECT i.myID, i.myType
> > FROM INSERTED i
> > JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> > = 'In'
> > GROUP BY i.myID, i.myType
> > HAVING COUNT(*) > 1
> > )
> > BEGIN
> > ROLLBACK transaction
> > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > END
> > Return
> > END
> >
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'In' )
> >
> > /*
> > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > Error: step: Duplicate record. rollback is done.
> > Msg 3609, Level 16, State 1, Line 1
> > The transaction ended in the trigger. The batch has been aborted.
> > */
> >
> > /* But this still works */
> > INSERT INTO myTable ( MyId, myType )
> > VALUES( 1, 'Ot' )
> >
> > UPDATE myTable
> > SET myType = 'In'
> > WHERE MyId = 2
> > AND myType = 'Ot'
> >
> > /*
> > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > Error: step: Duplicate record. rollback is done.
> > Msg 3609, Level 16, State 1, Line 1
> > The transaction ended in the trigger. The batch has been aborted.
> > */
> >
> >
> > John
> >
> > "JIM.H." wrote:
> >
> > > This is part of my trigger on table T1. I am trying to check if the records
> > > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > > If it is available rollback T1. It does not do that although I insert the
> > > same records twice.
> > >
> > > -- duplicate record check
> > > SET @.step = 'Duplicate record'
> > > IF EXISTS (
> > > SELECT i.myID, i.Type
> > > FROM INSERTED i INNER JOIN
> > > myDB.dbo.myTable c ON i.myID = c.myID
> > > GROUP BY i.myID, i.Type
> > > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > > )
> > > BEGIN
> > > ROLLBACK transaction
> > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > Return
> > > END
> > >
> > > What is problem?
> > >
> > >|||Thanks John. I will try to implement this.
"John Bell" wrote:
> Hi Jim
> The trigger is not the same as your code. As T1 it would be
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> RETURN
> END
> This would allow duplicates for any value of MyId and a Type of 'NotIn', but
> only one occurance of a value of Type of 'In' with any single value of MyId.
> If the table in your remote database can only be populated by this procedure
> all you need to do is
> insert the values into that database through the trigger
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> ELSE
> BEGIN
> INSERT INTO myDB.dbo.myTable ( myId )
> SELECT MyId
> FROM inserted i
> LEFT JOIN myDB.dbo.myTable c ON i.myID => c.myID
> WHERE c.MyId IS NULL
> AND i.[Type] = 'In'
> END
> RETURN
> END
> If records can be inserted into the remote database independent of this
> process then would not be sufficient.
> John
>
> "JIM.H." wrote:
> > Hi John,
> > Thank you very much for your help. Here is a little bit more detail. The
> > trigger is on T1. T1 is inserted records by an external system. T1.Type can
> > be â'Inâ', or â'NotInâ' If it is In I need to insert this to a remote database
> > too which is myDB.dbo.myTable. There is no myTable.Type field in remote
> > database.
> > 1. So if T1.myID=1 and T1.Type=â'Inâ' comes more than one and myID=1 is
> > already available in remote database, return error to the external system and
> > do not accept insert into T1.
> > 2. If T1.myID=1 and T1.Type=â'NotInâ' can come more than one continue without
> > error.
> > How should I do this?
> >
> > So in the example you gave
> > INSERT INTO T1 ( MyId, Type )
> > VALUES( 1, 'In' )
> > This should not let another (1,â'Inâ') inserted.
> >
> > INSERT INTO T1 ( MyId, Type )
> > VALUES( 1, 'Ot' )
> > This can be many times, there should not be any restriction on this, because
> > of this I could not use unique constraints on T1.
> >
> > It seem your trg_myTable example is the same as my example (am I missing
> > anything) but it does not seem that is working, I am able insert (1,â'Inâ')
> > more than one.
> > Any idea would be greatly appreciated.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Jim
> > >
> > > I asked in your previous thread that you posted example DDL and data along
> > > with statements you expected to work and those that you don't want to work.
> > > Without doing so you have got the same replies as before a UNIQUE constraint
> > > would be the best solution. For example:
> > >
> > > CREATE DATABASE TESTDB
> > > GO
> > >
> > > USE TESTDB
> > > GO
> > >
> > >
> > > CREATE TABLE myTable ( MyId int NOT NULL,
> > > myType char(2) NOT NULL,
> > > CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 2, 'In' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 2, 'Ot' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > /*
> > > Msg 2627, Level 14, State 1, Line 1
> > > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > > duplicate key in object 'dbo.myTable'.
> > > The statement has been terminated.
> > > */
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > > /*
> > > Msg 2627, Level 14, State 1, Line 1
> > > Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> > > duplicate key in object 'dbo.myTable'.
> > > The statement has been terminated.
> > > */
> > >
> > > /* If this the above statement should have been allowed then you can not use
> > > a unique constraint, but can make the exception in the trigger */
> > > ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> > > GO
> > >
> > > CREATE TRIGGER trg_myTable ON myTable
> > > FOR INSERT,UPDATE
> > > AS
> > > BEGIN
> > > DECLARE @.step varchar(30)
> > > -- duplicate record check
> > > SET @.step = 'Duplicate record'
> > > IF EXISTS (
> > > SELECT i.myID, i.myType
> > > FROM INSERTED i
> > > JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
> > > = 'In'
> > > GROUP BY i.myID, i.myType
> > > HAVING COUNT(*) > 1
> > > )
> > > BEGIN
> > > ROLLBACK transaction
> > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > END
> > > Return
> > > END
> > >
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'In' )
> > >
> > > /*
> > > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > > Error: step: Duplicate record. rollback is done.
> > > Msg 3609, Level 16, State 1, Line 1
> > > The transaction ended in the trigger. The batch has been aborted.
> > > */
> > >
> > > /* But this still works */
> > > INSERT INTO myTable ( MyId, myType )
> > > VALUES( 1, 'Ot' )
> > >
> > > UPDATE myTable
> > > SET myType = 'In'
> > > WHERE MyId = 2
> > > AND myType = 'Ot'
> > >
> > > /*
> > > Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> > > Error: step: Duplicate record. rollback is done.
> > > Msg 3609, Level 16, State 1, Line 1
> > > The transaction ended in the trigger. The batch has been aborted.
> > > */
> > >
> > >
> > > John
> > >
> > > "JIM.H." wrote:
> > >
> > > > This is part of my trigger on table T1. I am trying to check if the records
> > > > inserted to T1 is available in myDB.dbo.myTable or not (destination table).
> > > > If it is available rollback T1. It does not do that although I insert the
> > > > same records twice.
> > > >
> > > > -- duplicate record check
> > > > SET @.step = 'Duplicate record'
> > > > IF EXISTS (
> > > > SELECT i.myID, i.Type
> > > > FROM INSERTED i INNER JOIN
> > > > myDB.dbo.myTable c ON i.myID = c.myID
> > > > GROUP BY i.myID, i.Type
> > > > HAVING (COUNT(*) > 1) AND (i.Type = 'In')
> > > > )
> > > > BEGIN
> > > > ROLLBACK transaction
> > > > RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> > > > Return
> > > > END
> > > >
> > > > What is problem?
> > > >
> > > >

Dumping rows of 2 columns of a table to a different table with different no of columns

Hi,

I have two tables

Table Source

{

category varchar(20),

LastUpdate datetime

}

Table Destination

{

SubjectId varchar(20),

SubjectDate datetime,

category varchar(20),

LastUpdate datetime

}

Please note that the number columns are different in each table.

I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.

And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.

Is there anyway to bulk insert into these columns.

Your quick response will be appreciated

~Mohan Babu

It is a very simple solution:

1. create a view on destination
CREATE VIEW [viewDestination]
AS
SELECT category, LastUpdate
FROM dbo.Destination

2. Then
insert into dbo.viewDestination
select * from Source

doesn't matter the order in Destination table.|||Thanks for the reply.But i don't want to create a view on this as the number of coulmns on the table will be changed dynamically. Can you please suggest any other way

Dumping rows of 2 columns of a table to a different table with different no of columns

Hi,

I have two tables

Table Source

{

category varchar(20),

LastUpdate datetime

}

Table Destination

{

SubjectId varchar(20),

SubjectDate datetime,

category varchar(20),

LastUpdate datetime

}

Please note that the number columns are different in each table.

I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.

And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.

Is there anyway to bulk insert into these columns.

Your quick response will be appreciated

~Mohan Babu

It is a very simple solution:

1. create a view on destination
CREATE VIEW [viewDestination]
AS
SELECT category, LastUpdate
FROM dbo.Destination

2. Then
insert into dbo.viewDestination
select * from Source

doesn't matter the order in Destination table.|||Thanks for the reply.But i don't want to create a view on this as the number of coulmns on the table will be changed dynamically. Can you please suggest any other way

DTSX package data transfer error

I will try to explain things the best I can.

When the data is transferred from source to destination (replace not append), the data in one field in one table is incorrect. Both source and destination tables have the same number of rows (8493). The ProductID field data range at the source is from 58958 to 73008. When the table is copied the ProductID field data runs from 1 to 8493.

What would cause this skewing of data?

This happens on brand new dtsx packages and this only happens in one field out of 5 different tables.

I am baffled. Any help is appreciated.

Thanks.

Andrew

Sounds like ProductID is an identity field.

In the OLE DB destination, ensure that you are using a data access mode of "Table or view - fast load" and then make sure the check box is selected for "Keep identity."

See if that helps.|||

Thanks for the quick response Phil.

Where exactly do I look for this setting in Visual Studio?

I must be overlooking it.

Thanks.

Andrew

|||It's in the OLE DB Destination component. Double click on it.|||Did you develop the package, or use the Import/Export wizard?|||I devolped it.|||

FordyH500HP wrote:

I devolped it.

Then what destination are you using? Use the OLE DB Destination if you're not using it already.|||

I'm using the Transfer SQL Server Objects Task from the toolbox.

Will that work for what you are talking about?

|||

FordyH500HP wrote:

I'm using the Transfer SQL Server Objects Task from the toolbar.

Will that work for what you are talking about?

Ahh... What values for the options under "Table Options" do you have?|||

Sorry if I should have stated that sooner.

I'm just above Newbie status in Visual Studio.

CopyIndexes - True

CopyTriggers - False

CopyFullTextIndexes - False

CopyPrimaryKeys - False

CopyForeignKeys - False

GenerateScriptsInUnicode - False

|||What happens if you set CopyPrimaryKeys and CopyForeignKeys to True?|||Same result. It renumbers the ProductID field starting with 1.|||

FordyH500HP wrote:

Same result. It renumbers the ProductID field starting with 1.

Yep, doing a quick search shows that the Transfer SQL Server Objects task does not support identity columns. Will future versions?

Let's find out:
[Microsoft follow-up]

You can do this on your own though, by using an execute sql task in the control flow to truncate the destination tables and then as many data flows as you have tables. Inside the data flows, you can use an OLE DB source and hook it up to an OLE DB destination, and use the options I suggested above.|||

Ok. I think I understand.

So I want to check Keep Identity then in the data flow destination?

Also, can I put multiple Source/Destinations in one data flow?

|||Yep

DTSRun causes The Memory could not be "read" application error

Hi,

I've got this dts package in sql server 2000 sp4 that has several
transformation tasks with an oracle database (10g) as the destination.
The package executes successfully when run through the dts designer but
when run using dtsrun, I get the following error at the completion of
all tasks in the dts package. The data inserts into the oracle database
but i always get this Application error

The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
memory could not be "read"

Does anyone know how I can fix this?

Thanks
LynHi

I am not sure why this should happen but you may want to check that your
arguements are correct http://support.microsoft.com/?kbid=308801 and that
you can the same versions of MDAC on the machine it is running on and that
it is consistent (see MDAC component checker http://tinyurl.com/6gsv )
http://support.microsoft.com/?kbid=255900

John
<lyn.duong@.gmail.com> wrote in message
news:1130286935.981909.22760@.g43g2000cwa.googlegro ups.com...
> Hi,
> I've got this dts package in sql server 2000 sp4 that has several
> transformation tasks with an oracle database (10g) as the destination.
> The package executes successfully when run through the dts designer but
> when run using dtsrun, I get the following error at the completion of
> all tasks in the dts package. The data inserts into the oracle database
> but i always get this Application error
> The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
> memory could not be "read"
> Does anyone know how I can fix this?
> Thanks
> Lyn|||Hi John,

The mdac problem is relevant to sql server 7.0 and I've checked the
other kb. I don't pass any arguments. I have already got the latest
service pack installed for sql server 2000.

Lyn

John Bell wrote:
> Hi
> I am not sure why this should happen but you may want to check that your
> arguements are correct http://support.microsoft.com/?kbid=308801 and that
> you can the same versions of MDAC on the machine it is running on and that
> it is consistent (see MDAC component checker http://tinyurl.com/6gsv )
> http://support.microsoft.com/?kbid=255900
> John
> <lyn.duong@.gmail.com> wrote in message
> news:1130286935.981909.22760@.g43g2000cwa.googlegro ups.com...
> > Hi,
> > I've got this dts package in sql server 2000 sp4 that has several
> > transformation tasks with an oracle database (10g) as the destination.
> > The package executes successfully when run through the dts designer but
> > when run using dtsrun, I get the following error at the completion of
> > all tasks in the dts package. The data inserts into the oracle database
> > but i always get this Application error
> > The Instruction at "0x7c8327f9" referenced memory at "Oxffffffff". The
> > memory could not be "read"
> > Does anyone know how I can fix this?
> > Thanks
> > Lyn

DTS Works, job fails!(data source Foxpro and destination SQL Server 2000

The DTS works perfectly when I run it manually. However, when I run it
as a job it fails. Before you ask if i'm running it under different
security context. I have already made sure of that. I was logged into
the server through remote viewer, when I created and ran the package,
as well as scheduling the job. So the accounts they're running under
are consistent. They're the same accounts as the SQL Agent is running
under and it's the sys admin account.

The data source is a Fox pro database with a pull of two tables. I am
using Microsoft OLE DB Foxpro driver as my source connection and OLE DB
Connection for SQL Server as my destination. I am doing a simple table
to table transformation. The path of my connection is a mapped Drive:
E:\Main. There are other packages and jobs within my job queue that are
pointing to the same database and they seem to run fine using the above
mapped drive. The ONLY difference between this package and other
packages are that, they're few months old and this one was created last
night. I have also enabled logging on this package and here is the
below error when the job fails:

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft OLE DB Provider for Visual FoxPro
Step Error Description:Invalid path or file name.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 9/23/2006 11:39:17 AM
Step Execution Completed: 9/23/2006 11:39:17 AM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0No one can help me out with this? Or make any suggestions at the least?

DTS Work Flow issue

OK, this shouldn't be this hard, I'm sure I am missing something
simple.
I have a DTS package with 1 source connection and 1 destination
connection.
I want to transform 2 tables, each as a separate Transformation task.
Now to the isssue.
(this one I get..)
If both Succeed, execute another set of procedures..
(This one I can't seem to figure out)
If Either of them fail, Execute a different set of procedures.
How can you set an OR precedence on failure?
TIA!
DavidOn Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david.hay@.gmail.com>
wrote:
>OK, this shouldn't be this hard, I'm sure I am missing something
>simple.
>I have a DTS package with 1 source connection and 1 destination
>connection.
>I want to transform 2 tables, each as a separate Transformation task.
>Now to the isssue.
>(this one I get..)
>If both Succeed, execute another set of procedures..
>
>(This one I can't seem to figure out)
>If Either of them fail, Execute a different set of procedures.
>How can you set an OR precedence on failure?
On success of a step, go to another step that sets a variable.
Have a step downstream that reads both variables.
And check out the .dts newsgroup!
J.|||On Aug 10, 12:17 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david...@.gmail.com>
> wrote:
>
> >OK, this shouldn't be this hard, I'm sure I am missing something
> >simple.
> >I have a DTS package with 1 source connection and 1 destination
> >connection.
> >I want to transform 2 tables, each as a separate Transformation task.
> >Now to the isssue.
> >(this one I get..)
> >If both Succeed, execute another set of procedures..
> >(This one I can't seem to figure out)
> >If Either of them fail, Execute a different set of procedures.
> >How can you set an OR precedence on failure?
> On success of a step, go to another step that sets a variable.
> Have a step downstream that reads both variables.
> And check out the .dts newsgroup!
> J.
Thanks!

DTS w/dynamic source & query

I'm looking for a way to transform the contents of n source tables
into a single destination table. This by itself is no problem.

However, the name of the source tables change, so I'll need to base
the transform task on a global variable that I can update via external
code. Not sure how to do that. I'm ok with executing the package 10
times if there's 10 source tables.

The last unknown piece is modifying the query used for the transform.
There are 10 columns in the source table, but there are 12 columns in
the destination table. I must provide the 2 missing columns. They will
simply contain a year and month, ie. 05 2003.

I'm taking a bunch of source tables (for a given month and year) and
rolling them together into one destination table, and carrying over
the month and year. I assume the month and year would also be globals.
But I'm not sure how to incorporate them into the transform task since
it wants strict SQL syntax.

Any help is appreciated, thanks in advance!"znelson" <znelson@.hpis.net> wrote in message
news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> I'm looking for a way to transform the contents of n source tables
> into a single destination table. This by itself is no problem.
> However, the name of the source tables change, so I'll need to base
> the transform task on a global variable that I can update via external
> code. Not sure how to do that. I'm ok with executing the package 10
> times if there's 10 source tables.
> The last unknown piece is modifying the query used for the transform.
> There are 10 columns in the source table, but there are 12 columns in
> the destination table. I must provide the 2 missing columns. They will
> simply contain a year and month, ie. 05 2003.
> I'm taking a bunch of source tables (for a given month and year) and
> rolling them together into one destination table, and carrying over
> the month and year. I assume the month and year would also be globals.
> But I'm not sure how to incorporate them into the transform task since
> it wants strict SQL syntax.
> Any help is appreciated, thanks in advance!

One way to pass in a global variable at runtime is as a parameter to
dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
you, then parameterize it using your preferred language:

http://www.sqldts.com/default.aspx?301

This is useful when the number of parameters is fairly limited. An
alternative if you have many global variables is to put the values in a
database table or .INI file, and use a Dynamic Properties task to retrieve
them and assign them to the variables. This allows for more complex logic,
conditional values based on server name, AD site name etc. Finally, you can
assign global variable values directly in an ActiveX task.

As for how to pass in the month/year to an INSERT, these pages may be
useful:

http://www.sqldts.com/?234
http://www.sqldts.com/?205

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<409a6999$1_2@.news.bluewin.ch>...
> "znelson" <znelson@.hpis.net> wrote in message
> news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> > I'm looking for a way to transform the contents of n source tables
> > into a single destination table. This by itself is no problem.
> > However, the name of the source tables change, so I'll need to base
> > the transform task on a global variable that I can update via external
> > code. Not sure how to do that. I'm ok with executing the package 10
> > times if there's 10 source tables.
> > The last unknown piece is modifying the query used for the transform.
> > There are 10 columns in the source table, but there are 12 columns in
> > the destination table. I must provide the 2 missing columns. They will
> > simply contain a year and month, ie. 05 2003.
> > I'm taking a bunch of source tables (for a given month and year) and
> > rolling them together into one destination table, and carrying over
> > the month and year. I assume the month and year would also be globals.
> > But I'm not sure how to incorporate them into the transform task since
> > it wants strict SQL syntax.
> > Any help is appreciated, thanks in advance!
> One way to pass in a global variable at runtime is as a parameter to
> dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
> you, then parameterize it using your preferred language:
> http://www.sqldts.com/default.aspx?301
> This is useful when the number of parameters is fairly limited. An
> alternative if you have many global variables is to put the values in a
> database table or .INI file, and use a Dynamic Properties task to retrieve
> them and assign them to the variables. This allows for more complex logic,
> conditional values based on server name, AD site name etc. Finally, you can
> assign global variable values directly in an ActiveX task.
> As for how to pass in the month/year to an INSERT, these pages may be
> useful:
> http://www.sqldts.com/?234
> http://www.sqldts.com/?205
> Simon

Simon,

Thanks a lot, very helpful!

DTS Truncates data to destination

Hi all
I am using SQL Server 2000, since I dont have OLEDB provider for
Postgres, and SSIS does not support ODBC connection for migartion of
data.
I want to move data from my source tables to other database, for about
1000 tables.
Problem is that size of columns in source is large while the size of
columns in destination is small. I dont have control over desing of
destination database.
When I migrate data from source to destination, DTS truncate data to
size of dstination column and migarate data, but does not show any
warning that data is truncated.
If I will get warning it will be definately good for us to find out
tables in which data is truncated.
Because this we are not able to find out for which columns data is
truncated, its a big headache for the developers.
RegardsHi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:

> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>|||Hi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:

> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>

DTS Truncates data to destination

Hi all
I am using SQL Server 2000, since I dont have OLEDB provider for
Postgres, and SSIS does not support ODBC connection for migartion of
data.
I want to move data from my source tables to other database, for about
1000 tables.
Problem is that size of columns in source is large while the size of
columns in destination is small. I dont have control over desing of
destination database.
When I migrate data from source to destination, DTS truncate data to
size of dstination column and migarate data, but does not show any
warning that data is truncated.
If I will get warning it will be definately good for us to find out
tables in which data is truncated.
Because this we are not able to find out for which columns data is
truncated, its a big headache for the developers.
RegardsHi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:
> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>

DTS transfer

Hi,

I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.

Private

Sub TransferSQLData(ByVal SourceDetailsAs Admin_upload.EnvironmentDetails,ByVal DestinationDetailsAs Admin_upload.EnvironmentDetails)

'Transfer database from source to destination.Dim oPackageAsNew DTS.Package2Dim oConnectionAs DTS.Connection2Dim oStepAs DTS.Step2Dim oTaskAs DTS.TaskDim oCustomTaskAs DTS.TransferObjectsTask2Try

oStep = oPackage.Steps.New

oTask = oPackage.Tasks.New(

"DTSTransferObjectsTask")

oCustomTask = oTask.CustomTask

oPackage.FailOnError =

FalseWith oStep

.Name =

"Copy Database design and data"

.ExecuteInMainThread =

TrueEndWithWith oTask

.Name =

"GenericPkgTask"EndWithWith oCustomTask

.Name =

"DTSTransferObjectsTask"

.SourceServer = "MYSERVER"

.SourceUseTrustedConnection =

True

.SourceDatabase = SourceDetails.MetaDB

.SourceLogin = SourceDetails.MetaUser

.SourcePassword = SourceDetails.MetaPWD

.DestinationServer = "MYSERVER"

.DestinationUseTrustedConnection =

True

.DestinationDatabase = DestinationDetails.MetaDB

.DestinationLogin = DestinationDetails.MetaUser

.DestinationPassword = DestinationDetails.MetaPWD

.CopyAllObjects =

True

.IncludeDependencies =

False

.IncludeLogins =

False

.IncludeUsers =

False

.DropDestinationObjectsFirst =

True

.CopySchema =

True

.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData

EndWith

oStep.TaskName = oCustomTask.Name

oPackage.Steps.Add(oStep)

oPackage.Tasks.Add(oTask)

oPackage.Execute()

Catch exAs Exception

LabelUploadMeta.Text =

"Failed to Upload MetaData: " & ex.MessageThrow exFinally

oConnection =

Nothing

oCustomTask =

Nothing

oTask =

Nothing

oStep =

Nothing

IfNot (oPackageIsNothing)Then

oPackage.UnInitialize()

EndIfEndTryEndSub

This works fine but when I set the following within the method:

.DestinationServer = "ANOTHERSERVER"

It won't transfer the data.

I can access the remote server and read and write data to it.

Any ideas?

Is the second SQL server registered on the SQL server where you run your DTS?

Does you user under DTS is running has rights to access second server?

Thanks

DTS transaction problem

I created DTS package that has two connections “Source” and “Destination”, contains several “Execute SQL” tasks and several “Data Driven Query” tasks after them. For all tasks workflow properties are set to “Join transaction if present”, “Rollback transaction on failure” and “Fail package on step failure” except the last “Execute SQL” task that has also “Commit transaction on successful completion of this step” property set. The problem: when package started it successfully complete all “Execute SQL” tasks, but first DDQ task produce error “Transaction context in use by another session”. What can be a reason of such behavior? How to solve this problem? I need everything done in one transaction.

Thanks.

Andrey,
You're on the wrong forum. Try http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts

-Jamie