January 20, 2009 at 2:07 pm
USE Program;
GO
SET NOCOUNT ON;
--print header information
PRINT '======================================================================================';
PRINT 'SETTING SYSTEM OPTIONS ' + CAST(GETDATE() AS VARCHAR);
PRINT '--------------------------------------------------------------------------------------';
---------------------------------------------------------------------------------------------------
-- Sets the System Options
---------------------------------------------------------------------------------------------------
DECLARE @numOptions INT,
@idocINT,
@SuccessINT,
@optionContentsVARCHAR(8000),
@currentOptionVARCHAR(8000),
@currentValueVARCHAR(8000),
@valueIDVARCHAR(8000),
@optionIDINT,
@optionTypeIDINT,
@lookupBIT,
@ExecCmd VARCHAR(8000);
--Set Variables
EXECRead_File @FileName = '"\Default Program Settings\Editable Settings\Default_Editable_Settings.xml"',
@FileContents = @optionContents OUTPUT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @optionContents;
SELECT @numOptions = count(*) FROM OPENXML(@idoc, '/Root') WHERE localname = 'SystemOption';
Select @optionTypeID = MessageID FROM Program_OptionType WHERE Description = 'System';
WHILE @numOptions > 0
BEGIN
--current Settings
SELECT @currentOption = optionName FROM OPENXML(@idoc, '/Root/SystemOption/Name', 1) WITH
(id INT '../@id',
optionName varchar(255) 'text()')
where id = @numOptions;
SELECT @currentValue = Value from OPENXML(@idoc, '/Root/SystemOption/Value', 1) WITH
(id INT '../@id',
Value varchar(255) 'text()')
where id = @numOptions;
SELECT @optionID = MessageID FROM Program_Option WHERE Description = @currentOption;
--check if option is a lookup value
SELECT @lookup = IsLookup FROM Program_Option WHERE Description = @currentOption;
IF @lookup = 1
BEGIN
--get valueID
SELECT @valueID = ValueID FROM Program_OptionValue WHERE
Description = @currentValue and OptionID = @optionID;
END
ELSE
BEGIN
SET @valueID = @currentValue;--use current value
END
--update the system option
EXEC Program_UpdateOptionSetting @OptionID = @optionID,u5
@optionTypeID = @optionTypeID,
@ObjectID = null,
@Value = @valueID,
@LocaleID = (31001),
@CallingUserID = null,
@CallingWorkstationName = null,
@Success = null;
PRINT @currentOption + ' was changed to ' + @currentValue;
SET @numOptions = @numOptions - 1;
END
EXEC sp_xml_removedocument @idoc;
Whenever I try to run the above code, I get the following error message:
Msg 170, Level 15, State 1, Server VBSA, Line 65
Line 65: Incorrect syntax near '@ObjectID'.
I didn't write this code, I just modified it, but I'm not experienced enough to really solve all the problems with it. If anyone could help me clean it up I would really appreciate it.
January 20, 2009 at 2:18 pm
mlang (1/20/2009)
EXEC Program_UpdateOptionSetting @OptionID = @optionID,u5@optionTypeID = @optionTypeID,
@ObjectID = null,
@Value = @valueID,
@LocaleID = (31001),
@CallingUserID = null,
@CallingWorkstationName = null,
@Success = null;
I don't think that u5 is supposed to be there.
If you double click the error message in Query analyser, it should take you to the line with the error. Or, since you know the error's on line 65, you can just go to line 65 and see what's there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2009 at 2:23 pm
Then this popped up:
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 6603, Level 16, State 1, Server VBSA, Procedure sp_xml_preparedocument, Line 28
XML parsing error: Invalid at the top level of the document.
And I reset all the varchar values to 8000 (some were at 255) but it didn't change anything when I did that, either.
January 20, 2009 at 2:29 pm
Oh, and the value passed to @LocaleID shouldn't have brackets around it. A couple lines below the other one.
The new error is saying the XML is invalid. You'll have to check what the xml looks like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2009 at 2:33 pm
thanks, i'll take a look at it.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply