The Ten Most Asked SQL Server Related Programming Questions In Newsgroups

I have been participating in newsgroups/forums for about five years now and lately I have noticed that at least 60% of the questions are the same ten questions.
That is why I will create a blogpost that lists these ten questions and also provides an answer for them. I think that to write this will take me about a week.

Here is what I have so far

1 selecting only current day/yesterday from a table
2 adding days/weeks/years/months to dates
3 splitting string values
4 select * from one table that doesn't exist in another table
5 getting all rows from one table and only the latest from the child table
6 getting all characters until some character (charindex + left)
7 NULL stuff ( a bunch of stuff here dealing with nulls)
8 Row values to column (PIVOT)
9 Show 0001 as 1 and show 1 as 0001
10 CASE and checking for NULLs

I guess I could have added date display formatting. Did I miss any other obvious questions?

SQL Server Speaks Almost As Many Languages As I Do

Saw this error while running a job

Error Code: 4860
Cannot bulk load. The file "%ls" does not exist.
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
???????????????? "%1!" ???????? No se puede realizar la carga masiva......


This is the first time that I have seen a multi-language error. It appears that SQL Server 2005 is a multi-language 'speaker'.
So we have 4 languages in this error

English
Cannot bulk load. The file "%ls" does not exist.

German
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.

French
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.

Spanish
???????????????? "%1!" ???????? No se puede realizar la carga masiva (don't know what the deal is with those question marks, probabaly a missing font)


So I speak 2 of these languages and 2.5 other languages and also a dialect. But I can curse in many more ;-)

Microsoft Joins OpenAjax

OpenAjax Alliance, an open industry collaboration dedicated to developing and expanding Ajax, today announced that its membership has grown to 72 with the addition of Microsoft Corporation and 30 other companies.

"Microsoft is joining the OpenAJAX Alliance to collaborate with other industry leaders to help evolve AJAX-style development by ensuring a high degree of interoperability," said Keith Smith, group product manager of the Core Web Platform & Tools to UX Web/Client Platform & Tools team at Microsoft Corp. "By joining OpenAJAX, Microsoft is continuing its commitment to empower Web developers with technology that works cross-browser and cross-platform."

The newest OpenAjax Alliance members include: 24SevenOffice, ActiveGrid, ActiveState, Appeon, Aptana, Arimaan Global Consulting, Custom Credit Systems (Thinwire), ESRI, Getahead (DWR), Global Computer Enterprises, GoETC, Helmi Technologies, HR-XML, iPolipo, Isomorphic Software, JSSL, Lightstreamer, Microsoft, MobileAware, NetScript Technologies, OpenSpot, OpenSymphony (OpenQA), OpSource, OS3.IT, Redmonk, Tealeaf Technology, Teleca Mobile, Transmend, Visible Measures, Visual WebGui and Volantis Systems.

The Alliance is also announcing that the following members have all been awarded OpenAjax Interoperability certificates: Apache XAP, Dojo Foundation, ICEsoft, ILOG, Isomorphic, IT Mill, Lightstreamer, Open Link, Open Spot, Nexaweb, Software AG and TIBCO. The interoperability certificates represent progress by both OpenAjax Alliance and its members towards defining and achieving industry support for OpenAjax Conformance.

Read the press release here: http://www.marketwire.com/mw/release_html_b1?release_id=228535

Perfect SQL Developer Setup

Got this pic in the mail today. I have been working with a dual-monitor setup since 2001 but this is unquestionably better. Here is how I would use it.

Monitor1: Outlook, Word and Excel
Monitor2: Production SQL Servers
Monitor3: Staging and Development SQL Servers
Monitor4: Visual Studio, EditPlus, XML Spy, Sybase Power Designer, Visio, SQL Compare etc etc

You see, all work and no fun (makes Jack a dull boy)
Very SQLicious don’t you think? How would you use it?

St. Patrick's Day Joke: Irish Daughter

An Irish daughter had not been home for over 5 years. Upon her return her father cussed her. "Where have ye been all this time? Why did ye not write to us, not even a line? Why didn't ye call? Can ye not understand what ye put yer old mum thru?

