asked
To store Japanese’s Text in SQL server 2008 more than 15000 characters
Hello All, Aim : To store Japanese’s Text in SQL server 2008 more than 15000 characters Table :TP_Data Col : Stringv varchar(max) -> (old datatype is varchar(4000) Issue : Please let me know how to modify the below function so that i can use varchar(max) with Stringv column in table TP_Data CREATE function dbo.FSFormatString ( @ssvar sql_variant, @isPK int = 1, @binaryOption int = 0 ) returns varchar(8000) as begin declare @pstrout varchar(8000) ,@basetype varchar(128) select @basetype = CAST(SQL_VARIANT_PROPERTY ( @ssvar, 'BaseType' ) as varchar(255)) if (@ssvar = '') and (@ssvar <> ' ') select @pstrout = '''''' else if (@ssvar IS NOT NULL and @basetype IS NOT NULL) begin if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in('varchar', 'nvarchar'))--, 'char', 'nchar')) begin if @isPK = 1 select @pstrout = '''' + REPLACE(CAST(@ssvar as varchar(8000)), '''', '''''') + '''' if @isPK <> 1 select @pstrout = dbo.FSvarbintohexsubstring(1, CAST(@ssvar as varbinary(8000)), 1, 0) --select @pstrout = '''' + REPLACE(CAST(@ssvar as varchar(8000)), '''', '''''') + '''' end --else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar') -- select @pstrout = '''' + REPLACE(CAST(@ssvar as varchar(8000)), '''', '''''') + '''' else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('char', 'nchar')) begin if @binaryOption = 1 select @pstrout = dbo.FSvarbintohexsubstring(1, CAST(@ssvar as varbinary(8000)), 1, 0) else select @pstrout = '''' + REPLACE(RTRIM(CAST(@ssvar as varchar(8000))), '''', '''''') + '''' end --else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar') -- select @pstrout = '''' + REPLACE(RTRIM(CAST(@ssvar as varchar(8000))), '''', '''''') + '''' else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary')) select @pstrout = dbo.FSvarbintohexsubstring(1, CAST(@ssvar as varbinary(8000)), 1, 0) else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric')) select @pstrout = CAST(@ssvar as varchar(40)) else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real')) select @pstrout = CONVERT(varchar(60), @ssvar, 2) else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney')) select @pstrout = CONVERT(varchar(40), @ssvar, 2) else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier') select @pstrout = '''' + CAST(@ssvar as varchar(40)) + '''' else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime')) select @pstrout = '''' + CONVERT(varchar(40), @ssvar, 121) +'''' else select @pstrout = '''Invalid Datatype' + lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) + '(' + CAST(@ssvar as varchar) + ')''' end -- All done return rtrim(coalesce(rtrim(@pstrout), 'NULL')) ----------------------------------------------------------------------------------------------------- Please find below details : Database 1 Database 2 Table 1 TP_Data Table 2 TP_Data Column Stringv1 varchar(4000) Column Stringv2 varchar(4000) Scenario : We extract the data from Table 1 and Database 1 and run it Database 2. we have written a pattern which will delete complete data (Delete queries) of TP_Data (Database 2) and Insert into TP_Data(Database 2). The above function is used in many of the store proc which are used to delete /insert (mainly in insert ) . this Insert store proc (eg below ) inturn referring the same table TP_Data and also using the function (.FSFormatString ). So when we run the pattern to extract data from Database 1 and apply it Database 2 it will show that error . so the requirement is Stringv1 shud be around 15000. so i have modified Database 1 Database 2 Table 1 TP_Data Table 2 TP_Data Column Stringv1 varchar(MAX) Column Stringv2 varchar(MAX) so above function shud be modified so that pattern will Extract the data and execute on other DB properly. eg below begin if @binaryOption = 1 begin if (@colKey = 'CoreSiteCode' and (@CoreSiteCodeDest <> '')) set @ifnotexists = @ifnotexists + @colKey + ' = ''''' + @CoreSiteCodeDest + ''''')' else if (@colKey = 'SiteCode' and (@SiteCodeDest <> '')) set @ifnotexists = @ifnotexists + @colKey + ' = ''''' + @SiteCodeDest + ''''')' else if @dataType in (167,175,231,239) and @colKey not in ('CoreSiteCode', 'SiteCode')-- char, nchar, nvarchar, sysname, varchar set @ifnotexists = @ifnotexists + 'Convert(Varbinary(' + @length + '), ' + @colKey + ') = '' + dbo.FSFormatString(SOURCEDB.' + @colKey + ', ' + cast(@isPK as char(1)) + ', 1) + '')' else set @ifnotexists = @ifnotexists + @colKey + ' = '' + dbo.FSFormatString(SOURCEDB.' + @colKey + ', ' + cast(@isPK as char(1)) + ', 0) + '')' end else begin if (@colKey = 'CoreSiteCode' and (@CoreSiteCodeDest <> '')) set @ifnotexists = @ifnotexists + @colKey + ' = ''''' + @CoreSiteCodeDest + ''''')' else if (@colKey = 'SiteCode' and (@SiteCodeDest <> '')) set @ifnotexists = @ifnotexists + @colKey + ' = ''''' + @SiteCodeDest + ''''')' else set @ifnotexists = @ifnotexists + @colKey + ' = '' + dbo.FSFormatString(SOURCEDB.' + @colKey + ', ' + cast(@isPK as char(1)) + ', 0) + '')' Thanks naina
Sounds interesting but somehow not sure where is VARCHAR(MAX). Are you saying the input is VARCHAR(MAX) i.e. 15000 characters to be passed to sql_variant which have max 8000 allowed?
Thanks a lot Usman Butt thanks for ur response. We have table **TP_Data** which consist of Localization data . In this table one column **stringv** is Varchar(4000) so which can support max 500 HTML codes when converted and inserted in database. Our Extraction script which is Extracting data from Sql Server is using above Function. If we change stringv Varchar(MAX) (as Varchar(8000) is still very less ) this Function will through an error - > Operand type clash: varchar(max) is incompatible with sql_variant. Please let me know how to modify above function so that it can allow Varchar(MAX). Thanks Naina
**This solution is based on the assumption that you are using SQL 2008 and you need to convert VARBINARY TO STRING (because this is what this function was meant to be). Otherwise, it does not make sense, why you need this function. Please let us know if the situation is different.** Since you have tagged it in SQL Server 2008, there is no need to use this undocumented function. AFAIK, You can easily convert it to String e.g. DECLARE @varbin VARBINARY(MAX) SET @varbin = 0x736B616A64666C61736266647361 SELECT CONVERT(VARCHAR(MAX), @varbin, 2) EDIT: Although I did not understand (really weird question), but you could do a split of VARCHAR(MAX) to two VARCHAR(8000) and then use it (Since you have 15000 maximum characters). But purely, this is just to help your strange cause. You can change it according to your need DECLARE @LongField VARCHAR(MAX) SET @LongField = REPLICATE('I', 8000) SET @LongField = @LongField + REPLICATE('A', 8000) SELECT LEN(@LongField) SELECT master.sys.[fn_sqlvarbasetostr](CAST(@LongField AS VARCHAR(8000))) + master.sys.[fn_sqlvarbasetostr](CAST(SUBSTRING(@LongField, 8001, 8000) AS VARCHAR(8000))) EDIT : Hope this helps.
Thanks again Usman Butt .. Please find below details : Table TP_Data Columns sno,stringv,......... Front End : Convert Data into HTML codes and Inert in DB Issue : we have written a scripts to retrive data from this Table (all Localization table ) Please find below Insert Procedure which is using above function. So, i have to modify above function so that it will give expected result without any error. EG. Extracted scripts Insert Into TP_Data(Data,Datetime1) Values ( 'Invalid Datatypebr','2011-08-29 01:18:44.663' ) GO Issue : Please let me know how to solve or modify this FSFormatString Function.
@Naina_S, it is quite difficult to understand without the DDLs of the objects. I can only give a hint as I did. But If you want the exact change then you must post the code as well. Thanks.
I did not go into the details of your FSFormatString function and code, but just to get you started, you can do it like --Assuming @colKey referencing to a column which is VARCHAR(MAX) --...REST OF YOUR CODE SINPPET ' = '' + dbo.FSFormatString(CAST(SOURCEDB.' + @colKey + ' AS VARCHAR(8000), ' + cast(@isPK as char(1)) + ', 0) + '') + dbo.FSFormatString(CAST(SUBSTRING(SOURCEDB.' + @colKey + ', 8001, 8000) AS VARCHAR(8000)) , ' + cast(@isPK as char(1)) + ', 0) + '') --...REST OF YOUR CODE SINPPET But do look out for additional " ' " characters in formatted string after concatenation. hope this helps.
Thanks again Usman Butt .. plaese let me know if u can help in modifiying above function so that can use varchar(max ) as at present its throwing below error Operand type clash: varchar(max) is incompatible with sql_variant. Please note that i have given complete function in my question tab.
For that I would need to understand the logic completely. Have you acted upon my last suggestion?
hello Usman Butt , Thanks a lot for all ur inputs. Nope iam working on changing FSFormatString based on ur inputs. Yes i have modified SQL_VARIANT to VARCHAR(MAX). but still not getting out put in correct format. At times we ger result in Binary if SQL_VARIANT to VARCHAR(MAX). else will get stored procedure, function, trigger, or view nesting level exceeded (limit 32)". Thanks Naina
