Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dtoddp
Starting Member
12 Posts |
 Posted - 2012-10-17 : 11:34:13
|
| We recently upgraded to SQL 2008 R2 SP2 - All of a sudden the following SQL Statement no longer works:WHERE (NOT ([Project Status] IN (N'Cancelled', N'Completed'))) AND ([Work Area] = N'Field and Campus Construction'Appears to be a problem with the "N" statements that Query Builder will set up for you.Is the above SQL code incorrect? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-17 : 11:45:42
|
| Query builder is prefixing with "N" probably because Project Status and Work Area columns are NCHAR or NVARCHAR type columns. I cannot see how that would cause a problem.The only thing I see is a few extra brackets, but I assume that is because you posted only part of the query. If you remove those "N"'s, does the query work as expected? |
 |
|
|
dtoddp
Starting Member
12 Posts |
Posted - 2012-10-17 : 13:17:19
|
| Yeah, my bad I left a brackett off when I posted.If I take out the "N" statements, it still doesnt work. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-10-17 : 13:30:51
|
quote:
Originally posted by dtoddp
If I take out the "N" statements, it still doesnt work.
What does that mean? Does it error or not produce the resutls you want or...? |
|
|
|
dtoddp
Starting Member
12 Posts |
Posted - 2012-10-17 : 14:37:54
|
| I was getting a "blank". But I have resolved the issue - the value "Field and Campus Construction" - was supposed to be "Field & Campus Construction" - my bad in that area, BUT, up until this week, it worked with the word "and" instead of "&". So methinks something happened in SP2 that changed the use of "and" and "&" - perhaps before SP2 you could use them interchangeably? (I swear it worked fine before!) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-17 : 19:23:16
|
As far as I know, "and" and "&" are not interchangeable. So the stored proc (or the data in the table that has the [Work Area] column) must have changed. Applying SP2 would not do that.You can find the last modified time of the stored procedure by querying sys.procedures:select modify_date from sys.procedures where name = 'YourStoredProcName'; |
|
|
|
|
|
|
SQL Code "N" not working in SQL 2008 R2 SP2