cast or convert to use "sys.fn_sqlvarbasetostr()"

Question

cast or convert to use "sys.fn_sqlvarbasetostr()"

ralf_huster

SSC Veteran

Points: 223
More actions
November 16, 2012 at 3:14 am

#284961

Hi folks,
in order to generate INSERT-Statements for a table I found the following procedure
(thanks to "www.itrain.de"):
CREATE PROCEDURE sp_GenerateInsertStatements
@TableName NVARCHAR(255),
@maxRows INT = 100
AS
SET NOCOUNT ON
DECLARE @INSERT_START NVARCHAR(4000)
DECLARE @COLUMN_LIST NVARCHAR(4000)
DECLARE @SELECT_LIST NVARCHAR(4000)
DECLARE @maxRowStatement NVARCHAR(20)
DECLARE @QuotedTableName NVARCHAR(257)
SET @quotedTableName = QUOTENAME(@TableName)
SET @INSERT_START = 'INSERT INTO ' + @QuotedTableName + ' ('
SELECT @COLUMN_LIST = CASE WHEN @COLUMN_LIST IS NULL THEN QUOTENAME(COLUMN_NAME)
ELSE @COLUMN_LIST + ', ' + QUOTENAME(COLUMN_NAME)
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TableName
AND DATA_TYPE NOT IN ('text', 'ntext', 'image', 'timestamp', 'sql_variant')
ORDER BY ORDINAL_POSITION
SELECT @SELECT_LIST = CASE WHEN @SELECT_LIST IS NULL THEN 'ISNULL(CAST(master.dbo.fn_sqlvarbasetostr(' + QUOTENAME(COLUMN_NAME) + ') As NVARCHAR(4000)), ''NULL'')'
ELSE @SELECT_LIST + '+ '', '' + ISNULL(CAST(master.dbo.fn_sqlvarbasetostr(' + QUOTENAME(COLUMN_NAME) + ') as NVARCHAR(4000)), ''NULL'')'
END
FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName
AND DATA_TYPE NOT IN ('text', 'ntext', 'image', 'timestamp', 'sql_variant')
ORDER BY ORDINAL_POSITION
SET @COLUMN_LIST = @COLUMN_LIST + ') VALUES ('
SET @maxRowStatement = N'TOP ' + CAST(@maxRows As NVARCHAR(10))
EXEC ('SELECT ' + @maxRowStatement + ' ''' + @INSERT_START + @COLUMN_LIST + ' '' + ' + @SELECT_List + '+ '')''' + ' FROM ' + @QuotedTableName)
Unfortunatly it doesn't work for column type 'text', because of typecollision with type
sqlvariant used by the systemfunction "fn_sqlvarbasetostr()".
Does anybody know, if there's another systemfunction or can anybody tell me how to
cast or convert the argument to fn_sqlvarbasetostr() ?
Thanks in advance

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

That is an interesting script. The easiest way to avoid the collision is to stop using the deprecated text datatype. Use (n)varchar(max) instead.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323546 More actions · Answer 2

Sean Lange (11/16/2012)
That is an interesting script. The easiest way to avoid the collision is to stop using the deprecated text datatype. Use (n)varchar(max) instead.

i was trying a simple code to verify that would work, but even though fn_sqlvarbasetostr has a sqlvariant as the parameter type, and sqlvariant supposedly supports varchar(max), i was getting an error anyway:

Msg 206, Level 16, State 2, Line 13
Operand type clash: varchar(max) is incompatible with sql_variant

[my test code, trying to convert existing data to varchar(max):

CREATE TABLE [dbo].[EXAMPLETEXT] (

[EXAMPLEID] INT NOT NULL,

[ATEXTFIELD] TEXT NULL,

CONSTRAINT [PK__examplet__C65E847960B24907] PRIMARY KEY CLUSTERED (exampleid))

insert into EXAMPLETEXT(EXAMPLEID,ATEXTFIELD)

select 1, REPLICATE ('ABCDEFG01234567890',800) UNION ALL

select 2, REPLICATE ('cast or convert to use "sys.fn_sqlvarbasetostr()"',800)

select 3, 'cast or convert to use "sys.fn_sqlvarbasetostr()"'

select

EXAMPLEID,

master.dbo.fn_sqlvarbasetostr(ATEXTFIELD)

FROM

(SELECT EXAMPLEID,CONVERT(varchar(max),ATEXTFIELD) AS ATEXTFIELD FROM EXAMPLETEXT) myAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Of course I didn't actually test it but since the issue was with a deprecated type I made the assumption....which certainly made one out of me. 😉

It does appear to be a pretty handy script for scripting out data inserts. I could see adding one more parameter to the mix for @DestinationTable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/