Generate Range of Values (Dates or Numbers) for SQL queries

Many times when querying SQL you'll need to join against a date range, or a number range. This can be done less-elegantly with a while loop, or using CTEs.
(All of the examples assume SQL 2008 or later, due to "date" data type.)

Using a while-loop, into a temp-table:

DECLARE @date DATE, @EndDate DATE
SELECT @date='2012-01-01', @EndDate='2013-01-01'
DECLARE @dateRange TABLE (dateValue DATE)
WHILE @date <= @EndDate
BEGIN
  INSERT INTO @dateRange VALUES (@date)
  SET @date = DATEADD(d,1,@date)
END

A date range using CTE:

DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate='2012-01-01', @EndDate='2013-01-01'
;WITH cteRange AS
(
  SELECT @StartDate as [DateValue]
  UNION ALL
  SELECT DATEADD(d,1,DateValue)
  FROM cteRange
  WHERE DATEADD(d,1,DateValue) <= @EndDate
)
SELECT DateValue
FROM cteRange
OPTION (MAXRECURSION 0)

Or for an integer range:

DECLARE @Start int, @End int
SELECT @Start=1, @End=100
;WITH cteRange AS
(
  SELECT @Start as theNumber
  UNION ALL
  SELECT theNumber + 1
  FROM cteRange
  WHERE theNumber + 1 <= @End
)
SELECT theNumber
FROM cteRange
OPTION (MAXRECURSION 0)

Change PowerShell Path to Script File's Path

When working in PowerShell IDE, put this snippet at top of file to change the current directory to the one the file is currently running from.

$path = Split-Path $MyInvocation.MyCommand.Path -Parent 

write-host "`nSetting working directory to $path" 

Set-Location -literalPath $path

Links regarding SQL "OUTPUT INTO" clause

2013/06/12: OUTPUT Clause (Transact-SQL)

SQL Joins - Illustrated

Found at: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

Some useful T-SQL bits...

It seems that I'm repeatedly needing to use some functionality or utility of SQL. So here's a few, for my own reference...

------------------------------------- 2013-06-12
Table Counts - Local and SQL Azure Friendly

---- ROW COUNTS IN LOCAL SQL TABLES ----
SELECT
    t.TABLE_SCHEMA + '.' + t.TABLE_NAME as 'TableName'
    ,[RowCount] = SUM(sp.[rows])
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions sp
    ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
ORDER BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
-----------------------------------

---- ROW COUNTS IN SQL AZURE ----
select 
t.schema_id,t.name as 'TableName'
, s.row_count as 'RowCount'
from sys.tables t
join sys.dm_db_partition_stats s ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and s.index_id = 1
ORDER BY t.schema_id,t.name
---------------------------------

--------------------------------------

For SQL Server installed versions info...

select serverproperty('Edition') as SQLEdition,

Serverproperty('ProductLevel') as ServicePack,
ServerProperty('ProductVersion')as Version

----------------------------------------------

To save your SELECT query results to a text file, use this query :

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'

--------------------------------------------------------
Over at another blog, there's TONS of good stuff.
http://www.sqlservercurry.com/2008/02/how-to-send-email-using-sql-server-2005.html

-----------------------------------------

Use this query to get all the tables and stored procedures in a database

http://www.sqlservercurry.com/2007/12/display-all-tables-and-stored.html

USE DBNAME
GO
SELECT * 
FROM Sys.Objects
WHERE Type IN ('U', 'P')
ORDER BY type_desc

------------------------------------------------

Display the size of all tables in Sql Server 2005
http://www.sqlservercurry.com/2008/02/display-size-of-all-tables-in-sql.html

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

---------------------------------------------

Check if a user has access to a database in Sql Server 2005
http://www.sqlservercurry.com/2008/03/check-if-user-has-access-to-database.html

SELECT [Name] as DatabaseName from master.dbo.sysdatabases
WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]

Another way:
sp_helplogins
sp_helplogins 'LoginName'

----------------------------------------------------

Copy a table from one database to another in SQL Server 2005
http://www.sqlservercurry.com/2008/03/copy-table-from-one-database-to-another.html
To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1.

-----------------------------------------------------

Find out the tables having the largest size in your database using SQL Server 2005 
http://www.sqlservercurry.com/2008/04/find-out-tables-having-largest-size-in.html

DECLARE @TblNames Table
(
  COUNTER INT IDENTITY(1,1),
  tbl_name nvarchar(100) NULL,
  row varchar(10),
  reserved varchar(10),
  data varchar(10),
  index_size varchar(10),
  unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) 
SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT 
BEGIN 
  SELECT @str = tbl_name 
  FROM @TblNames 
  WHERE COUNTER = @I 

  INSERT INTO @TblNames 
  EXEC sp_spaceused @str 

  SET @I = @I +1 
END 

-- Display results in Sorted order 
SELECT tbl_name as TableNm, 
  CAST(REPLACE(data, ' kb', '') as int) as TblSize,
  CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize 
FROM @tblNames 
ORDER BY TblSize DESC 

-------------------------------------------
Populate a table from a .CSV or .TXT file using SQL Server 2005
http://www.sqlservercurry.com/2008/04/populate-table-from-csv-or-txt-file.html

-----------------------------------

DATENAME() Function

