SQL SERVER – Unable to Start SQL Browser

My laptop is exposed to the internet and I have a passion to learn new software. Due to this, sometimes, I download some random software (unknowingly malware) on my machine and end up in infecting my laptop with a virus. These are never intentional. I think I have written earlier that I use a virtual machine for all such fancy stuff so that I am not losing any data. I have formatted my virtual machine endless times. This gives me the opportunity to learn by trying different things with confidence.

One fine day, I was unable to start SQL Browser service on the virtual machine. When I start, it goes to “Started” and back to “Stopped” automatically. I looked into Application Event Logs and found below error

Log Name:      Application
Source:        SQLBrowser
Date:          25-Dec-15 6:26:38 AM
Event ID:      20
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      PINALVM1.sqlauthority.com
Description:
The SQL Server Browser is closing. 
There are no instances of SQL Server or SQL Server Analysis Services.

The message was not showing me correct information because my machine had SQL Server running and I was able to connect as well. As per the documentation, I can start the SQLBrowser in console mode with -c switch. On my machine SQLBrowser.exe is in “C:\Program Files (x86)\Microsoft SQL Server\90\Shared” folder.

Here is the text of above run

C:\WINDOWS\system32>"C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" -c
SQLBrowser: starting up in console mode
SQLBrowser: Both SSRP and OLAP redirection services are disabled. Shutting down browser service

SSRP = SQL Server Resolution Protocol.

I started troubleshooting and used one of my favorite troubleshooting tool called Process Monitor. I downloaded and captured the process monitor while trying to start from command prompt. To avoid the noise, I added a filter as below

I also removed all “SUCCESS” entries to avoid further noise. Searched for SSRP and found below entries

SQLbrowser was looking for below two registry keys

HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser\AnalysisServiceListener

HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser\SsrpListener

On this virtual machine the keys were missing!!! I created as below

And bingo!!! I was able to start SQL Browser from command prompt

SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on ::[1434]
SQLBrowser is successfully listening on 0.0.0.0[1434]
[10020]: Waiting for next request...
[10468]: Waiting for next request...
[11292]: Waiting for next request...
[1628]: Waiting for next request...
[10020]: Received request...
[10468]: Received request...
[11292]: Received request...
SQLBrowser: starting up OLAP redirection service
[1628]: Received request...
[10020]: Waiting for next request...
[10468]: Waiting for next request...
[11292]: Waiting for next request...
[1628]: Waiting for next request...

Stopped via Ctrl + C and then started via configuration manager and started fine. Then I played with the values of registry keys and changed between 1 and 0 to see what happens. If both are missing or set to 0 then we would get an error. I think each one of them is for the SQL Server Engine and Analysis Service.

Have you ever done any troubleshooting using a process monitor? This is a powerful tool which I had learnt in my stint at Microsoft years back. Do let me know how you have used the process monitor on your servers for troubleshooting?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read More

Easy Way to Import and Export SQL Server Data

I know from my personal experience that many SQL Server novice users have issues while uploading and downloading data in various formats. In my practice, this kind of tasks arise very often. I would prefer to automate the process for the recurrent tasks, if the file structure is not going to be changed. I can create a SSIS package, deploy it on a server and then schedule one through the SQL Agent.

In practice, clients operate with numerous data formats (excel and xml). In some cases, Import and Export Wizard included in Microsoft SQL Server Management Studio helps greatly. However, I prefer dbForge Data Pump for SQL Server a new SSMS add-in from Devart. The tool allows me to quickly and easily upload and download data in a variety of formats.

Recently, I needed to upload data from a large XML file with quite simple structure:

<users>

<user Email="joe.smith@google.com" FullName="Joe Smith" Title="QA" Company="Area51" City="London" />

</users>

Let’s compare three approaches to resolving the problem: SSIS, XQuery, and Data Pump. We cannot use the SSMS Import and Export Wizard as it does not work with XML.

1. SSIS

In Integration Service, create a new Data Flow Task:

On the Data Flow tab, select the XML data source. Then we need to specify the XML file to load data from and generate the XSD schema:

Since all XML values are stored in the Unicode, I need to convert them to ANSI for the following insertion:

Then we need to specify where to insert data:

We need to specify the table to insert data to. In this example, I created a new table.

Then just press the Execute Results and check the package for errors:

The SSIS package creation process took 10 minutes. The package ran for 1 minute.

2. XQuery

Often, writing a query is much faster than creating a SSIS package:

IF OBJECT_ID('dbo.mail', 'U') IS NOT NULLDROP TABLE dbo.mail
GODECLARE @xml XML
SELECT @xml = BulkColumnFROM OPENROWSET(BULK 'D:\users.xml', SINGLE_BLOB) xSELECTEmail = t.c.value('@Email', 'VARCHAR(255)')
, FullName = t.c.value('@FullName', 'VARCHAR(255)')
, Title = t.c.value('@Title', 'VARCHAR(255)')
, Company = t.c.value('@Company', 'VARCHAR(255)')
, City = t.c.value('@City', 'VARCHAR(255)')INTO dbo.mailFROM @xml.nodes('users/user') t(c)

