I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @x char(1)
SET @x = (SELECT '1')
SELECT cast(@x as smallint)
RETURN
Please give an example of what doesn't work for you.
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECT id,
[Name],
SirName
FROM #test
DROP TABLE #test
RETURN
GO
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
create procedure etl.executeScript @scriptId int, @sessionId varchar(50)
as
set nocount on
declare @script nvarchar(max)
select @script=replace(script,'SESSION-ID',@sessionId)
from etl.script
where id=@scriptId
exec sp_executesql @script
return