changing datasource to SQL Server

Greetings,
I have a set of reports that were created using the report designer in
Visual Studio. They were originally written to run against an Informix
database, but I now need to change them to run against an SQL Server
DB. I added a new datasource for the SQL Server DB, but couldn't find
*any* way to change the reports to point to this new datasource. I've
worked around this problem by changing the properties of the existing
(Informix) datasource to point to the SQL Server DB, but if anyone
knows how to change *just* the datasource of a report, I'd be very
grateful if they'd let me know.
The queries that the reports execute currently use '?' to indicate an
Informix SQL parameter, but I believe SQL Server uses '@.param_name'
instead. If I replace each ? with @.param_name (in the 'SQL' pane of
the 'Data' tab) and map each @.param_name to the corresponding
ReportParameter, will this work, or is there something else I need to
do?
Thanks in Advance,
DonThe way you change the datasource is by going to the dataset, click on the
... and change the datasource. Or you can edit the rdl. Yes, changing and
remapping the parameters should work. One problem you will have is that RS
will try to create the report parameters so you very well will end up with
extra report parameters that you have to remove. Or, do this. Make sure you
have a copy of the rdl. Open up the report into the designer and go into
code view. Edit the xml with the parameter mapping, save, go to layout and
preview and see if it works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Don" <donalmurtagh@.yahoo.co.uk> wrote in message
news:aa1c6f54.0504290857.60998e06@.posting.google.com...
> Greetings,
> I have a set of reports that were created using the report designer in
> Visual Studio. They were originally written to run against an Informix
> database, but I now need to change them to run against an SQL Server
> DB. I added a new datasource for the SQL Server DB, but couldn't find
> *any* way to change the reports to point to this new datasource. I've
> worked around this problem by changing the properties of the existing
> (Informix) datasource to point to the SQL Server DB, but if anyone
> knows how to change *just* the datasource of a report, I'd be very
> grateful if they'd let me know.
> The queries that the reports execute currently use '?' to indicate an
> Informix SQL parameter, but I believe SQL Server uses '@.param_name'
> instead. If I replace each ? with @.param_name (in the 'SQL' pane of
> the 'Data' tab) and map each @.param_name to the corresponding
> ReportParameter, will this work, or is there something else I need to
> do?
> Thanks in Advance,
> Don|||Hi Bruce,
Thanks very much for your response. I thought about editing the .rdl
directly in order to change the datasource, but the autogenerated IDs
scared me off - what exactly are these used for?
I'm aware that RS adds report parameters whenever you change an SQL
parameters - it's an annoying "feature", but not catastrophic. Are you
saying that if I replace every reference to the '?' parameters with a
'@.param' in the XML code then I won't have to deal with this?
Thanks Again,
Don|||Yep. I also do this sometimes when I am modifying a complicated SQL
Statement because sometimes with ODBC datasource it loses all the mappings
and I have to remap so I will go in and modify the SQL so the helpful IDE
doesn't mess everything up. Again, make a copy because it is easy to mess up
the xml.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<donalmurtagh@.yahoo.co.uk> wrote in message
news:1114946322.881807.132260@.g14g2000cwa.googlegroups.com...
> Hi Bruce,
> Thanks very much for your response. I thought about editing the .rdl
> directly in order to change the datasource, but the autogenerated IDs
> scared me off - what exactly are these used for?
> I'm aware that RS adds report parameters whenever you change an SQL
> parameters - it's an annoying "feature", but not catastrophic. Are you
> saying that if I replace every reference to the '?' parameters with a
> '@.param' in the XML code then I won't have to deal with this?
> Thanks Again,
> Don
>