MSSQL Help Wanted

[Nuttzy99]

I don't have access to a MSSQL DB, so I could use some help from someone experience with both MySQL and MSSQL. I need people that can run tests for me and suggest fixes and improvements.

I'm really unsure about when to use "GO". Note that each of the statements I have gets processed one at a time, so there should not be a need for semicolons. Also, I assume it's no big deal that I'm putting everything on to one long line instead of doing some elaborte indenting. For starters, let's get these 5 MySQL statements translated to MSSQL correctly:

ADD TABLE:

CREATE TABLE phpbb_mytable (
FirstName varchar(40) NOT NULL DEFAULT '',
LastName varchar(40) NOT NULL DEFAULT '',
DateOfBirth int(11) NOT NULL DEFAULT '0',
SSN int(11) UNSIGNED NOT NULL DEFAULT '' auto_increment,
KEY DateOfBirth (DateOfBirth),
PRIMARY KEY (SSN)) ;

CREATE TABLE [phpbb_mytable] ( [FirstName] [varchar] (40) NOT NULL, [LastName] [varchar] (40) NOT NULL, [DateOfBirth] [int] NOT NULL, [SSN] [int] UNSIGNED IDENTITY (1, 1) NOT NULL) ON [PRIMARY] GO

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_FirstName] DEFAULT ('') FOR [FirstName]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_LastName] DEFAULT ('') FOR [LastName]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_DateOfBirth] DEFAULT ('0') FOR [DateOfBirth]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_SSN] DEFAULT ('') FOR [SSN]

CREATE INDEX [DateOfBirth_phpbb_mytable_index] ON [phpbb_mytable] ([DateOfBirth]) ON [PRIMARY] GO

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [PK_phpbb_mytable] PRIMARY KEY CLUSTERED ( [SSN] ) ON [PRIMARY] GO

ADD COLUMN

ALTER TABLE phpbb_mytable ADD
nut_test int(8) NULL DEFAULT '0' ;

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD [nut_test] [int] NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_nut_test] DEFAULT ('0') FOR [nut_test]

MODIFY COLUMN

ALTER TABLE phpbb_mytable MODIFY
nut_test varchar(10) NULL DEFAULT '' auto_increment ;

ALTER TABLE [phpbb_mytable] ALTER COLUMN [nut_test] [varchar] (10) IDENTITY (1, 1) NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_nut_test] DEFAULT ('') FOR [nut_test]

...I probably need to drop any existing constraints, but I'm not sure how to automate this without screwing up the table.


DROP COLUMN

ALTER TABLE phpbb_mytable DROP
COLUMN nut_test ;

ALTER TABLE phpbb_mytable DROP COLUMN nut_test

...will related constraints also be dropped automatically?


DROP TABLE

DROP TABLE phpbb_mytable ;

DROP TABLE phpbb_mytable ;

Let me know what I've got wrong and what's missing. I will try to add more info here in the future, such as datatype conversions.

Thanks!
-Nuttzy

[masterconor]

I do not have access to MS-SQL, but I do have some experience. I ported IntegraMOD 1.3.1 to MS-SQL.

Lemme know if I can help.

Email-- masterconor@gawab.com

masterconor

[Woodley]

I would love to help with this.

Woodley6453@yahoo.com

thanks,

woodley

[SiLiCoNDragon]

Sorry, I know this post is a month old, but did not know if you still needed help. My knowlage of MS-SQL is pretty limited, I do most of my work in MS Access connecting to my MS SQL Server, but with my current attempt at converting the 2.1 MySQL Schema to MS SQL, I've been giving my self a bit of a crash course.

You can download MSDE (Basic MS SQL Server) from

http://www.microsoft.com/sql/msde/downloads/default.asp" target="_blank

It does not have any kind of managment interface, but you can use MS Access or a Web Driven interface to be able to exam tables, delete tables, etc.

This link goes to the MSDN Website and has some very useful documentation on MS SQL, but comparing the 2.0.10 schema to the SQL documents, you are going to have some of the same HUH's, that I am having, I'm sure.

http://msdn.microsoft.com/library/defau ... 2_8g9x.asp" target="_blank


The one thing I did notice, and to the best of my knowlage, MS SQL does not support Signed and Unsigned Integers, so I am using the follow as the converstion in the utility I am coding.

Basic If Statments, I'm using, rather than posting Delphi Code.

if TinyInt is Signed then use SmallInt
else use Tinyint

If SmallInt is unsigned then use Int
else use SmallInt

If MediumInt is signed or unsigned replace with Int

If Int is Unsigned then use BigInt
else use Int

If BigInt is signed use BigInt
else Error, no converstion to that big number.


