The identifier that starts...Maximum length is 128
-
I recently installed SQL 2000 on my Desktop. I kept the existing SQL 7 installation.
I run a stored procedure daily for data releases. The stored procedure runs fine in SQL Query Analyzer 7, But after 2000 was installed it fails to run with the following message...
Server: Msg 103, Level 15, State 7, Line 12
The identifier that starts with ''TEXT', 'TEXT', 'TEXT', 'TEXT', 'TEXT', 'TEXT', 'TEXT', 'TEXT', 'TEXT' is too long. Maximum length is 128.
Is there a way around this ? can I install the version 7 of the analyzer separate ?
Why would Microsoft make a parameter smaller
-
They havent that I know of. Could you post the first part of the proc?
Andy
-
CREATE PROC DBO.Copydb
@TARGET_DB VARCHAR (100) = 'SDS093.COPYTEST',
@SOURCE_DB VARCHAR (100) = 'SDS_DBServer.DBNAMEOUT' ,
@PROJECT2COPYVARCHAR (100) = "'-'" ,
@PROJECT_LISTVARCHAR (255) = "'-'" ,
@LANGUAGE_LISTVARCHAR (255) = "'-'"
AS
DECLARE @TSQLSTMT VARCHAR (3000)
-- ****************************************************************** --
--BEGIN TRAN
/*-- Step 0: Clear Tables First Before Copying -- */
-- NOTE TRUNCATE TABLE cannot be used on the table in linked server
--D1
SET @TSQLSTMT = 'DELETE FROM ' + @TARGET_DB + '.DBO.TABLE '
PRINT ''
PRINT '-- DELETE FROM TABLE ...'
PRINT @TSQLSTMT
EXEC ( @TSQLSTMT )
SET @TSQLSTMT = NULL
-
The Procedure is being run against a sql 7 Database. The only thing different is that is that its now being run using SQL 2000 Analyzer.
-
Could the folling be something to do with it ?
FROM BOL
Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default.
-
FROM "System Configuration" in BOL
quote:
The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks ("). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks; for example, if its name contains characters that are otherwise not allowed in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimited by square brackets.The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:
SELECT "x" FROM T
If QUOTED_IDENTIFIER is set to ON, "x" is interpreted to mean the column named x. If it is set to OFF, "x" is the constant string x and is equivalent to the letter x.
If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would respond as if it were set to ON and treat "x" as the column named x.
You are right about the " being your problem, with Quoted Identifiers On the "'-'" is treadted as column name '-' and not a string value. To get around and make easiest on yourself change " to '', so "'-'" is '''-''', which always works.
Sorry the above is hard to see make " + ' + - + ' + " instead ' + ' + ' + - + ' + ' + ' and it will work properly both 7 and 2000.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 04/16/2002 04:50:21 AM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply