Escaping a single quote in SQL Server 2005
I think you just meant:

select supplierid from dbo.suppliers
where companyname ='Grandma Kelly''s Homestead';


(In other words, there was only one apostrophe to replace.)