SSMS: Beware of Modify

When you want to change the defintion of a sql programmability object in SQL2008 eg Trigger, Stored Procedure or Function using SQL Server Management Studio, you get to options when right clicking the object, Modify or Script..ALTER to.



I've never really understood the differences between the two as both appear to have the same behaviour of scripting out your object with an ALTER command. But there does appear to be a minor difference, one which i'm sure isn't by design, is only minor but definitely one to be careful of.

When using Modify to script out your object to a new window the GO separator is not included whereas it is included using the Script command. So if you happen to make a few modifications in the same window below the end of the definition of the object to test something "ad hoc", you'll need to remember to highlight only the part of the query window which forms the object. Otherwise, your testing statements will be included as part of object when you just hit F5 to persist the ALTER.

In the following screenshot, i've hit the Modify option and then written an adhoc query for a different purpose. If, I forget to remove the adhoc statement, forget to add a GO statement at the end of the procedure or don't highlight the procedure definition, this test query will be persisted as part of the procedure leading to some undesirable results.

T-SQL: Table Valued Parameters - "adios" to the Split function

A very common problem that developers have faced is the ability to pass in arrays to SQL Server and although there are various options available for editions up to SQL2005, the arrival in SQL2008 gave us Table Valued Parameters which allows a much neater way of passing in multiple values.

Here is an example using a simple c# console app to add multiple values to a database table. The key part to note here is that the input parameter needs to be set as READONLY and within the c# code you need to pass in a datatable as the parameter.


-- set up the objects
USE tempdb
GO
-- your table type
CREATE TYPE dbo.string_list_tbltype AS TABLE (nm NVARCHAR(255))
GO
-- your table
CREATE TABLE dbo.tblBeatles (Nm NVARCHAR(255), Dt DATETIME)
GO
-- the insert procedure
CREATE PROCEDURE dbo.AddBeatlesMembers
@Members dbo.string_list_tbltype READONLY
AS
INSERT INTO dbo.tblBeatles
SELECT nm, GETDATE()
FROM @Members
GO


And now the c# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Music
{
    class Program
    {
        static void Main(string[] args)
        {
            UseTVP();
        }

        public static void UseTVP()
        {
            DataTable BeatlesTable = new DataTable();
            BeatlesTable.Columns.Add("Name", typeof(string));
            
            // Fill with row
            BeatlesTable.Rows.Add("John");
            BeatlesTable.Rows.Add("Paul");
            BeatlesTable.Rows.Add("Ringo");
            BeatlesTable.Rows.Add("George");

            using (SqlConnection conn = new SqlConnection("Server=MyServer;Database=tempdb;Trusted_Connection=True;"))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "dbo.AddBeatlesMembers";
                SqlParameter param = cmd.Parameters.AddWithValue("@Members", BeatlesTable);

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }
    }
}

There is also an MSDN article that explains this in more detail.

FTP: Make Windows Explorer Interactive

I often use Windows Explorer as my FTP client as its quick and simple to drag and drop files on to a server. However, recently something had changed on my system which meant that when using Explorer the mode wasn't interactive and I was unable to use it in the way I would expect:



After some digging around, I managed to stumble across this MS article which although relevant to Windows 2000 also seemed to work for me running Windows XP SP3.

Essentially you need to edit a couple of options in IE - Tools, Internet Options, Advanced, Enabled Folder View for FTP sites.



A restart of Windows Explorer and a visit to my ftp site got me the much more user friendly:



I can only assume that some windows updates or IT security patch had reset these settings.