Recreating Tables

Last Post 29 Nov 2007 02:36 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Amber_Robertsona
New Member
New Member

--
22 Nov 2007 01:24 AM
Hello,
We have noticed recently that when we drop and recreate a table in SQL Server 2000 via TSQL it seems to keep the original collations even if we haven't specified them in the create table statement.

This is the sql query we are using to recreate the tables:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ytbIED_CommideaAuthOut2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ytbIED_CommideaAuthOut2]
GO

CREATE TABLE [dbo].[ytbIED_CommideaAuthOut2] (
[lngId] [int] IDENTITY (1, 1) NOT NULL ,
[lngCommideaFileId] [int] NOT NULL ,
[tintTransactionType] [tinyint] NOT NULL ,
[strPaymentCardNumber] [varchar] (30) NOT NULL ,
[strPaymentSecurityNumber] [varchar] (6) NULL ,
[strPaymentExpiryDate] [varchar] (4) NOT NULL ,
[strPaymentIssueNo] [varchar] (4) NULL ,
[strPaymentStartDate] [varchar] (4) NULL ,
[decTransactionValue] [decimal](18, 4) NULL ,
[strSentAuthorisationCode] [varchar] (10) NULL ,
[strQuotedReferenceNumber] [varchar] (20) NOT NULL ,
[tintTransactionResult] [smallint] NOT NULL ,
[strReturnedAuthorisationCode] [varchar] (10) NULL ,
[strReturnedMessage] [varchar] (128) NULL ,
[strSchemeName] [varchar] (20) NULL ,
[sintEFTSequence] [smallint] NULL ,
[strBxOrderNo] [varchar] (20) NULL ,
[sintOrderStatusId] [smallint] NULL ,
[strAccountCode] [varchar] (20) NULL ,
[tsRowVersion] [timestamp] NULL
) ON [PRIMARY]
GO


Then we run sp_help ytbIED_CommideaAuthOut2 which shows lots of columns with Collation =Latin1_General_CI_AS.


Does anybody know what we have done wrong?


Any suggestions much appreciated

Regards
Amber
Amber_Robertsona
New Member
New Member

--
26 Nov 2007 01:55 AM
Thank you for your reply, after looking at the table again it does appear that Varchar columns do have the collations specified even though we didn't specify they at table recreate. Do you know if the database collations are changes would the column varchar collations automatically updated as well?


Regards
Amber


Amber_Robertsona
New Member
New Member

--
28 Nov 2007 04:33 AM
Thanks for letting me know.


Kind Regards
Amber
SQLUSA
New Member
New Member

--
29 Nov 2007 02:36 AM
Try to keep away from mixed-collations.

It is a real drain on database developer (and dba) productivity.

Kalman Toth
http://www.sqlusa.com/ssis/
You are not authorized to post a reply.

Acceptable Use Policy