keywords in column names

Question

Post reply

keywords in column names

Uripedes Pants

Hall of Fame

Points: 3701
More actions
September 29, 2010 at 11:00 am

#100316

I've inherited an old and massive stored proc. Inside the sp, several ##temp tables are created using "year" as a column name. Tables that are used to load these temp tables also have "year" for column names. The sp seems to be choking on that. I've gone through and changed all references to [year] and get the same error msgs. I can't modify the real tables. Error message is:
"Server: Msg 1038, Level 15, State 3, Line 11
Cannot use empty object or column names. Use a single space if necessary."
if I'm counting my lines correctly, it looks like it's happening on the year = 0 part of inserts like this:
INSERT INTO ##2nd_Append_Public
SELECT
Sign_Year=0,
Sign_Month=0,
Facility_City =VM_FA_Location_City,
Facility_Name =VM_FA_Facility_Name,
RERFT_Certification_No=VM_FA_Certification_No,
Make ="",
Model ="",
Year =0,
Facility_Address =VM_FA_Location_Address,
Facility_Phone =VM_FA_Phone,
Specialize_All_Vehicles =0,
Specialize_Domestic =0,
Specialize_Imports =0,
Specialize_European_Imports =0,
Specialize_Japanese_Imports =0,
.
.
.
any ideas?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Enclose the keyword in brackets: [Year]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 2

Uripedes Pants

Hall of Fame

Points: 3701

September 29, 2010 at 11:58 am

#1229246

that was my first thought.... didn't work, same errors

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 3

never mind. That wasn't the offending line, it was the two prior:

Make ="",

Model ="",

didn't like empty string. Used 'null' instead and it works.

Thanks

JenMidnightDBA Right there with Babe Points: 787 More actions · Answer 4

Side note: it may not have balked at the empty string (unless those fields aren't character types), so much as the use of double quotes ("") instead of single ('').

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 5

ahhhh, thanks for that. I just tested single quotes and that also works. I'd already changed the sp (all 3157 lines of the beast) to use nulls.

...so... I'm sure this sp has been ported up beginning in SQL 6.5, through 7, then 2000. I'm guessing it worked at some point. Where in the chain would that have broken? I see several more of these in my future :o(

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

it can also be a change in set quoted_identifier... quite likely across ?5-10? years of sql server upgrades...

David Webb-CDS SSCoach Points: 17398 More actions · Answer 7

It may not like the "" because someone set QUOTED_IDENTIFIER ON. It's looking for an object name between the double quotes.

And then again, I might be wrong ...
David Webb

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 8

thanks again. The quoted identifiers setting sounds very likely to be the culprit.

Obviously, I'm a fairly new reluctant DBA that's just transfered from applications.

'preciate all help.