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 altering a table after applying Hotfix 821334 need to instal

After I installed the hotfix required to install the Reporting Server Service
I can no longer alter a table. I get the message:
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
I have also installed sp1 for the reporting service and still have the same
problem.
Is there a fix for this?
-- JesseHi Jesse,
From your descriptions, I understood that you will not able to alter table
column in the SQL Server Enterprise Manager. Have I understood you? Correct
me if I was wrong.
Based on my scope, it was an known issue of us. To resolve this problem,
install the hotfix that is included in the following Microsoft Knowledge
Base article:
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
Please notice that asking an hotfix will be a free incident with Microsoft
PSS:)
To find more detailed description of this issue, please check following KB
An addition to the SQL Server 2000 Reporting Services SP1 Readme.htm file
http://support.microsoft.com/?id=843369
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Thank you, once I installed the hotfix referred to on the link below,
everything is fine. It's odd that I didn't find that page when I was
searching though.
-- Jesse
""Mingqing Cheng [MSFT]"" wrote:
> Hi Jesse,
> From your descriptions, I understood that you will not able to alter table
> column in the SQL Server Enterprise Manager. Have I understood you? Correct
> me if I was wrong.
> Based on my scope, it was an known issue of us. To resolve this problem,
> install the hotfix that is included in the following Microsoft Knowledge
> Base article:
> FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
> later in SQL Server 2000
> http://support.microsoft.com/kb/831997
> Please notice that asking an hotfix will be a free incident with Microsoft
> PSS:)
> To find more detailed description of this issue, please check following KB
> An addition to the SQL Server 2000 Reporting Services SP1 Readme.htm file
> http://support.microsoft.com/?id=843369
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi Jesse,
It's great to hear it works fine for you:)
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||The hotfix is not available on the web site? where is it available?
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:Q3cY1UAuEHA.3660@.cpmsftngxa10.phx.gbl...
> Hi Jesse,
> From your descriptions, I understood that you will not able to alter table
> column in the SQL Server Enterprise Manager. Have I understood you?
> Correct
> me if I was wrong.
> Based on my scope, it was an known issue of us. To resolve this problem,
> install the hotfix that is included in the following Microsoft Knowledge
> Base article:
> FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
> later in SQL Server 2000
> http://support.microsoft.com/kb/831997
> Please notice that asking an hotfix will be a free incident with Microsoft
> PSS:)
> To find more detailed description of this issue, please check following KB
> An addition to the SQL Server 2000 Reporting Services SP1 Readme.htm file
> http://support.microsoft.com/?id=843369
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi Gary,
You will have to contact Microsoft Product Support Services to obtain the
hotfix. For a complete list of Microsoft Product Support Services phone
numbers and information about support costs, visit the following Microsoft
Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
The knowledge base articles have described this in detail. And here is why
it is not public released: A supported hotfix is now available from
Microsoft, but it is only intended to correct the problem that is described
in this article. Only apply it to systems that are experiencing this
specific problem. This hotfix may receive additional testing. Therefore, if
you are not severely affected by this problem, Microsoft recommends that
you wait for the next Microsoft SQL Server 2000 service pack that contains
this hotfix.
Hope this helps.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!