Column Modification Checklist

This is one of those things that I always forget part of, so because I just went through this, I thought I would document what needs to be done to sync your application whenever you modify any column in your database.



This tutorial is designed for:

• MS SQL Server


• Visual Studio (for web or desktop)

Quick Checklist:

1. In SQL Server Management Studio (SSMS)




1. Check for source and destination columns (for instance if widening "Address1" from 40 to 50, make sure all the columns in the order table, address book, Shipping and Billing etc are all the same)


2. Update All Views that depend on this column. (SQL Server does not do this automatically)


3. Update all stored procedures that operate on this column (for instance in and out parameters that access the changed column)




2. In your Desktop App:




1. Check all dataset xsd files to ensure the result column maxlengths are updated.


2. Check all dataset xsd files to ensure the query parameter maxlengths are updated.


3. Ensure all databound textboxes are set to the correct MaxLength.


4. Ensure all DataGridView Columns are set to the correct MaxInputLength.

Updating Views:



There is actually a stored procedure for updating views.  Once you have found a dependent view, just run...

EXECUTE sp_refreshview 'dbo.v_myViewName';

That will take care of it.  Of course if your view is no longer valid because of the change, you'll get an appropriately misleading error message from Microsoft.



Updating Stored Procs:



Your stored procs have a header much like this:




ALTER PROCEDURE [dbo].[StoredProcName]
    -- Add the parameters for the stored procedure here
    @customer varchar(10),
    @PurchaseOrder varchar(15),
    @Address1 varchar(40)
AS

These header parameters and any internally declared variables must be changed to match any column changes.







...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Temp Tables in SQL Server

We all know you can create variables in SQL Server...


declare @customer varchar(20)

set @customer='a customer'



...but what if there is a need to store more complex data?

As it turns out, there is an easy way to accomplish that as well.




declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)


--get the counts of customer service reps orders for each customer.

insert into @csrlist (customer, csr, counts)
(
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr
) 

The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.


--find the CSR with the most orders for each customer

insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))




---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Cannot execute as the database principal.

Cannot execute as the database principal because the principal "username" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I am going to read your mind now.

1. You recently backed up your database copy-only and moved it to another server or development box. 


2. You're attempting to perform an  "Execute As..." command.


3. Your software has been running for some time and this new error just started cropping up after you "refreshed" your copy of the database (from production?).


4. You looked at the server logins, and the database users, and they seem to match (there is a login with the same name as the user).

What happened is that the SIDs (Security IDs) from the server login does not match the database user of the same name.  Remember that LOGINS are stored at the server level and USERS are in the databases.



What you need to do is re-create the user in the database (and reassign any roles and permissions).

USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myUser')

DROP USER [myUser]

GO



USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

GO



CREATE USER [myUser] FOR LOGIN [myUser] WITH DEFAULT_SCHEMA=[dbo]

GO

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server Matching on NULL parameter

So I have this query where I am trying to select on customer, unless the user doesn't enter a parameter for customer.  When they leave it blank, we want to see all customers.



So normally I would do it like this:




Select * from Orders where Customer=@customer
And then to handle the null parameter I would change it like this:




Select * from Orders where ((Customer = @customer) OR (@customer is null))
 This works great but then I came across this way of making it simpler.




Select * from Orders where Customer = isnull(@customer, Customer)
The isnull()  effectively handles the case where the parameter (@customer) is null by replacing it with the content of the [Customer] data column, matching to itself!  Problem solved!





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.