Map exisiting SQL Server logins

Stored in Vault
I always forget this system procedure sp_change_user_login. You need this when you perform a database backup/restore from one SQL Server to another. The restore process doesn’t map database logins with SQL Server logins so in effect you have an orphaned database login. You can’t simply delete this account either so what we want to do is remap the database login to the SQL Server login.

sp_change_users_login
EXEC sp_change_users_login ‘Update_One’, ‘MaryB’, ‘MaryB’;

There are a couple of other options:
Report allows you to list all orphaned users
Auto_Fix checks if a database login and to SQL login have the same name, if not it creates ones.

http://msdn2.microsoft.com/en-us/library/ms174378.aspx

Reporting Services 2000 – Keep Goup Together

Why doesn’t RS 2000 have a Keep Together property for groups (like MS Access, Crystal) that doesn’t allow group records to be split across mulitple pages?

After a bit of digging  I did find a Keep Together property for a table. So I put a nested table inside my group and set this property to True. This then keeps the table records together for the group and doesn’t split the group records across multiple pages.

Does RS 2005 have a Keep Together property for a group?

Sql Server Analysis Services 2005 Performance Guide

Microsoft has recently released their SSAS 2005 performance guide

Major topics from the contents page include:

• Enhancing Query Performance
  • Understanding the querying architecture
  • Optimizing the dimension design
  • Maximizing the value of aggregations
  • Using partitions to enhance query performance
  • Writing efficient MDX
• Tuning Processing Performance
  • Understanding the processing architecture
  • Refreshing dimensions efficiently
  • Refreshing partitions efficiently
• Optimizing Special Design Scenarios
  • Special aggregate functions
  • Parent-child hierarchies
  • Complex dimension relationships
  • Near real-time data refreshes
• Tuning Server Resources
• Understanding how Analysis Services uses memory
• Optimizing memory usage
• Understanding how Analysis Services uses CPU resources
• Optimizing CPU usage
• Understanding how Analysis Services uses disk resources
• Optimizing disk usage

Need to script one object per file in SQL Server 2005

For some strange reason beyond comprehension the SQL Server 2005 team removed the ability to script out one object per file. The only option is to script out selected objects into one (large) file.

I would script out one object per file when I felt it was time to add my SQL Server objects to source control. This easily allowed my to script out my schema objects in their own file which I then simply added to a Database project and bound to source control.

Anyway, I then came across scriptio. A cool utility (with source) that allows you to….yes, script out one sql object per file

It uses click once deployment to install and manage updates also, very cool.

SQL Down Under Code Camp

I have just been reading Greg Low’s blog and guess what is happening at Wagga Wagga on the 7th & 8th of October, 2006…..yes you guessed it. The inaugral Austrailan SQL Down Under Code Camp.

This will be a very cool event and with so many new features in SQL Server 2005 this would be a great place to talk/listen to the leaders in the industry.

DBPro edition in VSTS

I have just been reading Greg Low's blog about DBpro.

Definately a product that the industry has been asking for for a very long time and will hopefully give some control and confidence back to database development and deployments.

SQL Express: Great product

I have been using SQL Express for a clients project over the last few months, and I must say wow!

Microsoft have really improved on their previous free SQL product MSDE. For one, SQL Express at least has "SQL" in the name for clear identification.

• Supports only one CPU.
• 1 GB of memory
• 4 GB of limit for database size – excluding log files. However, there is no restriction on number of databases that can be present on the server.
• Unlike MSDE, SQL Server Express does not implement a ' (5) concurrent workload throttle' mechanism to limit its scalability under heavy load. Many people confused this with 5 concurrent connections which isn't the case
• Unlike MSDE, this edition does not include SQL Agent, DTS, and replication publishing.
• Does not support Windows 9x, Me, and NT.
• SQL Server Agent service is not available
• AND……. the best feature is that you now have a database management UI which is a consistent look and feel across all SQL 2005 editions.

New Features

Michael Otey has blogged about the new features here

Abstract from blog 

"As a part of the release of SQL Server 2005 Service Pack 1, we’re happy to announce the availability of a new package of SQL Server Express called Advanced Services.  Advanced Services includes SQL Server Reporting Services and Full Text Search, along with SQL Server Management Studio Express and SQL Server Express Toolkit (used to build reports).  SQL Server Express also continues to be available without the complementary features to keep a small download package for those who require it.  For a more in-depth comparison of the versions, check out the feature comparison page we’ve created to help with deciding which version of SQL Server Express is right for you."

How cool is this, Reporting Services, Full Text Search. This is a great product and I applaude Microsoft for providing this free of charge. My clients reports were all completed in Crystal so now I can move them across to reporting services.

SQL Prompt: Free Intellisense for SQL Query Analyzer!

 

Redgate have a SQL Query Analyzer tool that you can download for free until September 1st!

Works with SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32 and there are no time restrictions.