Question Re: Database Name in Technology Q&A

Database Name

Former Member — Wed, 23 Feb 2011 20:08:55 GMT

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

Re: Database Name

Former Member — Wed, 23 Feb 2011 20:31:18 GMT

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.

Re: Database Name

Former Member — Wed, 23 Feb 2011 21:09:33 GMT

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

Re: Database Name

former_member11696 — Wed, 23 Feb 2011 23:57:05 GMT

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

Re: Database Name

Former Member — Thu, 24 Feb 2011 15:23:46 GMT

That's an even better idea Don!

Re: Database Name

Former Member — Thu, 24 Feb 2011 15:49:14 GMT

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

- Scott

Re: Database Name

Former Member — Thu, 24 Feb 2011 17:36:41 GMT

I learn something new every day!