Web Hosting Java Blog

292 Part I EXAM PREPARATION not on the list will not be allowed to use the remote server by choosing the Not Be Made option. You can choose that the users not on the list be forced into guest status, if available, on the remote server by choosing the Be Made Without Using a Security Context option. You can choose to attempt to pass through the authentication that the user has on the local server by choosing the Be Made Using the Login s Current Security Context item, or you can force everyone who isn t on the list in the top part of the window to use a specific login and password with the Be Made Using This Security Context option. Now that you know how to create linked servers, it s time to look at using them in a little more detail. Using Linked Servers There are two ways to use an established linked server. The first and easiest to understand method is to use the four-part name as shown in the Step By Step 9.1. As you remember, a three-part name is database.owner.object, so for the sysobjects table in the Master database, you can reference it as master.dbo.sysobjects. For a linked server, you can use a four-part name, which is server. database.owner.object, or, in the example above, sqltest1. master.dbo.sysobjects. The four-part name works great when you re using other SQL Servers as the remotes. What happens when you want to use something other than SQL Server on the other side? Then you need to use the OPENQUERY function. OPENQUERY looks suspiciously similar to OPENROWSET, but it takes only two arguments: the linked server name and a query. So you could run this: SELECT * FROM openrowsettest.master.dbo.sysobjects or you could run this: SELECT * from OPENQUERY(OpenRowsetTest, SELECT * FROM .master.dbo.sysobjects ) Notice that the linked server name does not have quotation marks around it. SQL Server doesn t like quotation marks there. Both these queries return the same resultset. These linked servers are really great, but don t get any ideas about using them to import data. Although it works well for smaller

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 291 5. If it is a SQL Server, choose the SQL Server radio button. This disables the rest of the input on the screen, and you can continue on with the next step. Otherwise, choose the Provider Name from the drop-down box, then fill in the product name, data source, and provider name as appropriate for the provider you are using. If you re using SQL Server, you have to put in the server s real name. 6. On the Security tab, shown in Figure 5.2, you can choose how users will log in to the linked server. A discussion on this tab follows these instructions. 7. The Server Options tab, shown in Figure 5.3, includes various options you can use to change the status of the connection. The Collation Compatible option specifies whether the remote server uses the same collation as the local server. The next three options, Data Access, RPC, and RPC Out, define how the connection can be used. A Data Access connection allows tables to be queried, whereas an RPC connection allows only stored procedures to be called. The RPC Out option enables the server to call remote stored procedures on the local machine. The Use Remote Collation options specifies that the linked server s collation order is to be used to run the query. Collation Name is the name of the collation to be used if the server is not collation compatible or if the Use Remote Collation option is off. Connection Timeout is the length of time a query should wait for the server to respond to a connection request, and a Query Timeout is the length of time a query should wait for a response. 8. Click OK to create the linked server. The Security tab, mentioned in Step 6 in the Step by Step, needs a little more elaboration on a couple of fronts. First of all, in Figure 5.2 you ll notice that there are two different parts to the window. In the top part you can specify local logins and map them to logins on the remote server. In the bottom part you can specify what happens to local logins who aren t in the list. You can specify that users who are FIGURE 5.2 Window used to set the security options for the linked server. FIGURE 5.3 Other options for the linked server.

For high quality website hosting services please check tomcat web hosting website.

290 Part I EXAM PREPARATION STEP BY STEP 9.1 Creating a Linked Server in Enterprise Manager 1. Open SQL Server Enterprise Manager. If you don t have the server you want to work with registered yet, you should do that now. 2. Expand the server you want to work with, then expand the Security container, and then click on the Linked Server container. 3. In the right side of the window, right-click in an empty spot and choose New Linked Server from the context menu. The Linked Server Properties – New Linked Server window appears, as seen in Figure 5.1. 4. There are a few boxes to fill in here. Fill in the Linked Server box with the name of the linked server you want to use. If the linked server is a SQL Server, you need to use the real name of the server in the Linked Server check box. FIGURE 5.1 Window used to define a linked server.

For high quality java hosting services please check java web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 289 There are two ways to create a linked server: Using the Query Analyzer and using Enterprise Manager. Which one is easier? Well, the explanation for using Query Analyzer is going to take a paragraph, and the explanation for using Enterprise Manager is going to use an involved Step by Step. You be the judge. In Query Analyzer, you create the linked server using the sp_addlinkedserver system stored procedure: sp_addlinkedserver [ @server = ] server [ , [ @srvproduct = ] product_name ] [ , [ @provider = ] provider_name ] [ , [ @datasrc = ] data_source ] [ , [ @location = ] location ] [ , [ @provstr = ] provider_string ] [ , [ @catalog = ] catalog ] Does that look familiar? The only parameters that aren t part of the OPENROWSET you ve already learned about are server and provider; the rest are just bad names on the same parameters. Server is the name of the linked server you are creating. Any SQL Server-approved object name works; it doesn t necessarily have to be the name of the server, but it probably should be. The srvproduct argument is the name of the OLE-DB data source. The provider argument specifies the same thing as the srvproduct, but does it with an OLE-DB PROGID, which is a long identifier. The rest of the options are from OPENROWSET. If you re linking to another Microsoft SQL Server, you can specify a @srvproduct of SQL Server only, and the @server argument must be the server s name. None of the other arguments are required or allowed. Here s a linked server definition for the server you saw used in the previous section: sp_addlinkedserver @Server = OpenRowsetTest , @SrvProduct = sqloledb , @Provider = sqloledb , @DataSrc = SQLTest1 Then and this is the really great part you can query it like this: SELECT * FROM openrowsettest.master.dbo.sysobjects That s a four-part name. They are required when using linked servers. You can t shortcut the object owner part either; you have to fully specify the entire thing. You could, of course, alias it, join it, or whatever you want to do; it s going to act just like a normal table. Step by Step 9.1 takes you through one command operation done in full color in Enterprise Manager.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

