Archive for the 'SQL Server' Category

How to Query SQL Server Role Members

March 17th, 2010

You can use SQL Server Database Roles to control access to various parts of your application. Let’s say I have created a .NET application. I have a menu called Administration and I only want certain users to be able to see the menu. One way to do this is to use Active [...]

Ordinal Numbers in T-SQL

February 27th, 2009

I wanted to write a query that took numbers and turned them into ordinal numbers – re: 1 = 1st, 2 = 2nd, 3 = 3rd, etc.
I took this code from Chip Pearson’s web site. His posting was about ordinal numbers in Excel. I converted the code to a T-SQL function, and I can now [...]

Using “Execute As” to Test a Stored Procedure as a Different User

February 19th, 2009

In an earlier blog post, I wrote about how to use “Execute As” in a stored procedure to access a separate database. When you include “Execute As” in a stored procedure, you can pull data from a separate database without giving your users direct permissions on that database.
Another way I really like to use “Execute [...]

Simple way to renumber records in SQL Server

February 17th, 2009

Recently I needed to renumber a column in a database.  Specifically, I needed to be able to take a Report Number series that started with 1 (1, 2, 3, 4) and change the start number and renumber all subsequent Report Numbers (e.g, change above to 14, 15, 16, 17).
I thought I’d probably be able to [...]

How to connect to a SQL Server Named Instance

August 21st, 2008

This is something I just don’t remember, so I thought this would be a good place to post it. If you are trying to connect to a named instance of SQL Server, for example TECHKNOWSOLVE\TECHKNOWSOLVE2000, you might get an error in your code that says this server doesn’t exist. The quick and easy [...]

Using “Execute As” in a Stored Procedure To Access A Separate Database

April 2nd, 2008

Here is a scenario that I often run across. A client wants me to create an add-on to a pre-existing application that runs on SQL Server. I don’t want to touch the existing application and database (let’s call this database PROPIETARY) so I create a separate database (we’ll call it TECHKNOWSOLVE) that queries [...]

Pass multiple strings to stored procedure IN statement using XML

November 5th, 2007

(Revised 3.18.08)

Let’s say you have a listbox with multiple items selected, like this:And let’s say that you want to take the selected items and send them to a stored procedure to run in an IN statement in the WHERE clause, like this: SELECT * FROM tblProjects WHERE ProjectCity IN (‘Lewiston’, ‘Portland’, ‘Spokane’)
Turns out it’s not [...]

How to set up database mail in SQL Server 2005

June 29th, 2007

One of the great improvements in SQL Server 2005 is that it no longer requires an Exchange Server in order to send email. One of the most basic uses of email in SQL Server is to notify someone if a job fails – a backup, for example.
Here is a link to a most excellent article [...]