Database Name

[Former Member]

Hello everyone,

I'm designing a series of reports that are to run against a number of databases (not all at once, just one at a time). Each database has the exact same structure, so it's just a matter of changing the datasource location. The issue that I'm having is that we need to have the name of the database included on the report itself. I don't want to add it as a parameter, as that would open it up to human error.

There are two possible solutions that I can see, but I'm having trouble getting them working...

The first possible solution is to get the database via an sql query. The report already had 1 command to query the data for the report... I've added a second command that queries the name of the database from the MS SQL server. This works fine, unless the main report command returns 0 rows. When that happens, the second command that gets the db name also returns 0 rows, leaving the field blank.

So, is there some way to make commands independant, to ensure that one command always returns results, depite the second command returning zero rows?

The second potential solution is the one that I'm hoping will be possible. I know that there are a number of special fields for each report. (recordcount, etc. ) Is there some way to natively display the name of the database used?

I'd appreciate any help that anyone could offer. Thanks.

- Scott

[former_member11696]

Hi Scott,

Add a SQL Expression, please note, you can NOT use SQL Expressions if you have more than one data source, and type in:

DB_NAME()

Then drop that expression in the report.

Thank you

Don

[Former Member]

That's an even better idea Don!

[Former Member]

Thianks for all your help, everyone. Don's solution worked amazingly. Thanks again!

- Scott

[Former Member]

I learn something new every day!

[Former Member]

Of course you can make the 2 commands independent. Don't link them... I can't imagine what you'd be linking them on to begin with.

As Sharon pointed out, a simple command written like...

SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName

will only return a single row, so now need to worry about creating a Cartesian product with 2 unlinked commands.

I'm not aware of any special fields that will nativity pull in the db or server names.

HTH,

Jason

[Former Member]

i know i sql in sp you can put

DB_NAME() as a field and it will display the database it is connected to

see if in the comand file it works the same.