September 17, 2009 at 11:48 pm
Hi everyone,
In a few weeks we will be moving a major enterprise database to a new server. We have multiple DTS packages that use OLE DB connections to this database, and rather than madly change hundreds of connections on go-live night, we thought we'd swap the OLE DB connections for a single ODBC connection. Then, on the night, we simply have to change the ODBC connection.
This strategy works for Execute SQL Tasks, or for Data Transformations that copy data from one source table/query to a destination. However, we have quite a few data transformations that have a stored procedure as their source. Now, I find the source transformations have quite literally disappeared - when clicking on the transformations tab, first the dialogue box appears saying there are colums that don't exist, and when I opt for the redo auto-mapping, there is nothing in the source pane.
The source for the Transform Data Task is
EXEC dbo.RH_BOOT_ACUTE_LIST ?,? where the two question marks are global variables representing a start and end date/time in string format (eg 18/09/2009 15:00).
The stored procedure looks like this:
CREATE PROCEDURE dbo.RH_BOOT_ACUTE_LIST
(
@START_DATEVARCHAR(20),
@END_DATEVARCHAR(20))
AS
-- Start extract processing
SET DATEFORMAT DMY
SET NOCOUNT ON
DECLARE @START_DTTM AS DATETIME
DECLARE @END_DTTM AS DATETIME
SET @START_DTTM = CONVERT(DATETIME,@START_DATE)
SET @END_DTTM = CONVERT(DATETIME,@END_DATE)
SELECT
:
:
FROM
:
:
WHERE
:
:
The stored procedure works fine in Query Analyzer, and the Transform Data Task worked with the OLE DB connections. I've had a similar problem in SSIS which I solved by inserting SET FMTONLY ON into the source SQL statement, but this isn't working in this case.
September 20, 2009 at 11:18 pm
Me again...
I've discovered the problem is that ODBC basically strips the metadata from stored procedures in this case, which means, in this case, if the stored procedure returns a result set, the column headers for the result set are stripped away.
If anyone else ever has this problem, you can solve it by replacing your stored procedures with functions that return a table variable; but you may find this causes performance issues if you are returning large data sets.
Function example:
CREATE FUNCTION function_name
(
@START_DATEVARCHAR(20),
@END_DATEVARCHAR(20)
)
RETURNS @return_table TABLE
(table definition)
AS
-- Start extract processing
BEGIN
(any pre-select processing like formatting parameters)
INSERT INTO @return_table
SELECT * (make sure you have all your table columns defined)
FROM ~~
WHERE ~~
RETURN
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply