SSMS Copy Column Headers

Needing some sort of variation or at least a break from my certification studies I decided to revisit a title I purchased a while back: "Beginning SQL Server 2008 for Developers" from @Apress written by Robin Dewson. Truly glad I did because one of the problems I have when illustrating data related issues to clients is having to piece together a bloody spreadsheet. I typically would query the tables then query the INFORMATION_SCHEMA.COLUMNS or sys.columns to get the column names to use as headers. Then I would copy and paste them into the predetermined rows.

Well not any more because page 42-43 of this awesome book had life changing screenshots of the Options dialog. Big deal right? Well a big deal for me at least! Following the path of Query Results --> SQL Server --> Results to Grid lead me to a sudden state of euphoria. There I saw it as the lights of heaven filled my home, blurring everything around me as if a spotlight and magnify focused in on the wonderful words that read: "Include column headers when copying or saving the results". Was it really true, are my days of querying sys.columns or the INFORMATION_SCHEMA.COLUMNS finally over? In a way, maybe, not really, but at the very least delivering requested spreadsheets to clients will be a bit faster. I was so excited I tried this on MSSQL 2005 and sure enough it is there!

What you need to do is: 

Step 1 - Go to Tools --> Options

Step 2 - Expand Query Results --> SQL Server --> Results to Grid

Then check the box titled: Include column headers when copying or saving the results

Step 3 - Copy some cells

If you have tabs opened already, open a new query window and try it. You should see the menu item "Copy with Headers" as a right click option

Step 4 - Then paste your results into the spreadsheet

Just a side note, if you are dealing with datetime data types you should consider making the predetermined columns in the spreadsheet as a text category by formatting the cells, under the Number tab select the "Text" category.

Cover image taken from Apress.

Posted via email from wetmatter nonsense

Creating a Database Snapshot

This is a quick vid that steps you through the process of creating a database snapshot, restoring a database from a snapshot and dropping a snapshot. I typically use snapshot while I am in a testing mode. I quickly create a snapshot and make all my modifications, run through my test and blow it all away by restoring from my recent snapshot. It is very useful and I use it often on my local development instances. Hopefully you will find it just a useful.

Posted via email from wetmatter nonsense

SQL Saturday Phoenix?

Is SQL Saturday really coming to Phoenix? Well the announcement was made public at the Arizona PASS chapter in a recent user group meeting. It is headed up by @Hafthor, @Coneybeer and myself @sqlsamson. We have teamed up and are looking to get the community involved! If you don't know what SQL Saturday is then allow me to brief you on it. It is a one day FREE training event that is conveniently held on a Saturday. In these troubled times FREE training never sounded so good! How great is that? SQL Saturday offers two tracks with four to six sessions for each track lasting about an hour each. The best part is that the speakers are fellow SQL Server enthusiast from our very own local community. You know them and you love them so come out and show your support!

If you are familiar with SQL Saturday then you know it was picked up by (PASS) the Professional Association of SQL Server not long ago and the fact it is in its planning stages here in Arizona is exciting. With such a large and overwhelming amount of knowledge sharing that SQL Saturday has to offer, it will be to valuable asset to the community. Keep in mind that SQL Saturday won't be a success unless you get involved. We need support from the community in several different areas. If you are apart of another technical group please spread the word that SQL Saturday is coming and we are looking for speakers/presenters, sponsors, volunteers, and of course venues.

If you are willing to present, sponsor, volunteer or can assist with getting locations please contact either one of the following:

• Samson Loo: @sqlsamson - sqlsamson [at] gmail.com
• Chris Coneybeer @coneybeer
• Hafthor Stefansson @hafthor

For more information about SQL Saturday please visit SQLSaturday.com. I hope to hear from you and more importantly I hope to see you there!!!!

For information about the Arizona PASS chapter please visit Arizona PASS

-- samson

Posted via email from wetmatter nonsense

Installing SQL Server 2000

This video steps you through the installation process of installing
SQL Server 2000 very quickly and very straightforward. This video is
one part of a series that goes through the upgrade process from 2000
to 2005 and on to 2008.

Posted via email from wetmatter nonsense

sqlshots: Installing SQL Server 2008 via Configuration File

Posted via web from wetmatter nonsense

sqlshots: Extracting Installation Files from SQL Server 2008 Express Install Package

Posted via web from wetmatter nonsense

sqlshots: Extracting Installation Files from SQL Server 2008 Express Install Package

Posted via web from wetmatter nonsense

sqlshots: Add Leading Zeros

Typically in most cases you find yourself removing leading zeros but in this case I needed to add leading zeros to a column. 

Remove Leading Zeros

SELECT CAST(CAST(ColumnName AS INT) AS VARCHAR(10)) FROM TableName

Let's Pad the field

Since I needed 6 chars this will add 3 spaces to the front of the numbers

SELECT STR(ColumnName, 6) FROM TableName

Add Leading Zeros

Let's say we need to add zeros to an employee id, well this would be an easy way to accomplish it.

SELECT REPLACE(STR(ColumnName, 6), SPACE(1), '0') FROM TableName

SELECT SalesPersonID       ,REPLACE(STR(SalesPersonID, 6), SPACE(1), '0') AS PaddedSalesPID FROM   Sales.SalesPerson

Note: the SPACE(1) is equivalent to ' ' (That is a Tick Space Tick) So two Ticks with a space in between)

Update Table

UPDATE Sales.SalesPerson SET newSalesPID = REPLACE(STR(SalesPersonID, 6), SPACE(1), '0')

Posted via email from wetmatter nonsense

sqlshots: What version are you running?

1. This option is okay but not what I needed.

SELECT @@VERSION AS [Version Info]

2. This option is okay as well but does not indicate if it is 2000, 2005, 2008. Yeah it shows the product version number, but if you don't readily know it then you have to look it up.

