Issues using parameterised reports connecting to Oracle using ODBC and Microsoft OLE DB Provider

I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?

Thanks.

It could have something to do with the syntax you are using to pass the parameters. Oracle uses : (colon) instead of @. for SQL Server

See this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=679346&SiteID=1

|||

Hi,

Cheers. Unfortunately that's not the issue. Have query like this:

"select unique change_id from change

where (change.change_id like :changeCategory)"

Any other ideas?

Thanks.

|||

I'm not sure, but I think that depending on which provider you connect with you may need to change the named parameter syntax to unnamed. I think you replace you variable with a question mark (?)

You can give it a go. Other than that I can't really help - not my area of expertise.

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver t
o
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas
"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:

> I have used the ole db services for odbc to utilise a informix odbc driver
to
> create my linked server in SQL server. The Informix IDS server in questio
n
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.
Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas

"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.
|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:

> I have used the ole db services for odbc to utilise a informix odbc driver to
> create my linked server in SQL server. The Informix IDS server in question
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

issues in Linked server to Informix IDS

I have used the ole db services for odbc to utilise a informix odbc driver to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.Look up in SQL Server Books Online Linked Servers OLEDB Provider Properties.
If you misset these, then SQL Server will have to copy all data to a temp
table before applying any filters and/or rollup aggregations. Basically,
you want to offload as much work as possible to the data provider for which
you are extracting data.
To modify these properties, initiate the creation of a new linked server for
the same OLEDB provider type, then select the provider properties button.
After setting the properties, and saving, then you can cancel the creation
of that specific linked server.
Provider properties are set server-wide for linked servers of that provider
type.
Keep in mind, however, that if you ever join a table from a linked server
and the local server, there will have to be some sort of data copy into a
temp table in order to hash the join. There is no way around this as the
data must be gathered collectively to a single host to process. You want to
process as much pre-filtering on the invidual host prior to joining the
remaining result sets.
Sincerely,
Anthony Thomas
"CPiO" <CPiO@.discussions.microsoft.com> wrote in message
news:76C6DE2C-F066-41A3-9245-39B3742DB9B2@.microsoft.com...
I have used the ole db services for odbc to utilise a informix odbc driver
to
create my linked server in SQL server. The Informix IDS server in question
is v9.4 on Suse Linux Enterprise V8.
If I use the informix ODBC via Query in Excel I can retrieve data from the
IDS databases with no problem, it is very quick.
If I create a view in SQL server (which uses the linked server) to look at
the same data in the IDS databases the same data will take an avaerage of
50seconds to come back.
I am lost, please advise.
Could it be the way in which I have setup the linked server, msdtc, tempdb
etc etc.|||Hi Anthony,
Thankyou for your reply, as suggested I have tried modding the properties
behind the ole provider for odbc but this has made no difference.
All that we are doing is creating a view inside a SQL dbase which is looking
at a table in the IDS system.
THEN
We are then firing a selective query to that view where we are seeing 3-4
mins of wait time. Again access into that table is fine via query and odbc.
Any more ideas would be greatly apreciated.
James
"CPiO" wrote:
> I have used the ole db services for odbc to utilise a informix odbc driver to
> create my linked server in SQL server. The Informix IDS server in question
> is v9.4 on Suse Linux Enterprise V8.
> If I use the informix ODBC via Query in Excel I can retrieve data from the
> IDS databases with no problem, it is very quick.
> If I create a view in SQL server (which uses the linked server) to look at
> the same data in the IDS databases the same data will take an avaerage of
> 50seconds to come back.
> I am lost, please advise.
> Could it be the way in which I have setup the linked server, msdtc, tempdb
> etc etc.

Issue with OLE DB Command while writing to DB2 database

Hi,

I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.

The same package works fine when the target is on Oracle database or a SQL Server database.

For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.

Any suggestion regarding this would be really helpful.

Thanks,

Manish

Some thoughts on this:

1. It is better to use the OLE DB Destination to insert data into a table instead of the OLE DB Command with an insert statement. You should give that a try.

2. Are you using the Microsoft OLE DB provider for DB2 from our SQL Server 2005 Feature Pack? That's the provider that's been tested with SSIS.

3. I don't understand what you mean by not able to get input columns. What are the error messages when the package fails?

|||

Yes, I am using the Microsoft DB2 drivers only. Using the OLE DB Destination, it worls only for insert. But, I wanted to simulate a scenario, where we do, both insert and update to the target table. So, we used OLE DB Command, and wrote the insert/update query.

The same works fine on Oracle and SQL Server target database. But on DB2, it doesnt work, as in the OLE DB command, we need to map the input columns to the parameters value. The drivers are not able to fetch the input data, and everytime, it insert NULL as it is not able to get the input values.

Let me know if you need any more information on the same.

Issue with Dynamic SQL stored procedure and SSIS OLE DB Data Reader

We are writing SSIS packages to pull data from MSO’s through a stored procedure and drop the output into the pipeline to store it on our local database.

Here is an example of a very simple data flow task that executes the following query in step 1

EXEC GetCustomerData @.SELECT = 'OrganizationID', @.FROM = 'Organization',@.WHERE = 'StateOrProvinceCode = ''WA'''

This command is stored in a variable called SQLOrgCommand.

When I open the task and click on Columns I do not see any columns returned

This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.

While profiling we realized that it executed the following SQL:

SET FMTONLY ON

to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100

Invalid length parameter passed to the SUBSTRING function.

Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101

Invalid length parameter passed to the RIGHT function.

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116

Invalid length parameter passed to the SUBSTRING function.

Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117

Invalid length parameter passed to the RIGHT function.

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187

Invalid length parameter passed to the SUBSTRING function.

The same stored procedure executed without FMTONLY OFF generates the proper output.

Is this a known bug in SQL Server? If so is there a patch that we can install.

Can you post an example of the stored proc you're using? I created a dynamic sql sproc and it seems to work just fine.