One report, multiple databases and autologin

[Zelandakh]

Apparently you can't do this (now there's a challenge!)...

I have a CR that works perfectly on one SQL database. But they want to run it on each database (we have one per country so all the tables are the same but different data). They also don't want to have to log in to the database each time.

1. Can I set the CR to prompt for the database name as well as prompting for the data to go in it?
2. Can I embed the username and password into the report (same across all databases) so that they don't get asked each time?

 

[synapsevampire]

One option for the multiple databases concern might be to use a Stored Procedure with a parameter which passes the name of the database, now in the SP you would use the appropriate table in the select (dynamic SQL or numerous selects with but one returning data based on the parm).

If you use an ODBC connection, you can set it to use NT Authentication or a SQL Server Account and have the password embedded, hence avoiding the prompting.

-k

 

[Zelandakh]

Hey Synapse, thanks for that.

I use the Windows 2000 ODBC connectors on System DSN but it doesn't save the password. Any ideas why?

I'll look into Dynamic SQL / SP as I know the database names already and the tables are the same throughout.