The girl, crying, replied, "Sniff, sniff... dad... I became a prostitute..."

Ye what!!? Out of here, ye shameless harlot! Sinner! You're a disgrace to this family."

OK, dad-- as ye wish. I just came back to give mum this luxurious fur coat, title deed to a ten bedroom mansion plus a savings certificate for $5 million. For me little brother, this gold Rolex and for ye daddy, the sparkling new Mercedes limited edition convertible that's parked outside plus a membership to the country club....(takes a breath). ... and an invitation for ye all to spend New Years Eve on board my new yacht in the Riviera, and..."

Now what was it ye said ye had become?" says dad.

Girl, crying again, "Sniff, sniff.... a prostitute dad! Sniff, sniff."

Oh! Be Jesus! Ye scared me half to death, girl! I thought ye said a Protestant'. Come here and give yer old man a hug!"

Google Buys Gapminder

If you work with data and are trying to visualize this data then you will appreciate Google's latest purchase Gapminder. Gapminder and Google share an enthusiasm for technology that makes data easily accessible and understandable to the world. Gapminder’s Trendalyzer software unveils the beauty of statistics by converting boring numbers into enjoyable interactive animations. I found Human Development Trends, 2005 the more interesting application (two screenshots are at the bottom of this post). This application shows you visually that the number of poorest people continues to increase while the richer are getting richer among other things. There is also another application available: Gapminder World, 2006. You can see a screenshot of that app at the top of this post. Click on the images to see a bigger image or better yet visit the URL to see these apps in action. Also make sure to visit http://www.gapminder.org/links/data/. this URL contains links to the following sites:

GeoHive - the World in Regions
Nationmaster
OECD Data
Social Watch
Statistics Sweden (Satistiska Centralbyrån)
Sustainable World
The World Factbook
United Nations Common Database (UNSCB)
UNESCO Institute for Statistics
World Bank: World Development Indicators
WorldHistory.com

These two images below are from the Human Development Trends, 2005 app.

Don't Know What To Say In Meetings?

So you have these boring meetings and you want to say stuff that sound smart but no one will understand what you talking about. What do you do? Well you use the Web Economy Bullshit Generator of course. This tool mixes a bunch of predefined verbs, adjectives and nouns. The result are gems like these


grow collaborative deliverables
disintermediate ubiquitous web-readiness
seize intuitive users
orchestrate integrated portals


So go ahead and give it a try: http://dack.com/web/bullshit.html

I could have sworn I saw some of these phrases in books ;-)

Amazon Elastic Compute Cloud (Amazon EC2)

Okay we all know what Amazon Simple Storage Service (Amazon S3) is. Now there is a new service from Amazon called Amazon Elastic Compute Cloud (Amazon EC2). with S3 you use Amazon to store your data, images,whatever but with EC2 you use Amazon to do your computing for you.

From the site:

Amazon EC2 Functionality
Amazon EC2 presents a true virtual computing environment, allowing you to use web service interfaces to requisition machines for use, load them with your custom application environment, manage your network's access permissions, and run your image using as many or few systems as you desire.

To use Amazon EC2, you simply:
Create an Amazon Machine Image (AMI) containing your applications, libraries, data and associated configuration settings. Or use our pre-configured, templated images to get up and running immediately.

Upload the AMI into Amazon S3. Amazon EC2 provides tools that make storing the AMI simple. Amazon S3 provides a safe, reliable and fast repository to store your images.

Use Amazon EC2 web service to configure security and network access.

Use Amazon EC2 web service to start, terminate, and monitor as many instances of your AMI as needed.

Pay for the instance hours and bandwidth that you actually consume.

Service Highlights


Elastic
Amazon EC2 enables you to increase or decrease capacity within minutes, not hours or days. You can commission one, hundreds or even thousands of server instances simultaneously. Of course, because this is all controlled with web service APIs, your application can automatically scale itself up and down depending on its needs.


