I have a stored procedure that builds insert statements for the table I
pass.
This procedure works perfect at my office. But at my client site, it doesn't
seem to work correctly. Both are Sql 2008. But one is on a server the
other is on a desktop. Not sure why that would be.
I was confused as to why my insert statements were getting cut off until I
copied one of them and put them on Word and lo and behold, the number
characters was exactly 255. An interesting number. Now the 2 variables I
use are varchar(2000) and varchar(8000), so there should be no problem.
I am running this from my Query screen in Management studio.
The SP is:
************************************************
/****** Object: StoredProcedure [Transform].[usp_Build_Insert_Statements]
Script Date: 11/06/2009 14:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE BuildInsertStatements
@Table_Name varchar(200)
AS
SET NOCOUNT ON
DECLARE @Script varchar(8000),
@column_list varchar(1000),
@decl varchar(500),
@q char(1),
@ktr smallint,
@Sql nvarchar(2000),
@Identity varchar(100)
--Column Info variables
DECLARE @column_name varchar(65),
@column_data_type varchar(30),
@column_id int,
@ident_flag bit
SET NOCOUNT ON
SET @Script = ''
SET @column_list = ''
SET @q = char(39)
SET @ktr = 0
SET @Identity = ''
SET @Sql = 'SELECT @ktr = count(*) FROM ' + @Table_Name
EXEC sp_executesql @Sql, N'@ktr int OUTPUT', @ktr OUTPUT
-- If no rows in table, don't bother setting up response as
-- There are to insert statements to build
if @ktr = 0
RETURN
--Cursor for the columns within a table
DECLARE c_columns CURSOR FOR
SELECT c.name, CASE WHEN c.xtype = c.xusertype THEN t.NAME ELSE 'varchar'
end, c.colid
FROM sysobjects o JOIN syscolumns c on c.id = o.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.NAME = @table_name and t.name not in ('timestamp')
ORDER BY c.colid
OPEN c_columns
FETCH NEXT FROM c_columns INTO @column_name, @column_data_type, @column_id
/* check if table has IDENTITY property set to YES */
SELECT @ident_flag = count(1), @Identity = c.name
FROM sysobjects o JOIN syscolumns c on c.id = o.id
WHERE o.NAME = @table_name AND COLUMNPROPERTY (c.id, c.name,
'IsIdentity') = 1
GROUP BY c.name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Script = @Script +
(SELECT CASE
WHEN @column_data_type
in ('varbinary', 'binary', 'varchar', 'char', 'nchar',
'nvarchar', 'datetime', 'smalldatetime')
THEN 'CASE WHEN ' + @column_name +
' IS NOT NULL THEN @q + LTRIM(RTRIM(REPLACE(' + @column_name +
',char(39),char(39) + char(39)' + ')))
+ @q ELSE ''NULL'' END + '','' + ' ELSE
/*'cast(' + @column_name + ' as varchar(50)) + '',''
+ ' END) + char(13)*/
'CASE WHEN ' + @column_name + ' IS NOT NULL THEN + CAST(' + @column_name
+ ' as varchar(50))
ELSE ''NULL'' END + '','' + ' END) + char(13)
--IF @ident_flag = 1
SET @column_list = @column_list + @column_name + ','
FETCH NEXT FROM c_columns into @column_name, @column_data_type, @column_id
END
DEALLOCATE c_columns
--IF @ident_flag = 1
SET @column_list = '(' + LEFT(@column_list, LEN(@column_list) - 1) + ')'
SET @Script = LEFT(@Script, LEN(@Script) - 6) + ')'
SET @Script = 'SELECT ' + @q + 'INSERT ' + @Table_name + @column_list + @q
+ '+ char(13) +' + @q
+ 'VALUES (' + @q + '+' + char(13) +
@Script + @q + char(13) + 'From ' + @Table_name
SET @decl = 'declare @q char(1)
SET @q = char(39) '
IF @ident_flag = 1
PRINT 'SET IDENTITY_INSERT '+ @Table_name + ' ON'
/* PRINT '@decl'
PRINT @decl
PRINT ''
PRINT '@Script'
PRINT @Script
PRINT ''
*/
if @Script <> ''
PRINT 'DELETE [' + @Table_Name + ']'
EXECUTE (@decl + @Script)
SET @Script = ''
IF @ident_flag = 1
BEGIN
PRINT 'SET IDENTITY_INSERT '+ @Table_name + ' OFF'
SET @Sql = 'SELECT @MaxSeed = MAX(' + @Identity + ') FROM ' + @Table_Name
+
';DBCC CHECKIDENT (' + @Table_name + ',RESEED,@MaxSeed)'
PRINT @Sql
PRINT ''
END
************************************************
As you can see, @Sql is varchar(2000) and @Script is (8000).
Nothing is 255.
And the result of one of the lines is:
INSERT
FormLayout(FormLayoutID,FormatDescription,FormFileTypeID,IsFixedLength,Trig ger1Value,Trigger1Column,Trigger2Value,Trigger2Column,IsHeader,HeaderRows,I nsertId,InsertDate,LastUpdateId,LastUpdateDate,ConcurrencyId)
VALUES (25,'XLS Format 135 End Delimi
Obviously the VALUES clause is missing some data. All of the lines are like
this.
Why would that be???
Thanks,
Tom
|
