SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View creation on Linked Server


View creation on Linked Server

Author
Message
Ness
Ness
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 974
Hi All,

I have had an issue in SQL 2008 SP1 when creating a view in a db bases on a linked server via SQLCMD. It appears to have an issue creating a view that points back to the first server. I.e. Run SQLCMD from Server 1 to create a view on Server 2. The view on Server 2 points to a table that resides on Server 1.

The linked server is set up and has RPC from/to and data access set

Has anyone seen this before? Any ideas?

Many thanks

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29605 Visits: 39986
can't help that much yet;
you didn't mention any specific error you get back, and it might be nice to see the actual command you are using in sqlcmd to create the view;

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29605 Visits: 39986
also, to create a view on Server 2, FROMserver 1, you need to do one of two things:
either the SQLcmd needs to actually connect to server 2, and not server 1

--sqlcmd -H<Host name if on different machine> -S<SQL server name\instance name> -U<username if needed> -P<password> -E -i<input file for execution> -o<output file for results>
sqlcmd -HServer2 -S<SQL server name\instance name> -U<username if needed> -P<password> -E -i<input file for execution> -o<output file for results>


or
you need to use a command featuring EXECUTE AT on server 1
example:

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ness
Ness
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 974
Hi,

Thanks for the response and apologies about the vagueness. I'll try to be a bit more specific.

The SQLCmd is getting the sql from a networked file using:
sqlcmd -S [Server2] -d [DBName] -b -i "\\NetworkScriptLoc\testview.sql"

The testview.sql just contains
If not exist...
Create view as
Select c2, etc from Server1.DbName.dbo.TableName

and the error is the old favourite - "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors". However, other scripts creating other objects work even when this fails and they are all using the same technique and service account. The only difference is that the view has a reference back to the server initiating the request.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 1019
Looks like you are running into the "double hop" problem.

Suggested readings:
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
http://www.sqlservercentral.com/Forums/Topic1043330-359-1.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search