SELECT SERVERPROPERTY('productversion')  AS [Version]         ,SERVERPROPERTY('edition')            AS [Edition]         ,SERVERPROPERTY('productlevel')     AS [Service Pack]

3. This option works okay but the you'll have issues with the Edition as the char length will differ

SELECT RIGHT(LEFT(@@VERSION,25),15)  AS [Product]        ,RIGHT(LEFT(@@VERSION,40),12) AS [Product Version]         ,LEFT(RIGHT(@@VERSION,65),17) AS [Edition]          ,SERVERPROPERTY('productlevel')  AS [Service Pack]

4. This is the winner for now (not great for version 6.5 & 7 as some additional char proceeds after due to the char length) 

SELECT RIGHT(LEFT(@@VERSION,25),15)    AS [Product]       ,RIGHT(LEFT(@@VERSION,40),12)   AS [Product Version]       ,SERVERPROPERTY('edition')      AS [Edition]       ,SERVERPROPERTY('productlevel') AS [Service Pack]

5. After some thought I like this route better

SELECT RIGHT(LEFT(@@VERSION,25),15)    AS [Product]    ,SERVERPROPERTY('productversion')   AS [Version]    ,SERVERPROPERTY('edition')          AS [Edition]    ,SERVERPROPERTY('productlevel')     AS [Service Pack]

I haven't tried this with any other version other than 2005, but I suspect 2000 & 2008 will work just fine. Earlier versions like 6.5 & 7 I am not sure of.

Posted via web from wetmatter nonsense

SQL Server 2005...

With SQL Server 2005 you can install multiple instances of certain components to run concurrently on a system. These are known as the instance aware components.

• Database Engine
• Analysis Services
• Reporting Services

EXCEL: SQL Generator...

In my line of work I write T-SQL statements quite a bit so having to write them over and over again can be tiresome. Here is tip that can help you generate T-SQL insert statements very quickly, especially if you seem to use the same tables over and over again. This is my first attempt using another tutorial software so bare with me.

Enjoy!

SQL (70-431) Question of the week...Q3

You are the database administrator for a shipping company named Cargoflow. You are asked to create a database for the company's marketing department for trend analysis of shipments. This database will be bulk loaded with information from a data warehouse when it is first created. Data will be analyzed but not modified in any way. You are trying to decide on an appropriate recovery model for the database. Which recovery model should you implement for this new database? Choose the best option(s) from those listed below.

a) Full recovery
b) Bulk-logged recovery
c) Simple recovery
d) Warehouse recovery

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
The simple recovery model is the most appropriate recovery model to use in this scenario. Since the data in the database will never change, point-of-failure recovery is not necessary. This means that data in the transaction log is not critical to recovering the database and does not necessitate being backed up. The simple recovery model relies strictly on full and differential backups of the database to recover.

Correct Option(s):
c) Simple recovery

Incorrect Option(s):
a) Full recovery - The full recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
b) Bulk-logged recovery - The bulk-logged recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
d) Warehouse recovery - SQL Server 2005 does not support a warehouse recovery model.

Questions Provided by SkillSoft

SQL (70-431) Question of the week...Q2

You are creating a new SQL Server 2005 database for Brocadero's sales department. To ensure maximum availability and reliability you decide to implement the database across multiple data files. When creating the data files you want to follow Microsoft's recommended best practices for naming. How should the primary and secondary data files be named? Choose the best option(s) from those listed below.

a) The primary data file should have an .mdf extension.
b) The primary data file should have an .ndf extension.
c) The secondary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Microsoft's recommended best practices state that a heavily used database should store the database catalog in a primary data file and all data and objects in secondary data files for the best performance, availability, and reliability. Microsoft recommends that primary data files use the .mdf file extension, while secondary data files should use the .ndf extension.

Correct Option(s):
a) The primary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Incorrect Option(s):
b) The primary data file should have an .ndf extension - Primary data files should use the .mdf extension.
c) The secondary data file should have an .mdf extension - Secondary data files should use the .ndf extension.

Questions Provided by SkillSoft

SQL (70-431) Question of the week...Q1

You are the SQL Server administrator for your company. You have been assigned the task of installing Microsoft SQL Server 2005 Enterprise Edition on an existing server. This server has a 600 MHz Pentium III processor, 256 MB of RAM, 10 GB hard disk and Microsoft Windows 2000 Server with Service Pack 1 installed. All of the components installed are upgradeable if required. What components must you upgrade before installing SQL Server 2005 on this server? Choose the best option(s) from those listed below.

a) Processor
b) RAM
c) Hard disk
d) Operating System

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Before installing SQL Server 2005 Enterprise Edition on the server, you would need to upgrade to 512 MB of RAM and install Service Pack 4 or later just to meet the minimum system requirements. Microsoft recommends the following system requirements for a 32 bit system:

Processor - 600 MHz Pentium III-compatible or faster processor; 1 GHz or faster processor recommended

Operating System - Microsoft Windows 2000 Server with Service Pack (SP) 4 or later; Windows Server 2003 Standard Edition, Enterprise Edition, or Datacenter Edition with SP 1 or later; Windows Small Business Server 2003 with SP 1 or later

Memory - 512 MB of RAM or more; 1 GB or more recommended

Hard Disk - Approximately 350 MB of available hard-disk space for the recommended installation

Correct Option(s):
b) RAM
d) Operating System

Incorrect Option(s):
a) Processor - The minimum requirement for a processor is a 600 MHz Pentium III-compatible; therefore, the processor in this scenario meets the minimum requirements.

c) Hard disk - The minimum requirement for a hard disk is approximately 350 MB of available hard-disk space; therefore, the available hard disk space in this scenario exceeds requirements.

Questions Provided by SkillSoft