Parameters & Sybase DB

I have a report that takes a single date parameter. My DataSource however,
could be either a MSSQL db or a Sybase db. Both of these are included as
shared datasources in my project (This is becase some clients of the report
may point to the MSSQL db while others will point to the Sybase db). However,
so far it seems that the rules for writing parameterized queries differ for
these 2 platforms i.e.
For MSSQL, i have:
where date > @.date,
but for Sybase, I had to say:
where date > ?
How can I write just one report so that when deployed, changing the
datasource from MSSQL to Sybase does not cause a problem?Have you tried doing a stored procedure and passing a the parameter in?...
That might work for both...(Although I am surprised that there is even the
difference you have discovered.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>I have a report that takes a single date parameter. My DataSource however,
> could be either a MSSQL db or a Sybase db. Both of these are included as
> shared datasources in my project (This is becase some clients of the
> report
> may point to the MSSQL db while others will point to the Sybase db).
> However,
> so far it seems that the rules for writing parameterized queries differ
> for
> these 2 platforms i.e.
> For MSSQL, i have:
> where date > @.date,
> but for Sybase, I had to say:
> where date > ?
> How can I write just one report so that when deployed, changing the
> datasource from MSSQL to Sybase does not cause a problem?|||One important principle of Reporting Services 2000 is to not "rewrite" the
dataset query. The statement is essentially sent directly to the data
provider. Some data providers do not support named parameters (like OleDb
data providers). Other data providers support named parameters, but they use
different syntax to mark parameters and may have various flavors for SQL
keywords.
E.g.:
Managed SQL Provider: select * from emp where name = @.Name
Managed Oracle Provider: select * from emp where name = :Name
For the Sybase provider, it looks like Aparna is using the Sybase OleDb
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> Have you tried doing a stored procedure and passing a the parameter in?...
> That might work for both...(Although I am surprised that there is even the
> difference you have discovered.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>>I have a report that takes a single date parameter. My DataSource however,
>> could be either a MSSQL db or a Sybase db. Both of these are included as
>> shared datasources in my project (This is becase some clients of the
>> report
>> may point to the MSSQL db while others will point to the Sybase db).
>> However,
>> so far it seems that the rules for writing parameterized queries differ
>> for
>> these 2 platforms i.e.
>> For MSSQL, i have:
>> where date > @.date,
>> but for Sybase, I had to say:
>> where date > ?
>> How can I write just one report so that when deployed, changing the
>> datasource from MSSQL to Sybase does not cause a problem?
>|||Hi Robert,
I actually tired using both the Sybase ASE Ole DB provider, as well as the
'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
Problems'. Here are the steps he mentioned:
---
1. Create the ODBC Machine DSN and test that connection works
2. Pick provider Microsoft OLE DB Provider for ODBC drivers
3. Next
4. Use Data Source name and pick the DSN you created in 1
5. Enter username and password to use.
6. Pick initial catalog to use and click on test connection
7. Click OK
You should now be done. If you have a problem with username and password
after you are done double click on the data source and go to the credential
tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
---
In both cases, I tried running the parameterized query (using @.Date) in both
the Generic Query Designer as well the Visual Query Designer. In both cases,
I get the message "The Data Extension ODBC does not support named parameters.
Use unnamed parameters instead".
I did read about Oracle's conventions for using named parameters, and tried
searching for info. on using named parameters in Sybase, but to no avail. I
know Wayne mentioned using stored procedures, but I wanted to know if I could
use a simple query for both Sybase & Sql Server platforms. Any idea how named
parameters are used for Sybase? All in all, it seems to me that because
there are slight variations in syntax, etc between the various providers, it
may not be possible to write a parameterized query and then dynamically
change the data source provider once deployed (i.e. from Sql Server to Oracle
or Sybase)...Is that correct?
--Aparna.
"Robert Bruckner [MSFT]" wrote:
> One important principle of Reporting Services 2000 is to not "rewrite" the
> dataset query. The statement is essentially sent directly to the data
> provider. Some data providers do not support named parameters (like OleDb
> data providers). Other data providers support named parameters, but they use
> different syntax to mark parameters and may have various flavors for SQL
> keywords.
> E.g.:
> Managed SQL Provider: select * from emp where name = @.Name
> Managed Oracle Provider: select * from emp where name = :Name
> For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > Have you tried doing a stored procedure and passing a the parameter in?...
> > That might work for both...(Although I am surprised that there is even the
> > difference you have discovered.)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> >>I have a report that takes a single date parameter. My DataSource however,
> >> could be either a MSSQL db or a Sybase db. Both of these are included as
> >> shared datasources in my project (This is becase some clients of the
> >> report
> >> may point to the MSSQL db while others will point to the Sybase db).
> >> However,
> >> so far it seems that the rules for writing parameterized queries differ
> >> for
> >> these 2 platforms i.e.
> >> For MSSQL, i have:
> >> where date > @.date,
> >>
> >> but for Sybase, I had to say:
> >> where date > ?
> >>
> >> How can I write just one report so that when deployed, changing the
> >> datasource from MSSQL to Sybase does not cause a problem?
> >
> >
>
>|||For Sybase you cannot use named parameters (regardless of provider used).
For Sybase you put a ?
I have (unfortunately) been living in Sybase with RS so let me know if you
have any other difficulties. There are little quirks from time to time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
> Hi Robert,
> I actually tired using both the Sybase ASE Ole DB provider, as well as the
> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
> Problems'. Here are the steps he mentioned:
> ---
> 1. Create the ODBC Machine DSN and test that connection works
> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
> 3. Next
> 4. Use Data Source name and pick the DSN you created in 1
> 5. Enter username and password to use.
> 6. Pick initial catalog to use and click on test connection
> 7. Click OK
> You should now be done. If you have a problem with username and password
> after you are done double click on the data source and go to the
credential
> tab.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ---
> In both cases, I tried running the parameterized query (using @.Date) in
both
> the Generic Query Designer as well the Visual Query Designer. In both
cases,
> I get the message "The Data Extension ODBC does not support named
parameters.
> Use unnamed parameters instead".
> I did read about Oracle's conventions for using named parameters, and
tried
> searching for info. on using named parameters in Sybase, but to no avail.
I
> know Wayne mentioned using stored procedures, but I wanted to know if I
could
> use a simple query for both Sybase & Sql Server platforms. Any idea how
named
> parameters are used for Sybase? All in all, it seems to me that because
> there are slight variations in syntax, etc between the various providers,
it
> may not be possible to write a parameterized query and then dynamically
> change the data source provider once deployed (i.e. from Sql Server to
Oracle
> or Sybase)...Is that correct?
> --Aparna.
> "Robert Bruckner [MSFT]" wrote:
> > One important principle of Reporting Services 2000 is to not "rewrite"
the
> > dataset query. The statement is essentially sent directly to the data
> > provider. Some data providers do not support named parameters (like
OleDb
> > data providers). Other data providers support named parameters, but they
use
> > different syntax to mark parameters and may have various flavors for SQL
> > keywords.
> > E.g.:
> > Managed SQL Provider: select * from emp where name = @.Name
> > Managed Oracle Provider: select * from emp where name = :Name
> >
> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> > provider.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > > Have you tried doing a stored procedure and passing a the parameter
in?...
> > > That might work for both...(Although I am surprised that there is even
the
> > > difference you have discovered.)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> > >>I have a report that takes a single date parameter. My DataSource
however,
> > >> could be either a MSSQL db or a Sybase db. Both of these are included
as
> > >> shared datasources in my project (This is becase some clients of the
> > >> report
> > >> may point to the MSSQL db while others will point to the Sybase db).
> > >> However,
> > >> so far it seems that the rules for writing parameterized queries
differ
> > >> for
> > >> these 2 platforms i.e.
> > >> For MSSQL, i have:
> > >> where date > @.date,
> > >>
> > >> but for Sybase, I had to say:
> > >> where date > ?
> > >>
> > >> How can I write just one report so that when deployed, changing the
> > >> datasource from MSSQL to Sybase does not cause a problem?
> > >
> > >
> >
> >
> >|||I have a Sybase Anywhere db (version 8.x) that I have been using since RS
was released. For some reason my parameter (specified with a ?) is not
linking up with the selection entered in by the user prompt I have
associated with it. Any ideas? =)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> For Sybase you cannot use named parameters (regardless of provider used).
> For Sybase you put a ?
> I have (unfortunately) been living in Sybase with RS so let me know if you
> have any other difficulties. There are little quirks from time to time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
> both
>> the Generic Query Designer as well the Visual Query Designer. In both
> cases,
>> I get the message "The Data Extension ODBC does not support named
> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
> tried
>> searching for info. on using named parameters in Sybase, but to no avail.
> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various providers,
> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
> as
>> > >> shared datasources in my project (This is becase some clients of the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>|||Sometimes you have to hook them backup up. Go to the dataset, click on the
..., parameters tab. On the left put ? on the right pick the appropriate
report parameter. This should be done in the order that the ? appear in your
query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:11hhcmvr55mpl81@.corp.supernews.com...
>I have a Sybase Anywhere db (version 8.x) that I have been using since RS
>was released. For some reason my parameter (specified with a ?) is not
>linking up with the selection entered in by the user prompt I have
>associated with it. Any ideas? =)
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> For Sybase you cannot use named parameters (regardless of provider used).
>> For Sybase you put a ?
>> I have (unfortunately) been living in Sybase with RS so let me know if
>> you
>> have any other difficulties. There are little quirks from time to time.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
>> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
>> both
>> the Generic Query Designer as well the Visual Query Designer. In both
>> cases,
>> I get the message "The Data Extension ODBC does not support named
>> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
>> tried
>> searching for info. on using named parameters in Sybase, but to no
>> avail.
>> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
>> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
>> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various
>> providers,
>> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
>> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
>> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
>> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
>> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase
>> > OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
>> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
>> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
>> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
>> as
>> > >> shared datasources in my project (This is becase some clients of
>> > >> the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase
>> > >> db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
>> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>>
>