Web Development Tips and Examples
SQL Server 2000 is an old database platform now, but it is still fairly widely used. That’s why it’s good to know how to pass a list of parameters to a stored procedure on the SQL Server 2000 database platform. Likewise, the code is forwards compatible and will work with more modern versions of SQL Server such as 2008.
Have a look here if you are looking for a working example of passing XML to a stored procedure using SQL Server 2008. Continue reading “SQL Server 2000: Passing a List with XML”
This error showed up on a stored procedure that I was trying to execute from an ASP.NET Web page. The strange part was that I could execute the stored procedure directly from SSMS without any problems.
The procedure was very simple in its design, but the database I was working in was new to me and I had very limited access. The procedure was set up to receive an XML parameter and then to do a bulk insert into a table from the XML. Continue reading “Correcting: SELECT failed because the following SET options have incorrect settings: ‘ARITHABORT’”
Sometimes while developing a tool, a developer needs to pass a list of values with a variable size to their database. There are several ways of doing so, one of which involves passing the list in XML format.
The introduction of SQL Server 2005 offered the use of XML datatypes, which had not existed in previous versions of SQL Server. The good thing about the XML datatype is that it is not just good for storing information in XML format, but also has advantages such as being fully query-able similar to a database table.
As you look through this example, please feel free to check out other articles I have written for more SQL Tips
A big benefit of the XML datatype is the ability to pass lists of items to stored procedures as XML format parameters. In SQL Server 2000 one would have to pass a list of parameters as a String that would then need to be parsed into its components in the Stored Procedure. This a tedious process that could easily introduce parsing errors. Subsequent versions of SQL Server have added extra ways in which to pass information lists to Stored Procedures:
As you can see from the list above, the methods of passing lists to stored procedures have increased in number over the last few iterations of SQL Server. The catch of course is that as a developer you will need to adapt your code to whichever instance of SQL Server your project is based on.
Justin Cooney 