To create a view in a linked SQL Server 2005 database with some data types transformations

There are two linked SQL Server 2005 machines, server A with a lot of data and empty base on sever B.

Our user does not have a permission to create views on A, but have this ability on server B. We checked out a possibility to make selects @B to A, and it's ok, there's a connection.

Please, prompt us on how to make a view @B, that just copies a table with transforming some fields' data types开发者_运维问答 from binary to nvarchar.

---

You say that the servers are already linked servers, and you've proven the ability to run a SELECT on Server B that queries Server A. All you have to do at that point is prefix the SELECT with a CREATE VIEW [dbo].[MyView] statement and you've created a view for that query.

To convert to/from nvarchar & binary, use the CONVERT function. http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx

If you want to copy the data to a table on Server B, write an INSERT INTO ... SELECT ... FROM [ServerA].[DatabaseName].[SchemaName].[SourceTable]