Quite often, MS SQL Server users are faced with a task converting data from a format database MS SQL Server to Excel format. If you know the sequence of actions to complete this task, then you can make sure that this is very simple.
As an example, suppose you need to convert the data of a product directory into Excel - the Tovary plate of our OOO_RogaKopyta training base.
To complete the first step, we get to the table we need in the Object Browser panel (in Management Studio, of course) and select the right button in the context menu Select firstlines :
Note How to change the number of rows for selection by the right button, you can seehere. Well, and besides, in a similar way, we model the receipt of the results of some sample. It is quite obvious that instead of the given query of type Select Top 1000 there can be absolutely any other query that returns at least one line.
Next, in the Results table, click on the upper left corner:
Now we get anywhere on the records selected in this way and select in the context menu Save the result as. :
. and save our data in the format Csv:
Note By the way, the manipulation described above will help answer the popular question - how to output the query results to a text file
Let's continue. Now switch to Excel, go to the menu File>Open and choose Text files :
We are looking for the Tovary file just created above:
We get to the next data reading form Test File Import Wizards:
Click Next and select the separator character. Semicolon :
Click Next again:
Now it remains to choose the form above Done and get the result we need - the table in Excel:
There is another, maybe even simpler and more affordable way to upload to Excel - in the grid of query results in Management Studio, in the context menu (right-click), select Copy with captions . :
. and immediately paste the contents of the clipboard into Excel:
That's all for now ... Good luck
With another approach to the task of import / export from Excelcan be foundhere
Source data for creating a report
As I said, as a constructor we will use the environment SQL Server Business Intelligence Development Studio, and as a data source we will have a test table in a test database located on SQL Server 2008 R2.
The table we will have is the following (description of the fields below):
- > In other words, our table contains a list of goods, their price and the cities in which they were produced.
By the way, if anyone is interested in the topic of SQL Server and Transact-SQL, then I recommend reading the following materials:
- T-SQL Programmer's Way - Transact-SQL Tutorial
- Transact-SQL Reference - A description of the basic construction of the Transact-SQL language,
- T-SQL programming basics - Describes the Transact-SQL programming basics.
The data itself we will have, for example, the following
And based on this data we will build a report.
Supported HTML Tags Supported HTML Tags
Below is a complete list of tags that are prepared for viewing in HTML format, if they are defined as placeholder text. The following is a complete list of tags that will render as HTML when defined as placeholder text:
Title, style and block elements:,
Text formatting:,,, Text format:,,,
Work with lists:
- , List handling:
All other HTML markup tags are skipped during report processing. Any other HTML markup tags will be ignored during report processing. If the HTML code represented by the expression in the placeholder text does not have the correct format, then the placeholder is prepared for viewing in plain text. If the HTML represented by the expression in the placeholder text is not well formed, the placeholder is rendered as plain text. All HTML tags are case-insensitive. All HTML tags are case-insensitive.
If the text in the text field contains only one block of text, then any HTML code in the placeholder that defines the elements of the block is prepared for viewing correctly. If the text in your text box contains only one block of text, any HTML in the placeholder that defines block elements will render correctly. But if the text field has several blocks of text, then the HTML tags are skipped and the text structure is determined by blocks of text. However, if the text box has multiple blocks of text, the HTML tags are ignored and the structure of the text is defined by the blocks of text.
If more than one tag is defined for the text and if Reporting Services Reporting Services encounters a conflict between HTML code and existing report restrictions, then only the innermost HTML tag will be considered as HTML code. If more than one tag is defined for text, and Reporting Services Reporting Services detects a conflict between the HTML and existing report constraints, only the innermost HTML tag will be treated as HTML.
Limitations of Cascading Style Sheet Attributes
When using cascading style sheet (CSS) attributes, only the core set of tags is defined. When using cascading style sheet (CSS) attributes, only a basic set of tags are defined. The following is a list of attributes that are supported:
text-align, text-indent, text-align, text-indent
Only valid report size definitions in absolute CSS length units are supported. Only valid RDL size values, in absolute CSS length units are supported. Supported units: in, cm, mm, pt, pc. Supported units are: in, cm, mm, pt, pc.
Relative CSS length units are skipped and not supported. Relative CSS length units are ignored and not supported. Unsupported units include em, ex, px,%, rem. Unsupported units include em, ex, px,%, rem.
padding, padding-bottom, padding-top, padding-right, padding-left, padding, padding-bottom, padding-top, padding-right, padding-left
Below are some guidelines for using CSS. Here are some considerations for using CSS:
Incorrect format CSS values are skipped the same way as incorrect format HTML code. Malformed CSS values are ignored in the same way as malformed HTML.
If an attribute and CSS style attributes exist in the same tag, then the CSS property takes precedence. When both attribute and CSS style attributes exist in the same tag, the CSS property has a higher precedence. For example, if the text is
, only the text-align attribute will be applied and the text will be right-aligned.
For CSS attributes and styles, if a property is set more than once, only the last instance of the property is applied. For attributes and CSS styles, if a property is specified more than once, only the last instance of the property is applied. For example, if the text is
Database Profile Component Profile Settings
In order to configure the Database Mail account, you need to in the tree "Object Browser" (Object Explorer) select a custom instance of SQL Server, go to "Control"(Management), hereinafter"Database Mail Component"(Database Mail). By right-clicking on this item, a context menu will open, in which you must select "Tune…»(Configure ...), after which the setup wizard window will open.
DatabaseMail component in Object Explorer
You can skip the first page, so go straight to the second. Here we need to select the first item “Install Database Mail ...” (Setup Database Mail ...) and click "Further" (Next). On the third page, set the name and description of the profile, then press the button Add to add an SMTP account. In the window that opens, fill in the account information (e-mail address, server and SMTP port, as well as authentication parameters).
Database Mail Configuration Wizard Create a new Database Mail profile
On the next page, you must set the profile security: specify whether it is open or private (and for which users), and you can also specify whether the profile is the default. For our purposes, it is enough to make the profile open and the default profile. On the fifth page we leave everything by default, on the last page click Done (Finish)
Configuring Database Mail Profile Security
After the profile is configured, it must be checked, for this, in the context menu of the item “Database Mail Component” (by right-clicking on this item), select "Send a test message" (Send Test E-Mail). In the window that opens, fill in the field “To” (To) and click "Send ..." (Send ...)
Sending test emails using Database Mail
If everything is done correctly, then in the near future a test letter will be delivered to the mail.
Add alert operator
Operators are pseudonyms for people or groups that may receive electronic notifications when tasks, tasks, or alerts are complete. To add a new alert operator, you need to in the tree "Object Browser" (Object Explorer) select a custom instance of SQL Server, go to SQL Server Agent (SQL Server Agent), next "Operators" (Operators). By right-clicking on this item, select Create Operator (New Operator), after which the alert operator properties window will open.
Operators in the Object Explorer Tree
Operator settings are on the tab "General" (General). Here you must fill "Name" (Name), state "On" (Enabled), enter an email address. In general, there are alternative notification methods besides e-mail: using the net send command or by sending a message to a pager.
Setting Operator Properties
This completes the operator setup, go to the next step.
SQL Server Agent Mail Configuration
This item is required for sending email notifications by SQL Server Agent. Such mailing occurs, for example, to notify about the status of the task. To configure agent mail, you need to in the tree "Object Browser" (Object Explorer) select a custom instance of SQL Server, go to SQL Server Agent (SQL Server Agent), select from the context menu "Properties" (Properties).
SQL Server Agent in the Object Explorer Tree
In the window that opens, go to the tab Warning System (Alert System), check the box Enable Email Profile (Enable mail profile), leave as the mail system “Database Mail Component” (Database Mail) and select the previously created profile in the appropriate field.
SQL Server Agent Alert System
Enabling triggers and alert tasks
For the basis for connecting alerts, take the service plan from the article “Service Plans” mechanism and the MS SQL Server task engine.
For the convenience of checking, we’ll configure alerts to successfully complete tasks; in real life, it makes more sense to connect alerts in case of an error. In order to demonstrate, we’ll set up alerts for our "The task" (Job), and also add an alert to our service plan.
So, let's open our service plan, in it we will allocate a subplan "UpdateStatistics". Drag the task onto the work area of the subplan Operator Notification (Notify Operator Task). We stretch the arrow from the task "Updating statistics". Double click on a task Operator Notification open its properties, mark the created operator, and also introduce Theme (Subject) and "Text" (Message) letters. To change the alert condition (default view arrow "Successful completion") you need to right-click on the arrow and select its type: successful execution / error / execution.
Adding an Operator Notification Task to a Service Plan Setting properties of the "Notification of the operator" task
Now let's move on to setting alerts in "Tasks" (Jobs). Open the properties of our task and go to the tab Notifications (Notifications). Set the daws next to the types of notifications that we want to use (for me it is only email), then we will select the operator and the notification condition (successful completion / completion with an error / any completion).
Setting Job Properties
Checking the health of alerts
Let’s run our task manually, for this, right-click on it and select "Run the task in step" (Start Job at Step). As a result, 2 letters should come to the mail: one (with the subject and text set by us) corresponds to the task Operator Notifications in terms of service, the second - informs about the task as a whole.
Database Mail Notifications
If you haven’t received a letter then, it’s possible:
- Error in SMTP account settings. Try to send a test message, if it is not sent - double-check the parameters
- Error in profile security rights. Recheck if rights are set as described earlier. If everything is set correctly, but there is no result, try to specify all the rights: open and all private, as well as make the default profile for each right
If you didn’t receive only the letter with the result of the task: check that SQL Server’s mail is configured in accordance with the above.
Creating a report server project
Next, we need to call the project creation window, we can do this on the initial page, using the "Create project"Or through the menu"File -> Create -> Project».
After we need to create "Report Server Project", We can set the name of this project and indicate the path to the directory in which all the project files will lie.
Then, let's immediately set the address of the report server for our project, the address itself can be viewed in the Reporting Services configuration manager in the “Web service url", By default on the local computer it is - http: // localhost / ReportServer.
In BIDS, select the menu “Project -> Properties (Release configuration)” and in paragraph TargetServerURL indicate our address.
Note!The report server address must be specified for each project configuration.
Now we can immediately from B> Next, let's create one data source to connect to the SQL server, so as not to store this source in each report, but to store only a link to it. This is done so that in case of changing the address of the SQL server we do not have to edit all the reports, i.e. we will fix everything in one place.
For this inSolution Explorer" in point "Shared Data Sources"Right-click"Add New Data Source».
Then we indicate the name of our source and, accordingly, we choose what kind of source. In our example, this is "Microsoft SQL Server."
To specify the connection string, click Edit, and fill in the relevant data, i.e. in our case, it is localhost, the test database. Also here you need to set the authentication type, if you specify the type "Use Windows Authentication”, Then the SQL server will be logged in on behalf of the account under which you loaded the operating system. If you specify "Use SQL Server Authentication", Then, accordingly, a special account must be established on the server and, of course, the server itself must be configured for this authentication method. For an example I will use Windows authentication.
You can click "Check connection", And if all is well click"OK". After which the connection string will be displayed, and we can click "OK»To create a common data source. In the Solution Explorer, we’ll have the newly created data source.
Now let's create the report itself, for this, in the Solution Explorer, right-click on the “Reports -> Add -> Create Item»
Note!If you noticed that there is a “Add a new report” item, but if you click it, then the report creation wizard will start, it’s more convenient for me to create reports from scratch without using the wizard.
Then in the window "Adding a New Item"In the templates we select"Report”And in the item name we set the name of our report.
Then you can configure the environment as you like, but so that you would have the following windows at hand: Solution Explorer, Toolbox, Properties, and Report Datasince these windows will be required often enough.
For example, it’s convenient for me like this
Now create a data source for the report, i.e. as agreed, create a link to a common data source. To do this, in the "Report Data"Click"Create -> Data Source»Or you can also right-click on the data sources item.
Where we set the name of the data source and select "Use a link to a shared data source", Click"OK».
Then we add a data set, I suggest using a simple query that will select all the data from our test table
Click "Create -> Dataset"Or again through the right button.
In the window "Dataset properties"We set the name of the data set, select"Use the dataset embedded in the report"And in the request text, respectively, insert our request, click"OK»
It remains for us to design the information output template itself, for this purpose on a layout with "Toolbars"Drag and drop"Table". This item is great for displaying tabular data.
Now you can drag the fields from the dataset to this table to display.
In principle, you can immediately check the operation of this report, for this, go to the “View", I.e. thus, you can debug without publishing the report to the server.
And to publish the report and the entire project on the report server, you need to in the menu item "Building"Click"Expand Test Project»
After which, if you go to "Report Manager", The default is http: // localhost / Reports, then you will see that you have there appeared a directory "Test project"Which contains the report"Test”, As well as an appropriate data source. To run the report, go to the directory and click on the report.
I propose to end here, in the following materials we will consider the process of creating more complex reports using groupings, parameters, interactive sorts, and other cool features of SQL Server Reporting Services. Good luck