Display SQL Server Name in Report Footer

[BilldCon]

Does anyone have any idea of the best way to AUTOMATICALLY have the name of the SQL server(s) that are providing data to a Crystal Report appear in the report footer of a report? I am using 8.5, and I cannot for the life of me find a Crystal or Basic Syntax statement that will accomplish this goal, so I've recently turned to using the SQL expression tool. I've tried creating a SQL expression named "sql_SERVER_NAME(S)" with the following as the contents of the expression:

SELECT @@SERVERNAME

This does not work - does anyone know of a way that this expression can be modified to work in CR 8.5, or of another way to pass server name information to the report footer?

(right now I am manually putting server name information in the report comments and then inserting that field - talk about clumsy.)

Thanks in advance!

 

[synapsevampire]

Just use:

@@SERVERNAME

in the SQL Expression.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BilldCon]

Thanks for the suggestion - I have one (hopefully) final question. Should @@SERVERNAME be the only thing entered into the SQL expression? I have tried this, and it does not display any information in the report when it is printed or displayed on screen (although it does not give me an error - progress!).

Thanks again for your help!

 

[synapsevampire]

You might also try:

CONVERT(char(20), SERVERPROPERTY('servername'))

And yes, you don't want to add in a select, a SQL Expression adds th element to the select generated by CR, so if you stated SELECT, you'd end up with:

select table.field1, table.field2, table.field3, select @@servername

Which would cause errors.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BilldCon]

Still no luck - I think I might be facing a permissions issue - I am checking with our DBA right now...thanks so much for your time and help!

 

[synapsevampire]

If you don't get any rows back, then this function will fail.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BilldCon]

It IS NOT a permissions issue - I can perform the query in QA using the same username and password I use to log on to the DB with Crystal.

Any other ideas? Is there some simple syntax I can place in the SQL expression that will return information (so I can test to see if ANYTHING will work)? Please send the complete text of what should be in the SQL expression and where I should drag - and - drop the SQL expression field to get it to display.

Thanks in advance!

 

[cjlarue]

using northwind DB

SELECT CustomerID, CompanyName, ContactName, @@ServerName as ServerName
FROM Customers

 

[synapsevampire]

Sure, try getdate() in a different SQL Expression.

Also, you might try creating a new SQL Expression, once they get erros in them, they sometimes don't reset, even when corrected.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BilldCon]

Thanks everyone - your help is very appreciated, and, while I still cannot display the information I am after, I think our investigation has uncovered problems that are preventing me from getting to the information in question, and it is now up to our DBA to find the answers.

Again, thanks for all your help!