It took me 3 minutes to create the query. However, the execution time exceeded 9 minutes. The reason is that parsing server-side values is quite expensive.

3. dbForge Data Pump for SQL Server

Now’s the time to try Devart’s Data Pump.

In the Database Explorer shortcut menu, select Import Data.

Then we need to specify the file type and the file path.

Then we can select a table to insert data. The table can be created automatically.

Select the XML tag for parsing:

Now we can select what columns we need (from the selected tag).

Now we need to check the Use bulk insert option to reduce the load on the log file when inserting data.

Click Import and that’s it! The whole process took 2 minutes. This approach does not require any special qualification. The tool allows me to quickly and easily upload data in any formats convenient for my clients.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read More

Using Formulas in Microsoft Excel

This is the math lesson, and whether you love or hate math, by the end of this lesson you will agree that Excel makes math easy. We will start by explaining some of the basic concepts you need to understand regarding how Excel does math and then will get into entering formulas.

Lesson Goals

• Learn about math operators and the order of operations.
• Learn how to enter basic formulas.
• Use AutoSum to sum data with one click.
• Learn about absolute, relative, and mixed cell references.
• Copy formulas and functions.

Math Operators and the Order of Operations

Math Operators

The six mathematical operators you need to know to enter basic formulas in Excel are:

1. Addition. Plus sign (+).
2. Subtraction. Minus sign (-).
3. Multiplication. Asterisk (*).
4. Division. Forward slash (/).
5. Percent. Percent sign (%).
6. Exponentiation. Exponents sign (^).

Math Operators

When a formula uses more than one operator, the order of operations in Excel is as follows:

1. Parentheses. Operations contained in parentheses (or brackets) are executed first.
2. Exponents. Exponents are executed second.
3. Multiplication / Division. Multiplication and division, which Excel treats equally, are executed third, from left to right.
4. Addition / Subtraction. Addition and subtraction, which Excel treats equally, are executed last, from left to right.

Entering Formulas

The simplest (not always the easiest) way to enter a formula in Excel is to:

1. Select the cell in which you wish to enter a formula.
2. Press "=" on your keyboard.
3. Select the first cell which contains data you will use in your formula.
4. Type the operator (+, -, *, or /).
5. Select the second cell which contains data you will use in your formula.
6. If necessary, type additional operators and select additional cells. If some operations should be performed before others, enclose those operations in parentheses.

AutoSum (and Other Common Auto-Formulas)

Some formulas can be entered simply by clicking a button in Excel. Excel will even guess which data the formula applies to, meaning that if your spreadsheet is properly laid out, you don't have to select the cells.

AutoSum

To quickly sum a row or column of data:

1. Select the cell to the right or at the bottom of a set of numbers:
2. On the HOME tab, in the Editing group, click the AutoSum command:
3. Note that Excel guesses the data you wish to sum and gives you the option to edit the formula if you wish:
4. Press Enter.

Count Numbers

To quickly count the number of cells in a row or column that have data:

1. Select the cell to the right or at the bottom of a set of numbers:
2. On the HOME tab, in the Editing group, click the drop-down arrow to the right of the AutoSum command:
3. Select Count Numbers:
4. Note that Excel guesses the cells in which you wish to count numbers and gives you the option to edit the formula if you wish:
5. Press Enter.

Average

To quickly average the numbers in a row or column:

1. Select the cell to the right or at the bottom of a set of numbers:
2. On the HOME tab, in the Editing group, click the drop-down arrow to the right of the AutoSum command:
3. Select Average:
4. Note that Excel guesses the cells in which the data you wish to average lies and gives you the option to edit the formula if you wish:
5. Press Enter.

Min

To quickly find the minimum number in a row or column:

1. Select the cell to the right or at the bottom of a set of numbers:
2. On the HOME tab, in the Editing group, click the drop-down arrow to the right of the AutoSum command:
3. Select Min:
4. Note that Excel guesses the cells in which you wish to find the minimum number and gives you the option to edit the formula if you wish:
5. Press Enter.

Max

To quickly find the maximum number in a row or column:

1. Select the cell to the right or at the bottom of a set of numbers:
2. On the HOME tab, in the Editing group, click the drop-down arrow to the right of the AutoSum command:
3. Select Max:
4. Note that Excel guesses the cells in which you wish to find the maximum number and gives you the option to edit the formula if you wish:
5. Press Enter.

Copying Formulas and Functions

Formulas can be copied from one cell to other cells in Excel. To copy formulas in Excel:

1. Select the cell which contains the formula you wish to copy.
2. On the HOME tab, in the Clipboard group, click the Copy command.
3. Select the cell into which you wish to copy the formula.
4. On the HOME tab, in the Clipboard group, click the Paste command.

Another way to copy formulas is by using Autofill, or dragging the Fill Handle in the cell containing the formula to the other cell or cells in which you wish to copy the formula. Autofill, which was introduced earlier in this course, is especially useful for copying formulas.

