After being bitten by this for the second time, I thought I should write a post for future reference. I had setup a brand new virtual machine and I wanted to access the SQL Server 2008 instance. Because of the firewall built into Windows all external connections are blocked – this means I cannot ping the machine, let along connect to the SQL Server instance running.

Disabling the firewall isn’t a problem, in the control panel you simply set the option.

Generally, this solves the problem. However, after doing this on 2008, I still couldn’t ping the machine.  After paying a bit more close attention, I found that the Windows 2008 Firewall has different profiles, each with their own firewall setting. While I had disabled the firewall for the domain, public and private where still active and blocking my connection.

After disabling the firewall on both of those profiles, I could happily ping the machine and connect to the SQL Server Instance running.

Labels: SQL Server 2008, Windows 2008

Within CTP6 and RC0, there is a bug based around enabling filestream.  Within the setup process, there is a tab which allows you to enable filestream as it is disabled by default. If you happen to miss that dialog and process in the installer you will have trouble enabling filestream via the sql configuration manager. In fact, on the properties dialog, after enabling FS the OK button does nothing.

The team have now wrote a blog post explain this: Enabling FILESTREAM post SQL2008 Setup - a Known Issue in SQL Config Manager

The solution is to run a WMI script (available on CodePlex - http://www.codeplex.com/SQLSrvEngine/Wiki/View.aspx?title=FileStreamEnable&referringTitle=Home)from the console . To do this:

1) Download (http://www.codeplex.com/SQLSrvEngine/Release/ProjectReleases.aspx?ReleaseId=14071)

2) Execute the vbs script (cscript filestream_enable.vbs /Machine:. /Instance:(local) /Level:3 /Share:MSSQLSERVER)

File stream should now be enabled. Hopefully this will be fixed for RTM.

Labels: SQL Server 2008

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