Wednesday, June 24, 2009

Multipurpose Row_Number() Function - Madhivanan

 

Multipurpose Row_Number() Function

One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes

Multipurpose Row_Number() Function - Madhivanan

Monday, June 22, 2009

Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?

Shudder.  If you need to, here’s the way automate Excel via PowerShell.  Just don’t stick this on a server someplace…

How Can I Use Windows PowerShell to Automate Microsoft Excel?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?

Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?

SSIS Junkie : ExecuteSQLExecutingQuery : SSIS Nugget

 

One of the custom log entries that I didn’t know about was the Execute SQL Task’s ExecuteSQLExecutingQuery and now having taken a look at it I wish I’d have known about it a lot sooner. The real power of this custom log entry is that it shows the SQL statement that gets executed against the data source which is especially valuable if the SQL statement is constructed using an expression.

SSIS Junkie : ExecuteSQLExecutingQuery : SSIS Nugget

Thursday, June 18, 2009

Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live

 

In Part 4 of this series, I discussed Internet Explorer setup.  To date, the series has been about steps needed to set up Kerberos Constrained Delegation with Windows Integrated Authentication.  While the first parts of the series were not a step-by-step guide in how to set up Kerberos, it was meant to provide an overall checklist of things to make sure were properly set up.  What happens if you have an issue with Kerberos after it is set up?  That’s what this part of the series is about.

Unfortunately, many Kerberos setups have problems because:

Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live

Tuesday, June 16, 2009

Lifehacker - Five Best Alternative File Copiers - File Management

 

If you do any serious file copying on a Windows system, you'll quickly discover that there are substantial limitations to the default file copier. Ease your file copying frustrations with these five alternative copiers.

Lifehacker - Five Best Alternative File Copiers - File Management

Thursday, June 11, 2009

Get Performance Tips Directly From SQL Server - SQLServerCentral

 

When SQL is compiled, an ‘optimal’ plan is created and stored in the plan cache, the plan contains details of table and index access. To produce this plan, the optimiser uses information from various sources, including indexes, statistics and row counts.

If the optimiser would like to use certain information but can’t find it, it adds details of what it wanted to use to the plan. Inspecting the plans for these details will help us improve the performance of our SQL.

For example, if a certain index could have been useful for a given SQL statement, the optimiser looks for that index, if that index is not found, the missing index’s details are stored together with the plan.

There are various performance related items we can search for including: missing indexes, columns with no statistics, and the occurrence of table scans.

This utility makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), so can be used by SQL Server 2005 or greater.

Get Performance Tips Directly From SQL Server - SQLServerCentral

Color Palette and the 56 Excel ColorIndex Colors

No wonder Excel graphs usually look like crap.   No 32 million color palette?

And who doesn’t use a color monitor with Excel these days?

Each Microsoft Excel workbook has a palette of 56 colors that you can apply to cells, fonts, gridlines, graphic objects, and fills and lines in a chart.  If you are using a color monitor, you can customize the shade and intensity of the colors in the color palette for each workbook.

Color Palette and the 56 Excel ColorIndex Colors

And some add-ons to get around this limitation.

http://color-palette.qarchive.org/

This step-by-step article explains how to change the color palette so that you can use custom colors for elements of the workbooks in Microsoft Excel. You can modify colors for many workbook elements, including the following elements:

  • Worksheet Tabs
  • Fonts
  • Charts and chart elements
  • Cells (fills and borders)

This article explains how to change the color palette so that you can use custom colors for these elements.

http://support.microsoft.com/kb/288412

There’s some “interesting” challenges keeping color consistent between Excel and PowerPoint too.

Retrieve List of Databases and their Properties using PowerShell

If you are a DBA monitoring more than 1 database server, Powershell is the way to go on a budget.

Problem
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.

Solution
One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We've already seen how to access the Server object - its properties and methods - using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.

Retrieve List of Databases and their Properties using PowerShell

Tuesday, June 09, 2009

Dynamic SQL execution on remote SQL Server using EXEC AT

 

Problem
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions.

In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.

Dynamic SQL execution on remote SQL Server using EXEC AT

Friday, June 05, 2009

SQL Server backup and recovery: Idera SQL safe backup

Useful script to backup all databases on a server with Idera SQL safe

alter proc dbo.sp_backupalldb
as

declare @dbname as varchar(100)
declare @fname as varchar(255)
declare @rownum as int
SET @rownum = 1

declare @dbcount as int
select @dbcount = count(*)
    from sys.databases
    where name not in ('tempdb')

while @rownum <= @dbcount
begin

select @dbname = name from (
    select name, row_number() over (order by name) rownum
    from sys.databases
    where name not in ('tempdb') ) dbs
where rownum = @rownum

if @@rowcount = 0 RETURN

print @dbname

set @fname = 'D:\Backup\' + @dbname + '.safe'

print @fname

EXEC [master].[dbo].[xp_ss_backup]  
@database = @dbname,  
@filename = @fname,  
@backuptype = 'Full'

set @rownum = @rownum + 1

end

SQL Server backup and recovery: Idera SQL safe backup

Default trace - A Beginner's Guide - SQLServerCentral

 

We have all been subject to or know someone who has been in a situation where an object has been altered/created/deleted, without our knowledge, and the application comes to a screeching halt. After fixing the problem, your boss asks you some questions, like what happened, why did it happen, and who did it. SQL Server 2005 introduced a new type of trigger called a DDL trigger that can provide all the answers we need; however, you did not get a chance to implement this functionality. So... what do you do?

Default trace - A Beginner's Guide - SQLServerCentral

How to Identify Blocking Problems with SQL Profiler

 

In SQL Server 2000 and earlier, identifying blocking issues was not an easy task. One option was to use Enterprise Manager to view existing connections to see if any blocking was occurring, or using the sp_who or sp_who2 commands. If you were really ambitious, you could write some code to extract blocking data from system tables.

In SQL Server 2005, the situation has improved. Besides Management Studio, stored procedures, and system tables, we also have DMVs and even the Performance Dashboard. However, most importantly, we have a new SQL Server Profiler event, Blocked Process Report. This event does a great job of helping you to identify blocking issues and, at the same time, provides you with much of the information you need to help correct the problem.

How to Identify Blocking Problems with SQL Profiler

Phoenix: SQL Server 2005 Best Practices

 

The following are some of the best practices adopted by SQL Server DBA's. I have covered some important stuffs starting with Tempdb, Memory, DB engine, Replication and Index.

Phoenix: SQL Server 2005 Best Practices

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

Learn something new every day.  Until now, I was using the sql client alias configuration tool to set this one up.

No more dependencies on server admins!

Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

There's more info in MS KB Article 313295:

TCP/IP:

server=tcp:hostname

You can optionally specify a specific port number. By default, the port is 1433.

server=tcp:hostname, portNumber


Named Pipes:



server=np:hostname

You can optionally specify a specific named pipe.



server=np:\\hostname\pipe\pipeName




[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway