Would you expect this error ?
Now I know why, but what I'm thinking is that if I create an error should I get valid data returned?
To explain, I was browsing through the dmvs for queries which might benefit from tuning and I identified a query with two clustered index scans ( table scans ). I don't know all the schema off by heart and I was looking for a select by a LoginID column.
I assumed this would be numeric and promptly entered an integer value to examine the query plan, yeah I should have looked at the table definition first! To my surprise there were two distinct events here, firstly the column LogonID actually was an email address, not numeric, secondly the error message returned a valid email address from the table, which I could then use to check the query properly.
So here's a roll your own example:-
create table dbo.test( NumKey int identity(1,1),blah nvarchar(500));
insert into dbo.test(blah)
values
('colin@mycompany.com'),
('fred@mycompany.com'),
('beert@mycompany.com'),
('sandra@mycompany.com'),
('jane@mycompany.com'),
('tarzan@mycompany.com'),
('auntiejill@mycompany.com'),
('marquisdesade@mycompany.com'),
('meerkat@mycompany.com');
select * from dbo.test where blah = 123;
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
This will return the error, but with valid data - interesting eh?
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'colin@mycompany.com' to data type int.