SQL script that works in 7.0 not working in 2000 
Author Message
 SQL script that works in 7.0 not working in 2000

Hi all.  I have a sql script that works in sql 7.0, but is giving me an
error in sql 2000.  I am going to post it here in hopes that someone can how
to correct the error.

Most of the sql script works fine, and I won't bore you with a couple
hundred lines of code, but here are the pieces that do not seem to work
correctly.

insert into dbo.T_Config
            (ID_Config, CFG_Key, CFG_Value, CFG_Desc)
     VALUES (9, 'FormChar',
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D

){}[]<>`?='! ", 'erlaubte Zeichen fr Formulareingaben')

GO

This gives me 2 errors in response:
Server: Msg 103, Level 15, State 7, Line 3
The identifier that starts with
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D
??????YT?a????????e??????yt012345678' is too long. Maximum
length is 128.

Server: Msg 128, Level 15, State 1, Line 3
The name
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D
??????YT?a????????e??????yt012345678' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.

The table properties for this table is:
Column Name        Datatype        Length        Allow Nulls
ID_Config              Int                    4
(Primary Key)
CFG_Key              Varchar            25
CFG_Value            Varchar           1000            Yes
CFG_Desc             Varchar           255              Yes

Also I am having problems with adding some countries into tables.  Here is
the code for one of them.  The thing that seems to be common with the ones I
am having problems with is countries with ' in their name.

insert into dbo.T_Country
            (CO_Country, CO_A2, CO_A3, CO_Number)
     VALUES ("Cote D'Ivoire", 'CI', 'CIV', 384)

GO

The error is:

Server: Msg 128, Level 15, State 1, Line 3
The name 'Cote D'Ivoire' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.

The table properties for this table is:

Column Name        Datatype        Length        Allow Nulls
CO_Country          varchar          100
CO_A2                  varchar           2
(Primary Key)
CO_A3                  varchar           3                    Yes
CO_Number           int                  4                    Yes

I am hoping that someone can help me with these problems.  Thanks all!



Mon, 08 Mar 2004 21:41:41 GMT
 SQL script that works in 7.0 not working in 2000

Either SET QUOTED IDENTIFIERS OFF or don't use Double Quotes. It's best if
you don't use Double Quotes.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive


Quote:
> Hi all.  I have a sql script that works in sql 7.0, but is giving me an
> error in sql 2000.  I am going to post it here in hopes that someone can
how
> to correct the error.

> Most of the sql script works fine, and I won't bore you with a couple
> hundred lines of code, but here are the pieces that do not seem to work
> correctly.

> insert into dbo.T_Config
>             (ID_Config, CFG_Key, CFG_Value, CFG_Desc)
>      VALUES (9, 'FormChar',

"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D

Quote:
> ){}[]<>`?='! ", 'erlaubte Zeichen fr Formulareingaben')

> GO

> This gives me 2 errors in response:
> Server: Msg 103, Level 15, State 7, Line 3
> The identifier that starts with

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D
Quote:
> ??????YT?a????????e??????yt012345678' is too long.
Maximum
> length is 128.

> Server: Msg 128, Level 15, State 1, Line 3
> The name

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D
Quote:
> ??????YT?a????????e??????yt012345678' is not permitted in
> this context. Only constants, expressions, or variables allowed here.
Column
> names are not permitted.

> The table properties for this table is:
> Column Name        Datatype        Length        Allow Nulls
> ID_Config              Int                    4
> (Primary Key)
> CFG_Key              Varchar            25
> CFG_Value            Varchar           1000            Yes
> CFG_Desc             Varchar           255              Yes

> Also I am having problems with adding some countries into tables.  Here is
> the code for one of them.  The thing that seems to be common with the ones
I
> am having problems with is countries with ' in their name.

> insert into dbo.T_Country
>             (CO_Country, CO_A2, CO_A3, CO_Number)
>      VALUES ("Cote D'Ivoire", 'CI', 'CIV', 384)

> GO

> The error is:

> Server: Msg 128, Level 15, State 1, Line 3
> The name 'Cote D'Ivoire' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.

> The table properties for this table is:

