[RESOLVED] unix to microsoft - same sql query?

raspberryh

Hi,
I am used to working on a Unix server using SQL... But right now the site I'm working on is on a Microsft Server using Microsoft SQL... I never really knew if there was much of a difference, but my query which was working fine on the Unix server is giving me errors on the SQL server. It's a create table query. Maybe someone can see the problem right away?

CREATE TABLE jobs (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL default '',
  description mediumtext NOT NULL,
  qualifications mediumtext NOT NULL,
  dateadded int(8) NOT NULL default '0',
  acceptingthru int(8) NOT NULL default '0',
  contact varchar(255) NOT NULL default '',
  phone varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default '',
  address mediumtext NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

And the error I get is this:
Line 2: Incorrect syntax near 'auto_increment'

I have tried looking up stuff on auto_increment for MS SQL but can't find anything.

Thanks,
Heather

rane500

Well, there are a few problems there. TYPE=MyISAM isn't standard SQL and I don't think SQL Server can use the MyISAM table storage system anyway, so that should be removed entirely. Also, I think the auto increment and primary key declarations have to be done right at the field you're trying to set...and I think (I'm stretching here, but a quick check on Google seems to confirm my spotty memory) you set incrementing with the IDENTITY flag...although it's exact usage is beyond me.

Hope that helps some.

bpat1434

MS SQL doesn't have auto_increment.... or at least it's not called that.

I'd say just take what you have to do is manually create teh table(s). MSSQL doesn't have anything on the net as far as documentation is concerned (or atleast from what I've found).

Typical MSSQL queries look like:

CREATE TABLE [dbo].[domain](
  id smallint IDENTITY( 1, 1),
  column2 char(72)
)

IDENTITY(seed, incerement)

So, seed is the start (1,2,3) and the increment is the number added to the previous. So IDENTITY(1,1) would give autoincrements:
1,2,3,4,5...
IDENTITY(1,2):
1,3,5,7,9...
IDENTITY(7,1):
7,8,9,10...

Understand?

Doug_G

The entire SQL Server Books Online is, well, Online. Here is a link to the CREATE TABLE page for SQL Server 2000, you can go back up through the table of contents to find more documentation than you'll probably ever need. SQL Server 6.5 books in paper were maybe 10 pounds or so.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp

btw, most Microsoft documentation is online in the msdn library. I used to get their library CD's it was about 1.5gb of documentation and didn't include all the online content.

bpat1434

Try googling for that.... I've never had MSDN show up when I search for MSSQL documentation.... kool... that'll be a good reference.

raspberryh

Thank you so much for your replies! Especially bpat1434, your reply is really helpful. I really appreciate it. I'm going to go try it now! Thanks again!!!!
Heather