How to fix SqlError: FILESTREAM feature is disabled.

I was trying to restore a database and got the error “System.Data.SqlClient.SqlError: FILESTREAM feature is disabled. (Microsoft.SqlServer.Smo) from the Restore Wizard.

This baffled me a bit as I know I went into the DB Server setting in Sql Manager and specifically set that value to Allow it. Well, after smacking the computer around a bit, she finally confessed that there is another place you have to enable that setting. Go figure.

You need to go to the Sql Configuration Manager (Not SQL Server Management Studio) and also enable the settings.

 

Here is the original error:

image

Here is me checking the settings on the DB:

Look, I think it’s set:

image

 

But really, no. Check the Running Values to see it laugh at you.

image

 

So, resolution? Go to Sql Configuration Manager. Open the properties window of your Server.

image

Check the boxes. Hit OK. Now right click your server instance and hit Restart. Watch the progress bar and ponder great thoughts.

Now, go to SQL Management Studio.

Connect to your server and run this simple command.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

Now restart your Sql instance once more. You are done. Repeat whatever miserable operation you were doing before you got this error and repeat to yourself. “When I design software, I will never make someone set an option in two programs ever….ever….never ever…”

 

Happy Coding!

Right click doesn’t work in Excel 2007 anymore.

My right click context menu stopped working in Excel 2007 all of a sudden. It was driving me nuts. The Excel context menu wouldn’t display.

I came across the solution, which seems to be an absurd on, but it worked.

Open excel
hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
Type this and hit enter:
      application.CommandBars("Cell").Reset
then the same with this line:
     application.CommandBars("cell").Enabled = True
Hit alt-f11 to get back to excel and test it out.

 

I am quite impressed someone found this solution, but also trying to figure out how in the world this happened in the first place.

Source to pay tribute to.

SQL Script to extract address information into individual fields

I had a need to write a script or a program that would extract an address that is stored in one field and break it out into its parts. Address, Address2, City, State and Postal Code(zip code).

It’s a real bugger to do in a single SQL statement, I had one but it was really really ugly. I mean, wing man, take one for the team, dating the hot girls friend kind of ugly.

So, I made this simple script that you can either run outright to convert your data or you can easily modify this into a procedure.

Background:

I created the new fields on the original table and I also added a field I called SiteAddressBackup. I did an update into this field before running this script in order run against real live data and still have it safe and sound if I mucked things up. I suggest you do the same to preserve your data.

The script. Should be easy to follow.


--Seekford script to extract addresses
--Tested on 2100 real addresses from early production db
--examples
--1234 GREENVIEW DR SW,SUITE # 222,ROCHESTER, MN 55902-1080
--1555 SAM NUNN BLVD,PERRY, GA 31069-1954
   
DECLARE AddressUpdate CURSOR FOR         
SELECT MyOrdersID,SIteAddress
FROM tMyOrders
ORDER BY MyOrdersID
        
DECLARE @ID INT        
DECLARE @SiteAddress VARCHAR(255)        

update tMyOrders set SiteAddress = SiteAddressBackup
        
OPEN AddressUpdate        
        
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
        
WHILE @@FETCH_STATUS = 0        
BEGIN  

	
declare @Addr as varchar(100)
declare @Addr2 as varchar(100)
declare @City as varchar(100)
declare @State as varchar(100)
declare @Postal as varchar(100)
declare @Temp as varchar(100)

--extract the postal and state
set @Temp = rtrim(ltrim(SUBSTRING(@SiteAddress,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1)) +2 ,100)))
set @Postal = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1)) +2 ,100)
set @State = SUBSTRING(rtrim(ltrim(@Temp)),0,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1))+1)

--extract the city )pulls everything but the state and zip
set @Temp = SUBSTRING(@SiteAddress,0,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1))+1  )

set @City = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

--reparse
set @Temp = SUBSTRING(@temp,0,len(@temp) - len(@city) )
set @Addr2 = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

if(LEN(@Addr2) =0) 
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2)+1 )
end
else
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2) )
end


update tMyOrders set SiteAddress = @Addr,siteaddress2=@Addr2,SiteCity = @City,SiteState = @State , SitePostalCode = @Postal
where MyOrdersID = @ID 
     
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
 
END        
        
CLOSE AddressUpdate        
DEALLOCATE AddressUpdate   

--Query to show them broken out against the original
SELECT [MyOrdersID]      ,
SiteAddress,SiteAddress2,SiteCity,SiteState,SitePostalCode  ,siteaddressbackup   
  FROM [tMyOrders]
 

How To Fix: Windows Update Blocked by Group Policy

Problem: I cannot check for updates, it says “Some settings are managed by your system administrator”

Solution:

So, you can’t get windows update because some yahoo in IT blocked your access, eh?

First, type gpedit.msc in run,
In the window drill down User configuration>administrative templates>windows components>windows Update. In the right pane double click on remove access to access all windows update features and make it disabled.

If that doesn’t work, change the following key:

Disable and remove links to Windows Update

(User ConfigurationAdministrativeTemplatesStart Menu & Taskbar)
NoWindowsUpdate

(HKCUSoftwareMicrosoftWindowsCurrentVersionPoliciesExplorer)

There are a few other keys and settings that could affect it.

Check this link out for all the registry locations for Group Policy.