Could a patch to the Server create this? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Could a patch to the Server create this?

Hi Group,
I am trying to get to the bottom of this….
I have several SP’s which have been running for a number of years then ‘suddenly’, I get the following errors: Here is the SP:
CREATE PROCEDURE CountSalesCallFrequency
— SCA Frequency
AS — Select the data and insert it into the temp table
INSERT INTO #TempStore(
ActionDate,
BranchNo,
Consultant,
ConsInt,
SG,
LoginName,
SaleCalls
)
SELECT
case when Len(Field2) = 6 then
substring(Field2,3,2) + "/" + substring(Field2,1,2) + "/" + substring(Field2,5,2)
Else
substring(Field2,2,2) + "/0" + substring(Field2,1,1) + "/" + substring(Field2,4,2)
end as ActionDate,
BranchNo = Case Len(Field3) WHEN 1 THEN "00" + Field3 WHEN 2 THEN "0" + Field3 WHEN 3 THEN Field3 WHEN 4 THEN RIGHT(Field3 ,3) ELSE "000" END,
UPPER(Field4) As Consultant,
UPPER(Field5) As ConsInt,
Field6 as SG, –CONVERT(int,Field6) as SG,
Field7 as LoginName,
COUNT(1) As Frequency
From tblData
WHERE Field1 = ‘SCA’
GROUP BY Field2, Field3, Field4, Field5, Field6, Field7
ORDER BY Field2, Field3, Field4, Field5, Field6, Field7
And here’s the error message I’m receiving:
Server: Msg 207, Level 16, State 3, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘/’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘/’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘/0’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘/’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ’00’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘0’.
Server: Msg 207, Level 16, State 1, Procedure CountSalesCallFrequency, Line 7 Invalid column name ‘000’ The fix was to put single quotes around the strings/values…. The funny thing is is that we have 2 Servers – one for development one for production. On the development server, the double quotes work OK – no errors. On the production, the error above is given.
Could the technicians have applied a patch/fix to the production server/changed a setting for this to happen only on the prodution Server and not on the Development one? Kind regards..
Check if Regional Settings in both servers are equals. Luis Martin
Moderator
SQL-Server-Performance.com
What should I be looking for that would have had this effect?
Sorry, check database properties en both server, see: Use quoted identifiers Luis Martin
Moderator
SQL-Server-Performance.com
It’s unchecked in both databases….
Could it possibly be that a change had been made to the Development Server from quoted identifiers being checked to uncheck but the Server has not been updated to reflect the change?
updated as in ‘refreshed’……
Recompile the SP with the following SET OPTION before the statement CREATE PROCEDURE.
SET QUOTED_IDENTIFIER OFF
I am sure the problem will be resolved. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
… and leave the double quotes around the values?

Yup! Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>