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.

Refresh All Views on SQL Server

Whenever you make a change to a table - say, to modify a column - you need to update all the views that depend on that column.  This script will find and update all your views. 




-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(
    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''',0
    FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    WHERE so.type = 'V'
    and is_schema_bound_reference = 0
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp




Of course if a view is now no longer correct, you'll see on your messages tab in SQL Server Management Studio.




...

---

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.

Selecting scalar values from a stored procedure

The easy and obvious answer is to use a function, not a procedure.   Then you can use it like any built-in T-SQL function.




select 1, dbo.Function()

But alas, functions do not allow us to store (insert, update, delete) any data to the database and my stored procedure needs to do exactly that. 



In a moment of brilliant engineering, SQL Server does not allow the return value of a stored procedure to appear as a column in a query (select, insert, update, where clause),  so you can't just say...




select orderID, dbo.StoredProcedure() from...

In my task, I had to insert the results of a stored procedure into a column in a table.  The code example I show below creates a temporary lookup table, and uses it later for a list of inserts.  try to follow this...




--creates a temp table for later use in joining (not shown)
declare @PickListNo table(OrderID varchar(20), PickListNo int, done bit);

--loads the temp table, except the column from the stored procedure
insert into @PickListNo (OrderID, done)
(
    Select distinct AOO.[order number], 0 from uploads.open_orders AOO
)

--vars the stored procedure needs
declare @A int
declare @NextVal int   

--while there are unprocessed rows...
while exists (select 1 from @PickListNo where PickListNo is null)
BEGIN
    --execute the procedure and capture the return value
    exec @A=dbo.NextPickListNo @NextVal OUTPUT
   
    --update one row in the table
    --I used Max(ID) to find a single row, but I might have used MIN, or Select top 1 as well.
    update @PickListNo set PicklistNo = @A where OrderID=(select MAX(orderID) from @PickListNo where PickListNo is null)
END

This use of the while loop is my way of cheating and not using cursors.  Cursors are nicely powerful but demand a lot of babysitting and resources (so I am told).



...

---

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.

Passing a List as a SQL Parameter

Take a simple query something like this:

select * from Orders
where OrderID in (1724, 1722, 1710)
order by OrderID desc

In the course of using this query, we might try input it's values as parameters. 

select * from Orders
where OrderID in (@ord1, @ord2, @ord3)
order by OrderID desc

But we don't always know how  many of them there are.  For this case we need what is called a table valued parameter - or a list parameter.









...

---

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: Resync Users to Logins after Restore

...

---

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.

Auto Close and why you want it OFF

So I was perusing my SQL Server Error Logs on my web server and found all my databases restarting. 



All night. 



Every 2 seconds.  



It turns out that the reason for this is that the database is set to automatically shut down whenever there are no more connections.  In a web environment, that means every single time there is a page hit.



We need to turn this off.  Now.



Open SQL Server Management Studio and log in.

From the Object Explorer select

• Server




• Databases




• DatabaseName → Properties

Make sure Auto Close is set to False.





...

---

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: Friendly Time Differences

Ok, so I always like the Facebook smart date displays, like

posted by Bob Loblaw about 12 minutes ago

So I set about recreating this on my own sites in SQL Server.  Here is a fairly easy way to produce this kind of friendly time differences.




Select *
,iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days  ',
''
)+
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) % 24 as varchar(90))+' hours  ',
''
)+
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) % 60 as varchar(90))+' minutes  ',
''
)+
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) % 60 as varchar(90))+' seconds ago.',
''
)
[friendlytime]
from errorlog
--where errdate > DATEADD(HOUR, -1, GETDATE())
order by errdate




This produces a nice, friendly time difference that is better than a list of raw dates for human interpretation.

errdate	page	errmessage	friendlytime
1961-06-17 11:07:21.820	TEST	THIS IS A TEST ERROR MESSAGE	18993 days  0 hours  20 minutes  36 seconds ago.
2010-06-17 11:06:05.470	TEST	THIS IS A TEST ERROR MESSAGE	1096 days  0 hours  21 minutes  52 seconds ago.
2012-06-17 11:04:41.123	TEST	THIS IS A TEST ERRORMESSAGE	365 days  0 hours  23 minutes  16 seconds ago.
2013-06-17 11:07:59.197	TEST	THIS IS A TEST ERROR MESSAGE	20 minutes  58 seconds ago.

Alternately, if you don't care to display all the way down to seconds when the time span is many days, you can nest it like this to only show the most relevant time gap.




iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days ago',
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) as varchar(90))+' hours ago',
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) as varchar(90))+' minutes ago',
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) as varchar(90))+' seconds ago.',
'now'
)
)
)
) [friendlytime2]


This produces a result as follows:

18993 days ago

1096 days ago

365 days ago

38 minutes ago

...

---

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.