invalid pointer

[sedgely]

i am trying to create a dts package that uses a sp to get data and export to a text file.

the dts has a sql table as source and .txt file as destination the transform is

declare @date1 datetime
declare @date2 datetime
set dateformat dmy
SET @DATE1 = CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-7,103),103)
SET @DATE2 = CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-1,103),103)
SET DATEFORMAT DMY EXEC  my_sp @date1, @date2

and the sp is

CREATE PROC my_sp
@date1 DATETIME
,@date2 DATETIME

AS

SET NOCOUNT ON

SELECT COUNT(DT.TROUND)AS [NUMBER OF DELAYS],LU.[NAME] AS [HNAME]
FROM
(
SELECT HNAME1 AS HNAME,DBO.CWTIMEDIFF(ARRH1,GREEN1) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)
AND ARRH1 IS NOT NULL

UNION ALL

SELECT HNAME2 AS HNAME,DBO.CWTIMEDIFF(ARRH2,GREEN2) AS TROUND
FROM TBL1WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)

UNION ALL

SELECT HNAME3 AS HNAME,DBO.CWTIMEDIFF(ARR3,GREEN3) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)

UNION ALL

SELECT HNAME4 AS HNAME,DBO.CWTIMEDIFF(ARRH4,GREEN4) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)
)DT
LEFT OUTER JOIN LU_HS LU
	ON RTRIM(DT.HNAME) = RTRIM(LU.[CODE])
WHERE TROUND > 25
AND ISNULL(LU.[NAME],'')<>''
GROUP BY LU.[NAME]
ORDER BY HNAME
GO

When run this fails with a "invalid pointer" error.
if i use

declare @date1 datetime
declare @date2 datetime

set dateformat dmy

SET @DATE1 = CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-7,103),103)
SET @DATE2 = CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-1,103),103)
 
AS

SET NOCOUNT ON

SELECT COUNT(DT.TROUND)AS [NUMBER OF DELAYS],LU.[NAME] AS [HNAME]
FROM
(
SELECT HNAME1 AS HNAME,DBO.CWTIMEDIFF(ARRH1,GREEN1) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)
AND ARRH1 IS NOT NULL

UNION ALL

SELECT HNAME2 AS HNAME,DBO.CWTIMEDIFF(ARRH2,GREEN2) AS TROUND
FROM TBL1WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)

UNION ALL

SELECT HNAME3 AS HNAME,DBO.CWTIMEDIFF(ARR3,GREEN3) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)

UNION ALL

SELECT HNAME4 AS HNAME,DBO.CWTIMEDIFF(ARRH4,GREEN4) AS TROUND
FROM TBL1
WHERE ([DATE] BETWEEN @DATE1 AND @DATE2)
)DT
LEFT OUTER JOIN LU_HS LU
	ON RTRIM(DT.HNAME) = RTRIM(LU.[CODE])
WHERE TROUND > 25
AND ISNULL(LU.[NAME],'')<>''
GROUP BY LU.[NAME]
ORDER BY HNAME
GO

it runs fine.
any suggestions anyone?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Catadmin]

What data type is Dateformat?

I see you setting it, but not declaring it and I don't see it referenced in BOL as a reserved keyword.

And are you trying to set Dateformat as a dynamic SQL command?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"