C# Statement works for SQL Server Table...but not for SQL Server View

mtbound

Hello,

I'm new to LinqPad even though I've owned it for a few years I'm just starting to work with it now. I've run into an issue that is confusing me and can't find an answer to this on stackoverflow or Google search. I'm using a simple C# Statement.

// this is a table in SQL Server and this statement works
ES_RTH_Compact.Take(1000).Dump();

// this is a view in SQL Server and it errors with: "Specified cast is not valid."
V_ES_RTH_Compact.Take(1000).Dump();

The view is valid. I can run a top 1000 query in SSMS and it works.

Thanks,
Mark

nescafe

Could you limit the result set by selecting fewer columns and perhaps isolate the error cast to a single or few data type(s)?

Do you get the same error message when you run the generated SQL Query directly (SQL Tab => Analyze SQL => Open as SQL in New Tab)?

mtbound

Thank you nescafe.

I was able to 'Open as SQL in New Tab' and the results returned fine. So, I looked at the view definition again and found that I was converting to time but using format number 112 instead of 114 (CONVERT(TIME, QDateTime, 112) AS QTime ... when it should have been ... CONVERT(TIME(3), QDateTime, 114) AS QTime). The weird thing is the only application to every chirp about this was LinqPad. Looks like I will need to be auditing views today. Thanks again nescafe.

nescafe

It seems you are converting time values which are stored in text format (varchar) in the column QDateTime to SQL time values?

Perhaps there are some differences in the connection parameters between ssms and LINQPad which would cause unexpected CONVERT() behavior.

If you like some clarification on the weirdness, could you try running the following statements (obtained by running SQL Profiler on the LINQPad user query) before

SELECT TOP 1000 * FROM V_ES_RTH_Compact

in SSMS?

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Could you give an example of how a date is stored in the QDateTime field?

mtbound

The QDateTime column is a datetime column in the underlying table. I'm separating the fields out in the views for analysis purposes.

2012-01-05 06:30:00.000

The only differences I could see between the LinqPad connection and the SSMS connection was arithabort was set to 'on' for the LinqPad connection where the statement V_ES_RTH_Compact.Take(1000).Dump(); continued to error with the original view convert using 112. SSMS connection has arithabort set to 'off'.

I'm really puzzled now because I did not change any connection parameters for LinqPad, yet now when I open LinqPad and execute V_ES_RTH_Compact.Take(1000).Dump(); using the old view it does not error anymore and it returns the results. To make it even more puzzling, the arithabort for LinqPad is now set to 'off'. I've opened it and closed it a few times and arithabort is now always set to 'off';