Reports

[BillRady]

Hi, I am sort of new to SMS 2.0. Currently, my environment is an SMS2.0/SP3 server running on Win2k/sp2, dual proc, with SQL 7.0/sp3. I seem to be having difficulties accessing the default crystal reports component of SMS. Everytime I try and run a report, I get "Cannot open SQL Server" and the report is marked in red.

I think the SMS Server is setup correctly, as I see data in the collections. Interestingly enough, I don't see every machine in my network. Discovery methods uses is Heartbeat and Network Discovery.
Any help on this would be greatly appreciated.

Thanks. Bill

 

[Schoenberg]

Can you create a query in the SMS MMC?

How did you install SQL? Before SMS Setup, or during?

Did you use SQL Integrated Security?

Personally, I don't use Crystal Reports. Here's another reporting interfacce available for SMS 2:

http://www.microsoft.com/downloads/release.asp?ReleaseID=28039

This will install Web Reports, which will give you access to nice-looking reports from any browser.

 

[BillRady]

Hi, thanks for responding to me.

SQL 7.0 was installed prior to SMS 2.0. I am not sure if I am using SQL Integrated Security in SMS. How can I tell? If I am/not how do I fix?

Bill

 

[Jpoandl]

Bill,

When you logon to SQL, do you use the SA account or some other account. SA = the old way of logging on Any other account = Integrated security.

Also, I've heard that the Crystal Reports that comes with SMS isn't meant to be used with more than a couple hundred client machines in the database. You are supposed to buy the full version of Crystal reports to use it in bigger environments. Maybe this is your problem.

Instead of buying Crystal, you can setup Excel (and Access) to view information in your SMS database. To do this, you have to install an ODBC connector (very easy) on you client workstation. In addition, you can use tools in the Back Office Resource kit that has pre-defined queries to export data into you Excel spread sheet. This is how I've been doing it.

-later

Joseph L. Poandl
MCSE 2000

If your company is in need of experts to examine technical problems/solutions, please check out

http://www.esavio.com

 

[Chkaotic]

I experienced the same issue with getting a "Cannot open SQL server" error.. and what I found on Technet was that when you schedule your report to run, you enter a userid and password. You need to give that user account the user right of "Act as part of the Operating System" in order for it to have access to your SQL database.

It also said explicitly NOT to use the SQL SA account

Id link you to the article, but i dont have it anymore.. search technet for "SMS Cannot Open SQL Server", its what I did