SQL Server: Data dictionary

Data dictionary creator [1] is a simple-to-use free tool that helped me immensely with a SQL Server database documentation. It stores all the information in Extended Properties, so it’s easier to keep the documentation in sync with the database as it changes. I was able to generate a data dictionary with a couple of of mouse clicks after I have entered the connection string. The dictionary can be exported to various formats: Excel, XML, Word and HTML. Click on the reference [1] for a step by step tutorial on the usage of this utility. (The current version does not seem to support SQL Server 2008). This can be download from codeplex.

Reference:
1. Data Dictionary Creator

Ping Pong Tournament

I have participated in the first annual Ping Pong Tournament at work. After a grueling 8 rounds, I finally had my hands on the coveted trophy. Well I have something new that I can brag about This tourney has been going on for about 3 weeks now and I have thoroughly enjoyed participating in it. The best part is that I got to meet/know a lot of people (who were working on other projects) and was also able to make a few friends as well.

Sql Server: How to find the current version?

One of the following two SQL queries could be used to determine the current version of the Microsoft SQL Server [2] that you are working on. I like the first method, for the simplicity of its usage.

SELECT @@version

--OUTPUT
--Microsoft SQL Server 2005 - 9.00.XXXX.00
--Nov XX 200X 16:17:31
--Copyright (c) 1988-2005 Microsoft Corporation
--XXXXXX Edition on Windows NT 5.2 (Build XXXX: Service Pack X)

-------
--OR--
-------

SELECT SERVERPROPERTY('productversion') productversion,
 SERVERPROPERTY ('productlevel') Product Level,
 SERVERPROPERTY ('edition')

--OUTPUT
--Product Version Product Level  Edition
----------------- -------------- --------------
--9.00.XXXX.00    SPX            XXXXX Edition

Following are the product versions and their corresponding names.

8.0 stands for SQL Server 2000
9.0 stands for SQL Server 2005
10.0 stands for SQL Server 2008

Tip: You can run select @@servername in management studio query editor to find the SQL Server name.

References:

1. Microsoft support
2. Microsoft SQL Server

SQL Server: Import data from Excel

This article will demonstrate one of the several ways of importing data into a SQL Server table from an Excel spread sheet. I will use a simple SQL query to import data instead of SSIS/DTS. The precursor to achieving the data transfer will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server database. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor/Query Analyzer:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to import data from an MS-Excel spreadsheet. It must kept in mind that an Excel file with all the intended columns and data has to exit, before data can be imported (also the file must be closed when data is transferred). In the following code sample, data from the spreadsheet “EMP” in the Excel file, will be imported into the “employee” table in the SQL Server database using the OPENROWSET.

--Export data to Excel
select * into Employee from openrowset('Microsoft.Jet.OLEDB.4.0' ,
'Excel 8.0;Database=c:\test\test.xls','Select * from [EMP$]')

In the near future I will demonstrate how to import multiple spreadsheets into SQL Server database using SSIS, via dynamic discovery of all available spreadsheets in one Excel file.

References:
1. Server Settings Configuration Options

Useful run commands

Here are some of the commands that can be executed from the Run dialog box in the Start menu to launch the following applications. I have picked up them over the years and am not sure of one particular source as a reference.

• MSConfig – Microsoft configuration utility
• Explorer – Windows explorer
• Cmd – Command window
• Inetmgr – IIS Manager
• Services.msc – Windows services Administrator
• Pbrush – Paint brush
• Chrome – Google chrome browser.
• Firefox – Firefox browser
• ISqlw – Query analyzer
• Eventvwr.msc – Event viewer
• Dcomcnfg – Component services
• Notepad – Notepad
• Calc – Calculator
• Winword – MS Word
• Excel – MS Excel
• Access – MS Access
• Regedit – Registry editor
• Taskmgr – task manager
• Write – Wordpad
• Osk – On screen keyboard
• Logoff – logs of the user
• Shutdown /s – shutdowns the system
• Perfmon – Performance monitor
• Mstsc – Remote desktop
• Firewall.cpl – Windows firewall
• odbcad32 – ODBC administrator