January 23, 2009 at 10:19 am
OK, so after spending too much time on reviewing everything and trying to teach myself SQL, I'm wondering if this code would be able to handle larger .xml files? It was originally used for .xml code that was only about 40 lines long, but the new XML docs I'm using are upwards of 150 lines of code...
Either way, if someone could look this over for me and maybe help me work it so that it functions in a more general manner it would be appreciated. 🙂
Also, I recently have been receiving the following error message, but I can't figure it out either, it says that the syntax on line one is wrong, but from what I can tell, the syntax is fine. :unsure:
-----------------------------------------------------------
Msg 170, Level 15, State 1, Server VBSA, Line 1
Line 1: Incorrect syntax near 'ï'.
-----------------------------------------------------------
If someone could explain this that would be great. Thanks in advance. :hehe:
--------------- Code Below: For privacy reasons, the "PROG" replaces the name of the program I am changing the settings for ---------------------------------------------
USE PROG;
GO
SET NOCOUNT ON;
--print header information
PRINT '======================================================================================';
PRINT 'SETTING SYSTEM OPTIONS ' + CAST(GETDATE() AS VARCHAR);
PRINT '--------------------------------------------------------------------------------------';
---------------------------------------------------------------------------------------------------
-- Sets the System Options
---------------------------------------------------------------------------------------------------
DECLARE @numOptionsINT,
@idocINT,
@Success INT,
@optionContentsVARCHAR(8000),
@currentOptionVARCHAR(8000),
@currentValueVARCHAR(255),
@valueID VARCHAR(255),
@optionID INT,
@optionTypeIDINT,
@lookup BIT,
@ExecCmd VARCHAR(255);
--Set Variables
EXEC Read_File @FileName = '"\Default ROG Settings\Default_Settings\Default_Settings1.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 PROG_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 PROG_Option WHERE Description = @currentOption;
--check if option is a lookup value
SELECT @lookup = IsLookup FROM PROG_Option WHERE Description = @currentOption;
IF @lookup = 1
BEGIN
--get valueID
SELECT @valueID = ValueID FROM PROG_OptionValue WHERE
Description = @currentValue and OptionID = @optionID;
END
ELSE
BEGIN
SET @valueID = @currentValue;--use current value
END
--update the system option
EXEC PROG_UpdateOptionSetting @OptionID = @optionID,
@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;
----------------------------------------------------------------------------------------------------------
January 23, 2009 at 11:04 am
yep you are ring....your getting an issue because ov the varchar(8000)
150 lines X ~80 chars per line or so, and you pass the limit for the definition.
this proc, on SQL2000, is limited to tiny xml docs.
if you can upgrade to SQL2005, change all your variables to varchar(max) , and i think it'll work for you.
Lowell
January 23, 2009 at 11:16 am
OK, thanks. I'll see if it's possible to upgrade, but if not? Should I just chop up the XMLs and run this multiple times? Or is there an easier way to get this done?
January 23, 2009 at 11:23 am
the thing is, the XML gets chopped right in the middle....you can't loop thru it, because it's not like each 8000 chars are well-formatted XML. it'd truncate something;
without upgrading, i think you'd have to do this outside of SQL, and do it in vb6/vb.NET/C#.NET or some other programming language you might be familiar with.
Lowell
January 23, 2009 at 11:30 am
Alrighty then. Thanks for your help, I guess I'll get working on maybe some kind of application that I can run the whole process through...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply