July 8, 2008 at 12:42 pm
Hi,
This is the second portion of a 2 step process to copy data from a table in one
SQLServer 2005 database to a second archive database, and then to delete the
data out of the source table that was just copied to the destination.
I've simplified it here to be all in one database, to make it more easily testable.
When I run the query at the end, the following errors occur:
Msg 156, Level 15, State 1, Line 56
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ')'
Here is all the setup DDL, followed by the troublesome query:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#TRANS_RESULTS1','U') IS NOT NULL
DROP TABLE #TRANS_RESULTS1
IF OBJECT_ID('TempDB..#TRANS_RESULTS2','U') IS NOT NULL
DROP TABLE #TRANS_RESULTS2
--===== Create the test tables with
CREATE TABLE #TRANS_RESULTS1
(
IDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EXECUTION_DTDATETIME,
POLICY_NBRCHAR(9),
FORM_CDCHAR(4),
SEQUENCE_NBRCHAR(1),
CUSTOMER_IDVARCHAR(12),
SERVER_NMVARCHAR(64)
)
CREATE TABLE #TRANS_RESULTS2
(
IDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EXECUTION_DTDATETIME,
POLICY_NBRCHAR(9),
FORM_CDCHAR(4),
SEQUENCE_NBRCHAR(1),
CUSTOMER_IDVARCHAR(12),
SERVER_NMVARCHAR(64)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TRANS_RESULTS1 ON
SET IDENTITY_INSERT #TRANS_RESULTS2 ON
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== Insert the test data into the test tables
INSERT INTO #TRANS_RESULTS1
(ID,EXECUTION_DT, POLICY_NBR, FORM_CD, SEQUENCE_NBR, CUSTOMER_ID, SERVER_NM)
SELECT '4','Oct 17 2007 12:00AM','POL4','FORM4','1','CUST4','SRV4' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','POL5','FORM5','1','CUST5','SRV5' UNION ALL
SELECT '6','Oct 17 2007 12:00AM','POL6','FORM6','1','CUST6','SRV6' UNION ALL
SELECT '8','Oct 17 2007 12:00AM','POL8','FORM8','1','CUST8','SRV7'
INSERT INTO #TRANS_RESULTS2
(ID,EXECUTION_DT, POLICY_NBR, FORM_CD, SEQUENCE_NBR, CUSTOMER_ID, SERVER_NM)
SELECT '6','Oct 17 2007 12:00AM','POL6','FORM6','1','CUST6','SRV6' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','POL7','FORM7','1','CUST7','SRV7' UNION ALL
SELECT '8','Oct 17 2007 12:00AM','POL8','FORM8','1','CUST8','SRV8' UNION ALL
SELECT '9','Oct 17 2007 12:00AM','POL9','FORM9','1','CUST9','SRV9'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #TRANS_RESULTS1 OFF
SET IDENTITY_INSERT #TRANS_RESULTS2 OFF
--===== This query is simplified from the original, where the 2 tables
-- reside in 2 sperate databases.
DELETE
FROM [TRANS_RESULTS1] AS ODS
WHERE (EXISTS
(SELECT ODSARCH.EXECUTION_DT,
ODSARCH.POLICY_NBR,
ODSARCH.FORM_CD,
ODSARCH.sequence_nbr,
ODSARCH.CUSTOMER_ID,
ODSARCH.SERVER_NM
FROM [TRANS_RESULTS2] AS ODSARCH
WHERE ODS.EXECUTION_DT = ODSARCH.EXECUTION_DT
AND ODS.POLICY_NBR = ODSARCH.POLICY_NBR
AND ODS.FORM_CD = ODSARCH.FORM_CD
AND ODS.SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND ODS.CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND ODS.SERVER_NM = ODSARCH.SERVER_NM))
Can anyone help explain what is happening, and perhaps suggest an alternative that will work?
Many thanks!
July 8, 2008 at 1:43 pm
--===== This query is simplified from the original, where the 2 tables
-- reside in 2 sperate databases.
DELETE
FROM [TRANS_RESULTS1] AS ODS
WHERE (EXISTS
(SELECT ODSARCH.EXECUTION_DT,
ODSARCH.POLICY_NBR,
ODSARCH.FORM_CD,
ODSARCH.sequence_nbr,
ODSARCH.CUSTOMER_ID,
ODSARCH.SERVER_NM
FROM [TRANS_RESULTS2] AS ODSARCH
WHERE ODS.EXECUTION_DT = ODSARCH.EXECUTION_DT
AND ODS.POLICY_NBR = ODSARCH.POLICY_NBR
AND ODS.FORM_CD = ODSARCH.FORM_CD
AND ODS.SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND ODS.CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND ODS.SERVER_NM = ODSARCH.SERVER_NM))
Two issues with the above, the first issue is: what table are you deleting from? You have to specify the table, as in:
DELETE ODS
FROM [TRANS_RESULTS1] AS ODS
WHERE ...
Or
DELETE [TRANS_RESULTS1]
FROM [TRANS_RESULTS1] AS ODS
WHERE ...
The second issue is the where clause using EXISTS. This needs to be done as: WHERE EXISTS (subquery) and not as you have it: WHERE (EXISTS(subquery))
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2008 at 10:13 am
What Jeffrey pointed out about the delete statement is what's giving you both of your errors. The parentheses part actually doesn't matter, it works either way.
I had to modify your script to test it. For example, you can't set Identity_Insert On for two tables at the same time. Have to set it for table 1, do the insert, turn it off for table 1, set it for table 2, etc.
Also, the final delete statement didn't have the # symbol in front of the table names.
Additionally, the Form_CD column was defined in both tables as Char(4), but the insert statements included 5-character data for that column.
Other than that, changing the delete statement to the following handled the syntax error:
DELETE
FROM [#TRANS_RESULTS1]
WHERE
(EXISTS
(SELECT *
FROM [#TRANS_RESULTS2] AS ODSARCH
WHERE [#TRANS_RESULTS1].EXECUTION_DT = ODSARCH.EXECUTION_DT
AND [#TRANS_RESULTS1].POLICY_NBR = ODSARCH.POLICY_NBR
AND [#TRANS_RESULTS1].FORM_CD = ODSARCH.FORM_CD
AND [#TRANS_RESULTS1].SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND [#TRANS_RESULTS1].CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND [#TRANS_RESULTS1].SERVER_NM = ODSARCH.SERVER_NM))
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 11:07 am
Thanks Jeffrey and GSquared.
Jeffrey, your suggestions do indeed eliminate the sql syntax errors. However I could not find any substantiation for your suggestion on the delete clause when looking at the SQL Server 2005 Transact-SQL Reference for the delete stmt at . How did you figure that one out?
When implementing the 2 suggestions locally, I found that the query was not deleting the expected rows.
GSquared, your suggestions for fixing the test script was immensely helpful. I've incorportated all those changes and am pasting here below:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#TRANS_RESULTS1','U') IS NOT NULL
DROP TABLE #TRANS_RESULTS1
IF OBJECT_ID('TempDB..#TRANS_RESULTS2','U') IS NOT NULL
DROP TABLE #TRANS_RESULTS2
--===== Create the test tables with
CREATE TABLE #TRANS_RESULTS1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EXECUTION_DT DATETIME,
POLICY_NBR CHAR(9),
FORM_CD CHAR(4),
SEQUENCE_NBR CHAR(1),
CUSTOMER_ID VARCHAR(12),
SERVER_NM VARCHAR(64)
)
CREATE TABLE #TRANS_RESULTS2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EXECUTION_DT DATETIME,
POLICY_NBR CHAR(9),
FORM_CD CHAR(4),
SEQUENCE_NBR CHAR(1),
CUSTOMER_ID VARCHAR(12),
SERVER_NM VARCHAR(64)
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TRANS_RESULTS1 ON
--===== Insert the test data into the test tables
INSERT INTO #TRANS_RESULTS1
(ID,EXECUTION_DT, POLICY_NBR, FORM_CD, SEQUENCE_NBR, CUSTOMER_ID, SERVER_NM)
SELECT '4','Oct 17 2007 12:00AM','POL4','FRM4','1','CUST4','SRV4' UNION ALL
SELECT '5','Oct 17 2007 12:00AM','POL5','FRM5','1','CUST5','SRV5' UNION ALL
SELECT '6','Oct 17 2007 12:00AM','POL6','FRM6','1','CUST6','SRV6' UNION ALL
SELECT '8','Oct 17 2007 12:00AM','POL8','FRM8','1','CUST8','SRV7'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #TRANS_RESULTS1 OFF
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TRANS_RESULTS2 ON
--===== Insert the test data into the test tables
INSERT INTO #TRANS_RESULTS2
(ID,EXECUTION_DT, POLICY_NBR, FORM_CD, SEQUENCE_NBR, CUSTOMER_ID, SERVER_NM)
SELECT '6','Oct 17 2007 12:00AM','POL6','FRM6','1','CUST6','SRV6' UNION ALL
SELECT '7','Oct 17 2007 12:00AM','POL7','FRM7','1','CUST7','SRV7' UNION ALL
SELECT '8','Oct 17 2007 12:00AM','POL8','FRM8','1','CUST8','SRV8' UNION ALL
SELECT '9','Oct 17 2007 12:00AM','POL9','FRM9','1','CUST9','SRV9'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #TRANS_RESULTS2 OFF
select * from #TRANS_RESULTS1
select * from #TRANS_RESULTS2
DELETE
FROM [#TRANS_RESULTS1]
WHERE
(EXISTS
(SELECT *
FROM [#TRANS_RESULTS2] AS ODSARCH
WHERE [#TRANS_RESULTS1].EXECUTION_DT = ODSARCH.EXECUTION_DT
AND [#TRANS_RESULTS1].POLICY_NBR = ODSARCH.POLICY_NBR
AND [#TRANS_RESULTS1].FORM_CD = ODSARCH.FORM_CD
AND [#TRANS_RESULTS1].SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND [#TRANS_RESULTS1].CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND [#TRANS_RESULTS1].SERVER_NM = ODSARCH.SERVER_NM))
select * from #TRANS_RESULTS1
Everything works in this test script, and the #TRANS_RESULTS1 has 1 of the 4 inserted rows deleted just as expected: row 6 (row 8 has different values for SERVER_NM so would not be deleted). Jeffrey, it seems that the script works correctly both with and without the parenthesis that you suggested I remove, thus I left them in.
Alas, it still doesn't work for me. In real life, this is a stored procedure that runs against 2 seperate databases (not sure how to set that up in a script for posting in the forum).
USE [ODS_S1]
GO
/****** Object: StoredProcedure [dbo].[sp_ODSExportDelete] Script Date: 07/09/2008 12:59:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ODSExportDelete]
-- Add the parameters for the stored procedure here
@sourceServer varchar(30),
@sourceDB varchar(10),
@targetServer varchar(30),
@targetDB varchar(10)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
--SET ANSI_WARNINGS Off
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
declare @sql nVarChar(1050)
SELECT @sql = '
DELETE
FROM ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS]
WHERE
(EXISTS
(SELECT *
FROM ['+@targetServer +'].[' + @targetDB+ '].dbo.[TRANS_RESULTS] AS ODSARCH
WHERE ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].EXECUTION_DT = ODSARCH.EXECUTION_DT
AND ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].POLICY_NBR = ODSARCH.POLICY_NBR
AND ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].FORM_CD = ODSARCH.FORM_CD
AND ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS].SERVER_NM = ODSARCH.SERVER_NM))'
Exec (@SQL)
I run this by calling it from an SSIS job, and it throws these errors:
Message: Executing the query "exec sp_ODSExportDelete ?,?,?,?" failed with the following error: "The multi-part identifier "PLODS_DEV.ODS_S1.dbo.TRANS_RESULTS.SERVER_NM" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is there a way to put back in the 'ODS' table alias that Gsquared saw fit to remove, if perhaps that is related to my problem?
thanks
July 9, 2008 at 11:17 am
I run this by calling it from an SSIS job, and it throws these errors:
Message: Executing the query "exec sp_ODSExportDelete ?,?,?,?" failed with the following error: "The multi-part identifier "PLODS_DEV.ODS_S1.dbo.TRANS_RESULTS.SERVER_NM" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is there a way to put back in the 'ODS' table alias that Gsquared saw fit to remove, if perhaps that is related to my problem?
thanks
My best guess is that you don't have the linked servers set up. Since this is being run from SSIS - I would probably not use a stored procedure and instead would build the command in SSIS directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2008 at 11:45 am
You might try this for building your delete statement:
SELECT @sql = 'DELETE FROM ODS
FROM [' + @sourceServer '].[' + @sourceDB + '].dbo.Trans_Results ODS
INNER JOIN ['+@targetServer +'].[' + @targetDB+ '].dbo.Trans_Results ODSArch
ON ODS.EXECUTION_DT = ODSARCH.EXECUTION_DT
AND ODS.POLICY_NBR = ODSARCH.POLICY_NBR
AND ODS.FORM_CD = ODSARCH.FORM_CD
AND ODS.SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND ODS.CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND ODS.SERVER_NM = ODSARCH.SERVER_NM'
See if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 11:58 am
Jeffrey,
Thanks for your posting. I think the linked servers are set up, because a similar query does successfully copy the rows from ODS to ODSARCH without trouble. I would use the T-Sql cmds directly inside the job steps instead of a stored procedure, if I only knew how to make it all work.
Here is how the SSIS job is set up currently.
Step 1: Link to Source Server
General/SqlStatement: EXEC sp_addlinkedserver @server = ?
(I have no idea how this prior line works)
Parameter Mapping: User::sourceServer Input VARCHAR @sourceServer
Step 2: Link to Target Server
General/SqlStatement: EXEC sp_addlinkedserver @server = ?
Parameter Mapping: User::targetServer Input VARCHAR @targetServer
Step 3: Copy ODS to ODSArch
General/SqlStatement: exec sp_ODSExport ?,?,?,?
(I will list this stored procedure later)
Parameter Mapping:
User::sourceServer Input VARCHAR @sourceServer
User::sourceDB Input VARCHAR @sourceDB
User::targetServer Input VARCHAR @targetServer
User::targetDB Input VARCHAR @targetDB
Step 4: Purge ODS
General/SqlStatement exec sp_ODSExportDelete ?,?,?,?
(this is the stored procedure listed above, that keeps failing)
Parameter Mapping:
User::sourceServer Input VARCHAR @sourceServer
User::sourceDB Input VARCHAR @sourceDB
User::targetServer Input VARCHAR @targetServer
User::targetDB Input VARCHAR @targetDB
Step 5: Drop Target Server
General/SqlStatement EXEC sp_dropServer @server = ?, @droplogins = 'droplogins'
Parameter Mapping:
User::targetServer Input VARCHAR @targetServer
Step 6: Drop Source Server
General/SqlStatement EXEC sp_dropServer @server = ?, @droplogins = 'droplogins'
Parameter Mapping:
User::targetServer Input VARCHAR @targetServer
And here is the copy stored procedure that appears to be working fine:
USE [ODS_S1]
GO
/****** Object: StoredProcedure [dbo].[sp_ODSExport] Script Date: 07/09/2008 13:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ODSExport]
-- Add the parameters for the stored procedure here
@sourceServer varchar(30),
@sourceDB varchar(10),
@targetServer varchar(30),
@targetDB varchar(10)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
--SET ANSI_WARNINGS Off
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
declare @ArchiveStartDate varchar(20)
set @ArchiveStartDate = (convert(varchar(20),(select dbo.fnCalculateArchiveStartDate()),120))
declare @sql nVarChar(1050)
SELECT @sql = '
INSERT ['+@targetServer +'].[' + @targetDB+ '].dbo.[TRANS_RESULTS] (
EXECUTION_DT,
TRANS_CD,
SCHEMA_NM,
SCHEMA_VERS_NBR,
RESULTS_XML,
POLICY_NBR,
FORM_CD,
SEQUENCE_NBR,
CUSTOMER_ID,
SERVER_NM,
RULE_SVC_TYPE_CD)
SELECT EXECUTION_DT,
TRANS_CD,
SCHEMA_NM,
SCHEMA_VERS_NBR,
RESULTS_XML,
POLICY_NBR,
FORM_CD,
SEQUENCE_NBR,
CUSTOMER_ID,
SERVER_NM,
RULE_SVC_TYPE_CD
FROM ['+ @sourceServer+'].['+@sourceDB+'].dbo.[TRANS_RESULTS] AS ODS
WHERE (NOT EXISTS
(SELECT ODSARCH.EXECUTION_DT,
ODSARCH.POLICY_NBR,
ODSARCH.FORM_CD,
ODSARCH.sequence_nbr,
ODSARCH.CUSTOMER_ID,
ODSARCH.SERVER_NM
FROM ['+@targetServer +'].[' + @targetDB+ '].dbo.[TRANS_RESULTS] AS ODSARCH
WHERE ODS.EXECUTION_DT = ODSARCH.EXECUTION_DT
AND ODS.POLICY_NBR = ODSARCH.POLICY_NBR
AND ODS.FORM_CD = ODSARCH.FORM_CD
AND ODS.SEQUENCE_NBR = ODSARCH.SEQUENCE_NBR
AND ODS.CUSTOMER_ID = ODSARCH.CUSTOMER_ID
AND ODS.SERVER_NM = ODSARCH.SERVER_NM)
AND ODS.EXECUTION_DT < ''' +@ArchiveStartDate+ ''')'
Exec (@SQL)
CREATE function [dbo].[fnCalculateArchiveStartDate]()
-- Returns a datetime value
returns datetime
as
begin
declare @ArchiveStartDate datetime
set @ArchiveStartDate = (select dbo.fnDateOnly(getdate()) + dbo.fnTime(00,00,0))
--RETURN CONVERT(VARCHAR(12), @ArchiveStartDate, 101)
return @ArchiveStartDate
end
GO
GRANT EXECUTE ON [dbo].[fnCalculateArchiveStartDate] TO EXECPROC
GO
---------------------
The purge query is supposed to be a simplified variation of the existing copy query that works fine. Any suggestions?
July 9, 2008 at 2:48 pm
If you can't get it to work with the changes GSquared has outlined, I would rethink this approach. Based upon what you have shown to this point, it appears that you are not leveraging the abilities of SSIS and just using it to execute a stored procedure.
In SSIS - I would have a connection to the source database and a connection to the archive database. Then I would set up the following:
Control Flow - archive data
Data Flow
OLEDB connection to source, with a SQL command to extract the data to be archived.
OLEDB connection to archive, target would be the table that you are archiving to
Connect these - this will archive the selected data
A second control flow item, connected to the first - on success. This item would be an Execute SQL Task. Set this item up with the connection to the source, with the DELETE statement you are currently building.
That should be it - when run, the package would extract the rows from the source, input those rows into the destination table - and once completed, execute the delete statement on the source to remove the rows that were exported.
No need for linked servers at all - and can be setup/scheduled to run from the archive system (if Integrated Services has been installed there, of course).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply