Wednesday, January 5, 2011

SQL Execution Error when creating a view from a linked server in Management Studio

In my particular case, I'm using SQL 2005 SP4 to create a linked server to a SQL 2008 server. However, I know this happens in SQL 2008 as well. I was able to create linked servers without any problems in both SQL 2005 and SQL 2008, and I could query the tables on a linked server just fine, but if I tried to create a view using the Management Studio view creator I would get a SQL execution error that looks like this


In this case I was creating a view to a table on the linked server using [192.168.1.70].sqldb.dbo.tablename. For some reason when I tried executing or saving the view, the brackets surrounding my linked server were automatically getting removed. Luckily there is a workaround for this, which I found on stackoverflow.com. If you manually create the view within the query window, it will work. So what I did next was open a query window and entered the following

CREATE VIEW myView
AS
SELECT * 
FROM [192.168.1.70].sqldb.dbo.tablename
GO


Then I ran that query and it was successful. Refreshing the list of views available showed the view "myView", and I was able to use it like normal. However, if I opened the view in the view designer, the brackets were still missing and it would throw the same SQL Execution Error as above.

No comments: