Weighted Averages in SQL Server

Here is a screen shot of my brain dump regarding weighted averages.

The Financial Dictionary says:

Weighted Average definition

An average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average.
Investopedia Commentary
To demonstrate, let's take the value of letter tiles in the popular game Scrabble.
Value: 10 8 5 4 3 2 1 0
Occurrences: 2 2 1 10 8 7 68 2
To average these values, do a weighted average using the number of occurrences of each value as the weight. To calculate a weighted average:
1. Multiply each value by its weight. (Ans: 20, 16, 5, 40, 24, 14, 68, and 0) 2. Add up the products of value times weight to get the total value. (Ans: Sum=187) 3. Add the weight themselves to get the total weight. (Ans: Sum=100)
4. Divide the total value by the total weight. (Ans: 187/100 = 1.87 = average value of a Scrabble tile)

here's a start:

SELECT     ItemToCount, price AS value, COUNT(ItemToCount) AS weight, COUNT(ItemToCount) * price AS value_times_weight FROM         SalesTable GROUP BY ItemToCount, price ORDER BY ItemToCount

research used:
(I played “with rollup” to give me subtotals):
http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx
Webster’s Dictonary lookup on Weighted Average:
http://dictionary.reference.com/browse/weighted+average

Happy Mother's Day to All!

My daughter made me cry... on Mother's Day!

Alert on Job Failure (email alert)

I have had such luck getting a single failure alert to happen.  Here’s what I did.
I presume you have an email account set up in SQL Server.

Right-click SQL Server Agent –> Properties

Select Alert System.
Turn on Enable mail Profile and click OK. 
Then repeat by turning it off! Then turn it back on.

Then, I created a job called fail that would force to fail.
Right-click the ill-fated job –> Properties
 
select Notifications
don’t mess with Alerts (they annoy me at times; my ignorance shows).

Make the ill-fated-job  run so it can fail; check your mail.  If you have enabled mail properly, (turn off + turn on SQL Alert system) you typically won’t have any problems

Error: the encrypted value for the "logoncred" configuration setting cannot be decrypted

I got an error:

”the encrypted value for the "logoncred" configuration setting cannot be decrypted”

open cmd as Administrator
Navigate to: C:\Program Files\Microsoft SQL Server\90\Tools\Binn
RSKeyMgmt.exe should be in that directory.
type: rskeymgmt –d and it overrides the Reporting Services Management Console

Merged Cells in Reporting Services

 
When a report with headers is exported to Excel, you will most likely get merged cells.

 
This can mess up sorting inside your row data amongst other things.
[insert grumbling here]

How to enable SimplePageHeaders=True

A little Homework: Encryption Keys

Before you modify the xml file (.config file), you may stomp on the encryption data.

Encryption keys are based partly on the profile information of the Report Server service. If you change the user identity used to run the Report Server service, you must update the keys accordingly. If you are using the Reporting Services Configuration tool to change the identity, this step is handled for you automatically.

If initialization fails for some reason, the report server returns an RSReportServerNotActivated error in response to user and service requests. In this case, you may need to troubleshoot the system or server configuration. For more information, see Troubleshooting Initialization and Encryption Key Errors.

source: MSDN doc 157133

Ok… you ready to go ahead and modify the config file?

Modify RSReportServer.Config file on the server you need changing.  (do it locally to test first).

rsreportserver.config file location:

If you have a default installation , the location is C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer

Inside the file, look for Extension Name="EXCEL"

1. back up your config file
2. You will have to add a few tags inside the .config file.  Something like this:

Microsoft gives you a very nondescript article about Excel Device Information, but defines terms for you.


NO need to restart the file; it is an xml file.  source
source:

msdn article
msdn discussion site
one of my top discussion/content sites I love: simple-talk
Special thanks to Mike Schetterer MSFT

Navision 5 finsql.exe error

This came about when SQL Server cut out and was restarted.
A gal left for lunch with Navision minimized, opened it back up and found this error.
After searching across the internet for this error, I found that there are other causes.. hopefully this helps someone someday..

Who gets to ride with Mom this time?!!

Ava, Chase and Pomp are on their best behavior...

Dog Agility Videos I've enjoyed

Watching Elise Paffrath's videos have been an enjoyment.  I found her through ExpertVillage

