|
|
You're in the
sql server reporting services
group:Parameterized query that will work for both Oracle and SQL Server
sql server reporting services:
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,
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 [quoted text, click to view] > 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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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
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.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |