Yet another problem with reports! "Object reference not set to an instance of an object."

christunger · February 17, 2016, 10:54pm

**There needs to be some heavy considerations on taking a second look at the integrity of your reporting product. **

Working on a report pulling data from SQL server, spend two hours prepping and finalizing the report. Saved often, previewed, exported my results and got it exactly where it needed to be. I then hit the automation button to begin completing that process. Included Toad Report under Database Activities for my first report export, which exports report results to an excel file. That part works fine. Added a second Toad Report action and set this one to export an html file that would then be embedded in to my Email action body. Save my automation and run. It craps out on the second Toad Report action. Returns the error: “Object reference not set to an instance of an object.” I attempt to troubleshoot and open the report and it now returns the error “This report is specific to the Sql Server platform and cannot be run using the current connection.” Keep in my mind, I haven’t physically changed my connection at any point. Every activity in this process ties back to my NOC_Reporting connection. I can’t even edit this report at this point.

Since I can’t physically open this report now in TDP, after spending hours developing, I attempted to open the tdr file in Visual Studio along with the first report in the automation to see if anything stood out. Low and behold, the connection string in the msprop:Connection="" syntax was completely wiped out in the broken report. So I attempted to copy the string from the report that worked and paste it in to the one that broke and no luck. I can’t seem to salvage my work and needless to say I’m pretty frustrated.

Here’s is the data from my log file. I’ve x’d out sensitive info.

– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Setting up environment
– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Script run by Toad Data Point 3.6.0.3276
– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Build started
– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Compiling script
– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Start CompileWorkflow
– 2/18/2016 12:25:59 AM: TimeToKnow_PrevDay.log: Before Compile
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Return from CompileWorkflow
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Wokrflow Compiled without Errors
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Build completed
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Create Workflow instance
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Started workflow instance
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Begin execution script activities
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Started generating report
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Connection description = XXX.XX.XXX.X (XXXX), NOC_Reporting
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Connection node found
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Connection to: NOC_Reporting
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Connection successful
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Connection logon: XXXX
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Last schema: NOC_Reporting
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Default schema: NOC_Reporting
– 2/18/2016 12:26:01 AM: TimeToKnow_PrevDay.log: Auto Commit: True
– 2/18/2016 12:26:08 AM: TimeToKnow_PrevDay.log: Row count set to 24 rows
– 2/18/2016 12:26:08 AM: TimeToKnow_PrevDay.log: Exporting to X:\XXX\TimeToKnow_PrevDay_Detail_2016-02-18.xlsx
– 2/18/2016 12:26:08 AM: TimeToKnow_PrevDay.log: Report Finished, click TimeToKnow_PrevDay_Detail_2016-02-18.xlsx to view file.
– 2/18/2016 12:26:08 AM: TimeToKnow_PrevDay.log: Started generating report
– 2/18/2016 12:26:08 AM: TimeToKnow_PrevDay.log: Opening Cross-Query connection
– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Report_2 - Object reference not set to an instance of an object.

– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Start error email
– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Email To: xxx@xxx.com, From: xxx@xxx.com, cc:
– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Email Subject: Automation Script Error
– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Generating exception Email
– 2/18/2016 12:26:09 AM: TimeToKnow_PrevDay.log: Email message:

Automation script “TimeToKnow_PrevDay” had errors while executing. The exception messages are listed below and the log file is attached.

Exception Messages:
Report_2 - Object reference not set to an instance of an object.

What I don’t understand is why, if both reports were initially built utilizing the same connection (NOC_Reporting), the second report attempts to open a cross-query connection (highlighted above).

Debbie_Peabody · February 18, 2016, 8:07am

If you connection type is SQL Server you can manually edit the tdr file in text editor and set the the connection property.

msprop:ConnectionType=“SqlServer”

I do not know why is wants to use a cross-connection. If you post the tdr file I can look at the SQL and see if I can see why.

christunger · February 18, 2016, 9:28am

Thanks Debbie,

Both reports contain that string as you mentioned in your response. The Mobile Report version is the report that has crashed on me.

Mobile Report: TimeToKnow_PrevDay_Mobile.tdr:

msprop:ConnectionType=“SqlServer”

msprop:Connection=""

Detail Report: TimeToKnow_PrevDay_Detail.tdr:

msprop:ConnectionType=“SqlServer”

msprop:Connection=“XXX.XX.XXX.X (xxxx), NOC_Reporting”

I tried copying and pasting the msPropConnection=”XXX.XX.XXX.X (xxxx) NOC_Reporting” string from the Detail Report and pasting that in the Mobile Report in Visual Studio. Saved the file and attempted to open the report in TDP and it continues to return the error: “The report is specific to the SQL Server platform and cannot be run using the current connection.”

When you view the Mobile Report code, you’ll see the update to the mspropConnection string to reflect the Detail Report string.
TimeToKnow_PrevDay_Detail_and_Mobile.zip (10.8 KB)

Debbie_Peabody · February 19, 2016, 6:20pm

I looked into this today and was not able to reproduce the error. I agree that opening a Cross-Connection connection make no sense. I thought at first it might be the With statement you are using but I build my own simple one and had no issues,.