288 Part I EXAM PREPARATION Actually, OPENROWSET is a very useful function; it s just complicated and difficult to set up. After you have it figured out for a particular provider, it works very well. You should plan on spending several hours with a new OLE-DB provider to figure out how it works. Here s an example of OPENROWSET in use: SELECT * FROM openrowset( sqloledb , SQLTest1 ; sa ; , SELECT * FROM master.dbo.sysobjects ) This example is a fairly simple case; it just returns a rowset from another SQL Server in this case a SQL Server running on a box named SQLTest1, which has a blank SA password. You can tell it s a request to another SQL Server because sqloledb is the provider, and that s the name of the provider used to talk to other SQL Servers. It runs a simple SELECT statement and returns a simple rowset. Notice that it uses a full three-part name for the table. This isn t required, but it is good practice for doing remote queries. You can then use the results just as you would any other table: You can join them to other local tables, filter them, or whatever you like: SELECT SQLTest1sysobjects.name FROM openrowset( sqloledb , SQLTest1 ; sa ; , SELECT * FROM sysobjects ) SqlTest1sysobjects INNER JOIN sysobjects ON sysobjects.name = SQLTest1sysobjects.name WHERE SQLTest1sysobjects.type = u Notice that this uses a table alias to reference the OPENROWSET return values; it filters on the object type from the remote table and does a join. So it really does work as a table does. But the syntax is incredibly cumbersome. You re probably hoping there s some shortcut you can use to avoid all the complexity. Well, read ahead to see how that can happen. Creating a Linked Server You can think of a linked server as a prebuilt set of arguments for OPENROWSET stored in a database object. You can just create an object called a linked server, and create it with all the attributes that you d normally use in a call to OPENROWSET. Then, rather than having to type all those parameters in over and over again, you can just use the linked server.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 287 datasource. This is the name of the file, server, or whatever the OLE-DB provider needs to figure out what it should be talking to. user_id. This is the user name that the provider understands. Some providers may not need a user name, so this could be blank. password. This is the password that the provider understands. Some providers may not need a password, so this could be blank also. provider_string. This is a free text field that has everything else that the provider needs to initiate the connection. Only the provider knows for sure what this is; you ll have to dig through piles of documentation. catalog. This is the name of the database, catalog, or whatever the provider understands. It s the top level of the hierarchy of object names. It s also likely to be blank if the provider doesn t use it. schema. This is the name of the owner of the object. It also may be blank if the provider doesn t use it. object. This is the name of the object being manipulated. Believe it or not, this may be blank if there s only one object in the datasource, such as a text file. query. This is a string that s provider-specific, and it s passed directly to the provider as a query. It may be a SQL query; it may be something else. It s also not processed by SQL Server, so you should make sure that the syntax is valid before you send it, otherwise you ll get a very incomprehensible error message. If you are somehow missing the point, nearly every single argument in the entire OPENROWSET function is optional, and it s use or lack thereof depends on the provider you are using. Experience dictates that the documentation and examples for how to use the particular provider in which you are interested will be either non-existent or inaccurate. In other words, good luck.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

NOTE 286 Part I EXAM PREPARATION Which OLE-DB Providers Are Installed? You can find a list of the OLE-DB providers that you have installed in the Registry at HKEY_LOCAL_MACHINESoftware MicrosoftMSSQLServerProviders SQLOLEDB. stand how each different system works. The foundation of OLE-DB is the OLE-DB provider, which has to implement certain methods. At this level, an OLE-DB provider needs to know certain things to make a connection to the system. In the case of another SQL Server, the OLE-DB provider needs to know the name of the server and how to log into the server. In the case of a text file, the OLE-DB provider needs to know where the file is and what format the file is in, as well as other properties about the file, such as delimiters, whether the first row contains column names, and so on. The point here is that different providers have different requirements, but that after you get past those requirements they all work the same way as far as retrieving data. The only way to understand how to use the provider is to consult the documentation for that provider and for that version of the provider. That s not a very good answer it s not an answer that is going to make your life easier. Unfortunately, it s the only answer you re going to get. Using OPENROWSET The OPENROWSET function returns a rowset to SQL Server. It s used in a SELECT statement in the same place as a table. Here s the syntax of the OPENROWSET function: OPENROWSET( provider_name , { datasource ; userid ; . password } or provider_string , {catalog.schema.object} or query ) The exact definitions of the different arguments are in the list that follows, but here s the general idea of how the syntax works. You have to use a provider_name. You can either use datasource, userid, and password, or you can use a provider_string. You can also specify a catalog.schema.object or use a query. Notice that the datasource, userid, and password are delimited by semicolons. The reason for this is simple: to give the people who write the MCP exams something to trick you with. Here are the parameters for the OPENROWSET function. provider_name. This is the name of the provider, as specified in the Registry. It is not optional.