> Column Name        Datatype        Length        Allow Nulls
> CO_Country          varchar          100
> CO_A2                  varchar           2
> (Primary Key)
> CO_A3                  varchar           3                    Yes
> CO_Number           int                  4                    Yes

> I am hoping that someone can help me with these problems.  Thanks all!



Mon, 08 Mar 2004 21:57:44 GMT
 SQL script that works in 7.0 not working in 2000
Also, specify 2 single quotes in strings when one is desired.  For example:

insert into dbo.T_Country
             (CO_Country, CO_A2, CO_A3, CO_Number)
      VALUES ('Cote D''Ivoire', 'CI', 'CIV', 384)

--
Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------



Quote:
> Either SET QUOTED IDENTIFIERS OFF or don't use Double Quotes. It's best if
> you don't use Double Quotes.

> --
> Andrew J. Kelly,  SQL Server MVP
> TargitInteractive



> > Hi all.  I have a sql script that works in sql 7.0, but is giving me an
> > error in sql 2000.  I am going to post it here in hopes that someone can
> how
> > to correct the error.

> > Most of the sql script works fine, and I won't bore you with a couple
> > hundred lines of code, but here are the pieces that do not seem to work
> > correctly.

> > insert into dbo.T_Config
> >             (ID_Config, CFG_Key, CFG_Value, CFG_Desc)
> >      VALUES (9, 'FormChar',

"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D

Quote:
> > ){}[]<>`?='! ", 'erlaubte Zeichen fr Formulareingaben')

> > GO

> > This gives me 2 errors in response:
> > Server: Msg 103, Level 15, State 7, Line 3
> > The identifier that starts with

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D
Quote:
> > ??????YT?a????????e??????yt012345678' is too long.
> Maximum
> > length is 128.

> > Server: Msg 128, Level 15, State 1, Line 3
> > The name

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzfSOsoY?????????D

- Show quoted text -

Quote:
> > ??????YT?a????????e??????yt012345678' is not permitted
in
> > this context. Only constants, expressions, or variables allowed here.
> Column
> > names are not permitted.

> > The table properties for this table is:
> > Column Name        Datatype        Length        Allow Nulls
> > ID_Config              Int                    4
> > (Primary Key)
> > CFG_Key              Varchar            25
> > CFG_Value            Varchar           1000            Yes
> > CFG_Desc             Varchar           255              Yes

> > Also I am having problems with adding some countries into tables.  Here
is
> > the code for one of them.  The thing that seems to be common with the
ones
> I
> > am having problems with is countries with ' in their name.

> > insert into dbo.T_Country
> >             (CO_Country, CO_A2, CO_A3, CO_Number)
> >      VALUES ("Cote D'Ivoire", 'CI', 'CIV', 384)

> > GO

> > The error is:

> > Server: Msg 128, Level 15, State 1, Line 3
> > The name 'Cote D'Ivoire' is not permitted in this context. Only
constants,
> > expressions, or variables allowed here. Column names are not permitted.

> > The table properties for this table is:

> > Column Name        Datatype        Length        Allow Nulls
> > CO_Country          varchar          100
> > CO_A2                  varchar           2
> > (Primary Key)
> > CO_A3                  varchar           3                    Yes
> > CO_Number           int                  4                    Yes

> > I am hoping that someone can help me with these problems.  Thanks all!



Mon, 08 Mar 2004 22:03:07 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. IN SQL 7.0 Generate SQL Script not working

2. SQL string not works in VB and works in Access 2000

3. Script does not work on sql server 2000

4. works in 7.0 but not in sql 2000- please help

5. Send Mail script and Outlook 2000 not working

6. I don't understand why this worked in 7.0 and now not in 2000

7. Query works in 7.0 but not in 2000.

8. Object Browser in Query Analyzer Works 2000 but not in 7.0

9. Query works in 7.0 but not in 2000

10. Error in SQL Server 2000 but it works in SQL 7.0

11. SQL 2000 and Exchange on the same server and SQL Mail not working

12. SP works on SQL 7 but not SQL 2000


 
Powered by phpBB® Forum Software