Error with varchar(50)??

pablosantanderpablosantander Posts: 3
edited June 14, 2006 6:20AM in SQL Packager Previous Versions
Hello people,

I'm evaluating the 5.1 bundle.

My situation:
I currently work on an asp.net application. The developers seat includes a sqlserver2005express.

The live situation of the app will be on internet on a SQL 2000 server hosted with DiscountAsp.Net.

I reached the point of uploading the database schema and content. I tried to use SQL packager. I ran in the following error:

<SQLError Package="SQLPackage">
<Error>Line 23: Incorrect syntax near 'max'.</Error>
<Assembly>SQLPackage</Assembly>
<Package>Package1.resx</Package>
<Batch>Batch10</Batch>

<SQL>CREATE TABLE [dbo].[Contact]
(
[ContactId] [bigint] NOT NULL IDENTITY(1, 1),
[BusinessLabelId] [int] NOT NULL,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsFemale] [bit] NULL,
[DateOfBirth] [datetime] NULL,
[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TownCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryId] [int] NULL,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mobile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SkypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastLogin] [datetime] NULL
)
</SQL></SQLError>


Any suggestions?

Paul[/email]

Comments

  • Hi Paul,

    thanks for reporting this. The problem is, that the 'max' keyword for varchars is a SQL Server 2005 addition, it does not exist in SQL Server 2000.

    It's this line in your code that's causing the problem.

    [Comment] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    Since the live database will be on a SQL Server 2000 instance you will have to get rid of the 'max', i'm afraid. The biggest value you can enter instead of 'max' is 8000, so you might want to change the field to a text field instead. (Although 8000 chars for a comment seems plenty to me.)

    Not totally sure what SQL Packager is supposed to do in this case. We could replace the 'max' with '8000', but 'max' sets the maximum size of the varchar to 2^31-1 bytes, so maybe we should change the column to a text column. Or maybe we shouldn't do anything and just inform the user that there is a problem and let the user sort it out.

    Let me know how you get on.

    Regards,

    Tilman
  • Tilman,

    thanks for the reply. I didnot realise compatibility issues between 2005 and 2000 were of any concern ... obviously they are. Ai ...

    I'll follow your suggestion and get rid of the MAX and varchar(max) ... I'll change it into 'text'.

    What should red-gate software do with this??? Publish a list of compatiblity issues which exist, including suggestions how to deal with each one of them (much in the same sence as your reply was on this particular topic). -- huh -- I'm new to Red-Gate stuff ... maybe this already exists?

    Thanks for the reply ....

    Paul
  • Hi Paul,

    going from SQL Server 2000 to 2005 shouldn't cause any problems. Going the other way is where the difficulties lie. As a colleague pointed out to me, even if we cleverly made a change, say change a data type, that might break things in other places, i.e. it's not a trivial problem. Also, since there are quite a few changes between 2000 and 2005 a list of the changes with recommendations would get quite long (and would replicate the SQL Server documentation to some degree). So I think the best we can do is to make people aware of the problem and point them to the SQL Server documentation.

    Thanks again for posting.

    Tilman
Sign In or Register to comment.