SQL Server 2008 (Developer's version!)

If you are getting your feet wet into SQL Server, this is the license to get.

I bought a developer version of SQL Server 2008.  I am certified in SQL Server 2005 and  am slowly gaining in the learning curve with 2008.
If you have the money, buy it.  The developer license is exactly like the Enterprise edition (gobs of money!). 

Remote servers

How can you talk to another server on your network or across the internet?

There a lot of ways to skin a cat, and there are many ways of getting your through to the database you need to talk to.

Have on hand:

• A personal computer you can play with (SQL Server Express?)
  
• A windows login that is godlike (that can map to another computer). Password, etc.
  

Permissions

Practice on a personal computer you can play with -- SQL Server Express installed.
Then try it on a server on a network. If you gradually get the mapping of permissions down, you will understand how it works.

Think of a linked server as a rope bridge. The same permissions must be duplicated to the other server.
Windows permissions is a bit more tricky than that of a SQL login. You will find lots of discussions and many people relenting to SQL login permissions (I own my issue on that account!).

Try a script

What you need to do to make this work:

'[domain\[username]' : replace the text in the below script '[domain\[username]' with your own type of windows login.
this windows user name must match permissions exactly on the target server

'mylinkedserver' - you probably don't have a server called 'mylinkedserver', so find the name of the server/instance of your database you want to link to)..

EXEC master.dbo.sp_addlinkedserver @server = N'mylinkedserver', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=N'[domain]\[username]',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'use remote collation', @optvalue=N'true'

 
Troubleshooting: test the connection:



If you fall into any kinds of errors (I made a fake server to emulate an error):



Then, click the advanced technical error



Select the Message areas to get an error number and google the error number or the phrase; you will find some good some bad advice; stick to Microsoft or reputable websites.




 
If you have no errors, then you will call a table from the other server:
Select * from mylinkedserver.ReportServer.dbo.Catalog


 
Related articles (linked servers between 2005 and 2008)

Calling a linked server (microsoft's site)

Use this helpful documentation when scripting out linked servers!

Monitoring all kinds of Reports

I got this from an article a while back and tweaked it a little.


If you want to view failed reports, you would run this:

SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.Status not like 'rsSuccess' ORDER BY TimeStart DESC

Well, I found a lot of these nifty monitoring reports, so I made this script

DECLARE @query VARCHAR(max) select @query='failed_reports' -- change to 'recent' , 'active'... IF(@query='recent') SET @query='SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID' IF(@query='active') SET @query='SELECT TOP 10 EL.UserName, Count(*) AS ReportsRun, Count(DISTINCT [Path]) AS DistinctReportsRun FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.TimeStart>Datediff(d, GetDate(), -28) GROUP BY EL.UserName ORDER BY Count(*) DESC '  IF(@query='popular_reports') SET @query='SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun, AVG((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS AverageProcessingTime, Max((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS MaximumProcessingTime, Min((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS MinimumProcessingTime FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.TimeStart>Datediff(d, GetDate(), -28) GROUP BY C.Path, C.Name ORDER BY Count(*) DESC' IF (@query='failed_reports') SET @query='SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID = C.ItemID WHERE EL.Status not like ''rsSuccess'' ORDER BY TimeStart DESC' print (@query)

Adding Windows Authentication (Navision xp_ndo.dll error)

When installing a fresh instance of NAV and you want Windows Authentication

(in stead of the not-so reccomended SQL login..)

I found a reputable link to resolve the .dll problem in the installation process.

source: msdn blogger Lohndorf 

USE master

GO
EXEC sp_addextendedproc xp_ndo_enumusersids, 'C:\Data\xp_ndo_x64.dll'
GRANT EXECUTE ON [xp_ndo_enumusersids]
TO PUBLIC
GO
USE master
EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Data\xp_ndo_x64.dll'
GO
GRANT EXECUTE
ON [xp_ndo_enumusergroups] TO PUBLIC
GO
-- then go to the folder you have it sitting in and give NETWORK SERVICE permission.

Database Stuck in Restoring State?

Is your database stuck in (Restoring…) state?

Easy!Here, I have a database called debug, stuck in the restoring state.I run this script:

RESTORE DATABASE [debug] WITH RECOVERY

Results should return something like this:

RESTORE DATABASE successfully processed 0 pages in 5.414 seconds (0.000 MB/sec).

show desktop configuration on background (without downloading an app to do it!)

I noticed an eHow article that gives you the computer information on the desktop:

http://www.ehow.com/how_5487644_display-information-windows-xp-desktop.html

Jessica's 21st Birthday

 Wow, what an evening.

I'm excited for my daughter's 21st birthday.  She looked so stunnng.
We had a fantastic time at North India Bar and  Grill in Fresno/Clovis area. Fantastic variety & the owner is there to greet you!

Thanks, Carissa for such a beautiful up-do.  You rock.  Thanks goes out to Erin for her picture-taking savvy (slap a tag on the camera "take pics of everyone" and pass it down).  


So, Jessica, it was absolutely wonderful to meet your friends! 

Deborah and Jessica

picture L--> R) Erin, Charlie, [me], Juanisha.

Kale, loving .. Chutney?  (and is that how you spell Kale??)