Using Linked SQL server in a View

Hello All:

    I have a new view that reads data from the base source server/table.   Also, it links in another server (using the sp_addlinkedserver command).

When I run the script not in a view, it works.   When I create a view with this script, it does not find the linked server.   Is there some issue with linked servers from within a view or perhaps I am creating the view incorrectly in combination with the linking of the server?   I have begining of the view creation below.   Ideas?    Thanks

If OBJECT_ID('dbo.V_CombinedLaborHours') IS NOT NULL
    DROP VIEW dbo.V_CombinedLaborHours;
GO
use SQL_Server_1
EXEC sp_addlinkedserver 'SQL_Server_2', N'SQL Server'

GO
Create View dbo.V_CombinedLaborHours

AS

select

February 13th, 2015 10:27am

In your SQL command, you need to refer to the servername. Example:


CREATE VIEW dbo.test as
SELECT *
FROM SQL_SERVER_2.databasename.dbo.tablename

This will work if the linked server is created and your user has read permissions under the currenty securtiy context to the other environment.

Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 10:49am

Wow, thanks for the quick response on this.   Yes, I fully qualify the server/database/schema/table (See below).   However, when I select from the view it says that it cannot find the linked server.   It does not say no permission, it just says cannot find the linked server name.

[server_2].[databasename].[schema].[table]

February 13th, 2015 11:05am

You can refer to tables on a linked server from a view. The linked server must be available when you create the view.

Since you did not post any error message and nor the definition of the view, it is difficult to help you further.

Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 11:11am

Thanks for getting back to me.   Sorry, I have posted the error below that I get when just trying to "run" that view.   It seems to be acting as if the linked server is not there.  I can run the link outside of a view and the link to the second server works.   It is just after creating the view from my script and then try extract data from the view that it fails

Could not find server 'Server_2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Msg 4413, Level 16, State 1, Line 2

February 13th, 2015 11:26am

Read your error message.  It clearly states that your failing query is referring to Server_2, while the script you posted clearly creates SQL_Server_2
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 11:55am

Sorry, that difference in the name was a typo.   I manually changed the name for display purposes only.   My company forbids actual server and database names to be exposed to the outside.  

When I run the script from outside of the view, it works (linking the second server etc) and I can see the data coming from the view.   It is just when I create the view (as shown in the first entry in this thread) then it is as if the view is not running the sp_addlinkedserver command.

Perhaps I cannot run the link to the second server out side of the "AS" command?   I tried having it inside of the "AS" but then it throws error.  When I look at the definition of the View it is not showing the link to the second server as if it got ignored during the create of the view.

February 13th, 2015 12:09pm

Unfortunately, you still have not posted the script that creates the view nor the sql statement in which you use (or attempt to use) that view.  Lacking that information, it is impossible to guess what you are actually doing.
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 2:13pm

Thanks all.   I resolved this issue by removing the link to the external server from the View.   I created a separate call prior to running my view to do the link to the additional server.  Then once done with the view, I remove the link.   Thanks all for responding.
February 13th, 2015 4:11pm

It certainly sounds like a better idea to have the link there all the time...

Free Windows Admin Tool Kit Click here and download it now
February 13th, 2015 5:48pm

Yeah, I agree, but I could not get it to work with the link inside of the view.   The view just ignored the link command.   I guess I should keep after that to make it work?
February 13th, 2015 7:46pm

Yeah, I agree, but I could not get it to work with the link inside of the view.   The view just ignored the link command.   I guess I should keep after that to make it work?

I don't know what you mean with "to work with the link inside of the view". There is no problem with referring to a data source on a linked server inside of a view.

But a major problem in this thread is that you have refused to show the code you are actually using, so we have very little idea what you are talking about, and it becomes a frustrating guessing game for us all.

Free Windows Admin Tool Kit Click here and download it now
February 14th, 2015 6:34am

Hi Erland:

    Yes, I am totally for bidden to display actual code anywhere outside of the company.   That was why I provided some of the code revised to not indicate actual servers and tables.   Once I get back in, I will have to revise all the name then I should be able to post the code.   thanks.

February 14th, 2015 10:03am

It is perfectly understandable that you don't want to disclose the actual code. However, you need to be very careful when you mask names, that you do it consistently. The best is to use the Find/Replace capabilit of SSMS or some other editor. If you make changes manually, we will comment the code you post, because it is the only code we see.

Free Windows Admin Tool Kit Click here and download it now
February 14th, 2015 11:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics