Modern Data Insight

DTS (Data Transformation Services) in SQL Server is very powerful feature and can be used to solve many database related issues starting from data migration, conversion and import/export. Even content publication/management applications can be developed easily using DTS. SQL Server’s DTS support is extended with latest version of SQL Server 2000. This article’s main goal is not only to expose the DTS functionality of SQL Server but also to show the simplicity in using DTS packages to implement core functionality. This case study takes up a sample financial web application “Trade Analysis” designed and developed by Prakash Financials. Trade Analysis application supports trade analysis for client specific trade data. Client in this article represents mostly an organization. ‘User’ is any user that uses the services of “Trade Analysis”. There may be many users from one single organization/client and there may be more than one client registered for the using “Trade Analysis”.

VistaDB 2.1 database for .NET has been released This 2.1 update includes over 60 improvements, including new support for .NET 2.0 and Visual Studio 2005. VistaDB is a small-footprint, embedded SQL database alternative to Jet/Access, MSDE and SQL Server Express 2005 that enables developers to build .NET 1.1 and .NET 2.0 applications. Features SQL-92 support, small 500KB embedded footprint, free 2-User VistaDB Server for remote TCP/IP data access, royalty free distribution for both embedded and server, Copy 'n Go! deployment, managed ADO.NET Provider, data management and data migration tools. Free trial is available for download. - Learn more about VistaDB - Repost this to your blog and receive a FREE copy of VistaDB 2.1!

This article explains how to take a string of comma-separated values (CSV), and parse out the values individually. If you have a table with a column of CSV values, and you want to translate them into a normalized table, it can really difficult. You would need to use a cursor to loop through your CSV table and process each row. Fortunately there's a way to do this using just a regular SELECT. It requires a table of numeric values, but it does the job beautifully; you can INSERT normalized CSV values into a table with one SQL statement. The tricky part is that each word can be a different length, and each CSV can have a different number of words in it. We'll need a separate table (Tally), with a single column of sequential integer values. The "tally table" is mentioned in Joe Celko's SQL for Smarties -- the best SQL book ever written -- as a handy source for a numeric sequence. Here, it will replace the loop needed to parse out each value from the CSV string. SELECT

DBCC SHOWCONTIG is a wonderful tool which helps you to understand quite a bit more about your system than is obvious at first glance. And, frankly, the documentation doesn't use terminology that makes it very obvious either. So, this article will focus on a few of the big ideas behind the tool using SQL Server, and how you can use it to better understand what is going on inside your SQL Server box. Probably one of the most significant performance problems found in databases is centered around table data fragmentation. One situation that may be analogous to table fragmentation might be an index at the end of a large book. A single index entry in such a book might point to several pages scattered throughout the book. You must then scan each page for the specific information you require. This differs significantly from the index of the phone book which stores its data in sorted order. A typical query for the name "Jones" might span multiple consecutive pages, but are always

Following steps used to delete duplicate records 1. Identify duplicates select au_lname, au_fname, city, state, count(*) from dup_authors group by au_lname, au_fname, city, state having count(*) > 1 order by count(*) desc, au_lname, au_fname 2. Temp Table and Truncate select distinct * into #holding from dup_authors 3. Rename and Copy Back sp_rename 'dup_authors', 'temp_dup_authors' 4. Create a Primary Key (prevention) -- Add a new column -- In real life I'd put an index on it Alter table dup_authors add NewPK int NULL Article from SQLTeam, more

SQL Server CE is available as a download for use with SQL Server 2000 from the Microsoft Web site and as part of Microsoft''s eMbedded Visual Tools 3.0. If you don''t have SQL Server 2000 and just want to take SQL Server CE out for a spin, use the eMbedded Visual Tools version. Both come with the Windows CE version of Query Analyzer, which provides a way to create and administer databases on the Windows CE device. Those unfamiliar with Query Analyzer will find its GUI relatively intuitive. Article by Edmond Woychowsky, more

The SQL Server Web Data Administrator enables you to easily manage your SQL Server data, wherever you are. Using its built-in features, you can do the following from Microsoft Internet Explorer or your favorite Web browser: - Create and edit databases in SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine - Perform ad-hoc queries against databases and save them to your file system - Export and import database schema and data - Manage users and roles View, create and edit stored procedures Article by Steven Warren, more

Following 10 things you should know about SQL Server 2005 features : 1. There is now an XML data type 2. Distributed Management Objects (DMO) becomes SQL Server Management Objects (SMO) 3. Common Table Expresssions recursive queries 4. The Service Broker makes SQL Server traffic asynchronous 5. Create .NET triggers 6. SQL Server 2005 configuration is dynamic 7. Define your own data types 8. Many active result sets, one connection 9. WAITFOR ... RECEIVE 10. DTS is now Integration Services Article by Scott Robinson, more

