In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet.
Let's work through some examples with the SQL Server 2005 Adventure Works sample database for each operation with a sample Excel spreadsheet.
In SQL, a view is a virtual table based on the result-set of an SQL statement.
The only way that worked for me was using an alphabetical name for my Link Server without any dot '.' You can use these lines of code to add your link server and authentication: EXEC sp_addlinkedserver @server='TEST_LINK', @srvproduct='', @provider='SQLNCLI', @datasrc='tcp:0.0.0.0' EXEC sp_addlinkedsrvlogin @useself='FALSE', @rmtsrvname='TEST_LINK', @rmtuser='user', @rmtpassword='secret' For linked servers using the periods for a web service name - this won't work. You need to define a Linked Server before you can access it, since the linked server entry also contains the authentication details.
I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data.
The main command used in one of the tips is OPENROWSET.
If you just do a select query from the view you will see the view return results. Then the update failes because it is more than 4 parts. The Script that does work is posted at the link above.
But if you try to go into design mode for that view and try executing the design query the error will pop up again even though the view was successfully created. Dragging and dropping from a defined linked server into my view in management studio didn't work - the interface (Management Studio 2008) doesn't allow me to drag and drop.