This document describes the process (and SQLs) used to merge one or more Control products from one database to another.
WARNING: This article is for reference. The changes made will PERMANENTLY delete and/or affect your data. This is the exact procedure in almost NO cases and should only be carried out by a skilled implementer and SQL practitioner.
==== Concept ==== The idea of this approach is to use SQL queries to renumber and insert the relevant data from one table to another. The basic approach for each table will be: - Create a map table (containing OLDID, NEWID) for each table being moved - Extract the data to be copied into Temp Tables - ReIndex the database fields - ReIndex the XML fields - Insert the temp tables If there are any conflicts, the existing data will always take precedence. ==== Guidelines ==== Items not imported: * Inventory → All parts are changed to not Accrued and not track Inventory. * InventoryLog * QuickProducts * ProductTaxExemptLink * PricingGraphic * System Records (unless the ID doesn't exist in the destination database) * Modifiers not used **Other Notes:** * Only fields found in **both** the source and destination tables are copied. ==== Tables Addressed ==== The following Database Tables are involved in pricing in Control. This grid outlines which are converted into the new system: |~ Table Name||~ Converted|| |* [[:sql_structure_-_catalogitem_table|]]||No|| |* [[:sql_structure_-_customergoodsitem_table|]]||Yes|| |* [[:sql_structure_-_customrange_table|]]||No|| |* [[:sql_structure_-_discounttable_table|]]||Yes|| |* [[:sql_structure_-_discounttableitem_table|]]||Yes|| |* [[:sql_structure_-_element_table|]]||YesSelect rows from the Element Table that deal with pricing are brought over.|| |* [[:sql_structure_-_glaccount_table|]]||No|| |* [[:sql_structure_-_goodsitempartlink_table|]]||Yes|| |* [[:sql_structure_-_inventory_table|]]||??|| |* [[:sql_structure_-_inventorylog_table|]]||??|| |* [[:sql_structure_-_iotemplate_table|]]||Yes|| |* [[:sql_structure_-_marketinglistitem_table|]]||Yes|| |* [[:sql_structure_-_part_table|]]||Yes|| |* [[:sql_structure_-_partinfotemplate_table|]]||Yes|| |* [[:sql_structure_-_partinventoryconversion_table|]]||Yes|| |* [[:sql_structure_-_partuserfield_table|]]||Yes Only UDF values for columns already defined will be imported.|| |* [[:sql_structure_-_pricingelement_table|]]||Yes|| |* [[:sql_structure_-_pricinggraphic_table|]]||Yes|| |* [[:sql_structure_-_pricinglevel_table|]]||Yes|| |* [[:sql_structure_-_pricinglink_table|]]||Yes|| |* [[:sql_structure_-_pricingplan_table|]]||Yes|| |* [[:sql_structure_-_pricingtable_table|]]||Yes|| |* [[:sql_structure_-_prodmodlink_table|]]||Yes|| |* [[:sql_structure_-_producttaxabilitycode_table|]]||Yes|| |* [[:sql_structure_-_producttaxexemptlink_table|SQL Structure - ProductTaxExemptLink Table]]||Yes|| |* [[:sql_structure_-_productuserfield_table|]]||Yes|| |* [[:sql_structure_-_quickproduct_table|]]||No|| |* [[:sql_structure_-_selectionlist_table|]]||YesBecause they are stored in an XML record structure, Selection List links to parts and dependent selection lists are not converted.|| |* [[:sql_structure_-_selectionlistitem_table|]]||YesBecause they are stored in an XML record structure, Selection List links to parts and dependent selection lists are not converted.|| |* [[:sql_structure_-_statetaxexemptionlink_table|]]||No|| |* [[:sql_structure_-_station_table|]]||No|| |* [[:sql_structure_-_storeconstant_table|]]||Yes|| |* [[:sql_structure_-_taxclass_table|]]||No|| |* [[:sql_structure_-_taxlink_table|]]||No|| |* [[:sql_structure_-_userfielddef_table|]]||Yes|| |* [[:sql_structure_-_userfieldlayout_table|]]||Yes|| |* [[:sql_structure_-_username_table|]]||Yes|| |* [[:sql_structure_-_variable_table|]]||Yes|| SQL ---- ''– Section: Declare Variables and Create Databases – – This section is used to – * Declare all variables the user needs to set – * Create the working database (ConvTemp) – * Restore the source database (if that option is set) – * Restore the destination database (if that option is set) – * Writes all user variables to a database table for other sections to use '' ---- ''– Create the Temporary Database used to hold all Working Data – USE master; '' ''IF EXISTS(SELECT * FROM sys.DATABASES WHERE name 'ConvTemp') begin '' ''ALTER DATABASE ConvTemp SET single_user WITH rollback immediate; -- this will force all others users off! DROP DATABASE ConvTemp; '' '' '' ''end; CREATE DATABASE ConvTemp; go '' ''DECLARE @SourceDatabase varchar(255); DECLARE @DestDatabase varchar(255); DECLARE @RestoreDestDatabase bit; DECLARE @RestoreDestBackupFile varchar(255); DECLARE @RestoreDestFilePath varchar(255); DECLARE @RestoreSourceDatabase bit; DECLARE @RestoreSourceBackupFile varchar(255); DECLARE @RestoreSourceFilePath varchar(255); DECLARE @RenameExistingProducts bit; DECLARE @RenameExistingParts bit; DECLARE @RenameExistingModifiers bit; DECLARE @RenameExistingSelectionLists bit; DECLARE @RenamePrefix varchar(255); DECLARE @RenameSuffix varchar(255); DECLARE @DeleteDestPricingForms bit; DECLARE @DeleteDestUDFLayouts bit; DECLARE @S varchar(max); '' ''SET @SourceDatabase 'Scott_Latest_OOBData'; SET @DestDatabase 'Scott_Hansen_Updated'; '' ''SET @DeleteDestPricingForms 0; – Set this to 1 to delete ALL pricing forms in the destination before importing SET @DeleteDestUDFLayouts 0; – Set this to 1 to delete ALL UDF Layouts in the destination before importing SET @RenameExistingProducts 0; – Set this to 1 to rename ALL products in the destination with the prefix and suffix before importing SET @RenameExistingParts 0; – Set this to 1 to rename ALL parts in the destination with the prefix and suffix before importing SET @RenameExistingModifiers 0; – Set this to 1 to rename ALL modifiers in the destination with the prefix and suffix before importing SET @RenameExistingSelectionLists 0; – Set this to 1 to rename ALL selection lists in the destination with the prefix and suffix before importing SET @RenamePrefix 'Old_'; SET @RenameSuffix ''; SET @RestoreDestDatabase 0; – Set this to 1 to delete the destination database and restore from backup SET @RestoreDestBackupFile 'D:\Data\SQL Data\Dev\Scott\HansenSigns11-13-2009backup.bak'; SET @RestoreDestFilePath 'D:\Data\SQL Data\Dev\Scott\'; – include trailing backslash SET @RestoreSourceDatabase 0; – Set this to 1 to delete the source database and restore from backup SET @RestoreSourceBackupFile 'D:\DATA\SQL DATA\Dev\Scott\New OOB Dataset 2009-10-09.bak'; SET @RestoreSourceFilePath 'D:\DATA\SQL DATA\Dev\Scott\'; – include trailing backslash use ConvTemp; if exists(select * from ConvTemp.sys.objects where name 'TestLog') drop table ConvTemp.dbo.TestLog; create table ConvTemp.dbo.TestLog (id int primary key identity, token varchar(50) not null, value varchar(max)); create index TestLog_TokenIndex on ConvTemp.dbo.TestLog (token); – Create Helper Functions to Store and Retrieve Data if exists(select * from sys.objects where name 'LogData') Drop procedure LogData; set @S 'CREATE PROCEDURE LogData @Token varchar(50), @Value varchar(max) '+ 'AS '+ 'BEGIN '+ 'SET NOCOUNT ON; '+ 'DELETE FROM TestLog '+ 'WHERE token @Token; '+ 'INSERT INTO TestLog (token, value) '+ 'SELECT @Token, @Value '+ 'END '; execute (@S); if exists(select * from sys.objects where name 'GetData') Drop Function GetData; set @S 'CREATE FUNCTION GetData (@Token varchar(50)) '+ 'RETURNS varchar(max) AS '+ 'BEGIN '+ 'RETURN( SELECT Value FROM TestLog WHERE token @Token ) '+ 'END ' execute (@S); exec LogData 'SourceDatabase', @SourceDatabase; exec LogData 'DestDatabase', @DestDatabase; exec LogData 'TempDatabase', 'ConvTemp'; SET @S GetDate(); exec LogData 'Imported Started', @S; exec LogData 'DeleteDestPricingForms' , @DeleteDestPricingForms; exec LogData 'DeleteDestUDFLayouts' , @DeleteDestUDFLayouts; exec LogData 'RenameExistingProducts' , @RenameExistingProducts; exec LogData 'RenameExistingParts' , @RenameExistingParts ; exec LogData 'RenameExistingModifiers', @RenameExistingModifiers; exec LogData 'RenameExistingSelectionLists', @RenameExistingSelectionLists; exec LogData 'RenamePrefix' , @RenamePrefix; exec LogData 'RenameSuffix' , @RenameSuffix; – Restore the Destination Database (used for testing) – IF (@RestoreDestDatabase1) BEGIN SET @S 'IF EXISTS(SELECT * FROM sys.DATABASES WHERE name '''+@DestDatabase + ''') ' + 'begin '+ 'ALTER DATABASE '+@DestDatabase+' SET single_user WITH rollback immediate; '+ 'DROP DATABASE '+@DestDatabase + '; ' + 'end; '+ 'restore DATABASE '+@DestDatabase + ' ' + 'FROM DISK '''+@RestoreDestBackupFile + ''' ' + 'WITH '+ 'Recovery, '+ 'Move ''EmptyDataSet_Data'' TO '''+@RestoreDestFilePath+'StoreDataDest.MDF'', ' + 'Move ''EmptyDataSet_Log'' TO '''+@RestoreDestFilePath+'StoreDataDest.LDF'' '; EXECUTE (@S); END – Restore the Source Database (used for testing) – IF (@RestoreSourceDatabase1) BEGIN SET @S 'IF EXISTS(SELECT * FROM sys.DATABASES WHERE name '''+@SourceDatabase + ''') ' + 'begin '+ 'ALTER DATABASE '+@SourceDatabase+' SET single_user WITH rollback immediate; '+ 'DROP DATABASE '+@SourceDatabase + '; ' + 'end; '+ 'restore DATABASE '+@SourceDatabase + ' ' + 'FROM DISK '''+@RestoreSourceBackupFile + ''' ' + 'WITH '+ 'Recovery, '+ 'Move ''EmptyDataSet_Data'' TO '''+@RestoreSourceFilePath+'StoreDataSource.MDF'', ' + 'Move ''EmptyDataSet_Log'' TO '''+@RestoreSourceFilePath+'StoreDataSource.LDF'' '; EXECUTE (@S); END ————————————————————————————– GO ————————————————————————————– – Section: Create_IDMap Stored Procedure – – This section defines a stored procedure that creates an IDMap table in the – in the temporary database. The IDMap table: – * Needs to be called for each table you want to create. – * Is names IDMap_xxxxxx where xxxxxx is the table name. – * Matches based on a text expression passed in – * Generates new IDs for records not found ————————————————————————————– CREATE PROCEDURE Create_IDMap @TableName varchar(50), @TextExpressionCompared varchar(max), @ClassTypeID int -1, @SourceDatabasePath varchar(255), @TargetDatabasePath varchar(255), @CreateNewIDs bit AS BEGIN DECLARE @DEBUG bit; DECLARE @MapTableName varchar(100); DECLARE @TempExpr varchar(max); DECLARE @ClassTypeIDStr varchar(10); DECLARE @S varchar(max); SET @DEBUG 0; Execute( 'USE '+@SourceDatabasePath ); SET @SourceDatabasePath @SourceDatabasePath+'.dbo.'; SET @TargetDatabasePath @TargetDatabasePath+'.dbo.'; IF @ClassTypeID -1 SET @MapTableName 'IDMap_'+@TableName ELSE SET @MapTableName 'IDMap_'+@TableName+'_'+cast(@ClassTypeID as varchar(10)); SET @ClassTypeIDStr CAST(@ClassTypeID as VarChar(10)); – SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. IF (@DEBUG 1) SET NOCOUNT ON; IF OBJECT_ID(@MapTableName) IS NOT NULL Execute('DROP TABLE '+@MapTableName); – Create a mapping table SET @S 'USE ConvTemp; '+ 'CREATE TABLE '+@MapTableName+' '+ '( '+ ' TextValue varchar(255) NULL, '+ ' SourceID int NOT NULL, '+ ' TargetID int NULL, '+ ' IsNew bit NOT NULL, '+ ' IsChanged bit NOT NULL, '+ ' RowIndex int IDENTITY(1,1) NOT NULL '+ ') '+ '; '+ 'CREATE INDEX '+@MapTableName+'_SourceIDIndex ON '+@MapTableName+' (SourceID ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_TargetIDIndex ON '+@MapTableName+' (TargetID ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_TextValueIndex ON '+@MapTableName+' (TextValue ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_RowIndex ON '+@MapTableName+' (RowIndex ASC) '+ '; ' IF (@DEBUG 1) Print @S; Execute(@S); – In order to support combined fields, allow an expression in the TextExpressionCompared – To handle this, we have to accomodate the "Table." prefix anywhere in the expression. – If the just pass in a field, then we need to pre-prend the "Table." prefix. IF (PATINDEX('%TABLE.%', @TextExpressionCompared) 0) SET @TextExpressionCompared 'TABLE.'+@TextExpressionCompared; – Insert the Source values into the new Table SET @TempExpr REPLACE( REPLACE( @TextExpressionCompared, 'TABLE.', 'SRC.' ), 'DATABASE.', '' +@SourceDatabasePath+''); SET @S 'INSERT INTO '+@MapTableName+' '+ 'SELECT DISTINCT ('+@TempExpr+'), ID, NULL, 0, 0 '+ 'FROM '+@SourceDatabasePath+@TableName+' AS SRC '+ 'WHERE ID > 0 ' IF (@ClassTypeID -1) SET @S @S + ' AND (ClassTypeID '+ @ClassTypeIDStr +')'; IF (@DEBUG 1) Print @S; Execute(@S); – In Case of Duplicates, Delete all but the highest SET @S 'DELETE FROM '+@MapTableName+' '+ 'WHERE RowIndex NOT IN (SELECT Min(RowIndex) FROM '+@MapTableName+' GROUP BY SourceID)'; IF (@DEBUG 1) Print @S; Execute(@S); – Do not Renumber System IDs regardless of the name. SET @S ' UPDATE '+@MapTableName+' SET TargetID SourceID, IsNew (CASE WHEN SourceID IN (SELECT ID FROM '+@TargetDatabasePath+@TableName+' ) THEN 0 ELSE 1 END) WHERE SourceID < 1000 ;' IF (@DEBUG 1) Print @S; Execute(@S); – Update the Map with the ID of the Destination Table SET @TempExpr REPLACE( REPLACE( @TextExpressionCompared, 'TABLE.', 'DestTable.' ), 'DATABASE.', '' +@TargetDatabasePath+''); SET @S 'UPDATE MapTable '+ 'SET TargetID DestTable.ID '+ 'FROM '+@MapTableName+' MapTable '+ ' LEFT JOIN '+@TargetDatabasePath+@TableName+' DestTable '+ ' ON MapTable.TextValue ('+@TempExpr+') '+ 'WHERE TargetID IS NULL '; IF (@DEBUG 1) Print @S; Execute(@S); – Presumable, if there is no map field then the item does not exist. – In this case, give it a unique ID if desired IF (@CreateNewIDs1) BEGIN SET @S 'UPDATE '+@MapTableName+' '+ 'SET TargetID RowIndex + Coalesce((SELECT max(ID) FROM '+@TargetDatabasePath+@TableName+'), 1000), '+ ' IsNew 1 '+ 'WHERE TargetID IS NULL ' IF (@DEBUG 1) Print @S; Execute(@S); END; _FCKG_BLANK_TD_ SET @S 'UPDATE '+@MapTableName+' '+ 'SET IsChanged 1 '+ 'WHERE (IsNew 1) OR (TargetID SourceID) '; IF (@DEBUG 1) Print @S; Execute(@S); _FCKG_BLANK_TD_ END GO – Section: Remap_Field Stored Procedure – – This section defines a stored procedure that remaps the values in a table based – on the values stored in the IDMap table. – – Other Notes: – * An optional WhereClause can be specified for the table to control the replacement CREATE PROCEDURE Remap_Field @TableName varchar(50), -- 'CustomerGoodsItem' @FieldName varchar(255), -- 'CategoryID' @MapTable varchar(50), -- 'PricingElement' @WhereClause varchar(max) NULL -- 'ClassTypeID 12000' _FCKG_BLANK_TD_ AS BEGIN @@_SP_@@ - SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; @@_SP_@@ - Update the Field DECLARE @S varchar(2048); SET @S _FCKG_BLANK_TD_ ' UPDATE TheTable SET '+@FieldName+' coalesce(MapTable.TargetID, '+@FieldName+') FROM Temp_'+@TableName+' AS TheTable LEFT JOIN IDMap_'+@MapTable+' AS MapTable ON TheTable.'+@FieldName+' MapTable.SourceID WHERE TheTable.'+@FieldName+' IS NOT NULL _FCKG_BLANK_TD_ IF (@WhereClause IS NOT NULL and @WhereClause '') BEGIN SET @S @S + ' AND '+REPLACE( @WhereClause, 'TABLE.', 'TheTable.' ); _FCKG_BLANK_TD_ @@_SP_@@ - Print @S; END; Execute(@S) _FCKG_BLANK_TD_ END GO – Section: Parse_IDField Stored Procedure – – This section creates a stored procedure used to Parse and input string from a format – like (6034,1000,12000) or (6034,12000). The resulting ID and ClassTypeID are passed – back as OUTPUT parameters. – – Other Notes: – * In most cases, invalid data will result in -1,-1 being passed back. CREATE Procedure Parse_IDField @IDString varchar(50), @MapTable varchar(50), @ID int OUTPUT, @ClassTypeID int OUTPUT _FCKG_BLANK_TD_ AS BEGIN SET @ID -1; SET @ClassTypeID -1; _FCKG_BLANK_TD_ IF (@IDString IS NULL) RETURN; _FCKG_BLANK_TD_ DECLARE @S varchar(max); DECLARE @StartPos int; DECLARE @EndPos int; DECLARE @LastIDFound bit; _FCKG_BLANK_TD_ DECLARE @TempNewIDs Table(TextValue varchar(255), SourceID int, TargetID int); _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the ID Field SET @StartPos CharIndex( '(', @IDString ); SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ SET @ID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); IF @ID <0 RETURN; _FCKG_BLANK_TD_ SET @S _FCKG_BLANK_TD_ ' SELECT TextValue, SourceID, TargetID FROM IDMap_'+@MapTable+' WHERE SourceID '+Cast(@ID as VarChar(12))+' _FCKG_BLANK_TD_ INSERT INTO @TempNewIDs Exec(@S) _FCKG_BLANK_TD_ @@_SP_@@ - There should only be one record SET @ID (SELECT Top 1 TargetID from @TempNewIDs) IF (@ID IS NULL) RETURN; _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the Next ID Field SET @LastIDFound 0; SET @StartPos @EndPos; SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF @EndPos 0 BEGIN SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 ); SET @LastIDFound 1; END; _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ IF (@LastIDFound 1) BEGIN SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); RETURN; END; -- ELSE Skip it _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the Next ID Field SET @StartPos @EndPos; SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF @EndPos 0 SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); IF @EndPos 0 SET @EndPos CharIndex( ' ', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); RETURN; _FCKG_BLANK_TD_ END GO – Section: Remap_XML Stored Procedure – – This section defines a stored procedure that remaps ID Fields contained within – XML data. The technique used: – * Extracts the ID Record node (as a string) – * Uses Parse_IDField to retrieve the ID – * Looks up the new ID – * Replaces the ID Record in the XML with the new ID record – – Other Notes: – * This needs to be called individually on each XML field in a table. – * The XML node path and node text must be static (invariant) CREATE PROCEDURE Remap_XMLData @TableName varchar(50), -- 'SelectionListItem'; @FieldName varchar(255), -- 'DependentLinksXML'; @NodePath varchar(255), -- '//DependentListLink'; @NodeText varchar(255), -- 'DependentListID'; @MapTable varchar(50) -- 'SelectionList'; _FCKG_BLANK_TD_ AS BEGIN DECLARE @S varchar(max); SET NOCOUNT ON; _FCKG_BLANK_TD_ @@_SP_@@ - Create a temp table to use to iterate through and change the data CREATE TABLE #TempTable (ID int, IDRecordStr varchar(55)) ; CREATE INDEX TempTableIDIndex on #TempTable (ID); – Build a table of ID Records to be replaced SET @S ' SELECT SourceTable.ID, LinkRow.value('' (//'+@NodeText+')[1]'', ''varchar(55)'') IDRecordStr FROM ( SELECT ID, Cast('+@FieldName+' AS XML) XMLField FROM Temp_'+@TableName+' WHERE ('+@FieldName+' IS NOT NULL) AND (Len(Cast('+@FieldName+' AS VarChar(55))) > 20) ) SourceTable CROSS APPLY XMLField.nodes('''+@NodePath+''') AS LinkTable(LinkRow) WHERE LinkRow.value('' (//'+@NodeText+')[1]'', ''varchar(55)'') NOT IN ('' (-1,-1,-1)'' , '' (-1,-1)'') '; – Fill in the temp table INSERT INTO #TempTable EXECUTE (@S) DECLARE TempCursor CURSOR FOR SELECT * FROM #TempTable; DECLARE @CurrentRowID int; DECLARE @CurrentIDRecordStr varchar(55); DECLARE @ReplacementIDRecordStr varchar(55); DECLARE @CurrentID int; DECLARE @CurrentClassTypeID int; OPEN TempCursor; – Loop through each record FETCH TempCursor INTO @CurrentRowID, @CurrentIDRecordStr; WHILE @@Fetch_Status 0 BEGIN – Parse the Record String exec Parse_IDField @CurrentIDRecordStr, @MapTable, @CurrentID OUTPUT, @CurrentClassTypeID OUTPUT – Generate the Replacement String SET @ReplacementIDRecordStr '('+cast(@CurrentID as varchar(12))+','+cast(@CurrentClassTypeID as varchar(12))+')'; – Update the record SET @S ' UPDATE Temp_'+@TableName+' SET '+@FieldName+' REPLACE( cast('+@FieldName+' AS VarChar(max)), '''+@CurrentIDRecordStr+''', '''+@ReplacementIDRecordStr+''' ) '' '' WHERE ID '+cast(@CurrentRowID as varchar(12))+' '' '' '' '''; '' '' EXECUTE( @S ); '' '' '' ''FETCH TempCursor INTO @CurrentRowID, @CurrentIDRecordStr; '' '' END; '' '' '' ''DROP TABLE #TempTable; END '' ---- ''GO '' ---- ''– Function to parse a UDF field name into a valid Database Field Name CREATE FUNCTION MakeFieldNameFriendly '' '' ( @inputstr VARCHAR(255) ) '' '' '' ''RETURNS VARCHAR(255) AS BEGIN '' '' DECLARE @stripchrs varchar(15); DECLARE @charcounter INT; SET @stripchrs ' ./+()-%?'; SET @charcounter 1; WHILE @charcounter '' '' '' '''' ---- '''' '' '' '''' '''' '''' '''' '''' ---- '''' ---- '''' ---- '''' '''' '''' '''' - '''' '''' - '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' - '''' '''' '''' '''' '''' ---- '''' ---- '''' ---- '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' - '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' - '''' '''' '''' '''' '''' - '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' - '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' '''' ---- '''' ---- '''' ---- '''' '''' '''' '''' '''' '''' '''' - '''' '''' \\