SQL IMport Error -- Help Please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL IMport Error -- Help Please

Author

Topic

caddyjoe77
Starting Member

3 Posts

Posted - 2007-08-10 : 04:11:50

Hi, trying to import data from a MySQL DB to MSSQL 2005. Having some errors with collating it looks like. Maybe the syntax is incorrect...not sure. Never imported this way.

here is the import to create a table.

SET AUTOCOMMIT=0;
START TRANSACTION;

CREATE TABLE "tbl3215" (
"id" int(11) NOT NULL,
"IttNumber" text collate latin1_general_ci NOT NULL,
"IttTitle" text collate latin1_general_ci NOT NULL,
"IttDescription" text collate latin1_general_ci NOT NULL,
"IttJustification" text collate latin1_general_ci NOT NULL,
"TechSolution" text collate latin1_general_ci NOT NULL,
"DateReceived" text collate latin1_general_ci NOT NULL,
"DateRequired" text collate latin1_general_ci NOT NULL,
"Customer" text collate latin1_general_ci NOT NULL,
"OrgID" text collate latin1_general_ci NOT NULL,
"Funded" text collate latin1_general_ci NOT NULL,
"HBApID" text collate latin1_general_ci NOT NULL,
"Disapproved" text collate latin1_general_ci NOT NULL,
"DisReason" text collate latin1_general_ci NOT NULL,
"Completed" text collate latin1_general_ci NOT NULL,
"CompletionDate" text collate latin1_general_ci NOT NULL,
"Priority" text collate latin1_general_ci NOT NULL,
PRIMARY KEY ("id")
) AUTO_INCREMENT=4735 ;

here is the error returned.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 448, Level 16, State 2, Line 6
Invalid collation 'latin1_general_ci'.
Msg 448, Level 16, State 2, Line 7
Invalid collation 'latin1_general_ci'.
Msg 448, Level 16, State 2, Line 8
Invalid collation 'latin1_general_ci'.
Msg 448, Level 16, State 2, Line 9
Invalid collation 'latin1_general_ci'.
etc...

Now, i am not sure how to make this work, i am thinking sql2005 may not recognize latin1_general_ci? Is there a quide to which databases use which character sets and their incompatibilities?

Any help appreciated

Joe

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-10 : 05:35:31

It's not an import statment it's a create table statement.

run
SELECT * FROM ::fn_helpcollations()
or you can omit the collate statement and it will use the database default (you will probably have trouble if you have a different collation from the system databases)
You probably don't want these to be text columns - varchar(max) maybe but better to put a length on them.

SET AUTOCOMMIT=0;
START TRANSACTION;
AUTO_INCREMENT=4735 isn't valid on sql server

aren't valid on sql server
" should not be used as an identifier delimitter - use [] if you need one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

caddyjoe77
Starting Member

3 Posts

Posted - 2007-08-10 : 06:46:20

thank you, i have not ever had to do something like this and look at it as a challenge. I did find a good link for the data types in SQL: http://msdn2.microsoft.com/en-us/library/aa258233(SQL.80).aspx

now i am working on manually creating the tables via sql statements.

I didnt develop the original DB, i just have to move it from mySQL to SQL2005 and ASP/IIS.

I think the reason that everything is set to text is it seems to be a catch all for either numbers or letters. One of the problems i ran into previously (yes this is my second attempt at this!! :) ) was that the column ittnumber is in a format like this: YYYYNNNNN. This database tracks customer requirements, so there can be more than 1 requirement per day obviously. There may be numbers like 20070801, 02, 03 etc. So, on the old DB (my first attempt) sql automatically set the char type to float. This caused a problem as it kept multiple numbers for the same day. I discovered this when i was doing the asp, that when a query was run there were several 200701100, which lead me to look at my tables a little harder and now here we sit...lol

Thank You for the prompt response, much appreciated!

Joe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources