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
Here are the three main ways of passing lists of parameters to the different versions of SQL Server
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 a character-separated list contained in a String (VARCHAR) variable - SQL Server 2000 +
- As an XML variable – SQL Server 2005 +
- As a table variable – SQL Server 2008 +
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.
ASP.NET Error – Adding the specified count to the semaphore would cause it to exceed its maximum count
If you are working with ASP.NET in Visual Studio, then you may be mystified when you see an error code : Adding the specified count to the semaphore would cause it to exceed its maximum count.
This error had me wondering what exactly is going on… (more…)
I’m revisiting some classic ASP functionality, and in this case I’m reviewing how to set up a simple database connection to a SQL Server database. There are of course several ways to get this to work, and in this case we will use the SQLOLEDB provider.
Classic ASP is Still Used and Maintained
There are many applications built 10+ years ago, or still being built using the classic ASP framework. In my opinion this was a solid and very usable framework. ASP has been supplanted long ago by the ASP.NET framework which qualifies as a full programming language with a vast library of functionality to call on. However ASP is still used and does provide a simple and efficient way to get the job done.
I have been using Visual Studio’s Declarative WebForms programming model lately to quickly create Web-based form interfaces. As part of this process I have been making extensive use of control binding using the Eval() and Bind() statements, and in some cases even the old ASP Response.Write syntax. As part of my review, I will use this article to to go over the pros and cons of using either option.
As a general rule of thumb, both Eval() and Bind() work similarly for read operations but Bind() offers more functionality when binding controls to a data source for the full spectrum of Read/Write operations.
Running an ad-hoc query from SQL Server SSMS to an Excel spreadsheet is amazingly straightforward using the OpenRowset command.
Here’s how it’s done…
For starters, copy the Excel spreadsheet to a location where the installed version of SQL Server can access it. This is necessary since your query in SSMS (SQL Server Management Studio) will not execute from your local machine. The query will be passed to SQL Server, which will then attempt to locate the spreadsheet as an OLE DB data source in order to run your query on it.
So make sure that the Excel spreadsheet is located either on the computer SQL Server is installed on, or on a network share which the SQL Server account has access to.
Step two is to open SSMS, connect to SQL Server, and open a query window in SSMS where you can run your query from.