Enabling SQL FILESTREAM on your machine

I tried to play with FILESTREAM on my laptop. I didn’t enable the option at install time, so I went to the SQL Server Configuration Manager to enable the setting:

Enable FILESTREAM for Transact-SQL access

However when checking the first 2 checkboxes I was met with the following wonderful error:

There was an unknown error appyling the FILESTREAM settings. Check the parameters are valid.

There was an unknown error appyling the FILESTREAM settings. Check the parameters are valid.

This didn’t give me any search results, out of options I reinstalled SQL Server. When trying to enable FILESTREAM through the installer I was met with the following error:

Error 2114 validating the Windows share name MSSQLSERVER.

Good, searching on this resulted in the following blog (only 1 result!!!):

http://nfrelat.wordpress.com/2008/12/30/sql-2008-installation-strange-issue/

BAM, you need the Server Service running, which by default, is off on Windows 7.

Server Service Properties

Set it to automatic and start the service (or reboot machine).

And you’re good to go Open-mouthed smile

On a side note, it seems that the Server Service only needs to run when enabling the FILESTREAM, afterwards it seems to work fine with the thing set to Disabled… Don’t know why.

Have a good one,

-Kristof

Enabling SQL Server 2008 (R2) access over Network

First: enable SQL Server itself to be accessed over the network

  1. Open SQL Server Configuration Manager
  2. Expand SQL Server Network Configuration and click Protocols for MSSQLSERVER
  3. Doubleclick TCP/IP
  4. Set Enabled to Yes
Open SQL Server Configuration Manager
Open SQL Server Configuration Manager

Secondly: change the Windows Firewall to allow incoming connections on the TCP port of SQL Server

  1. Open Windows Firewall with Advanced Security
  2. Click on New Rule
Open Windows Firewall with Advanced Security
Open Windows Firewall with Advanced Security

Now in the wizard you set the type of the rule to Port.

Choose TCP or UDP port
Choose TCP or UDP port

Hit Next.

On the second window you set the Specific local ports to 1433:

Set the TCP port
Set the TCP port

Hit Next.

Allow the connection.

Allow the connection
Allow the connection

Hit Next.

Now enable the checkboxes you want to. I set mine only to Private. Because I only need to access the SQL on my laptop at home:

When does this rule apply?
When does this rule apply?

Hit Next.

Set a name for the rule
Set a name for the rule

Hit Finish and you’re ready to develop SQL over network 🙂

SQL Server 2008 Express Edition: Access over network

To connect to your SQL Server 2008 Express edition over network you need to enable the following options:

First: open SQL Configuration Manager (SQLServerManager10.msc):

image

Double click on ‘Named Pipes’ and enable them.

image

Then click ‘OK’ on the bottom. Now we have the access set up, now we need to start the service.

Now open Services (services.msc) and double click on ‘SQL Server Browser’:

image

Set the startup type to ‘Manual’ or ‘Auto’. That’s up to you. I have it on manual for security reasons. Then you can click start. But we have one more step to do. In the window copy the path to the executable (‘C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe’).

Then go to your firewall in your control panel and add the sqlbrowser path to allowed programs.

You’ll eventually see this:

image

You can also check the public, but that’s not necessary for me. If you set the sqlbrowser service to manual, you’ll need to start it each time you want it (just by making a shortcut to the path we copied above.) If not, it’ll start at startup 🙂

Good luck and post your questions below!

I’m going to try replication between 2 SQL Servers Express in one of these days and I’ll post the results on that too!

SQL Server 2008 Express Edition: DB Location

Let’s say you used Microsoft Web Platform Installer to install SQL Server 2008 Express Edition you aren’t presented with an option to set your database location. While this is not a problem if you use filebased DBs in your project, this can be a problem when you create the DBs in SQL Server Management Studio.

You can move the database folder to another folder/partition/…

To do this open SQL Server Management Studio. Connect to your instance:

image

And then do a right mouse click on your SQL Server instance and click properties.

image

This will open the following window: Click Database Settings on the left.

image

You can see (on the lower end of the pictures) the ‘Database default locations’. Change these to whatever you want. Be sure to change the log’s location too, you never now when they might come in handy.

While this doesn’t preserve your permissions (if you reinstall the instance,and attach the DBs yourself you will have to take ownership!), this will preserve your data!

Good luck.

SQL Server 2008 Express Edition + Visual Studio 2008 on 64-bit: Hotfix released

A while ago I wrote about me having a 64-bit system with SQL Server 2008 Express edition 64-bit and Visual Studio 2008 SP1 not beeing able to create a service based database because of some wrong registry keys. Installing a 32-bit version of the database should have solved the problem but that didn’t work for me. So I reverted to Sql Server 2005 Express Edition.

The problem is described on the Microsoft connect page.

Now Microsoft has released a hotfix for this. To get the hotfix you need to create a support case online (99$, which should be refunded) or use an MSDN support case voucher. Or you can use Google as I did and find the direct link to the hotfix 😉

I AM NOT RESPONSIBLE FOR INSTALLING THIS HOTFIX, IT WORKED FOR ME, BUT IT MIGHT NOT FOR YOU. THERE IS A REASON WHY MICROSOFT DOES NOT MAKES THIS HOTFIX PUBLIC. USE WITH CARE.

Linq To Entities with a SQL Compact Database

A while ago I wrote this (Dutch) article on how to perform LINQ to SQL for your SQL Compact databases, since Visual Studio did not understand this, we had to do it manually, as described in the article just mentioned.

But since the release of .NET Framework 3.5 SP1, and the therein included ADO.NET Entity Framework, it is now possible to generate a model out of a SQL Compact database!

Unfortunately this is not (yet) possible for Smart Device applications (Windows Mobile), but who knows what the future might bring!

SQL Server 2008 Express Edition + Visual Studio 2008 x64 User instance problems.

As described in this connect article there is an issue with the integration of SQL Server 2008 Express Edition ans Visual Studio 2008 if you are running on an x64 system (the OS itself! Not runing 32-bit Vista on a Core 2 Duo) (although some report this bug on a 32-bit OS).

This issue is currently unresolved, but you still can open the database in the server explorer if (and only if) you modify the connection string NOT to use a User Instance.

You can do this by rightclicking the database in the server explorer, modify connection and set ‘User Instance’ to ‘False’ (last item)

User Instances are not allowed anymore since SQL Server 2008, as described here.

In order to make it work as it should be you can either install a 32bit SQL Server 2008 Express (which I do) or do as described here.

Update: Back to SQL Server 2005 Express Edition, all the rest fails as described.

SQL Server Management Studio Express 2005 Security Error (29506)

Should you ever want to install SQL Server Management Studio Express 2005 you MUST run it as Administrator.

When you try to install it on Vista (by double clicking the .exe) it extracts the files to a temp folder.

Then it launches the setup, asking you some questions. After that UAC asks you to gain Administrative access.

No problem so far, but at the end the installer notifies you of error number 29506, it cannot modify the security properties.

Solution is described here, run the installer as Administrator from cmd.