Parameterized query that will work for both Oracle and SQL Server

Hi, I have a dataset that I am using the following SQL DML to gather the data
select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate = ?
This works fine when the Shared Data Source is Oracle. However when I am
using a SQL Server Shared Data Source I need to change it to the following
with the Native SQL Client
select * from hermes.vwsurveyprepostresults where ssn = @.SSN and surveydate
= @.SURVEYDATE
However when I change it to use OLEDB for SQL Server as a shared data source
and revert to the ? as parameter place holders I get the following error -
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
Incorrect syntax near '?'. Incorrect syntax near '?'.
Is there a parameterized query that will work for both Oracle and SQL Server?
--
Thank you,
JohnTry to set Oracle a linked server and using store procedure as the query
string.
You can generate you SQL synatx in store procedure depending on the input
parameter.
HTH
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Is this the easiest way to do it? Seems like a hack, inelegant and not
appealing.
Currently I am trying to figure out how to use a Web Service as my data
source - XML. That way I can control via a config file which database to
pull from and the paremeters will be the same.
Both these solutions seem like a convulusion of what should be a relatively
easy thing to do. Pass an ANSI-SQL query a parameter using OLEDB as the
provider and have the parameter signature always be the same. I cannot
believe this is such a difficult thing. If I did not know better I would
swear that this is a bug.
--
Thank you,
John
"Steffi" wrote:
> Try to set Oracle a linked server and using store procedure as the query
> string.
> You can generate you SQL synatx in store procedure depending on the input
> parameter.
> HTH
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||If you use ODBC against both then you will be able to use the same query.
But, keep in mind that Oracle and SQL Server can have different syntax. But,
if you are using the more recent Oracle (like 9i or 10i ... not sure what
the latest version is) then Oracle has started to support join syntax (inner
join, left join etc). Otherwise, if you get complicated at all then the
syntax can vary.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Hi John,
Thank you for your post.
OLE DB is a Microsoft's strategic low-level application program interface
(API) for access to different data sources.
An application using OLE DB would use this request sequence:
Initialize OLE.
Connect to a data source.
Issue a command.
Process the results.
Release the data source object and uninitialize OLE.
The Oledb provide just pass the sql command to the database engine to
execute and get the recordset from the engine.
Since the syntax of SQL Server only support the variable marked with @., you
could not use other mark to specify it's a parameter.
I think Steffi's suggestion is a great suggest for you. You could add a
linked server in the sql server and use the stored procedure to get the
result.
Configuring Linked Servers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_4uuq.asp
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help guys but I am leaning more toward using a Web Service
AKA XML as my data source. Played around with it over the weekend and got it
working. This actually has a number of benefits for me. The main one being
greater control over what is happening from a programatic stand point. I
have nothing against using Stored Procedures per se but I think that I would
run into problems linking the databases as I do not have much control over
our Oracle databases.
--
Thank you,
John
"Wei Lu" wrote:
> Hi John,
> Thank you for your post.
> OLE DB is a Microsoft's strategic low-level application program interface
> (API) for access to different data sources.
> An application using OLE DB would use this request sequence:
> Initialize OLE.
> Connect to a data source.
> Issue a command.
> Process the results.
> Release the data source object and uninitialize OLE.
> The Oledb provide just pass the sql command to the database engine to
> execute and get the recordset from the engine.
> Since the syntax of SQL Server only support the variable marked with @., you
> could not use other mark to specify it's a parameter.
> I think Steffi's suggestion is a great suggest for you. You could add a
> linked server in the sql server and use the stored procedure to get the
> result.
> Configuring Linked Servers
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _1_server_4uuq.asp
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks Bruce, this seems like the easiest and best short term solution.
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||John,
I wanted to put this post because I was trying to do the same thing that you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out there.
Rob Cuscaden
"John A" wrote:
> Thanks Bruce, this seems like the easiest and best short term solution.
> --
> Thank you,
> John
>
> "Bruce L-C [MVP]" wrote:
> > If you use ODBC against both then you will be able to use the same query.
> > But, keep in mind that Oracle and SQL Server can have different syntax. But,
> > if you are using the more recent Oracle (like 9i or 10i ... not sure what
> > the latest version is) then Oracle has started to support join syntax (inner
> > join, left join etc). Otherwise, if you get complicated at all then the
> > syntax can vary.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John A" <i-code4food@.newsgroups.nospam> wrote in message
> > news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > > Hi, I have a dataset that I am using the following SQL DML to gather the
> > > data
> > > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > > ?
> > >
> > > This works fine when the Shared Data Source is Oracle. However when I am
> > > using a SQL Server Shared Data Source I need to change it to the following
> > > with the Native SQL Client
> > > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > > surveydate
> > > = @.SURVEYDATE
> > >
> > > However when I change it to use OLEDB for SQL Server as a shared data
> > > source
> > > and revert to the ? as parameter place holders I get the following error -
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> > > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > > Incorrect syntax near '?'. Incorrect syntax near '?'.
> > >
> > > Is there a parameterized query that will work for both Oracle and SQL
> > > Server?
> > >
> > >
> > > --
> > > Thank you,
> > > John
> >
> >
> >|||Bruce -- I am just trying your suggestion, since I have the same issue. In
my query, how do I write the parameters (with a '?', ':' or '@.')?
I have tried all 3 with no success.
When I try '?' - I get an error saying that "Cannot add multi value query
parameter '?' for data set 'Report_main' because it is not supported by the
data extension."
When I try ':' - it says incorrect syntax near ':'
When I try '@.' - it says "The data extension ODBC does not support named
parameters. Use unnamed parameters instead."
Thanks in advance for your help.
--
LaurieT
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||Hello Laurie,
Have you tried the suggestion Rob Provided?
===========================From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
Date: Fri, 21 Jul 2006 09:39:01 -0700
Newsgroups: microsoft.public.sqlserver.reportingsvcs
John,
I wanted to put this post because I was trying to do the same thing that
you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out
there.
Rob Cuscaden
===========================
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes -- I think so.
I was already using ":" variables in my query. But I was connecting to the
database using an Oracle driver. So I set up a ODBC connection to my SQL
Server database, then I created a new datasource using ODBC and pointed to
the SQL Server ODBC connection. I get the following error: incorrect syntax
near the ':'.
I must be missing something?
--
LaurieT
"Wei Lu [MSFT]" wrote:
> Hello Laurie,
> Have you tried the suggestion Rob Provided?
> ===========================> From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
> Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
> Date: Fri, 21 Jul 2006 09:39:01 -0700
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> John,
> I wanted to put this post because I was trying to do the same thing that
> you
> are, using parameters with Reporting Services to hit an Oracle DB.
> The only way that I found out how to get this to work is by using a ":"
> instead of the "@.".
> So, for example:
> SELECT *
> FROM sometable x
> WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
> Then I set my Report Parameter(param_start) as a string value.
> It works fine for me right now, I do wonder when I try to roll it up into a
> stored proc that if it will work or not. I hope this helps others out
> there.
> Rob Cuscaden
> ===========================> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I've been working with some of the same issues and found that you can't
really blur the like between the Oracle and SQL Server data.
You have to query the Oracle db with Oracle syntax. If you want to roll
everything up to a SQL Server OLTP or OLAP db, design an SSIS package
and import your Oracle & SQL data into a single source.
I know it's not the answer you wanted, but in the end it will be faster.
Garth H
webdev511@.spamcop.net
Microsoft Certified Professional
Macromedia Certified Developer|||Hello Laurie,
I agree with Garth that design a SSIS package(in SQL 2005) or a DTS package
(in SQL 2000) will be a faster way to do this.
Also, you may try to build up a Linked server in SQL Server which point to
the Oracle database.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.