| Author |
Topic |
|
rfleming
Starting Member
9 Posts |
 Posted - 2005-10-07 : 17:56:50
|
| Are the field names YEAR and DATE considered SQL Reserved Words and therefore should not be used as field names in SQL tables?Would that be a safe statement? I have a table that was created this way and the trigger associated with it doesn't work. When i opened the trigger to troubleshoot, 1st thing i saw were those 2 tables. I, myself, know one shouldn't really use those exactly like that but maybe something like, YEARof and DATEof. |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-08 : 04:45:22
|
| its not Recommened to use the Reserved word but if you have any columns with this key words you can access by box brackets [].. somthing like [YEAR]=2004.. which should work fine.. I hope it solves you query ..Complicated things can be done by simple thinking |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-10 : 02:16:15
|
| orInstead of YEAR and DATE let them YYEAR and DDATEMadhivananFailing to plan is Planning to fail |
|
|
|
rfleming
Starting Member
9 Posts |
Posted - 2005-10-10 : 12:16:26
|
| what is the "proper" way of changing the field names, using the ALTER statement or opening the table in design view and changing?I've tried using the following ALTER statement but it doesn't work:ALTER TABLE bu_allocation CHANGE '[YEAR]' 'YYEAR' DEFAULT ('')I also have another field in this table that is a % field (float) datatype but I need it to default to blank instead of the 0 it currently defaults to now. I manually changed it's default to ('') and it seems to have worked.Was this correct also?Thx! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-10 : 12:23:41
|
| I usesp_rename 'dbo.MyTable.OldName', 'NewName'You can use the Design Tool too - but that will just generate SQL behind the scenes (use the "View Change Script" button to see the SQL which Enterprise Manager generates)Kristen |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-10 : 17:57:39
|
| In your search for better field names, might I suggest you ask yourself what the date is of and what the year is of? Are they DateCreated? DateLastModified? DateOrdered? YearOfBirth?Or maybe the table is a calendar table, perhaps to track holidays, in which case I would go with something like CalendarYear.---------------------------EmeraldCityDomains.com |
|
|
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-10-11 : 07:21:25
|
rfleming,while using more significant names for your fields, like CalendarYearor like, YEAR and DATE should not be reserved words, as far as I canunderstand from BOL  HCL"If it works fine, then it doesn't have enough features" |
|
|
|
rfleming
Starting Member
9 Posts |
Posted - 2005-10-11 : 09:55:43
|
| lessons learned here...I opened table is design view and manually changed the default value of one of the fields from 0 to blank. Then ran sp_rename and changed the 2 field names YEAR and DATE.When i opened the application that uses this table and proceed to "reorganize" the table, i got an ODBC SQL error of "syntax error converting datetime from character string". These fields did have data in them before i made the change. I wonder if i should have DTS'd the data out 1st, made the changes, then DTS'd the data back in?Now I'm going to have to restore the table from a backup. |
|
|
|
Sharky
Starting Member
23 Posts |
Posted - 2005-10-11 : 10:11:32
|
| select * into dbo.YOURTABLE_backup FROM dbo.YOURTABLE then you won't have to restore the backup to get to the table... ;-) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-11 : 12:34:58
|
quote:
Originally posted by HCLollo
rfleming,while using more significant names for your fields, like CalendarYearor like, YEAR and DATE should not be reserved words, as far as I canunderstand from BOL
YEAR and DATE are both ODBC Reserved Keywords and both appear on the Future Keywords list for SQL Server (as listed in BOL). On top of that, YEAR() is also a SQL Server function, so that could really get confusing and messed up. I would steer clear of them as field names.---------------------------EmeraldCityDomains.com |
|
|
|
rfleming
Starting Member
9 Posts |
Posted - 2005-10-11 : 12:41:57
|
| What would be the recommended "fix" then?1 - Clone the table/create new table2 - make changes to new table (field names, datatypes, etc)3 - truncate the new table if cloned4 - DTS(or select into) data from old table into new table5 - make updates to associated trigger |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-11 : 12:42:48
|
quote:
Originally posted by rfleming
lessons learned here...I opened table is design view and manually changed the default value of one of the fields from 0 to blank. Then ran sp_rename and changed the 2 field names YEAR and DATE.When i opened the application that uses this table and proceed to "reorganize" the table, i got an ODBC SQL error of "syntax error converting datetime from character string". These fields did have data in them before i made the change. I wonder if i should have DTS'd the data out 1st, made the changes, then DTS'd the data back in?Now I'm going to have to restore the table from a backup.
First of all, you can't restore just a single table from backup unless it's all alone on a separate file. Doing so in a production environment would risk getting your data out of sync between the table you restored and the others in the system. But you can restore the whole database, which I suspect is what you really meant.Second, changing the default to an empty space or null, and renaming the fields would not have caused the error you describe. What do you mean by "reorganize the table" from your application? What exactly did you do?By the way, the most common cause of that error message is that you are attempting to INSERT or UPDATE a datetime or smalldatetime field with a value that is not in a recognizable date-time format.---------------------------EmeraldCityDomains.com |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-11 : 12:47:46
|
quote:
Originally posted by rfleming
What would be the recommended "fix" then?1 - Clone the table/create new table2 - make changes to new table (field names, datatypes, etc)3 - truncate the new table if cloned4 - DTS(or select into) data from old table into new table5 - make updates to associated trigger
The process that EM would go to is often the long way around, but it goes something like this:1 - Create a table named Tmp_OriginalTableName with the new fields/structure/etc.2 - SELECT INTO Tmp_... FROM ...3 - DROP all Constraints, indexes, triggers, etc. on old table4 - DROP old table5 - Rename new table to old tablename6 - Recreate all the constraints, indexes, triggers, etc.Did you have triggers, constraints, or indexes already in effect on the fields you renamed? Try dropping and recreating those.---------------------------EmeraldCityDomains.com |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
Reserved words