I'd be happy to give you access to my WebServer and MS-SQL Server 2000. Send me an Email at jdoll@bravanet.net

[tzonkov]

I just downloaded the latest version of EasyMod (beta - 0.1.13) and tried the flag mode that comes with it. The MSSQL code is still problematic.

I am willing to test as well to get this fully working. This is a great software, and I would like to help to get it working for the dreaded MSSQL

[EVoskamp]

Hi,

I have a lot of database development experience and develop actively on both MSSQL and MySQL, the latter on FreeBSD 4.8. Currently I'm running MSSQL 2000 SP3a and MySQL 4.0.18. I'll be happy to test, answers questions and the like.

As far as your questions go:

GO is not part of MSSQL its SQL (known as Transact-SQL, or TSQL): it is a command line construct recognized by the various SQL interpreters (isql, osql, isqlw). It is used to transmit the current batch of SQL commands to SQL Server. The different interpreters interpret GO differently, but the differences should not matter.

GO has to be used on a line by itself, though you can put comments on the same line after it (introduced by --).

The use of GO is mostly defined by what you execute:

• several statements need to be the first statement of a batch, like several CREATE staments:
  • CREATE DEFAULT
  • CREATE PROCEDURE
  • CREATE RULE
  • CREATE TRIGGER
  • CREATE VIEW
  These have to be the first of a batch, so you would use GO before them.
• altering a table's columns and its new columns cannot be used in the same batch, so you would need to put a GO in between them.
• if you want to execute stored procedures without the use of the EXECUTE keyword, you have to put a GO before it: this can only be done on the first call of a batch

[EVoskamp]

• ADD TABLE will not work:
  • the UNSIGNED keyword on the [int] datatype is not valid: no such beast. Simply leave off the UNSIGNED keyword
  • ALTER TABLE on column SSN is invalid: you cannot have a default on an IDENTITY column
  • Note that several have the GO statement on the same line as the CREATE TABLE or ALTER TABLE statment: that is not valid: you have to put it on the next line.
• ADD COLUMN is fine
• MODIFY COLUMN is invalid:
  • the IDENTITY property is only valid on columns of type tinyint, smallint, int, bigint, numeric and decimal
  • you can also only have one per table
  If you wish to store numeric values in a varchar column that is autonumbering, your best bet would likely be to add a trigger on that table.
  
  A default constraint on a column should override the existing one.
• DROP COLUMN fails, as you had guessed because of the existing constraint: they are not dropped automatically.
  To make this work, first drop the constraints by name:
  ALTER TABLE [phpbb_mytable] DROP CONSTRAINT [DF_phpbb_mytable_nut_test]
• DROP TABLE works fine

[plbeane94]

I can get you as much MySQL databases as you need.

Email--patrick.beane@gmail.com

thanks,
Patrick B

[Ryan*]

This topic is rather old, but I'll post anyway.

As an addition to EVoskamp's work I'd like to add that auto_increment and default values are done by TRIGGERs as follows (the most compact code I could think of):

ADD TABLE:

Code: Select all

CREATE TABLE [phpbb_mytable] (
	[FirstName] [varchar](40) NOT NULL CONSTRAINT [Some_Unique_Name1] DEFAULT (''),
	[LastName] [varchar](40) NOT NULL CONSTRAINT [Some_Unique_Name2] DEFAULT (''),
	[DateOfBirth] [int] NOT NULL CONSTRAINT [Some_Unique_Name3] DEFAULT (0),
	[SSN] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [Some_Unique_Name] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO

IDENTITY is a property with two params: seed (start value) and increment.

Defaults cannot be created on columns with an IDENTITY attribute so SSN can not have both default and auto_increment (CONSTRAINT in this case).

Every UniqueName has to be unique. They are usually named this way:
XX_TABLE_COLUMN
where XX = DF for default, PK for key and so on (so we get PK_phpbb_mytable_SSN, DF_phpbb_mytable_FirstName and so on).

Code: Select all

ALTER TABLE [phpbb_mytable] ADD [nut_test] [int] NULL DEFAULT '0'

is ok, but CONSTRAINT will be named oddly (DF__phpbb_myt__nut_t__32E0915F). It would be better to use:

Code: Select all

ALTER TABLE [phpbb_mytable] ADD [nut_test] [int] NULL CONSTRAINT [Some_Unique_Name5] DEFAULT '0'

I can't imagine why would you like to store numeric data in varchar column, but I can provide a trigger for IDENTITY(1, 1) simulation on varchars.

It's 4am... Need some sleep...

plbeane94: great, anything more offtopic to add?

[plbeane94]

Really? To me it shows up as 9