varchar & char variables max size
-
Our office just recently loaded SS SP4 to our servers. I created a script today with a variable called:
@sql varchar(8000)
In populating this variable, I had a select statement that was going to generate an output of 7280 bytes in length. When I displayed the string to the screen, I discovered that the output had been truncated to 4000 bytes. I had the same thing happen when I changed the datatype to char. The len() function revealed that the string was indeed 4000 bytes in length. I have never had this happen to me before. I have always been able to use all 8000 bytes in a variable before. Is this behavior tied to SP4? Has anyone else seen this behavior?
Thanks.
-
How are you looking at the results? QA
if yes check the tools->options->result tab and check the max char length
* Noel -
If that doesn't work... can we see the code you're using the populate the variable (with the necessary info to reproduce the behavior)?
-
QA screen output size was set to 8000.
Declare @sql varchar(8000),
@TableName varchar(128)
select @sql = ''
-- select len(@SQL)
-- select datalength(@SQL)
select @sql = @sql + 'Create View vw_' + @TableName + char(10) +
'AS' + char(10) +
'select * ' + char(10) +
'from Server.DB.dbo.' + @TableName
from DB.dbo.sysobjects
where xtype = 'U'
order by name
print @sql
-- exec(@SQL)
-
Should read:
Declare @sql varchar(8000),
@TableName varchar(128)
select @sql = ''
-- select len(@SQL)
-- select datalength(@SQL)
select @sql = @sql + 'Create View vw_' + name + char(10) +
'AS' + char(10) +
'select * ' + char(10) +
'from Server.DB.dbo.' + name
from DB.dbo.sysobjects
where xtype = 'U'
order by name
print @sql
-- exec(@SQL)
-
This works on my pc... but I got many tables... so the end gets truncated... maybe you'd be better off doing loops of 20 views at the time. Also select * from is considered a worst practice. You'd be better off writting the column names.
-
And depending on the front end you should use chr(13) + chr(10) instead of chr(10) only
* Noel -
BTW you should really more looking into this :
to test the code :
exec dbo.pr__SYS_MakeAllRecordProc 'TableName', 1, 0
refresh the list of procs and see go see tablename_insert ...
You'll have 3 procs coded for ya
.SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[pr__SYS_MakeAllRecordProc] @sTableName as varchar(128), @bExecute as bit = 0, @UsingMsForEach as bit = 0
AS
SET NOCOUNT ON
if @UsingMsForEach = 1
BEGIN
SET @sTableName = substring(@sTableName, charindex('.', @sTableName) + 2, len(@sTableName) - charindex('.', @sTableName) - 2)
END
exec dbo.pr__SYS_MakeDeleteRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeInsertRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeSelectRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeUpdateRecordProc @sTableName, @bExecute
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeDeleteRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Delete'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Delete' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Delete a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Delete]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'DELETE' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[pr__SYS_MakeForEachTable] @bExecute as int
AS
--i know dynamic sql could do this in a single request but I can't get it to work.. sp_msforeachtable is not found for some reason even using Master..sp_msForEachTable
SET NOCOUNT ON
if @bExecute = 1
exec sp_MSForEachTable 'exec pr__SYS_MakeAllRecordProc ''?'', 1, 1'
else
exec sp_MSForEachTable 'exec pr__SYS_MakeAllRecordProc ''?'', 0, 1'
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeInsertRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sAllFields varchar(2000),
@sAllParams varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@HasIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET @HasIdentity = 0
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sAllFields = ''
SET@sWhereClause = ''
SET@sAllParams = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Insert'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Insert' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Insert a single record into ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Insert]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IsIdentity = 0)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@sAllFields '')
BEGIN
SET @sAllParams = @sAllParams + ', '
SET @sAllFields = @sAllFields + ', '
END
IF (@sTypeName = 'timestamp')
SET @sAllParams = @sAllParams + 'NULL'
ELSE IF (@sDefaultValue IS NOT NULL)
SET @sAllParams = @sAllParams + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
ELSE
SET @sAllParams = @sAllParams + '@' + @sColumnName
SET @sAllFields = @sAllFields + @sColumnName
END
ELSE
BEGIN
SET @HasIdentity = 1
END
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
BEGIN
IF @sTypeName = 'nvarchar'
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(4)) + ')'
ELSE
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
END
IF (@IsIdentity = 0)
BEGIN
IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')
SET @sKeyFields = @sKeyFields + ' = NULL'
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF
SET @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
IF (@HasIdentity = 1)
BEGIN
SET @sProcText = @sProcText + 'RETURN SCOPE_IDENTITY()' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
END
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeSelectRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sSelectClause varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sSelectClause = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Select'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Select' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Select a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Select]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
IF (@sSelectClause = '')
SET @sSelectClause = @sSelectClause + 'SELECT'
ELSE
SET @sSelectClause = @sSelectClause + ',' + @sCRLF
SET @sSelectClause = @sSelectClause + @STAB + @sColumnName
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSelectClause = @sSelectClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + @sSelectClause
SET @sProcText = @sProcText + 'FROM' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeUpdateRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sSetClause varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sSetClause = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Update'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC [DBO].[' + @sTableName + '_Update]' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Update a single record in ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Update]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
BEGIN
IF @sTypeName = 'nvarchar'
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(4)) + ')'
ELSE
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
END
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
ELSE
IF (@IsIdentity = 0)
BEGIN
IF (@sSetClause = '')
SET @sSetClause = @sSetClause + 'SET'
ELSE
SET @sSetClause = @sSetClause + ',' + @sCRLF
SET @sSetClause = @sSetClause + @STAB + @sColumnName + ' = '
IF (@sTypeName = 'timestamp')
SET @sSetClause = @sSetClause + 'NULL'
ELSE IF (@sDefaultValue IS NOT NULL)
SET @sSetClause = @sSetClause + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
ELSE
SET @sSetClause = @sSetClause + '@' + @sColumnName
END
IF (@IsIdentity = 0)
BEGIN
IF (@IsNullable = 1) OR (@sTypeName = 'timestamp')
SET @sKeyFields = @sKeyFields + ' = NULL'
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSetClause = @sSetClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'UPDATE' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sSetClause
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
I ran into this problem recently. I believe EXEC (@sql) is expecting an NVARCHAR string. Since NVARCHAR takes 2 bytes for every 1 byte of a VARCHAR string the max VARCHAR that can be passed to EXEC() is 4000. I had an SP that was generating a string that was 4076 so I had to subset the query:
- Declare @str1 varchar(4000)
- Declare @str2 varchar(4000)
- Select @str1 = generate SQL query with WHERE to get 1st half of results
- Select @str2 = generate SQL query with WHERE to get 2nd half of results
- EXEC (@str1+@str2) -- Worked!
-
Giving the customer what he asked for.
-
This is for display only in QA and char(10) works just fine.
-
ok... but you'll still have to split that thing at intervals of 50 tables or so... 8000 chars is just not be enough.
-
From BOL:
[N]'tsql_string'
Is a constant string. tsql_string can be of nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type with a maximum value of the server's available memory. If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string.
The 'N' is optional in the exec() call and if not present, then the 'tsql_string' is viewed as a varchar datatype.
I have been doing this for a few years now and have never had a varchar(8000) variable truncate to 4000 bytes, which is why I was suspicious of SP4. The truncation occured with the exec() call commented out.
-
We're at SP3 and we get an error if we pass a varchar to EXEC() that's over 4000 in actual length.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply