While creating a series of test databases, I was required to hardcode a location to store the files. This caused me a problem as I wanted the script to be reusable on different machines \ platforms while also being consistent. I couldn’t enter the path for the SQL Server data files as this can be different, and I couldn’t enter a different path because it might not exist and I wasn’t keen on creating directories via T-SQL and xp_cmdshell.

While, it turns out you can find the location of your default SQL Server store simply by querying the system tables –sysaltfiles and getting the path for a particular database such as master.

DECLARE @device_directory NVARCHAR(MAX)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1) FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

After executing the query, on my local machine the value of @device_directory is ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\’

The variable can then be used when creating the files, for example filegroups for your database.

EXECUTE (N'CREATE DATABASE Northwind
  ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')
  LOG ON (NAME = N''Northwind_log'',  FILENAME = N''' + @device_directory + N'northwnd.ldf'')')
go

Labels: SQL Server

SQL Management Studio is a great application, however there are times when SQL Server Management Studio (SSMS) is unavailable (either too slow to start, or simply not installed). However, there is a really useful application called sqlcmd which is installed with SQL Server. This is a command line application, located in C:\Program Files\Microsoft SQL Server\90\Tools\Binn (SQL Server 2005), you should find that this location has been set in your %PATH% environment variable meaning you can execute it from anywhere, which allows you to connect to a server, execute SQL commands and for the results to be outputted in the console window.

Below, I am connecting to my local SQL Express instance, and querying the Customers table in the Northwind database.  The results of the query, in this case the amount of rows in the table is displayed.

Z:\>sqlcmd -S .\SQLEXPRESS
1> USE Northwind
2> SELECT count(*) FROM Customers
3> GO
Changed database context to 'Northwind'.

-----------
       1000

(1 rows affected)
1>

It's really quick and it's great if you just want to execute a simple command and don't want to wait 5 minutes for SSMS to load. A word of guidance, to execute the query you need to execute the GO statement, this will then execute everything in the batch.

Labels: SQL Server

A while ago I had to test a report created with SQL Server 2000 Reporting Services.  After installing Reporting Services and uploading the reports, went I attempted to access them I was shown the following error message.

Error rsReportServerDisabled : The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service

A little bit scary as I had no backup! After a bit of searching, I worked out I had to execute this command:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn> rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

After which, everything worked just fine.

Labels: SQL Server

Last month we released SQL Data Generator which is a great tool for generating your dev\test data. One of the decisions we made for 1.0 was not to include an API. I wanted to be able to incorporate the data generation into my automated tests, so I have created an extension to SQL Data Generator as a side project which allows you to incorporate the generation process into your unit (well Integration) tests. You can now generate data as part of your unit tests.

I have wrote an article on my approach and how to use the framework over at Simple Talk - http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

For the extension, I took two approaches. One approach was just a POCO (Plain Old CLR Object) which would execute the command line application for a given project file. This could be placed anywhere in your code, ideally I was thinking in the TestFixtureSetup.

[TestFixtureSetUp]
public void TestFixtureSetup()
{
    SDGConsoleWrapper sdgConsoleWrapper = new SDGConsoleWrapper(@"Database\NorthwindEmployees_Orders.sqlgen");
    sdgConsoleWrapper.Execute();
}

As a parameter, you pass in the filename for the project file created via the UI.

The second approach is using custom attributes. By adding an attribute 'SDG' to the TestFixture and inheriting from 'SDGContext', we hooked into the calls for each method.  At the top of your test method, you can add the 'SDGExecute' with the project file name, this will then be executed before that test started.

[TestFixture]
[SDG]
public class DataAccessTests : SDGContext
{
   [Test]
   [SDGExecute("Database\\NorthwindCustomers_Orders.sqlgen")]
   public void GetCustomerByID_SDGProject_1000Rows()
   {
      DataTable dt = DataAccess.GetCustomerByID("00040");

      Assert.AreEqual("00040", dt.Rows[0]["CustomerID"]);
      Assert.AreEqual("Suppebentor Holdings ", dt.Rows[0]["CompanyName"]);
      Assert.AreEqual("Bobbi Yates", dt.Rows[0]["ContactName"]);
      Assert.AreEqual("Web", dt.Rows[0]["ContactTitle"]);
      Assert.AreEqual("564 White Old Freeway", dt.Rows[0]["Address"]);
   }
}

Best of all - this works with all of the unit testing frameworks! NUnit, MbUnit, XUnit, MSTest! They all can take advantage.  Feel free to download (from our CodePlex project) and start using it, I suggest you read the article for more of an in-depth overview of what is going on and the possible ways you can take advantage of it.

Finally, we would love to hear your feedback on this. I created this in my free time and is not a supported solution. However, do you think the application should have a fully supported API? Are you happy with the approach I have taken or do you think there are better ways of doing this?  Maybe your comments will help shape SQL Data Generator 2.0.

Article Link: http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

Application Download Link: Red Gate SQL Data Generator

Download and Source: http://www.codeplex.com/SDGGenerators

Labels: Red Gate, SQL Server

Partition Tables is a feature of SQL Server 2005 which allows you to separate your data into blocks (partitions) of data to improve performance and manageability as each partition can be stored in its own filegroup, which in turn can be on a separate disk array. In this post, I will cover how to partition your table, the table will contain a series of orders which I want to partition based on their order date.

The first thing I want to do is create a database.

CREATE DATABASE PartitionTest
GO

USE PartitionTest
GO

Next, I want to create a table which will contain all of my 'orders'.

CREATE TABLE Orders (ID INT NOT NULL, OrderDate DATETIME NOT NULL)

Next, I got SQL Data Generator to generate me 10 million 'Orders' for the table.  This gave me a large number of dates between 01/01/1953 and 30/12/2010 (values I had set) - so a nice big table which really needs to be partitioned. With that in place, we can create our partition. 

First, we need to create a partition function, give it a name and the data type we are partitioning based on - in this case DateTime. We need say RANGE LEFT (anything on the left hand side goes into the partition), we then list the series of values we want to split the data based on.  In this case, anything below 31/12/1950 goes into partition 1, between 1/1/1951 and 31/12/1960 into partition 2 etc

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('19501231 23:59:59.997', --YYYYMMDD
                        '19601231 23:59:59.997',
                        '19701231 23:59:59.997',
                        '19801231 23:59:59.997',
                        '19901231 23:59:59.997',
                        '20101231 23:59:59.997',
                        '20201231 23:59:59.997')

At this point, we create a partition schema.  In this case, I'm using saying all the partitions should be stored in the primary filegroup, however this is where we would allocate the partitions to separate filegroups.

CREATE PARTITION SCHEME OrderDateRangeScheme AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])

At this point, we have a table with 10 millions rows, a partition function and a partition schema. However, nothing links the partition to the table.  What we need to do is create a new Primary Key which we can set the partition schema on. If you have any existing keys, they will need to be dropped and re-created with the partition schema included.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, ID)
  ON OrderDateRangeScheme(OrderDate)
GO

If the table wasn't already created, we could include the partition schema in the create table script.

CREATE TABLE Orders (ID int, OrderDate DATETIME) ON OrderDateRangeScheme(OrderDate)
GO

In the above function, the year 2000 partition was missing. If we wanted to include this we would alter the function and include a split range

ALTER PARTITION FUNCTION OrderDateRangePFN () split RANGE ('20001231 23:59:59.997')

After executing all of this, your table will successfully be partitioned. To verify the details of the partition, there are a few queries we can execute.

The system view partitons contains all of the information, we can query this to return all the partition information for a table.

SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('Orders')

This returns the partition number and how many rows are in each partition.  This is useful for identifying if your partitions are split correctly or if one partition needs to be split down more.

The other way is to use a $Partition, this gives the partition number for the row of data. This allows you to see what data is in what partiton

SELECT *, $Partition.OrderDateRangePFN(OrderDate) AS [Partition] FROM [Orders] ORDER BY [Partition]

More information visit http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/

Labels: SQL Server

1022 builds later, today, we (Red Gate) released SQL Data Generator which is available to download as a 14 day trail. SQL Data Generator intelligently creates meaningful test data by automatically taking the table and column names in to account, along with field length and any existing constraints. We feel it's a great tool and is really useful during test and development, it's just a great tool to have in order to quickly fill your database. From a DBA's point of view, the tool is great for performance and scalability when huge amounts (millions of rows) is required to test against.  To prove this, see below for how I used the application to generate data for the Northwind database.

We are really looking forward to hearing what you think in order to make the best possible product.  If you have any comments or questions, please let us know via the forums.

On a side note, we have created a CodePlex site to host all any generators created by the community.  We already have a few online for you to download and use so I strongly recommend you take a look.  My favourite generator is the C# Code Generator created by Lionel, allowing you to write and compile C# code directly within the UI to use as the column generator.

Generating data for Northwind

Northwind is one of the sample Microsoft databases originally shipped with SQL Server 2000 and is used in a very large number of articles, as such it seemed logical to use it for this post. Below demonstrates how you can very quickly generate data for the Northwind database.

After loading the application, you will be presented with a Project Configuration dialog.  This allows you to specify which server and database you want to produce data for.  At this point you can configure project options, such as pre\post scripts to execute and if triggers should be fired when generating data.  I'm just going to connect to my local server and the Northwind database.

At this point, the application will load the schema and automatically attempt to match our built-in generators to columns within your table. The screenshot below has three main areas, the list on the left specifies which tables will be populated. The top middle panel provides all of the generation settings for the table or column (based on what is selected).  In this example, I have the Categories table selected and I wish to generate 1,000 rows.  In the lower middle panel, you have a preview table of how the data might look.  The CategoryName column has been populated with some possible categories, while Description has some latin text with the Picture column being populated with a valid image. This is straight out of the box, I haven't done anything yet apart from connect to the database.

Selecting a column in the preview table displays the column generation settings.  Each generator has its own set of properties allowing you to adapt the data to your own requirements, you can also select different generators by using the dropdown at the top. I'm happy with the pre-defined data so I will leave it as it is.

Clicking the Generate Data... button on the toolbar will display the Data Population Summary, this just simply says what is going to be done against the database. In this case, DELETE FROM and TRUNCATE TABLE commands will be executed with 1000 rows being inserted for each table. 

Clicking Generate Data will populate the table. After which a report will be displayed detailing what happened and if any errors occurred. The data looks to have been inserted successfully, It's amazing how quickly data can actually be generated using this application. 

That's it - very direct and simple. We can select data from the table using SQL Management Studio.  This data is all the default settings, I haven't modified anything.

If your happy with the data produced then you can save generation settings as a project file to use later, or you can tweak the settings to tailor the data generated for your own database.

However, the application has really loads of cool features which I haven't even touched on here, hopefully I will be able to cover them in later posts and articles.  Until then, why don't you download the free 14 day trail from the website and see what features you really like. The team and I would love to hear your feedback!

More information at http://www.red-gate.com/products/SQL_Data_Generator/index.htm

Product forums at http://www.red-gate.com/sqldatagenerator/forum/v1

CodePlex website at http://www.codeplex.com/SDGGenerators

Labels: Red Gate, SQL Server

If you ever looked at the dynamic management views or one of the system catalogs in SQL Server a column called Object ID will be associated, from the looks of it this number doesn't actually relate to anything - but there is actually a lot more to it than that. Object_ID is a unique id number for an object within the database, this is used internally by SQL Server.  It should be noted, not all objects have an object_id.  DDL triggers for example do not as they are not schema-scoped.

If we run a query against sys.columns, lots of information will be returned about all the columns in the database.

SELECT * FROM sys.columns

However, the result set will look something like this:

object_id | name | column_id ....
4    rowsetid    1
4    rowsetcolid    2
...
151671588    MiddleName    4

While the column name is in readable text, the object_id isn't actually anything useful (to a human).  In fact, this is the ID of the table which the column belongs to. In order to get the name of the object from the object_id there is a function called Object_Name(). This will take the object_id and return a readable name. If we re-run the query but including the object_name

SELECT OBJECT_NAME(object_id) as TableName, * FROM sys.columns

Then the table name is returned in a helpful manner.

TableName | object_id | name | column_id ....
sysrowsetcolumns    4    rowsetid    1
sysrowsetcolumns    4    rowsetcolid    2
vEmployee    151671588    MiddleName    4

We could also use the Object_ID() function to obtain the object_id for an object in database.  For example, the query below asks for the object_id for the Customers table in Northwind.  This returns 21575115 

USE Northwind
GO
SELECT OBJECT_ID('Customers')

Of course, flipping this around will return us Customers again.

There is also a similar function when you are given a schema_id. Schema_Name() works in the same fashion but returns the schema name based on the Schema_Id.

SELECT OBJECT_NAME(object_id), SCHEMA_NAME(schema_id), * FROM sys.tables

Hopefully this will clear up what Object_ID is. I know next time I query one of SQL Server tables I will be able to return at least some more meaningful information.

Labels: SQL Server

While reading my SQL Server 2005 MCTS book, I came across sp_helpdb.  This is a database engine stored procedure which can provide you with information about the databases in your system. You can execute the stored procedure within management studio using:

sp_helpdb

This will return the following results set.

As you can see, it has given information about each database - including db_size of the database.  The status containing more information about the database, the status for AdventureWorks is:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CS_AS, SQLSortOrder=0, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled

We cam also execute it for just a single database.

sp_helpdb @dbname = 'AdventureWorks'

This will return the same information as above for the table, but we also get information about the actual filegroups for the database, for AdventureWorks we just have the primary filegroup for the data and the transaction log.  This can also be obtained by executing sp_helpfile.

This can also be obtained by using the Report button on the Summary tab for a database within SQL Management Studio.  The report we are interested in is the Disk Usage report.

This will display the information in a nice table view, however takes a lot longer to execute and process than the raw queries.

The above report also gives information about Disk space used by tables (along with a few other items).  This can be obtained by executing sp_spaceused which will return the information on a database as a whole. If you are interested in a particular table, then you can set it as a parameter.

EXEC sp_spaceused @objname = 'HumanResources.Employee'.

Labels: SQL Server

Within SQL Server, there is an application called Bulk Copy Program (BCP) which is generally used for importing data very quickly into SQL Server.  However, the application also has the functionality to export data from a table or query into an external file.

Below is the command to export all of the data from Northwind.dbo.Customers into a NorthwindCustomers.csv file.  I then use the -t, to say the column separator should be a comma.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" Northwind.dbo.Customers out "C:\NorthwindCustomers.csv" -c -T -t,

If we wanted some more control, we could write a SQL query and use the queryout option to export the results returned.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" "SELECT * FROM Northwind.dbo.Customers" queryout "C:\NorthwindCustomers.csv" -c -T -t,

Combine this with xp_cmdshell, and all of it can be done from a stored procedure. Great way for getting data very quickly out of a table and into a flat file.

Labels: SQL Server

Having lots of fun with SQL Express tonight, attempting to load another sample I hit another error:

---------------------------
Microsoft Visual Studio
---------------------------
Failed to generate a user instance of SQL Server due to a failure in copying database files. The connection will be closed.
---------------------------
OK  
---------------------------

In the connection string, I changed it from creating a user instance each time to using a single instance and it appeared to solve my problem.

This is done by modifying the connection string to be "User Instance=False;" instead of "User Instance=True;"

Labels: SQL Server

While I am a fan of Vista and UAC, I have to admit there are times when other applications really cause it to be annoying, tonight it was SQL Express causing me pain.  I wanted to try one of Microsoft sample's, so I downloaded the zip, extracted it, loaded Visual Studio 2008 and hit F5.  At this point, the debugger stepped in and told me there was an error opening the database connection. 

"CREATE DATABASE permission denied in database 'master'.\r\nAn attempt to attach an auto-named database for file <<Path>>\App_Data\\MyDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

To create a database on SQL you need local admin permissions, as the application was automatically attaching the database I had to run Visual Studio as Administrator before I could execute it.  Just find it a bit annoying that you need access to Master to attach a SQL Express database as the whole point is for it to be zero friction.

Labels: SQL Server, Vista

Today, Red Gate have released the beta for SQL Data Generator 1.0. This is a product I have been working on for the past few months and its great to release a beta for everyone to use.

"This tool is aimed at generating test data for SQL Server databases. It can either generate data from scratch or import from existing sources of data.(Like SQL tables or CSV files.)
Features:
- Full SQL Server 2000 and 2005 support
- All data types supported except CLR types
- Pre & Post Scripts execution
- Command-line access version
- Import data from CSV or SQL tables
- Customizable generator settings that allow configuring the amount of nulls, unique values, minimum and maximum values, etc..
- Diverse range of inbuilt generators
The product is not yet complete, and we are looking for user feedback about what features and or functionality you would like in the final product.
*The beta version is set to work until 11 Apr 2008. "

This is a free open beta with the application due to expire on 11 April 2008 with a planned final released sometime before then. 

What we really want now is for everyone to use the software and provide us with feedback for the final version.  Please let us know on the forums what you like and don't like about the software and what we could do to solve all of your data generation problems!

Visit http://www.red-gate.com/MessageBoard/viewtopic.php?t=6140 for the announcement and download URL

Product page - http://www.red-gate.com/Products/SQL_Data_Generator/

Forums: http://www.red-gate.com/MessageBoard/viewforum.php?f=76

UPDATE: We have just released Beta 2 of the application, please visit the product page for more information and to download.

Labels: Red Gate, SQL Server

While at SQLBits, both Dave and Simon mentioned passing the Application name as part of the connection string to make debugging easier.  This was the first time I had heard this, so I decided to have a little look so I fired up my trusty linq to SQL Console and SQL Profiler and executed some queries.

Without using the application name parameter, SQL Profiler's results looks like this.

From this, SSMS uses the parameter and you get a nice friendly name however my console application doesn't and you get the information about the SqlClient data provider being used.  Helpful, if you only have one SqlClient running, unlikely if you have a production server.

If I modify my connection string and include the Application Name option as part of the connection string it would look like something below.

<add name="LinqConsole.Properties.Settings.NorthwindConnectionString"
    connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True;Application Name=LinqConsole"
    providerName="System.Data.SqlClient" />

Now if we execute the application, Sql Profiler uses the name in the connection string within the Application Name column. Much better and more useful!

But it's not only in Sql Profiler where its used, if you execute sp_who2 to see which connections are made to the server you can see that the program name is include.  Very useful for detecting resource leaks...

If that wasn't enough of a reason, then you can filter down which items are actually traced based on the application name.  When creating a new trace, simply go to Events Selection tab, select Column Filters and enter your application name in the Like (or Not Like) box.

I'm sure its used in many more places, however this was just a quick look at what it actually is and from now on I will definitely be including it in all my connection strings - it's just good manners for anyone using or support your application.

Labels: SQL Server

On Saturday I attended the first SQLBits conference.  The one day event was organized by UK SQL Server MVPs and focused on different areas of SQL Server with the sessions being categorised as DBA, Dev, BI and 2008.  The sessions themselves where very good, with Dave's Top 10 keywords and Simon's SQL Myths sessions being my favourites but there was a very good mix of different sessions to satisfy the different roles of attendees.  From what I got, most people seemed to be DBA's which was pleasing.  Dave was doing his podcasting rounds so keep an eye ear out for that...

One bonus which I wasn't expecting was that if you filled out the feedback form, you will be sent a £30 book voucher!! Take note DDD6 organisers ;) Very cool.  Think I will get a SQL Server book...

Afterwards, Red Gate paid for coaches to take people back (we also paid for the coaches to get people to TVP from the station in the morning as well) to the station but also onto the Group By event.  This was cool was at previous events this has always been a real pain.  The evening event was held at Reading Bowl, I had my reservations about bowling but it was actually really good fun.  The turnout wasn't great, but still had a really good time.

I'm now really looking forward to 2008 CTP 5 being released (was told its due this month sometime).  I have post about CTP 3 before, use the search box at the side to find them as sadly I didn't label them (sorry...).

On a side-note, I had my confirmation for TechEd Europe today!!! My first time @ TechEd and really looking forward to it.

Labels: SQL Server, SQLBits

The sessions for the SQLBits (SQLBits.com - UK SQL Server Community) conference has been published to the site. 

Registration isn't open yet, (Simon says a few more days), but if you pre-register on the site you will be emailed the link as soon as its live.

Sounds like it will be a great event.

Labels: SQL Server, SQLBits

Just tried to install Katmai (SQL Server 2008) on to my dev machine with Visual Studio 2008 on there. After installing, the Management Studio was not on the start menu - strange. As a quick look on the MSDN forum I found a pointer to this in the readme

4.5.1 Remove SQL Server 2005 Workstation Components before Installing SQL Server "Katmai"

Workstation components is an option under the SQL Server 2005 item in Add/Remove.

As uninstalling this, and repairing the SQL Native Client installation, still nothing.  I then re-ran the main install, and selected just the workstation components option.  This went ahead and installed a little bit more but then Windows reported that SSMS crashed, in the setup log saying:

MSI (s) (9C:8C) [16:37:06:048]: Product: Microsoft SQL Server "Katmai" Tools -- Installation failed.

MSI (s) (9C:8C) [16:37:06:057]: Windows Installer installed the product. Product Name: Microsoft SQL Server "Katmai" Tools. Product Version: 10.0.1019.17. Product Language: 1033. Installation success or error status: 1603.

Things just went from bad to worse from then.  I used system restore to roll back my system and start again.  This time, I uninstalled SQL Server 2005 components first, install worked fine this time and didn't fail.  However, when I tried to login to the server from Management Studio I was getting more errors:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Within event viewer it said

The Tabular Data Stream (TDS) version 0x73090003 of the client library used to open the connection is unsupported or unknown. The connection has been closed.  [CLIENT: 127.0.0.1]

Same errors when I tried to login from another VM machine with Katmai on and also Visual Studio.  However with Visual Studio, I could connect using the SA login and OLEDB and also sqlcmd, even though I couldn't from Management Studio.  The SqlClient provider is not correctly able to connect to Katmai, even when the server is working.

I tried to connect to Katmai working on another server and this also failed.

After a few hours of playing around I have given up! Not sure if I am going to attempt to uninstall Katmai or just flatten the machine.

Not sure why this is causing problems as Katmai and Visual Web Dev June CTP worked fine side-by-side. I think installing Katmai after Orcas was the killer.

Labels: Katmai, SQL Server

In this post, I am going to give an overview of the new Table Value Parameters feature coming in SQL Server 2008. Table Value Parameters allow you to pass user defined tables between queries, but also between client and server. With them being tables, you can do various different things with them, for example querying, joining or inserting values just like you would with a normal table. Now, instead of having the query take a long list of parameters they can simply just take a TVP as a parameter.

To create a TVP, you have to define a user defined type and the columns which the TVP will hold. Here we create a type of Customer which can hold an id, their name and postcode.

CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50), postcode nvarchar(50))

Then we create a dummy table to store the information.

CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO

CREATE TABLE CustomerPostCodes(id int, postcode nvarchar(50)) GO

We now create a procedure which takes a single parameter, which is a TVP. Based on this, we can insert the data into two separate tables, however from the outside its just a single object and a single stored procedure being called.

CREATE Procedure AddCustomers(@customer Customer READONLY) AS

INSERT INTO Customers SELECT id, CustomerName FROM @customer

INSERT INTO CustomerPostCodes SELECT id, postcode FROM @customer

GO

One important point when using TVP's as parameters is that they must have the READONLY attribute. Another important point, the TVPs are actually temp tables stored on the server in tempdb. As you can see, the stored procedure inserts the records from Customer, Customer could actually contain multiple customers into the related table by querying the table.

In order to use a TVP, we need to declare it like we would with a variable.

DECLARE @myNewCustomer Customer;

We can then use the table like we would any other table. INSERT INTO @myNewCustomer VALUES (1, 'Harry', 'NEW')

Finally, we can execute the stored procedure and pass the table as a parameter. EXEC AddCustomers @myNewCustomer GO

If we query the two tables, then it will contain the following records.

id CustomerName ----------- -------------------- 1 Harry

id postcode ----------- ------------ 1 NEW

TVP will solve a lot of problems and allow for a much more streamlined experience. TVP allows for joins, queries etc, and for inserting small number of records (<1000 is recommended). For larger inserts (>1000) - look at the Bulk insert functionality.

But, they would be a bit limited if you could use them only within SQL itself, so there is full support within ADO.net 3.0, using SqlDbType.Structured type. The parameters which can be passed in from ADO.net are DataTables, IEnumerable<SqlDataRecord> and DbDataReader. There is also support for ODBC, SSMS, and within Replication.

For example, adding a datatable as a parameter to the stored procedure from C# would have been something like:

DataTable dt = new DataTable(); dt.Columns.Add.... dt.Rows.Add..... SqlCommand cmd = new SqlCommand("AddCustomer", sqlConn); cmd.Parameters.AddWithValue("@Customer", dt); cmd.ExecuteNonQuery();

Now that's cool. Hope you have found this useful.

Links

Download my SQL sample here

Related Posts

SQL Server 2008- Change Data Capture (CDC)

SQL Server 2008- Declarative Management

SQL Server 2008- SQL MERGE Statement

Katmai June CTP

Labels: Katmai, SQL Server, SQL Server 2008

Another one of the new features within SQL Server 2008 is the Change Data Capture (CDC).  CDC is designed to capture insert, update and delete activity on a SQL table and place the information into a separate relational table.  It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change.  The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.  In later releases, there is also going to be a audit feature allowing you to audit who accesses what information and when, based more on select statements.

One of the posts on the forums details how to setup CDC, it's also in books online. To enable CDC, the following two statements are required. Note, SQLServerAgent must be running for this to work.

use testDatabase
--Activate CDC
EXEC sys.sp_cdc_enable_db_change_data_capture
--IsDatabaseEnabled?
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'testDatabase'
--Enable CDC on table
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'
--IsTableEnabled?
SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 'table_1'

The sp_cdc_enable_table... statement enables the tracking of the changes for a table, parameters are required for setting the source schema, the source name which is the table you want to track, and the role name, if the role does not exist it will create it for you.

After executing these statements you will have a number of new tables within your database.  The tables are:

• cdc.captured_columns - Information about the captured columns being tracked.
• cdc.change_tables - Tables being tracked.
• cdc.ddl_history - Changes to the tracked table, details the command issued on the table.
• cdc.index_columns - Guessing this is tracked indexed columns.
• cdc.lsn_time_mapping - When a transaction starts and ends. Relates to rows in the tracked tables.

There is also a table for each table which is tracked, in my case I have just the one - cdc.dbo_Table_1_CT.  If we execute a change to the table, such as inserting a new record.

INSERT INTO Table_1 VALUES (1, 'First Test')

A new row is added to the table, with a record also being inserted into lsn_time_mapping.

__$start_lsn           __$end_lsn             __$seqval              __$operation __$update_mask id          TestMessage
0x0000001E0000006B001A NULL                   0x0000001E0000006B0018 2            0x03       1           First Test

If we update the table again using

UPDATE Table_1 SET TestMessage = 'Updated First Test' WHERE id = 1

Two records are inserted into the tracked table. One with the TestMessage 'First Test' (the original message) and the other with 'Updated First Test' both relating to a single transaction in the lsn_time_mapping table.  If we do

DELETE FROM Table_1 WHERE id = 1

We have a single row inserted into dbo_Table_1_CT saying which row was deleted.

Within the dbo_Table_1_CT there is a column __$operation.  This gives you the ID relating to the operation executed on the row.  2 = Insert. 3 = Before Update. 4 = Post Update. 1 = Delete.

If we change the design of the table while it is being tracked, a row is inserted into cdc.ddl_history with the command executed on the table.

source_object_id object_id   required_column_update ddl_command                    
---------------- ----------- ---------------------- --------------------------------
1077578877       1093578934  0                      ALTER TABLE dbo.table_1 ADD Status nvarchar(50) NULL

However, if we execute another insert command on the table, the new column does not appear in the result set/dbo_table_1_ct.  It would be nice to keep the two insync and not have to worry about updating the table if it is being tracked. To get the tables back insync, I had to disable it (capture_instance parameter relates to the columns being captured I think) and re-enable the tracking. However, in doing so I lost all of the previous information - Boo.  The commands to do this are:

EXEC sys.sp_cdc_disable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @capture_instance = 'all'
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'

In case you are worried about this using up too much space, there is a sys.sp_cdc_cleanup_change_table stored procedure which can be used to remove entries up to a point in time.  You could then link this to a Job to archive the information, or simply report and remove.

I think that pretty much covers it. Another really useful feature, I have saw one or two attempts at auditing database activities like this and they are a bit hit and miss, so this will solve a lot of heartache. Looking forward to seeing what happens with the future auditing features.

One concern:  If a table is tagged as encrypted, is the cdc table also encrypted?  Will have to research this at a later date.

SQL BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/7d514b05-7c4b-4ef2-8f16-b720df3bf44a.htm

Labels: Katmai, SQL Server

One of the new features within SQL Server 2008 is Declarative Management Framework, a new policy based management framework allowing you to define what can or cannot be done on the server itself which can then be redistributed across your network of servers for more consistent management.  Content based on the June CTP.

Three main components make up the framework

• Policy management.  Create the policies from within SQL Management Studio.
• Explicit administration.  Select and manage targets for the policies.
• Automated administration. How and when the policies are evaluated.

Some of the important terminology includes:

• Facet.  Set of logical properties that model behaviour or characteristics.  There are a number of facets built into the server, each of these have a set of properties for which you can set a condition on based on what the policy is.  For example, there is a Surface Area Facet which allows you to set if a setting should be on or off. 
• Condition.  Expression of allowed states.
• Policy.  Condition and the expected behaviour.
• Policy group.  Group to help manage policies.  A policy can only belong to one group.
• Execution Mode.  How the policy will be executed. 

What is a Facet again?

Facet is one of the core concepts within the DMF.  They set out all of the properties which can be used as part of the condition/policy without you having to worry about how to locate and access the information.  When you create a new condition, you say which facet contains the information you want, and then set a condition to the properties you are interested in.

When it comes to creating a policy, the facet plays a part in what the policy can be targeted at.  The Multipart Name Facet can be targeted/applied to a number of different objects in the system while the surface area facet only applies at server level.  This makes managing the policy a lot similar as you only see the options which you are interested in.

If your unsure on where the property you are interested in is contained within which facet, you can right click on the object > policies > facet.  This will display all the related facets with all the properties set to match the selected object.  From here, you can also create a policy to define how it should be configured, makes it quick to setup a known baseline and tweak the policy from there.

What and How are they executed

DMF executes the created policies either on an ad-hoc basis, or more automated via Check on Schedule, Check On Changes or Enforce which are set when the policy is created, or changed at a later point.  Policies can also be set to disabled/enabled.  Policies can also be checked in test mode to make sure they are working correctly.  Check On Schedule uses a SQL Server Agent job to evaluate a policy.  Check On Changes uses event notification to evaluate based on when changes occur.  Enforce uses DDL triggers to prevent policy violations.  Notice, the first two do not prevent, just notify, while Enforce prevents the changes taking place.

Dan has done a really good write up on the details of enforce at Declarative Management- Behind Enforce. To summarise the main points.  Enforce works off a public DDL trigger which listens for all server and database events.  This is a single trigger and is updated with the events required which is based on the active polices, which in turn is based on the facet.  The DM engine runs inside the SQLCLR,  however it runs even if SQLCLR is set to Off as the Off mode still allows assemblies signed by Microsoft to execute.  An interesting point is that the DM is dependent on the DDL eventing and so can only enforce state on objects that are transactions.   If it is not a transaction, then it is not allowed to be enforce (option will be disabled) as it cannot be cleanly rollbacked if it fails.

How do you create a policy?

When creating a policy, the first thing you need to do is create a condition.  This is all built into SQL Management Studio, within the Object Explorer, under Management there is a new tree item for Policy Management. This contains all of the information relating to the facets, conditions and policies related to the connected server.

To create a new condition, right click Conditions or a Facet to display the create dialog, you will need to give it a name, choose the related Facet and then set the conditions.  You can do some really powerful expressions, however I do not think there is a way to do cross two facets.  Maybe giving this as a SQL statement would allow more flexibility.

Once the condition has been created, you can then attach a policy to that condition. Right click Policies and select new policy. You will need to give it a name, select the condition you just created, then select what the policy applies to, finally selecting the execution mode.  That's it, the policy is now setup to manage the server.  A policy can only have one related condition, however you can have a group of policies.

 As already mentioned, you can create a condition and related policy from an existing database object.

Where are they stored?

Policies are stored in the msdb database but also can be exported as a not very interesting XML file.  They can be accessed via views, or via SQL Management Studio.

Bug

There is currently a known bug in the CTP which means the DMF does not work. As mentioned, when the CLR is off only Microsoft signed assemblies can be executed, however the Declarative Management DLL did not get signed as part of the build process. When a policy is executed, it causes the following error:

Msg 10314, Level 16, State 11, Procedure sp_syspolicy_execute_policy, Line 25
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.DmfSqlClrWrapper, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A) ---> System.Security.SecurityException: Strong name validation failed. (Exception from HRESULT: 0x8013141A)
System.Security.SecurityException:

You need to enter the following command and restart services to allow SQL to execute non-signed/strong named assemblies.  The '89845DCD8080CC91' string is the Public Key Token for the assembly in case you was wondering.

"%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" -Vr Microsoft.SqlServer.DmfSqlClrWrapper,89845DCD8080CC91

Summary

The Declarative Management Framework is an excellent addition to the SQL Server toolset and I can see a lot of benefit for DBAs, no more going around 100 servers to check that the CLR is enabled, instead they can just execute an ad-hoc script or enforce it by default.

Dan Jones, who works on the SQL team, has lots of interesting information about this on his blog.

http://blogs.msdn.com/dtjones/default.aspx

SQL Books online:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/ef2a7b3b-614b-405d-a04a-2464a019df40.htm

Labels: Katmai, SQL Server