Cr e a t in g a Vie w f or An ot he r SQL Se r ve r I n st a n ce in VB.NET

Generate Data Matrix 2d barcode in VB.NET Cr e a t in g a Vie w f or An ot he r SQL Se r ve r I n st a n ce

One t ypical requir em ent is t o v iew a SQL Serv er row source, such as a t able, on anot her serv er. You can use t he OPENROWSET funct ion t o perform t his t ask, wit h argum ent s t hat specify a prov ider, ot her elem ent s of a connect ion st ring, and a SELECT st at em ent . The OPENROWSET funct ion can serv e as an argum ent for t he FROM clause of a SELECT st at em ent . This out er SELECT st at em ent , in t urn, m ust reside in a CREATE VI EW st at em ent w hen y our goal is t o cr eat e a v iew in t he curr ent dat abase t hat exposes a row source in anot her dat abase. When t he inner SELECT st at em ent t he one in t he call t o t he OPENROWSET funct ion point s at anot her SQL Serv er inst ance, t he prov ider for t he funct ion should be SQLOLEDB. Next y ou can denot e t he rem aining elem ent s of t he connect ion st r ing for t he ot her ser ver in t he follow ing order : t he ser ver inst ance nam e, a SQL Serv er login for t he serv er , and a password for t he login. Follow t he prov ider nam e by a com m a, but use a sem icolon for a delim it er aft er t he ser ver nam e and login nam e. A com m a separat es t he password fr om t he SELECT st at em ent . The follow ing scr ipt creat es a v iew on one SQL Ser ver running SQL Server 2000 t hat point s at a t able on t he cabxli ser ver running t he MSDE v ersion com pat ible wit h SQL Ser ver 7. You need t w o inst ances of SQL Ser ver t o evaluat e t his script ,

but y ou can nam e t he inst ances anyt hing y ou want . Just change t he r eferences t o cabxli t o t he nam e of a SQL Ser ver inst ance t o which you can connect . By t he way , t he t able is t he aut hors t able in t he pubs dat abase; MSDE doesn t rout inely inst all w it h t he pubs dat abase. Because cabx li is an int er nal t est serv er running Windows 98, t he serv er is available w it h sa and an em pt y password. Pr oduct ion serv ers should always have a password for t he sa login if you ar en t for cing Windows aut hent icat ion. The SELECT st at em ent r efer ences t he aut hors t able in t he pubs dat abase on t he cabx li ser ver. The ORDER BY clause along wit h t he TOP predicat e sort s t he result set by aut hor first nam e w it hin aut hor last nam e. The out er SELECT st at em ent t ak es t he OPENROWSET funct ion as t he ar gum ent for it s FROM clause. The SELECT list for t he out er SELECT st at em ent list s t he aut hors by first nam e, last nam e, and phone num ber, in t hat order. --CreatevewAuthorsSortedOnCabxli --Search for, and remove if found, the --vewAuthorsSortedOnCabxli view in the 04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = vewAuthorsSortedOnCabxli ) DROP VIEW vewAuthorsSortedOnCabxli GO --Create a new version of the vewAuthorsSortedOnCabxli --view in the 04 database from the --Shippers table in the Northwind database. CREATE VIEW vewAuthorsSortedOnCabxli AS SELECT au_fname, au_lname, phone FROM OPENROWSET( SQLOLEDB , cabxli ; sa ; , SELECT TOP 100 PERCENT * FROM pubs..authors ORDER BY au_lname, a u_fname ) GO --Select all rows and columns from the --vewAuthorsSortedOnCabxli view in 04. SELECT * FROM vewAuthorsSortedOnCabxli GO