Board index » Information Technology » changing datasource to SQL Server

Information Technology330
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


-

The 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



-

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



-