C# Statement works for SQL Server Table...but not for SQL Server View
Options
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
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
Comments
-
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)? -
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. -
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) beforeSELECT 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? -
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 statementV_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 executeV_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';