Error with parameters in OLEDB Source "execute with results"

Question

Error with parameters in OLEDB Source "execute with results"

komal145

SSCrazy Eights

Points: 9918
More actions
December 8, 2016 at 9:49 am

#338143

hi All,
I have a package , where i get the results from a storedprocedure and send it to a file.
meaning OLED source ( sql command) ---> Flatfile.
Pacakge runs fine if i pass paramters directly but when I map it through paramters tab. It throws error.
EXEC [dbo].[Sales] @StartDate=? , @EndDate=?
WITH RESULT SETS
(
(
A
,B
,C
)
)
The paramaters mapped to a variables which are string type.
@StartDate='20160601' , @EndDate='20160708' ...this works fine if i manually assign values.
If i map it ....to variables it throws error " cannot convert varcahr to numeric"
Please suggest.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

komal145 (12/8/2016)
hi All,
I have a package , where i get the results from a storedprocedure and send it to a file.
meaning OLED source ( sql command) ---> Flatfile.
Pacakge runs fine if i pass paramters directly but when I map it through paramters tab. It throws error.
EXEC [dbo].[Sales] @StartDate=? , @EndDate=?
WITH RESULT SETS
(
(
A
,B
,C
)
)
The paramaters mapped to a variables which are string type.
@StartDate='20160601' , @EndDate='20160708' ...this works fine if i manually assign values.
If i map it ....to variables it throws error " cannot convert varcahr to numeric"
Please suggest.

could it be your result sets?

i know you pseudocoded the result sets for the example you pasted , but if you define your results set to be

like this:

WITH RESULT SETS

(

A money,

,B money,

,C money

)

if your stored procedure actually returns a varchar for any of the columns (ie an empty string for some value), you would get that same error.

for stored procedure results like that, i typically insert into a temp table, then script/interogate the tempdb.sys.columns to get the exact data type being used for the results.

--insert the stored procs results into a temp table so i can script the resultset, then remove the temporary insert into

SELECT

TOP 3

*

INTO #temp

FROM sys.objects

--use this to build the resultset actual datatype/size

SELECT [colz].name,[colz].column_id,TYPE_NAME([colz].user_type_id),[colz].[max_length],[colz].[precision],[colz].[scale]

FROM tempdb.sys.columns colz

WHERE OBJECT_ID = OBJECT_ID('tempdb.dbo.#temp')

ORDER BY column_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

Lowell, that's a neat trick!

Next step would be some refinement to return the actual SQL needed, ready for cut & paste. This sort of thing (borrowing your code):

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL

DROP TABLE #temp;

SELECT TOP 3

*

INTO #temp

FROM sys.objects;

--use this to build the resultset actual datatype/size

SELECT

colz.name

, colz.column_id

, TYPE_NAME(colz.user_type_id)

, colz.max_length

, colz.precision

, colz.scale

, script = CONCAT('[', colz.name, '] ', TYPE_NAME(colz.user_type_id), lth.Lth,',')

FROM

tempdb.sys.columns colz

CROSS APPLY

(

SELECT Lth = (CASE

WHEN TYPE_NAME(colz.user_type_id) IN ('char', 'varchar', 'nchar', 'nvarchar') THEN

CONCAT('(', CAST(colz.max_length AS VARCHAR(4)),')')

ELSE

''

END

)

) lth

WHERE colz.object_id = OBJECT_ID('tempdb.dbo.#temp')

ORDER BY colz.column_id;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel