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
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! How can I use Windows PowerShell to automate Microsoft Excel?
Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?
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.
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
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
Find your self doing lots of cut-paste special-transpose in Excel?
TRANSPOSE( and CTRL-SHIFT-ENTER will be your friend.
TRANSPOSE
Applies to: Microsoft Office Excel 2003
Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
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
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:
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.
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
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.
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
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?
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.
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.
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
Show All
Hide All