MySQL over to SQL Server

[Guest]

I have recently moved from using MySQL 5.0 over to SQL Server 2008. It seems that my query code doesn't work anymore. Am I supposed to write my cfquery commands in a different format when I am using SQL Server? If so, what is the format?

[Newsgroup_User]

bje98f wrote:
> I have recently moved from using MySQL 5.0 over to SQL Server 2008. It seems
> that my query code doesn't work anymore. Am I supposed to write my cfquery
> commands in a different format when I am using SQL Server? If so, what is the
> format?
>

If they are basic ANSI 92 SQL code then, no you should not have to
re-write them

If you have a lot of sophisticated sql code that makes use of unique
MySQL features, then yes you have to rewrite them into similar SQL
Server constructs or forgo what can not be ported.

But as I have no idea of what you SQL looks like I can't say which might
be the case.

[Guest]

well normally this code that works fine in MySQL to create me a table called threads doesn't work. I now get this message:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '`'.

Any suggestions?

[Fetch]

Shot in the dark here but I think this may have something to do with the 'type' of apostrophe that is being used, i.e. the difference between

'

and

`

I saw this once before when using MySQL inserts but it was a long time ago, so I reserve the right to be wrong on this one. :-)

[-__cfSearching__-]

bje98f wrote:
> Error Executing Database Query.
> [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '`'.

There are some ddl differences between MySQL and MS SQL. MS SQL typically use square brackets, for escape characters, not '`' . There may be differences in reserved words as well.

> id` INT UNSIGNED NOT NULL AUTO_INCREMENT
> `minipic` BLOB NULL ,

You will also find slight differences in data types. IIRC, MS SQL does not have native "unsigned" types The MS SQL equivalent of BLOB is either image or varbinary, depending on sql server version. As far as keywords go, MS SQL uses "identity" rather than auto_increment. But they are roughly the same.

Do a search on MySQL to MS SQL migration. It should turn up a fair number of comparison guides highlighting the differences between the two databases (syntax, ddl, data types, etcetera).