Is this reproducible? Or did it happen just the one time? If it is reproducible I continue to work with you to debug.

christunger · February 22, 2016, 3:07pm

Hi Debbie,

This is the first time that this has happened. Completely random.

christunger · March 4, 2016, 9:37am

Hi Debbie,

I think I may have found the culprit. I think it stems from the following steps:

From the Navigation Manager -

Right clicking on a view name (we’ll use “dbo.View1” as example) under my NOC_Reporting database and selecting Generate SQL > Select statement > To Editor.

When a new editor window is initiated, the select statement is populated in that window. By default, in the FROM syntax of the SQL statement, the database name and view name is listed. So for this example the SQL populated by Toad was:

SELECT Field1, Field2, Field3

FROM NOC_Reporting.dbo.View1;

When I hit F5 to run the query, I’m prompted with a message stating: “Cross-connection queries cannot execute in a standard editor. Do you want to change in to cross-connection mode?” By default I always selected “No” because there was no need for that. Even though I select No, the data runs and produces results. From there I would right-click in the results returned and send to a data report. From here, I’m able to design, save, and preview the report. However, the minute I close the report and try to reopen it, this is when I received the error I originally posted about.

Backing up to the default SQL syntax I listed above. If I replicate the same steps:

Right clicking on dbo.View1 under my NOC_Reporting database and selecting Generate SQL > Select statement > To Editor.

Another editor initiates and the default select syntax is populated like before:

SELECT Field1, Field2, Field3

FROM NOC_Reporting.dbo.View1;

This time I removed “NOC_Reporting.” from the FROM statement so that the syntax reflected:

SELECT Field1, Field2, Field3

FROM dbo.View1;

When I hit F5 to run the query, the Cross-connection prompt didn’t pop. I then sent the selected results in to a report, saved, closed, and re-opened the same way I listed above and it worked without issue.

Debbie_Peabody · March 4, 2016, 9:52am

There is something here that is not making sense to me. There is something in that generated SQL statement that is triggering the Cross-Connection code. Can you generate the SQL and send to editor. make sure it executes and then generate a support bundle? In would like the SQLAnlyze.log out of the bundle. This gives me the original SQL and generated DDL so i can try to replicate the issue.

christunger · March 4, 2016, 10:52am

Hey Debbie, I ran the query and went to Help and selected Support Bundle. Going through the email generated and the attached zip file, I couldn’t find anything like SQLAnlyze.log. Was this the correct way to generate, or should I be doing something different?

Thanks!

Debbie_Peabody · March 4, 2016, 4:25pm

The window that has to have focus is the window that contains the SQL. You will also see a prompt that asks if you want to include the number of rows. I added this in TDP 3.8 or 3.7. What version are you using?

christunger · March 7, 2016, 7:26am

The current version on my laptop is 3.6. I’ll put in a request for a version upgrade. I know we have 3.7 packaged, but I’ll check on 3.8 as well. Also, my reporting machine is running 3.7. I’ll hop on that box to see if I can reproduce the occurrence. If so, I’ll attempt to provide you the file you’re requesting.

christunger · March 7, 2016, 7:31am

So the occurrence did replicate on my report box and here is the output from the SQLAnalyze.log file:

===== 3/7/2016 9:27:33 AM =====

Tables for query: SELECT Remedy_Login_ID, Full_Name, Support_Group_Name, Default_x

FROM NOC_Reporting.dbo.v_Remedy_NOC_Agents

Executed on SqlServer, connection name NOC_Reporting, on schema NOC_Reporting

Datasource: SqlServer

Version: 11.0.3128.0

CREATE TABLE NOC_Reporting.dbo.v_Remedy_NOC_Agents (

Remedy_Login_ID nvarchar(254),

Full_Name nvarchar(128),

Support_Group_Name nvarchar(60),

Default_x varchar(3));

Row count for table NOC_Reporting.dbo.v_Remedy_NOC_Agents: 55

Would you care for the whole support zip as well?

Debbie_Peabody · March 8, 2016, 1:41pm

Thanks for the SQL. I can reproduce this behavior but it isn't really an error. For Cross-connections, the only way we can figure out what datasource a table comes from is to prepend the connection name to the table/view. This is our own syntax. We use the Property name the user assigns the connection. In this case the Name of your connection is the same as the same as the SQL Server database. So we think it might be a cross-connection query. See below. When this SQL combination and named connection are used in automation you will get the error combination you show at the top of this thread.

To work around this, change the name of your connection. For me I had to delete the connection all together and re-add, giving a completely different name. Once that was done the automation script worked fine. Give it a try.

christunger · March 8, 2016, 2:12pm

Thanks Debbie, I will try renaming the connection as you suggested. To your point, even though this isn’t technically an error in this context, this did become a break point when I would right click on the results returned and send them to a Data Report. After saving, closing, and attempting to open the report back up, I received the error that I posted in my original post on this topic.

Debbie_Peabody · March 11, 2016, 7:32pm

You do have a point. But unfortunately I cannot reproduce and I need to reproduce in order to fix it.