This is a C# Open source version of SQL Query Analyzer with code completion - If you type in a table name and hit ctrl-space, it has a dropdown with the columns, etc. I need a version that works from usb drive. Source SQL Buddy .

This article describes how to programmatically extract and store Excel formulas in a SQL Server database and replicate the UI-side calculations using dynamic SQL. In Part 1 of this article series shows how to take advantage of XML maps in Excel to create a template for data entry. Stored procedures are then used to perform aggregations for parent locations and update calculated data as well. You can extend the sample code in this article as far as needed for most scenarios that require support of flexible XML mapping and Excel formulas applied to aggregate values. Currently a variation of this code in a project called "The Rhythm of the Business 2.0," which supports a flexible and constantly changing Excel template used by more than 1,000 employees. Article from msdn, more

This article reviews how locking plays a role in SQL Server and how locking can lead to potential blocking problems. It looks at the typical methods currently in use to identify blocking as well as their advantages and disadvantages. Also introduces the Sherlock tool and its uses in quickly and graphically identifying blocking. Article by Roberto Alexis Farah (msdn), more

Locks are the mechanism databases use to control problems that may arise from simultaneous multi-user access. Some common issues locks try to manage include; what happens when two users try to change the same piece of information at the same time - who wins? Or user one tries to read data while user two is making changes on the same information, - which version of the data should user one see, changed or unchanged? Without locks, these situations may cause the data or results of a query to be logically incorrect. Lock sophistication is a key difference between simple databases designed for single user applications and enterprise databases capable of simultaneously handling large volumes of multi-user actions. Article by Don Schlichting, more

When an error is encountered within a stored procedure, the best you can do (assuming its a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error. Article by Garth Wells, more

SQL is implemented in different architecture. Different DBMS products handle various SQL (and related) features differenlty. The goal of this article — is to gather information relevant for people who are porting SQL from one product to another and/or are interested in possibilities and limits of 'cross-product' SQL. Article by Troels Arvin, more

SQL is the most popular computer language used to create, modify and retrieve data from relational database management systems. The language has evolved beyond its original purpose to support object-relational database management systems. The most frequently used operation in transactional databases is the data retrieval operation. Technically, SQL is a declarative computer language for use with "relational databases". Theorists note that many of the original SQL features were inspired by, but in violation of, tuple calculus. Article from Wikipedia, more

Idera's SQLcheck quickly investigates, organizes and explains critical diagnostic information about your server's hardware, operating system and SQL Server. With SQLcheck's performance dashboard, you can quickly view detailed explanations and receive recovery suggestions. Get the information you need to efficiently manage your SQL Server environment. Make sure you have admin rights to the sql box. more tags: database , sql

SQL Server 2000 clustering can add much needed reliability and fault-tolerance to a production SQL Server 2000 system. But as you might imagine, its installation and configuration is not as easy an installing SQL Server 2000 on a single server. The purpose of this article is to show you how to install SQL Server 2000 Enterprise Edition onto a Windows 2000, two-node cluster using an Active/Passive configuration. Installing SQL Server 2000 clustering is not an easy task. It requires a good knowledge of Windows 2000, SQL Server 2000, and time. Article by Brad M. McGehee (SQL-Server-Performance)

Avoid publishing unnecessary data. Try to restrict the amount of published data. This can results in good performance benefits, because SQL Server will publish only the amount of data required. This can reduce network traffic and boost the overall replication performance. Article from MS SQL City, more

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption" There are 2 versions: one for SP's only and the other one for SP's, triggers and views version 1: INPUT: object name (stored procedure, view or trigger) version 2: INPUT: object name (stored procedure, view or trigger), object type('T'-trigger, 'P'-stored procedure or 'V'-view). Code by Joseph Gama, more

A database maintenance plan is implemented using a series of DBCC commands and system stored procedures. DBCC stands for database consistency check or database console command. There are numerous documented and undocumented DBCC commands, but only a few of them are used for maintenance. A typical database maintenance schedule involves the following activities. 1. Check database consistency and data integrity (DBCC CHECKDB) 2. Rebuild indexes or remove index fragmentation (DBCC DBREINDEX) 3. Update statistics (DBCC SHOW_STATISTICS) 4. Report on space utilization in data and log files (DBCC SHRINKFILE) Article by Kevin Kline and Baya Pavliashvili from SearchSQLServer, more

All UDFs are defined using the CREATE FUNCTION statement, which varies slightly for scalar, in-line, and multi-statement functions. Scalar UDFs have the following syntax: CREATE FUNCTION owner.function_name (parameter_name data_type [=default] [, n]) RETURNS scalar_data_type [WITH function_option] AS BEGIN Function_body RETURN scalar_expression END Article by Baya Pavliashvili, more Recommended Book:

User-defined functions (UDF) allow you to add custom solutions for unique application-specific problems. Tasks can be as simple as calculating a value or as complex as defining and implementing table constraints. Technically speaking, SQL Server UDFs are subroutines of encapsulated Transact-SQL (T-SQL) logic that you can call into action via other T-SQL code to return a scalar (single) value or a table variable. more

Following article by Alexander Chigrik talks about useful undocumented stored procedures. Most frequest ones I use - sp_MShelpcolumns, sp_MShelpindex etc. more

Reserved Keywords are part of the grammar of the Transact-SQL language used by SQL Server to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers. It is recommended to not use the reserved keywords as identifiers or object names. more

SQLCMD is DOS command prompt utility introduced in 2005 that supersedes isql.exe and osql.exe. Like isql and osql, SQLCMD.exe can be used to execute T-SQL statements and scripts. Unlike isql and osql, SQLCMD has extensive support for scripting and variables. SQLCMD.exe uses OLE DB to connect and execute the T-SQL batches. more Another example

Time to move to new SQL server . You can either download Yukon or order from microsoft. Some good links about sql 2005 online books and additional tools . Another good article on Yukon, SQLOS .

I use this tool to connect to sql server. It works without any kind of install on the client computer. Its similar to ms sql querry analyzer. Attached image shows how to connect to the server etc. Download Toad. More USB Tools Links .

This query shows any process thats running more than 30 secs. SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2) This query shows if a process is running more that 10 secs. SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 -- The wait time is measured in milliseconds AND spid > 50 you can kill the process with command - kill [spid]