To use Autofill to copy formulas:

1. Enter a formula in one cell:
2. Click on the Fill Handle, which is located in the bottom right cell of the selected cells:
3. Drag the Fill Handle for as many rows or columns as desired:
4. Release the mouse to enter the formula into the desired cells:

Displaying Formulas

When working with formulas, if you wish to display the formula within its associated cell in a worksheet, in Excel 2013, select the FORMULA tab and in the Formula Auditing group, select Show Formulas.

You can toggle this option on and off by selecting and de-selecting this option.

Relative, Absolute, and Mixed Cell References

Relative Cell References

Note that in the example above, when we copied the formula from one cell to others, the formula automatically changed to add the cells in the corresponding rows:

1. The formula in cell E4 added B4, C4 and D4.
2. The formula in E5 added B5, C5 and D5.
3. etc...

This happened because the formula we entered contained Relative cell references.

Relative cell references are the default in Excel. They are called "relative" because the actual cell row and column used in the formula are not significant. Instead, the significant thing is the location of the cell used in the formula relative to the location of the cell in which the formula is entered.

The way to think of the formula E4 = B4 + C4 + D4 is Result = (cell 3 places to the left) + (cell 2 places to the left) + (cell 1 place to the left) of the cell in which the Result is entered.

Absolute Cell References

Sometimes you want a formula to refer to a specific cell regardless of where in your worksheet the formula is executed. In the following example, the formulas in column F all refer to cell B1:

The way to tell Excel that a formula should always refer to a specific cell is to use dollar signs ($). In the above example, the formula we used in cell F4 is =E4 / $B$1. No matter where we put this formula in our worksheet, it will divide the cell one to the left of the cell in which we enter the formula by cell B1.

Finally, notice that we entered the dollar sign twice in the above formula, before the "B" and before the "1". The first $ made the column an absolute reference and the second made the row an absolute reference. In this example, it was not actually necessary to make the column an absolute reference. The takeaway here is that when you refer to a cell in a formula, you can make either or both of the column and row references Absolute or Relative.

Mixed Cell References

Mixed cell references are a combination of relative and absolute references.

Just like in absolute cell references, in mixed cell references the dollar sign ($) is used to refer to a specific cell that is fixed.

An example of a mixed cell reference would be: $F4. This would mean that the the column letter, F, is fixed, while the row number is allowed to be copied from cell to cell.

Working with Formulas

Duration: 15 to 25 minutes.

In this exercise, you will use formulas to enter additional data into the quarterly profit and loss statement we are creating for Dave's Lemonade Stand.

1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Working with Formulas.xlsx from your Webucator/Excel2013/Exercises folder.
2. Use AutoSum to sum the Total Income in row 5.
3. Enter a formula to make payroll 30% of Total Income each quarter.
4. Enter a formula to make marketing 25% of the each quarter's Total Income.
5. Enter a formula to make Supplies 20% of Total Income each quarter.
6. Use AutoSum to sum the Total Expense in row 10.
7. Use a formula to calculate Net Income (Total Income - Total Expense).
8. Enter "Margin" in cell A12 and then use a formula to calculate the Margin (Net Income / Total Income).
9. Format all cells to look like they do in the below image:
10. Save the workbook.

Solution:

1. Open or go to the specified file.
2. Use AutoSum to sum the Total Income in row 5.
   1. Select cell B5 and click AutoSum and press Enter.
   2. Grab the Fill Handle in cell B5 and drag the formula in cell B5 to cell E5.
3. Enter a formula to make payroll 30% of Total Income each quarter.
   1. In cell B7, enter "=B5*.3"
   2. Grab the Fill Handle in cell B5 and drag the formula in cell B7 to cell E7.
4. Enter a formula to make marketing 25% of the first quarter's Total Income each quarter (so all formulas refer to cell B5).
   1. In cell B8, enter "=$B$5*.25".
   2. Grab the Fill Handle in cell B8 and drag the formula in cell B8 to cell E8.
5. Enter a formula to make Supplies 20% of Total Income each quarter.
   1. In cell B9, enter "=B5*.2".
   2. Grab the Fill Handle in cell B9 and drag the formula in cell B9 to cell E9.
6. Use AutoSum to sum the Total Expense in row 10.
   1. Select cell B10 and click AutoSum and press Enter.
   2. Grab the Fill Handle in cell B10 and drag the formula in cell B10 to cell E10.
7. Use a formula to calculate Net Income (Total Income - Total Expense).
   1. Select cell B11 and enter "=B5-B10".
   2. Grab the Fill Handle in cell B11 and drag the formula in cell B11 to cell E11.
8. Enter "Margin" in cell A12 and then use a formula to calculate the Margin (Net Income / Total Income), which should be displayed as a percent.
   1. Enter "Margin" in cell A12.
   2. In cell B12 enter "=B11/B5".
   3. Grab the Fill Handle in cell B12 and drag the formula in cell B12 to cell E12.
   4. Select cells B12:E12 and click the Percent command.
9. Make any necessary formatting changes.
10. Save the workbook.

Read More