SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 no value given error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Posting Yak Master

237 Posts

Posted - 11/04/2013 :  07:29:49  Show Profile  Reply with Quote
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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/04/2013 :  07:32:46  Show Profile  Reply with Quote
sounds like you didnt map parameters for all the parameters in parameter mapping tab. ideally there should be two parameters defined with parametername as 0 and 1 corresponding to two ? placeholders used.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000