# Sams Microsoft SQL Server 2008- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
70
lượt xem
12

## Sams Microsoft SQL Server 2008- P10

Mô tả tài liệu

Tham khảo tài liệu 'sams microsoft sql server 2008- p10', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: Sams Microsoft SQL Server 2008- P10

1. Code Examples 431 Dim message As String Console.WriteLine(“Current Jobs”) Console.WriteLine((“================================” + Environment.NewLine)) Dim job As Job For Each job In jobs If job.Status = JobStatusEnum.Running Or job.Status = JobStatusEnum.[New] Then ‘ New goes away soon Console.WriteLine(“————————————————”) Console.WriteLine(“JobID: {0}”, job.JobID) Console.WriteLine(“————————————————”) Console.WriteLine(“Action: {0}”, job.Action) Console.WriteLine(“Description: {0}”, job.Description) Console.WriteLine(“Machine: {0}”, job.Machine) Console.WriteLine(“Name: {0}”, job.Name) 24 Console.WriteLine(“Path: {0}”, job.Path) Console.WriteLine(“StartDateTime: {0}”, job.StartDateTime) Console.WriteLine(“Status: {0}”, job.Status) Console.WriteLine(“Type: {0}”, job.Type) Console.WriteLine(“User: {0}” + Environment.NewLine, job.User) runningJobCount += 1 End If Next job If runningJobCount = 1 Then message = “There is 1 running job. “ Else message = String.Format(“There are {0} running jobs. “, runningJobCount) End If Console.Write(message) If runningJobCount > 0 Then Return True Else Return False End If End Function The example’s output is shown here: Current Jobs ================================ ———————————————— JobID: jgbfw045virghsnsgvbv4n55 ———————————————— Action: Render Description: Machine: SSRS2 lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
2. 432 CHAPTER 24 RS Utility Name: Company Sales Path: /AdventureWorks Sample Reports/Company Sales StartDateTime: 9/29/2008 8:39:17 PM Status: New Type: User User: SSRS2\Administrator There is 1 running job. The command completed successfully Some other useful scripts that you can execute with the RS utility include the following: . Deploy new reports (RDL files) and data sources . Configure data sources . Download contents of the Report Server . Deploy to multiple servers simultaneously . Set up subscriptions . Refresh caches As you can see, the RS utility can be used to accomplish almost anything you could use the Reporting Services web service to do. The work would be in developing script files to do it. Summary This chapter serves as an introduction to the RS utility. The RS utility can be used in conjunction with the web service endpoints to automate routine tasks or to script difficult deployments. Scripts written for the RS utility are basically VB.NET single code files. They start off by executing the Main() function. The web service endpoints are available in the script, and are referenced as the global web service proxy called RS. End users can use passed-in string variables that are available as global variables in the script. Script commands can be batched, and timeouts can be specified. The RS utility is a handy administrative and development tool. lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. CHAPTER 25 IN THIS CHAPTER . Adding Embedded Code Implementing Custom . Debugging Embedded Code Embedded Functions The simplest form of custom code available in SSRS is expressions, which were discussed in Chapter 10, “Expressions.” The next level of coding complexity is custom embedded code in a report. Adding Embedded Code To get a better understanding of how embedded custom code is used in a report, report developers can assume all the code placed in the code window gets compiled into a make-believe class called Code. You can add properties and methods to the make-believe Code class, and call class members from expressions in the report, which has the class defined. Embedded code is compiled into a report’s intermediate format and gets executed as needed when the report is rendered. Report Designer provides a simple text area to allow editing of the embedded code. To access the code editor, complete the following steps: 1. From the Report menu, choose Report Properties. Alternatively, right-click the area surrounding the report’s body and select the Properties menu. 2. Click the Code tab in the Report Properties dialog box. You should see a window similar to that shown in Figure 25.1 and can type the code in the Custom Code area. The code editor is basically a multiline text box, and it does not provide any productivity assistance, such as IntelliSense or debugging. You have to be extremely careful while using lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. 434 CHAPTER 25 Implementing Custom Embedded Functions this code editor. For example, only one level of “undo” action is allowed, as compared to Visual Studio’s infinite undo. FIGURE 25.1 Code editor. For all its difficulties, embedded code provides several key benefits for a developer, includ- ing the following: . A more elegant approach (as compared to expressions) to medium-complexity coding scenarios . A single repository for functions and methods that can be reused by multiple items within a single report, as opposed to complex copy-and-paste expressions . Direct access to the exception-handling functionality of VB.NET Every developer knows function reuse is beneficial over copy-and-paste programming. Consider the following fictitious example. Suppose two developers are assigned the paral- lel development of a single report. One developer uses embedded code, whereas the other one uses simple expressions. One of the initial goals was to highlight negative values in red. Later, this goal changed to a more complex requirement, such as color-coding numeric ranges. The developer who used embedded code could adapt to such change in requirements quickly. Embedded functions must be written in VB.NET. If you prefer C#, you would have to develop a custom assembly. This topic is covered in Chapter 26, “Creating and Calling a Custom Assembly from a Report.” lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
5. Adding Embedded Code 435 To add the function used in the preceding narrative, just follow these steps: 1. Open the report properties by either choosing Report Properties from the Report menu or right-clicking the area surrounding the report’s body and selecting Report Properties. 2. In the Report Properties dialog box, display the Code tab and enter the following function in the Custom Code area: Function Highlight(value As Integer) As String If value < 0 return “Red” Else return “Black” End If End Function 3. Drag a Textbox control from the Toolbox to the report and place the following code in the Background Color property: =Code.Highlight(me.value) 25 4. Place -1 (minus one) in the Value property of the text box. 5. Open the report in Preview mode to see the text box with a red background. Embedded code can address more complex scenarios. For example, if you need to calculate a median, you can use the following approach: 1. Add the following embedded code, using the method described in the previous example: Dim Data As System.Collections.ArrayList Function AddData(newData As Decimal) As Decimal If (Data is Nothing) Data = new System.Collections.ArrayList() End If Data.Add(newData) End Function Function GetMedianInfo() As String Dim count As Integer = Data.Count If (count > 0) Data.Sort() GetMedianInfo =”Median: “ & Data(count/2) & “; Total orders: “ & count End If End Function lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6. 436 CHAPTER 25 Implementing Custom Embedded Functions NOTE In our sample we use the SalesOrderHeader table from the Adventure Works data- base. We will calculate the median on the TotalDue field (or median order total). Adjust the code accordingly for different fields. The query to retrieve data is very simple: SELECT TotalDue FROM Sales.SalesOrderHeader 2. Call the data set OrderTotals. 3. Add a table. (Note that in this example we use the table for calculations only, and we do not need to fill anything on the table’s surface.) 4. Select the entire table by clicking the corner handler. Set the table’s Hidden prop- erty to True. NOTE It is important to hide the table. Because of on-demand report processing enhance- ments in SSRS 2008, there is no guarantee that the Code class will maintain state across pages. You can experiment with this by making the table visible and seeing the changes in the results by applying TOP N clause to the query. 5. From the grouping pane, open Group Properties for the Details group, display the Variables tab, and enter the AddDat in the Name field and the following code in the Value field to populate Data array: =Code.AddData(Fields!TotalDue.Value) NOTE You can enter any value you want in the Name field. In the case of this example, the value that you enter for the name does not matter because we are not going to use the variable in the sample’s code. However the name is required, and SSRS will call the variable (and therefore the AddData function) every time the group variable changes. In this example, it happens for every row in the OrderTotals data set. 6. On the bottom of the report, add a text box with the following expression: =Code.GetMedianInfo() 7. Run the report and see that the median order value in the SalesOrderHeader table is $865.20 and the total number of orders is 31,465. You can take this example further and develop Excel-like functionality in terms of having calculations based on data in nonsequential Table cells, similar to =$A$1/$B\$5 in Excel. lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
7. Debugging Embedded Code 437 To enable the previously described calculations, we can store the values from a data set in a two-dimensional array and use the array’s members to perform calculations. Although for performance reasons we do not advocate this method on large data sets, the method can provide an elegant solution in some cases. Debugging Embedded Code SSRS does not provide any facilities to step through the embedded code, and therefore you have two options: You can either debug code in Visual Studio .NET or use some pre-Visual Basic tricks for debugging. The first trick is to label code lines. This is beneficial to locate both compile-time and runtime errors. The following code fragment shows how to label code lines. It also has errors that have been intentionally placed for demonstration purposes: Function Add(n As Integer) 1: i = i + n 2: return i End Function 25 When you build a report with the code snippet or try to preview the report that calls this code, SSRS reports two issues (one warning and one error): . Warning: There is an error on line 0 of the custom code: [BC42021] Function without an ‘As’ clause; return type of Object assumed. Warnings display only if at least one error is found. . Error: There is an error on line 1 of the custom code: [BC30451] Name ‘i’ is not declared. Only the first error is displayed. With a small code fragment such as the preceding example, finding errors might not be an issue. For a code fragment that has a significant number of lines, locating the one with an error can prove burdensome. NOTE Keep in mind that labels can be present only inside of functions or subroutines and can repeat inside of different functions. TIP To properly determine line numbers, deliberately add an error and preview the report. The SSRS error indicates the line number. TIP To avoid laborious efforts of numbering and renumbering lines, you should only label key expressions or the first line of functions. Alternatively, you can use labeling to nar- row down a line with an error. lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8. 438 CHAPTER 25 Implementing Custom Embedded Functions The second trick is to locate a line that causes runtime errors by using a Try-Catch block: Function DivByZero() Dim x As Integer Try ‘ Set up structured error handling. 3: x = x/ 0 Catch ex As Exception Return ex.ToString() & vbCrLf & “Exception at Line: “ & CStr(Erl) End Try End Function The result of the call to the function DivByZero() is as follows: System.OverflowException: Arithmetic operation resulted in an overflow. at ReportExprHostImpl.CustomCodeProxy.DivByZero() Exception at Line: 3 Note that function DivByZero() uses the undocumented function Erl to return a line number for the line of code that produced the error. Erl really returns a label number. (In the preceding code, it is 3.) When you do not implement error handling, and then make a call to a function within the Value property of a report item, the report item shows #Error as a result. Depending on the precision of a return value provided from a function, other potential results are Infinity or NaN (Not a Number). TIP Always check the Error List window after a build operation has completed, and make sure that there are no warnings. Best practice suggests eliminating all warnings in pro- duction code. Exceptions within other properties can be caught during the build operation. Summary Custom embedded code can provide a more elegant approach to medium-complexity custom code than expressions through function reuse, centralized code repository, and additional error-handling options. Custom embedded code is VB.NET code embedded in a report. Code is embedded as a part of a Report Definition Language (RDL) file and compiled together with the container report. Many errors are caught by the compiler when a reporting solution is built. lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
9. Debugging Embedded Code 439 Although embedded code allows a developer to use the full object-oriented functionality of VB.NET, embedded code is mostly used for simple logic. It is possible to develop complex embedded code, but this is not usually done because of limited debugging facili- ties and limited functionality of the embedded code editor. The embedded code editor is a simple text box that does not have the advanced features, such as code completion, avail- able in Visual Studio. When functions are too complicated for embedded code to handle efficiently or you prefer to use C# instead of Visual Basic, you can develop and call a custom assembly from a report. The next chapter explains how to leverage a custom assembly within a report. 25 lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
10. This page intentionally left blank lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
11. CHAPTER 26 IN THIS CHAPTER . Initializing Assembly Classes Creating and Calling a . Strong-Named Custom Assemblies Custom Assembly from . .NET Security Primer for an a Report SSRS Administrator . Assemblies That Require Other Than Execute Permissions SSRS comes with a comprehensive set of functions that . Debugging Custom Assemblies can be used within reports. However, you might need to add custom functionality that is not covered by the set of common functions or is too complicated for embedded code to handle efficiently. In addition, if you, as a devel- oper, are hard set on C# as a language of choice, a custom assembly is the way to go. A couple of examples of func- tionality that are better handled by a custom assembly are encryption and trend analysis. NOTE Trend plotting functionality is available in the full ver- sion of the Dundas chart. However, the chart does not provide trend calculation information to a report. In some cases, trend information might be needed to trigger some action, such as formatting on a report, and this is where trend analysis assembly might be useful. Let’s start with a simple example and develop a function GetLibraryInfo(), which returns a single string with a library version information. Start Visual Studio 2008 and create a new Class Library Project (you can add this project to the report development solution you worked with previously). To create a Class Library Project, follow these steps: 1. Let’s use C#, by selecting Visual C# from Project Types on a New Project dialog box. 2. Let’s name the project RSCustomLibrary. lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
12. 442 CHAPTER 26 Creating and Calling a Custom Assembly from a Repor t 3. Make sure to select a version of .NET Framework you prefer the library to use. 4. Select the Add to Solution option. 5. See Figure 26.1 for the outcome of the previous actions. 6. Click OK to complete. FIGURE 26.1 New library project. Visual Studio creates a project with a single class Class1. Let’s rename the file Class1.cs in Solution Explorer to MainClass.cs. Note how Visual Studio changed the name of the class in the code. Substitute code in the class module with the following code: using System; //System.Reflection helps to get the assembly information //using System.Reflection; namespace RSCustomLibrary { public class MainClass { //Method GetLibraryInfo() returns this custom assembly information //RSCustomLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null public static string GetLibraryInfo() { //return Assembly.GetExecutingAssembly().GetName().ToString(); lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
13. Creating and Calling a Custom Assembly from a Repor t 443 return “RSCustomLibrary, Version=1.0.0.0, Culture=neutral, PublicKey Token=null”; } } } Now you may ask, “Why did they comment operations with the Reflection library? Wouldn’t it be the best way to retrieve version information?” You are absolutely correct. The problem at this point is that our library only has Execute permission. This permission means that we have “access to the CPU” and we can do math and string operations, but we will get an error when accessing the Reflection library. (In this case, we added a call to the library as an expression in the Textbox1: [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type ‘System.Security.Permissions. FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.) NOTE When a developer calls a .NET namespace, it might not be immediately clear whether the Execute permission is sufficient. For example, the GetExecutingAssembly() method requires FileIOPermission. However, it might be logically concluded that the Execute permission is sufficient because the method retrieves information about the 26 assembly it is called from and the assembly should be loaded in the memory. Contrary to the conclusion, the call to this method does PathDiscovery to check the assem- bly’s path and therefore requires FileIOPermission. Later in this chapter, we discuss the actions that we need to take to allow an assembly to elevate its security from Execute-only permissions; see the “Assemblies That Require Other Than Execute Permissions” section. NOTE SSRS2K5 Report Designer allowed any permission for an assembly in the Preview mode. This would make developers wonder: “It worked in Preview mode, why doesn’t it work anymore after I have deployed it to the server?” SSRS 2008 Report Designer fixed this discrepancy and will force you to deal with permissions in Preview mode, too. TIP To simplify a custom assembly test, developers can use a simple Windows application to call the assembly’s methods. This allows testing the assembly’s functionality prior to tests with SSRS. The compiled assembly must be located in directories in which it is accessible by lease purchase PDF Split-Merge on www.verypdf.com to remove this watermark.