In a SQL Server database I have a table called users which has a column named signature. This column is of type Image.
My production SQL Server is located in a shared hosting environment.
One problem I’ve been facing lately is that I need permission to execute an UPDATE statement to insert a signature image for a given user. This problem occurs because to insert a signature image for a given user I have to execute a BULK statement like this for example:
--Update existing user UPDATE users SET [signature] =(SELECT MyImage.* from Openrowset
Bulk 'C:\MyProject\trunk\MyCompany.Product\MyCompany.Product.Web\
Images\Signature.jpg', Single_Blob) MyImage) where Id
= '1111aaaa-1111-11aa-a111-111111a1a1a1'
The query above works fine in my local machine but when I tried to execute it on the remote/production server, I got this beautiful message:You do not have permission to use the bulk load statement.
In the shared hosting environment the execution of Bulk command is disabled by default for security reasons. This is annoying but totally understandable!
Using Bulk load - allows the user to populate a database from a file. It’s not available in shared environment because it is necessary to insert client files on SQL server locally (the production server).
So… great! I need a way to bypass this limitation because I won’t spend tubes of money paying a dedicated server… it’d make sense if and only if I needed a dedicated server.
1st try: import a specific user row from my local SQL Server to the remote instance using SQL Server Management Studio Import task. I got an error about constraint key violation because I already had the same row (for that user) on both databases. It’s just a matter of updating the signature column in the production database. This seemed to be a pain path.
2nd try: consider a dedicated server? 
3rd try and solution: a few days later I found myself thinking about this problem again (this signature column updating thing is a recurring task) and so I decided to find another way and it came to light - link the remote server to my local SQL Server Express instance and write a beautiful SQL query that does the job.
First I stopped in this excellent blog post with a step by step guide written by jen: Create Linked Server SQL Server 2008
This post provided everything I needed to link both SQL Server instances.
To make sure you have linked your server correctly, you can execute this query in your local server:
select server_id, name, product, provider, data_source, [catalog], is_linked
from sys.servers
The above query gave me this result:
Figure 1 - Linked servers linked to my local SQL Server Express instance
Then I Googled about Update with Select + SQL Server or something like that and found this StackOverflow question: Updating a table with multiple values from a select statement where the date matches. Lieven’s answer helped. I just had to adapt it to my case. This is the SQL code that does the dirty work:
UPDATE U SET U.[signature] = users.[signature] FROM [LOCAWEB].[laudotech].[dbo].[users] U INNER JOIN users
ON users.id = U.id AND U.id = '1234aaaa-5678-90aa-b123-456789a0a1a2'
The above query must be executed within the context of the local SQL Server instance of course. Where the linked server resides.
To give you a view… this is how all this is configured inside SQL Server Management Studio (SSMS):
There’s so many things one can do with SSMS that I feel really happy in learning one more of those things. Last week I blogged about Import/Export SQL Server database data with SSMS. Take a look at it.
Man! Have I said that I Iike working with databases!?
Hope it helps.