Completely Controlled
You have complete control of your instances. You have root access to each one, and you can interact with them as you would any machine. Each instance predictably provides the equivalent of a system with a 1.7Ghz x86 processor, 1.75GB of RAM, 160GB of local disk, and 250Mb/s of network bandwidth.


Designed for use with Amazon S3
Amazon EC2 works in conjunction with Amazon Simple Storage Service (Amazon S3) to provide a combined solution for computing and storage across a wide range of applications.


Reliable
Amazon EC2 offers a highly reliable environment where replacement instances can be rapidly and reliably commissioned. The service runs within Amazon's proven network infrastructure and datacenters.


Secure
Amazon EC2 provides web service interfaces to control network security. You define groups of instances and their desired accessibility.


Inexpensive
Amazon EC2 passes on to you the financial benefits of Amazon's scale. You pay a very low
rate for the compute capacity you actually consume. Compare this with the
significant up-front expenditures traditionally required to purchase and
maintain hardware, either in-house or hosted. This frees you from many of the
complexities of capacity planning, transforms what are commonly large fixed
costs into much smaller variable costs, and removes the need to over-buy "safety
net" capacity to handle periodic traffic spikes.


Get all the details here: http://www.amazon.com/gp/browse.html?node=201590011

Online Indexing Operations in SQL Server 2005 Whitepaper Available For Download

TechNet has made availabe a whitepaper that deals with Online Indexing Operations in SQL Server 2005. Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.

Included in this document:

• Introduction

• Data Structures and Concepts

• Overview of the Online Index Build Algorithm

• Capacity Planning and Concurrency Considerations for Index Create and Rebuild Operations

• Online Index Best Practices

• Conclusion

• Appendix A: Performance Study

• Appendix B: Diagnostics

• Appendix C: Log Measurement Script

• Appendix D: Online Index Limitations


Download this whitepaper (Word doc,25 pages) here: http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

Did You Have Your Pie On Pi Day?

March 14th is Pi Day as the date 3/14 corresponds to the mathematical number pi.

The mathematical constant π is a transcendental (and therefore irrational) real number, approximately equal to 3.14159, which is the ratio of a circle's circumference to its diameter in Euclidean geometry, and has many uses in mathematics, physics, and engineering. It is also known as Archimedes' constant and as Ludolph's number

Here are the first 100 digits of PI

3.
1415926535 8979323846 2643383279 5028841971 6939937510 5820974944 5923078164 0628620899 8628034825 3421170679 8214808651

GMail Storage Will Be 3560 MB on April 1st 2012

Gmail's quota will continue to increase in the following years.

What to expect in the future:

2835 MB on April 1st 2007
2980 MB on April 1st 2008
3125 MB on April 1st 2009
3270 MB on April 1st 2010
3415 MB on April 1st 2011
3560 MB on April 1st 2012

As you can see, Gmail's storage will increase with 145 MB a year. Great for storing all that spam. Now I wish they could increase the message size to 20MB from the current 10MB.

More detail here: http://googlesystem.blogspot.com/2007/03/gmails-updated-quotas.html

Priceless Comment About Slashdot Users

It is all here:http://apple.slashdot.org/comments.pl?sid=44091&cid=4592270


Enough said.

How To Get The Database Name For The Current User Process

This question pops up frequent enough so here are 4 ways to return the database name for the current user process

First up is the fastest method. this will run on SQL Server 200 and 2005

SELECT DB_NAME()


Next up is getting the name by joining the sys.dm_exec_requests dmv and sys.sysdatabases. this runs on SQL Server 2005 only

SELECT s.name
FROM sys.dm_exec_requests d
JOIN sys.sysdatabases s on d.database_id = s.dbid
WHERE session_id = @@SPID

Here is something similar, this also runs only on SQL Server 2005

SELECT name
FROM sys.sysdatabases
WHERE dbid = DB_ID()


And we end with something that runs on both SQL Server 2005 and 2000
SELECT name
FROM master..sysdatabases
WHERE dbid = DB_ID()


But like I said before you should always use DB_NAME()

