This project is read-only.

Trouble Opening Reports

May 24, 2011 at 6:51 PM

I'm having trouble getting reports to run w/ SQLNexus (Blocking, Bottleneck, 05PerfStats). The error says the DB doesn't have data to run report.  I've tried running StartSQLDiagTrace.cmd and StartSQLDiagDetailed_Trace.cmd against SQL 2005 server. SQLNexus is running on Win7 PC.

 

DateTime=2011-05-24T17:50:45.4333594Z
SQLNexus Information: 0 : The database doesn't have necessary data to run this report
    DateTime=2011-05-24T17:50:45.4333594Z
SQLNexus Error: 0 : System.Data.SqlClient.SqlException: Invalid object name 'tbl_Requests'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NexusInterfaces.CSql.ExecuteBatches(String[] batches) in C:\temp\nexus\sqlnexus_codeplex\NexusInterfaces\CSql.cs:line 102
Batches Executed 1

    DateTime=2011-05-24T17:50:48.0785107Z
SQLNexus Error: 0 : System.Data.SqlClient.SqlException: Invalid object name 'tbl_Requests'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NexusInterfaces.CSql.ExecuteBatches(String[] batches) in C:\temp\nexus\sqlnexus_codeplex\NexusInterfaces\CSql.cs:line 102
Batches Executed 1

    DateTime=2011-05-24T17:50:48.1765163Z
SQLNexus Error: 0 : System.Data.SqlClient.SqlException: Invalid object name 'tbl_Requests'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NexusInterfaces.CSql.ExecuteBatches(String[] batches) in C:\temp\nexus\sqlnexus_codeplex\NexusInterfaces\CSql.cs:line 102
Batches Executed 1

    DateTime=2011-05-24T17:50:52.3877572Z
SQLNexus Information: 0 : The database doesn't have necessary data to run this report
    DateTime=2011-05-24T17:50:52.3877572Z

May 24, 2011 at 7:24 PM

Can you check if tbl_requests table has any rows in the database? For Perf Stats reports, rows are required in the tbl_requests table. If there are no rows are present in the table and the SQL Perf Stats 2005 Script output was captured, then check why the file content was not imported. Any error messages with the import would be present in the SQL Nexus log as well.

May 24, 2011 at 7:44 PM

Yes there are two rows and I see the data looks to have been imported.

SQLNexus Information: 0 : Opening file C:\Users\shawn\Documents\DBA\SQL Server\SQLNexus\SQLDiagOutput\SHAWN-SQL__SQL_2005_Perf_Stats_Snapshot_Shutdown.OUT
    DateTime=2011-05-24T18:41:50.8216896Z
SQLNexus Information: 0 : Done. (0 sec, 0MB), 346 lines processed; 1 rows inserted.
    DateTime=2011-05-24T18:41:51.1737098Z
SQLNexus Information: 0 : Rowset Importer (blocker/perf script output) is a INexusImporter
    DateTime=2011-05-24T18:41:51.1817102Z
SQLNexus Information: 0 : Opening file C:\Users\shawn\Documents\DBA\SQL Server\SQLNexus\SQLDiagOutput\SHAWN-SQL__SQL_2005_Perf_Stats_Startup.OUT
    DateTime=2011-05-24T18:41:51.1877106Z
SQLNexus Information: 0 : Done. (3 sec, 1MB), 7646 lines processed; 6476 rows inserted.
    DateTime=2011-05-24T18:41:54.4098949Z
SQLNexus Information: 0 : Executing post-mortem analysis scripts...
    DateTime=2011-05-24T18:41:54.4208955Z
SQLNexus Information: 0 : Executing ReadTracePostProcessing.sql...
    DateTime=2011-05-24T18:41:54.4238957Z
SQLNexus Information: 0 : db name = sqlnexus
    DateTime=2011-05-24T18:41:54.4248957Z
SQLNexus Error: 0 : System.Data.SqlClient.SqlException: There is already an object named 'spReporter_TraceFiles' in the database.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

May 24, 2011 at 8:41 PM

The validation query that we use to decide if data is present to generate the report is:

select * from tbl_Requests where 1 = 0
select * from tbl_blocking_chains where 1=0
select * from tbl_notableActiveQueries where 1=0

Can you check if the two additional tables mentioned above have data? Additionally, the report is stating that the tbl_requests table cannot be found in the exception stack! If there is data in all three tables, can you create a new SQL Nexus database from the GUI and import the data into the new database created.

May 24, 2011 at 8:41 PM

What version of SQL Nexus are you using?

May 24, 2011 at 9:48 PM

The three tables do exist in the DB.

Doing a count of dbo.tbl_REQUESTS gives 2 records
Doing a count of dbo.tbl_BLOCKING_CHAINS gives 0 records
Doing a count of dbo.tbl_NOTABLEACTIVEQUERIES gives 1 record

Running the selects you provided returns 0 rows for all three queries.

I have previously dropped and recreated the sqlnexus DB and ran the import, but I keep getting the same results.

I am running SQL Nexus 3.0.0.0 (just downloaded today)

May 24, 2011 at 9:53 PM

Actually here is what I'm getting.  I re-ran the incorrect set just before replying.


select * from dbo.tbl_REQUESTS where 1=0
 0 rows
select * from dbo.tbl_BLOCKING_CHAINS where 1=0
 0 rows
select * from dbo.tbl_NOTABLEACTIVEQUERIES where 1=0
 0 rows


select * from tbl_Requests where 1 = 0
 Msg 208, Level 16, State 1, Line 1
 Invalid object name 'tbl_Requests'.
select * from tbl_blocking_chains where 1=0
 Msg 208, Level 16, State 1, Line 1
 Invalid object name 'tbl_blocking_chains'.
select * from tbl_notableActiveQueries where 1=0
 Msg 208, Level 16, State 1, Line 1
 Invalid object name 'tbl_notableActiveQueries'.

May 24, 2011 at 9:56 PM

That is why SQL Nexus is reporting the exception. Error 208 is reported normally when the tables do not exist. What is the default schema for your login?

May 24, 2011 at 10:17 PM

The default schema for my log in is dbo and I'm in the db_owner DB role and sysadmin server role.

May 24, 2011 at 10:24 PM

That's strange so let me run this all against a different machine.  The one I'm using is an "odd child" anyhow.  I'll let you know.  Thanks a log for the assistance so far!!

May 24, 2011 at 10:44 PM

No problem. Either way, post back the outcome of the test on the other machine.

May 25, 2011 at 3:57 PM

It was a problem on my test server.  It is working now on the other machine.  Thanks again for the help and quick replies!!!