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 using a stored procedure in OLE DB source

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode. It returns values when using the preview but when I test the package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider."how is the ole db source configured?|||For the the connection manager I just have a basic connection to the Sql server and then I am using the SQL command for the data access mode. The connection manager does work when I use other access modes like the table and view option.|||how is the connection manager configured? you may need to use a different configuration.|||ConnectionManagerType = OLEDB
Data Source = ABCSERVER;Initial Catalog=ABCDATABASE;Provider=SQLNCLI.1;OLE DB Services=-13;Integrated Security=SSPI;Auto Translate=True;
DataSourceID =
DelayValidation = False
Description =
RetainSameConnection = False
SupportsDTCTransactions = True|||

DustinT wrote:

ConnectionManagerType = OLEDB
Data Source = ABCSERVER;Initial Catalog=ABCDATABASE;Provider=SQLNCLI.1;OLE DB Services=-13;Integrated Security=SSPI;Auto Translate=True;
DataSourceID =
DelayValidation = False
Description =
RetainSameConnection = False
SupportsDTCTransactions = True

if i'm not mistaken, the sql native client provider (SQLNCLI.1) only works with sql server 2005.

if you are using a different version of sql server, then try using the microsoft ole db provider for sql server (SQLOLEDB.1)

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366077&SiteID=1

error when an OLEDB source points to an OLEDB destination.

Hi all,

I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow TaSK: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

my input columns firstname and lastname have 936 as codepages

but the destination columns lastname and firstname are set to 1252 codepages....and SSIS complains different codepages are used.

How do i change the defualt codepages to 936 for these two columns - firstname and lastname?

|||

You may want to try 1) set DefaultCodePage property at OLEDBDest to 936. 2) set AlwaysUseDefaultCodePage property at OLEDBDest to true.

That should fix the design time issue you got. However, you also need to make sure you set the destination column types properly at your dest table so as to receive good result.

HTH

wenyang

|||

I notice even if i set as 936 for the codepage...the default still doesnt change...so i add a data conversion transform which converts the column to 936 and add it to the destination column at 936 for codepage....

Error using MAX() - linked server to Orcl

I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl.
When I query "select MAX(IntColumn) from ..." I get the error below when
IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9).
No error for simple "select IntColumn from ..." on same tables.
The problem appeared only after SQL 2000 was uninstalled and reinstalled.
The problem persisted after reapplying SP3 for both SQL and for AS.
Also uninstalled/re-installed Orcl 9 client.
I can get around the problem, but I would like to know what DLL/component
got out of sync with all the uninstall/reinstalling.
Thanks,
Les McPheeI should have added details of the error I get...
OLE DB Provider 'MSDAORA" reported an error.
[OLE/DB provider returned message: ORA-01455: converting column overflows
integer datatype]
"lmcphee" wrote:

> I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl
.
> When I query "select MAX(IntColumn) from ..." I get the error below when
> IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9)
.
> No error for simple "select IntColumn from ..." on same tables.
> The problem appeared only after SQL 2000 was uninstalled and reinstalled.
> The problem persisted after reapplying SP3 for both SQL and for AS.
> Also uninstalled/re-installed Orcl 9 client.
> I can get around the problem, but I would like to know what DLL/component
> got out of sync with all the uninstall/reinstalling.
> Thanks,
> Les McPhee

Error trying to build and deploy for first time

I'm getting a timeout error when trying to deploy my SSAS project for the first time - any suggestions on where to go from here?

Error 1 OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00. 0 0
Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Property Address', Name of 'Property Address' was being processed. 0 0
Error 3 Errors in the OLAP storage engine: An error occurred while the 'Unit Number' attribute of the 'Property Address' dimension from the 'foo' database was being processed. 0 0
Error 4 Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure. 0 0
Error 5 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Property', Name of 'Property' was being processed. 0 0
Error 6 Errors in the OLAP storage engine: An error occurred while the 'Status Type ID' attribute of the 'Property' dimension from the 'foo' database was being processed. 0 0

How long time does the task take until you see the timeout error? Also, could you check network connectivity to the relational datasource? What authentication do you use?

Error tmp file is too large

I am getting the follwoing error when running my package.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80004005 Description: "File c:\docume~1\sortiz\locals~1\temp\00003589001o.tmp is too large.".

I use a select statement to join to large foxpro tables. The data contains over a million records.

Any help is greatly appreciated.

Thanks

Try loading the fox pro data into a SQL Server table first, and then do your join there.

error sqlcode -902

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.
how do i fix this? :(Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.


how do i fix this? :( Switch to a real RDBMS.|||Switch to a real RDBMS.There are no real RDBMS packages available for purchase (at least that meet the definition used by Codd and Date). Most of us make do with the tools that are commercially available like DB2, Microsoft SQL Server, Oracle, Sybase, etc. While Interbase doesn't often make that list, its biggest problem is its vendor support, not the underlying product itself.

-PatP

Error Setting a database in Crystal Reports 10

I have created a report in Crystal Reports 10 that is connect to an SQL Server 2005 database. I have used OLE DB (ADO).

The problem is that i want now to connect this report to another database instead, using "Set Datasource Location" and is giving me the error:
"Failed to open a rowset.
Details:ADO Error Code: 0x90040e14
Source:Microsoft OLE DB Provider for SQL Server
Description: Cannot resolve te collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation.
SQL State:42000
Native Error:468"

I went verify the two databases and i saw (properties of the database in sql 2005) that the two had diferent Collations.

One had "Latin1_General_CI_AS" and the other had "SQL_Latin1_General_CP1_CI_AS"

I have changed the first one to "SQL_Latin1_General_CP1_CI_AS" but when i set the new database and i do a refresh the same error appears.

I do not know what i am doing wrong. Can you help me?

Thank you in advanceTry --> 'Verify DataBase' after 'Set DataSource Location'|||When i verify the database it says: "The database is up to date".
When i do the preview after that the same error ocurrs.|||Anyone can help? I can't solve this problem that i am having.