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>
you need to use a command featuring EXECUTE AT on server 1
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;
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!