For high quality website hosting services please check java web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 285 Well, you now know all the parts of a SELECT statement that you ll need to know for the exam. SELECT is the backbone of SQL; it s how things get queried in the Structured Query Language. The structures you ve learned, especially the JOIN syntax, are used in the other main SQL statements INSERT, UPDATE, and DELETE and the syntax is the same as with SELECT. You ll find that CUBE and ROLLUP are useful, especially if you get into writing queries for reporting systems. MANIPULATING HETEROGENEOUS DATA WITH OPENQUERY, OPENROWSET, AND LINKED SERVERS . Manipulate heterogeneous data. Methods include linked servers, OPENQUERY, and OPENROWSET. So far, all the data you ve been able to access has been stored in databases on one SQL Server. This section talks about how to query data that s on other SQL Servers and in other systems without importing the data. Later sections in this chapter deal with how to import and export data with SQL Server. You can use three different types of syntax to access data; each one serves a different purpose. First, you re going to see OPENROWSET, which is the most flexible but hardest to use of the three. Then OPENQUERY, which is easier to use but requires a bit more setup. Then you ll see how linked servers are set up and used. Each of the three methods OPENROWSET, OPENQUERY, and linked servers relies on having an operable OLE-DB driver for the system you re trying to talk to. SQL Server 2000 ships with OLE DB drivers for SQL Server, text files, Oracle, IBM s DB/2, and others. To understand how to use each of these methods, you need to understand how to talk to an OLE-DB provider. A Brief Discussion on OLE-DB OLE-DB is a low-level database access protocol that is designed to enable a client to access different systems without having to under

For high quality website hosting services please check java web hosting website.

284 Part I EXAM PREPARATION When to Use CUBE and ROLLUP CUBE and ROLLUP should be used instead of COMPUTE and COMPUTE BY whenever possible. Why? Well, SQL Server s internal mechanics are better at figuring out how to optimize CUBE and ROLLUP because the syntax is simpler. Of course, it s hard to beat a WITH ROLLUP or WITH CUBE as far as simplicity of syntax. Also, CUBE and ROLLUP return a simple single resultset, instead of the extra stuff returned by COMPUTE and COMPUTE BY. In addition, in most cases a CUBE or ROLLUP provides you with the output you need, and the queries are shorter and simpler, making them easier to debug. So, now you know how to query data on a local server using different types of joins and how to summarize the data. What happens if the data isn t on just one server, but is on several servers, in several formats? The rest of this chapter is dedicated to talking to other servers, importing and exporting data, and dealing with XML to conduct business. REVIEW BREAK Review of Advanced Query Techniques So far, we ve covered a lot of material, from completing the SELECT statement to aggregating and summarizing data. We re about to move on to something completely different, so here s a review of what we ve done so far. . The JOIN operator, part of the FROM clause in a SELECT statement, is used to combine two related tables. . The UNION operator is used to combine the output from two SELECT statements into one rowset. . GROUP BY is used to create aggregates, which can then be analyzed with aggregate functions. . COMPUTE and COMPUTE BY are used to generate subtotals, and they create odd breaks in a SELECT statement. . CUBE and ROLLUP provide similar functionality to COMPUTE and COMPUTE BY, but SQL Server can do more optimization with them.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 283 9 NULL 15 10 41 6 10 NULL 6 NULL NULL 21 (6 row(s) affected) As promised, now it s time to see how to get rid of those darn NULLs everywhere. The GROUPING Keyword The GROUPING keyword is used to clean up the output of CUBE and ROLLUP to make the output look nicer. It provides you with a programmatic way to identify where CUBE and ROLLUP are putting NULLs, as opposed to where the NULLs are in the data being queried. It goes like this: SELECT CASE WHEN GROUPING(PersonID) = 1 THEN (Total) ELSE CONVERT(varchar(10), PersonID) END PersonID, CASE WHEN GROUPING(ProductID) = 1 THEN (Total) ELSE CONVERT(varchar(10), ProductID) END ProductID, SUM(QtyPurchased) as QtyPurchased FROM Sales WHERE personID > 4 GROUP BY PersonID, ProductID WITH ROLLUP Which yields the somewhat nicer looking resultset: PersonID ProductID QtyPurchased 9 37 10 9 38 5 9 (Total) 15 10 41 6 10 (Total) 6 (Total) (Total) 21 (6 row(s) affected) Now you don t have to mentally think Total ; you can actually put it into the output. The GROUPING function returns a 1 whenever the column is NULL because of a grouping summarization found in a CUBE or ROLLUP. Otherwise, it returns 0.

For reliable and cheap web hosting services please check cheap web hosting website.