Error when using SP in data flow

I want to execute a stored procedure in an OLE DB source in a Data Flow Task.

The stored procedure has a parameter.

When I put in an SQL command as :

EXEC sp_readcustomers 1

(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.

The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :

EXEC sp_readcustomers ?

In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.

@.par_company_id has been declared as int, var_company_id as Int16

When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :

Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Preview : No value given for one or more required parameters (Microsoft SQL Native Client)

I have already installed SQL 2005 SP2 and VS2005 SP1.

I have tried everything I know, please help ?!?!

Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

|||

Ronald Dirkx wrote:

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.

Error when updating

I get the following error when I execute the statement below. 'Invalid
length parameter passed to the substring function'
Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase
WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase
Else TxnUnits
END
Where TxnUnits is not Null
It took a while to get this code correct with help and now evidently one of
the answers from the math causes an error. Any help is appreciated.Hi
Posting DDL an example data would help to see what you are trying to achive
and help re-create the problem. The statement is assuming that TxnUnitsDur i
s
at least one character which could is probably causing the problem.
John
"Job" wrote:

> I get the following error when I execute the statement below. 'Invalid
> length parameter passed to the substring function'
> Update Tbl_Txn_UT
> Set Fxd_Units =
> CASE
> WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%' and TxnUnitsDur like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
> WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%')
> THEN TxnUnitsBase
> WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
> TxnUnitsBase
> Else TxnUnits
> END
> Where TxnUnits is not Null
>
> It took a while to get this code correct with help and now evidently one o
f
> the answers from the math causes an error. Any help is appreciated.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Ju
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Hi
You seem to have some problem posting?
John
"Job" <Job@.nomail.com> wrote in message
news:uUH$jpCoFHA.708@.TK2MSFTNGP09.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>|||John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly.
Cheers!
Job wrote:
> Ju
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>
>
>

Error when passing GUID as Parameter

Hello - I am using SSRS 2005 and my report dataset has a parameter of type
GUID. In 2000 I could just pass this in as a string. I now get the error:
"Failed to convert parameter value from a String to a Guid."
I've tried setting my Dataset parameter value as:
=Guid(Parameters!<my parameter>.Value)
and
=New Guid(Parameters!<my parameter>.Value)
and I've tried passing in my string with and without the {} braces around
the GUID string.
Anyone have the anwser here? Thank you.I have confirmed that this appears to be a bug. However, there is a
work-around:
The report in the VS report preview or when deployed will work properly with
a uniqueidentifier parameter when the value is being passed in as string. In
order to execute the dataset in your report project, temporarily modify your
stored procedure to accept the guid as a string and convert it to a
uniqueidentifier in your proc. When you execute it and get your dataset
fields to design your report, you can change it back to a uniqueidentifier
parameter.
"justinsaraceno" wrote:
> Hello - I am using SSRS 2005 and my report dataset has a parameter of type
> GUID. In 2000 I could just pass this in as a string. I now get the error:
> "Failed to convert parameter value from a String to a Guid."
> I've tried setting my Dataset parameter value as:
> =Guid(Parameters!<my parameter>.Value)
> and
> =New Guid(Parameters!<my parameter>.Value)
> and I've tried passing in my string with and without the {} braces around
> the GUID string.
> Anyone have the anwser here? Thank you.

Error using Multi-value Report Parameter

I've checked the Multi-value in a Report Parameter. When running the report
if I check more than one value in the parameter dropdown I get the
following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'ActivityDetail'.
(rsErrorExecutingCommand)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
Does anyone know what can be causing this error or what I'm supposed to look
for after I "navigate to the report server"? How do I enable remote errors?
Thanks - Ed TPost your SQL, that will help us know what is going on.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ed T" <etroche@.yahoo.com> wrote in message
news:X27Of.12$422.7@.fed1read10...
> I've checked the Multi-value in a Report Parameter. When running the
> report if I check more than one value in the parameter dropdown I get the
> following error:
>
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'ActivityDetail'.
> (rsErrorExecutingCommand)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>
> Does anyone know what can be causing this error or what I'm supposed to
> look for after I "navigate to the report server"? How do I enable remote
> errors?
>
> Thanks - Ed T
>