SQL Server 2005 Performance Dashboard Reports Available For Download

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

Keep in mind that the SQL Server instance being monitored must be running SP2 or later.
So what are you waiting for? dowmload it and read the details here: http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

R.I.P Visual FoxPro

Where is the wake ;-) Finally this thing is about to die, no more having to deal with 40 tables because each FoxPro table has a 2GB limit. And don’t get me started with exclusively locked tables either. I think that I will listen to die, die my darling by Metallica while I finish this post. Here are some of the lyrics

Die, die, die my darling
Dont utter a single word
Die, die, die my darling
Just shut your Foxy eyes (I replaced pretty with Foxy)

So for all you FoxPro lovers, there is a great new site (so there is hope for you ;-)) VFP-Conversion

Think Outside the Fox-Den!

map of the world, based on the frequency of its locations mentioned in books.

Here is something interesting and it has to do with data. Inside Google Book Search has some neat maps of the world based on the frequency of its locations mentioned in books. From the site:

We've all seen views of the Earth from space, where the numerous pinpoints of light on the ground combine to yield a speckled map of the world. I wanted to show the Earth viewed from books, where individual mentions of locations in books combine to yield another interpretation of the globe. The intensity of each pixel is proportional to the number of times the location at a given set of coordinates is mentioned across all of the books in Google Books Search.

Here is the link: http://booksearch.blogspot.com/2007/03/earth-viewed-from-books.html

Wladimir Klitschko Knocks Out Ray Austin In The Second Round

This is how I like my boxing fast and efficient. Wladimir Klitschko knocked out Ray Austin in the second round with a series of left hand punches. That is all there is to say about this fight

Scribd: The YouTube Of Documents

What is Scribd?
Scribd lets you publish and discover documents online. It is like a big online library where anyone can upload. We make use of a custom Flash document viewer that lets you display documents right in your Web browser. There are all sorts of other features that make it easy and fun to publish, convert, embed, analyze, and read documents.

Part of the idea behind Scribd is that everyone has a lot of documents sitting around on their computers that only they can read. With Scribd we hope to unlock this information by putting it on the web.

What kinds of documents can I publish on Scribd?
Literally, anything you can put in a Word (.doc), PDF (.pdf), text (.txt), PowerPoint (.ppt), Excel (.xls), Postscript (.ps), or LIT (.lit) file.

So go ahead and check it out: http://www.scribd.com/

Windows Server 2003 Service Pack 2 x64 Edition Release Candidate Available For Download

Microsoft Windows Server 2003 Service Pack 2 (SP2) Release Candidate (RC) is a cumulative service pack that provides the latest updates, security and stability enhancements, in addition to new features, feature updates and utility updates to the Windows Server 2003 operating system. SP2 helps secure your server and improve system reliability, security, and stability thereby ensuring the highest level of readiness to meet the ever increasing demands of today’s server operating systems.
Windows Server 2003 SP2 can be installed directly on the following operating systems:
Windows Server 2003 Editions (All 32-bit x86)
Windows Server 2003 Itanium-based Editions
Windows Server 2003 x64 Editions
Windows Server 2003 R2 Editions
Windows Server 2003 Storage Server R2 Edition
Windows Server 2003 Compute Cluster Edition
Windows Small Business Server 2003 R2
Windows XP Professional x64 Edition


Download it here: http://www.microsoft.com/downloads/details.aspx?familyid=AC8EDD44-96B3-4D11-9293-12970CD62FED&displaylang=en

how to save a whole heap of money on SQL Server 2000 licenses

I Just read this on the daily WTF (which got renamed to Worse Than Failure the other day)
An insurance company decides to install the trial version of SQL server 2000 on their production box because they don't want to pay the license. After 6 months they backup all the data, wipe out the hard drive (reimage) and install the trial version again, restore all the databases and are good to go for another 6 months...ROFL
Why not just use developer edition? You are violating the license anyway, it will at least save you the hassle of reinstalling every 6 months...
Link to original article is here http://worsethanfailure.com/Articles/SQL_on_Trial.aspx