DECLARE @Dt datetime
SET @Dt = '2008-04-15 8:34:54.713'
SELECT @Dt as 'Full Date String',
DATENAME(year, @Dt) as 'Year', -- Returns 2008
DATENAME(quarter, @Dt) as 'Quarter', -- Returns 2
DATENAME(month, @Dt) as 'Month', -- Returns April
DATENAME(dayofyear, @Dt) AS 'Day of Year',-- Returns 106
DATENAME(day, @Dt) AS 'Day',-- Returns 15
DATENAME(week, @Dt) AS 'Week',-- Returns 16
DATENAME(weekday, @Dt) AS 'Weekday',-- Returns Tuesday
DATENAME(hour, @Dt) AS 'Hour',-- Returns 8
DATENAME(minute, @Dt) AS 'Minutes',-- Returns 34
DATENAME(second, @Dt) AS 'Seconds',-- Returns 54
DATENAME(millisecond, @Dt) AS 'Milliseconds'-- Returns 713

DATEPART() Function
SELECT
DATEPART(year, GETDATE()) as 'Year',
DATEPART(month,GETDATE()) as 'Month',
DATEPART(day,GETDATE()) as 'Day',
DATEPART(week,GETDATE()) as 'Week',
DATEPART(hour,GETDATE()) as 'Hour',
DATEPART(minute,GETDATE()) as 'Minute',
DATEPART(second,GETDATE()) as 'Seconds',
DATEPART(millisecond,GETDATE()) as 'MilliSeconds'

----------------------------------------------------

How to Send Email from SQL 2005

http://www.sqlservercurry.com/2008_02_01_archive.html
With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]
Use the following script to send a mail from your Sql Server

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
  @recipients = 'admin@xyz.com; mailto:pqr@xyz.com,
  @body = 'Just testing the mail',
  @subject = 'Sending Mail using Database Mail' ;
GO

---------------------------------------------------

Compare Data Between Two Tables

The 'tablediff' utility is a new feature in SQL Server 2005 used to compare differences of schema and data between source and destination table or view. This utility is particularly useful while replicating data.
You can run this utility from the command line or a batch file.
Example:
Let us use the Customers table of the Northwind database as an example. Do the following :

1. Create another table called CustomerTemp.
SELECT * INTO CustomersTemp FROM Customers

2. Now change data in the CustomersTemp table
UPDATE CustomersTemp
SET City = 'Bern'
WHERE CUSTOMERID = 'ALFKI'

3. Run the TableDiff utility
The syntax for running this utility is as follows :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp"
where SUPROTIM is the servername, Northwind is the database, Customers is the source table and CustomerTemp is the destination table
The output is:
Microsoft (R) SQL Server Replication Diff ToolCopyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:-sourceserver SUPROTIM-sourcedatabase Northwind-sourcetable Customers-destinationserver SUPROTIM-destinationdatabase Northwind-destinationtable CustomersTemp
Table [Northwind].[dbo].[Customers] on SUPROTIM and Table [Northwind].[dbo].[CustomersTemp] on SUPROTIM have 1 differences.Err CustomerIDMismatch 'ALFKI'The requested operation took 0.244125 seconds.
If you want to generate the SQL scripts for the differences found, use the -f parameter with the file name:
Example :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp" -f "C:\temp"

---------------------------------------------------

Save Select Query Output To Text File

Ever needed to save the result of your SELECT query to a text file. Well use xp_cmdshell. xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.

To save your SELECT query results to a text file, use this query :

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'

One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.

Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

----------------------------------------

Using Radio Buttons with matching values doesn't work

After quite a bit of struggling and googling, I came to the realization that having matching values for multiple radio buttons doesn't work.

Had 2 radiobuttonlists, first one has values that select a second radiobuttonlist value. But having 2 radio buttons with same value causes the radio button with the same value but lowest index to be the selected item.

Working with MS Word 2007

I would think that, by now, new versions of MS Office would get easier to use. It's hard to think of how much more functionality they could possibly push into the tools that they offer.

However, my experiences with MS Office 2007 have been FRUSTRATING and changes don't make sense to me.

The new ribbon, what the hell? The categories and where they've placed different functionality on those ribbons is NOT intuitive. I usually have to look through multiple ribbons before I find what I want, sometimes not finding it at all. I don't know how many times I've resorted to searching on Google to find out how to do something, or that something isn't supported with Office 2007.

Updateable field codes in Word 2007 for things like "Page 4 of 8" in footers, and inserting document titles, or the last saved date, or the last saved by information, is a great idea. And they do appear to have this functionality available. But it doesn't work like it should.
I can't figure out how to have a document update those fields automatically when you save it, or open it. You have to either go to "print preview", or right click on each field individually and choose "update field", or hit Ctrl-A to select all and then hit F9. WTF?

How come I can't apply a document template to an existing document?

When you set-up your headers and footers to be different for the first page than for the rest, how do you actually edit the headers and footers for page 2+ if you don't have content that spans to the 2nd page yet? Is the official way really just to hit "enter" enough times to create a new page, and then edit it? That's unacceptable.

Why can't I edit a read-only document, even if I don't plan on saving it? Maybe I want to just put a note in there, or move something around just to see how it looks, but not save it, because it's not checked-out to me yet from a SharePoint 2007 document library? (SharePoint is an entire other list of frustrations of the way things SHOULD be.)

Here's a link to a blog with many Word 2007 shortcomings, and oftentimes helpful work arounds that may not be obvious.
http://word2007bible.herbtyson.com/category/computing/word-2007/

Here's a blog detailing the how and why of the ribbon.
http://blogs.msdn.com/jensenh/