isqlw -S [server name] -d [database name] -U [user name] -P [password] C:\>isqlw -S [server name] -d [database name] -U [user name] -P [password] -i "c:\project\sql\emp.sql" -o "c:\project\sql\test.csv" With this command line sql example you can run a sql command from dos prompt and send output to a file.

Following article shows how to use external text file in SQL server. But you have to remember the speed issue in using a text file compared to indexed table. Connect to Text Files with the Microsoft Jet ODBC Text Driver

SQL bcp Exprot to CSV: Following process can be used to create csv from sql server with bcp command. First of all we need a format file and then bcp command in stored proc or run directly from query analyzer. Sample Format File 7.0 [sql bcp version] 305 [number of data fields] 1 SQLCHAR 0 9 "," 1 MyField Exec master..xp_cmdshell bcp "SELECT * FROM ##WrkData_LBW_Exp_2" queryout ' + [export file].csv + ' -f\\[format file].fmt -U [user name] -P [pasword] reference: bcp

BCP Basics : bcp sql command can be used to import data. This process is faster than dts. bcp can be used with format file or data can directly imported to a pre-defined table. Format file is the key in this process. Code : BULK INSERT tempdb..U_LNBASE_IMPORT_LNKEYS FROM '\\dfw-iapp-fsnt51\wiredata\loans.txt' WITH ( FIELDTERMINATOR = '"', ROWTERMINATOR = '\n' )

SQL Table Definition : This sql query is used to find table definition without using enterprise manager table design. Code : SELECT a.[name] as 'Table', b.[name] as 'Column', c.[name] as 'Datatype', b.[length] as 'Length', CASE WHEN b.[cdefault] > 0 THEN d.[text] ELSE NULL END as 'Default', CASE WHEN b.[isnullable] = 0 THEN 'No' ELSE 'Yes' END as 'Nullable' FROM sysobjects a INNER JOIN syscolumns b ON a.[id] = b.[id] INNER JOIN systypes c ON b.[xtype] = c.[xtype] LEFT JOIN syscomments d ON b.[cdefault] = d.[id] WHERE a.[xtype] = 'u' -- 'u' for user tables, 'v' for views. and a.[name]='table name' AND a.[name] <> 'dtproperties' ORDER BY a.[name],b.[colorder]

SQL Table Space Script : This sql query uses "sp_mstablespace" stored procedure to get table space for all the user table in the server. Code : SELECT a.[name] as 'Table', (select convert(int, rowcnt) FROM dbo.sysindexes WHERE indid < 2 and id=a.id) as rows, (((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid < 2 and id = a.id) + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id))*8) as DataSpaceUsed, (((SELECT sum(used) FROM dbo.sysindexes WHERE indid in (0, 1, 255) and id = a.id) - ((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid < 2 and id = a.id) + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id)) )*8) as IndexSpaceUsed FROM sysobjects a WHERE a.[xtype] = 'u' AND a.[name] <> 'dtproperties' ORDER BY a.[name]

How to find all the user tables in a database : select * from sysobjects a where a.[xtype] = 'u' and a.[name] <> 'dtproperties' . This will give you list of all your tables.