hi
hace this sql script
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
-- Declare varibles
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)
SELECT @WorkingNamingTable = ?
SELECT @WorkingDataloadFile3 = ?
--Make sure the temp table has been dropped
--Only for use when testing in SQL mgmt studio
--BEGIN
-- DROP TABLE #StringReplace
--END
SET @Quote = '"'
-- Create temp table
SELECT @SQLStmt = '
Select PK_ID
INTO #StringReplace
FROM @WorkingNamingTable
-- Get the max ID in the table
-- table is new each time so do not need to worry about
-- ID not starting at 1
Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1
-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN
-- Preform replacement (note we are replacing in the whole field not just the end)
SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = StringReplacement from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
WHERE ' + @FieldName + ' IS NOT NULL
UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = REPLACE(' + @FieldName + ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
FROM ' + @WorkingDataloadFile3 + '
WHERE ' + @FieldName + ' IS NOT NULL
Select @pk = @pk + 1
END
SET @pk = 1
-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN
UPDATE '+ @WorkingDataloadFile3 +'
SET ' + @FieldName + ' = LTRIM(RTRIM(' + @FieldName + ')) FROM @WorkingDataloadFile3
WHERE ' + @FieldName + ' IS NOT NULL
Select @pk = @pk + 1
END'
EXECUTE(@SQLStmt)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
am getting the following error
[Execute SQL Task] Error: Executing the query "-- Declare varibles
DECLARE
@FieldName NVARCHAR(5..." failed with the following error: "[Execute SQL Task] Error: Executing the query "SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
-- D..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
anyone any ideas why