SQL Help

SQL 2008

2007-09-16T11:42:00.000-07:00

SQL Server 2008, the next release of Microsoft SQL Server, provides a comprehensive data platform that is more secure, reliable, manageable and scalable for your mission critical applications, while enabling developers to create new applications that can store and consume any type of data on any device, and enabling all your users to make informed decisions with relevant insights. more

Microsoft a BI Force

2007-03-17T18:43:00.000-07:00

Microsoft BI is a new tool. It is delivered through three layers or workloads: data warehousing, reporting and analysis, and performance management. These are designed to provide a consolidated, comprehensive data source and tools to help improve decision making. Microsoft BI is to help decision makers at all levels.An IDC report issued this summer—found that Microsoft’s BI market share growth

ARRAY In SQL Server 2000

2006-01-12T11:28:00.000-08:00

On a number of occasions that I have regretted the fact that there is no such thing as Array in Microsoft SQL Server's Transact-SQL. Lots of other SQL programmers would mention the same thing. The array is one of the most common and highly used programming structures. Indeed, T-SQL does not provide for the fully functional array structure. However, SQL 2000 introduced the new feature called a

Data Import Functionality Using SQL Server

2005-12-16T18:03:00.000-08:00

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

Embeded Database

2005-11-06T20:36:00.000-08:00

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

Parsing CSV Values Into Multiple Rows

2005-10-25T11:44:00.000-07:00

This article explains how to take a string of comma-separated values(CSV), and parse out the values individually. If you have a table witha column of CSV values, and you want to translate them into a normalizedtable, it can really difficult. You would need to use a cursor to loopthrough your CSV table and process each row. Fortunately there's away to do this using just a regular SELECT. It

Understanding DBCC SHOWCONTIG

2005-09-15T11:49:00.000-07:00

DBCC SHOWCONTIG is a wonderful tool which helps you to understandquite a bit more about your system than is obvious at first glance. And,frankly, the documentation doesn't use terminology that makes it veryobvious either. So, this article will focus on a few of the big ideas behindthe tool using SQL Server, and how you can use it to better understandwhat is going on inside your SQL Server box.

Deleting Duplicate Records

2005-09-09T13:44:00.000-07:00

Following steps used to delete duplicate records1. Identify duplicatesselect au_lname, au_fname, city, state, count(*)from dup_authorsgroup by au_lname, au_fname, city, statehaving count(*) > 1order by count(*) desc, au_lname, au_fname2. Temp Table and Truncateselect distinct *into #holdingfrom dup_authors3. Rename and Copy Backsp_rename 'dup_authors', 'temp_dup_authors'4. Create a Primary Key (

SQL Server CE

2005-09-08T21:13:00.000-07:00

SQL Server CE is available as a download for use with SQL Server2000 from the Microsoft Web site and as part of Microsoft''seMbedded Visual Tools 3.0. If you don''t have SQL Server 2000 andjust want to take SQL Server CE out for a spin, use the eMbeddedVisual Tools version. Both come with the Windows CE version ofQuery Analyzer, which provides a way to create and administerdatabases on the

SQL Server Web Data Administrator

2005-08-29T22:02:00.000-07:00

The SQL Server Web Data Administrator enables you to easilymanage your SQL Server data, wherever you are. Using its built-infeatures, you can do the following from Microsoft Internet Exploreror 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

SQL Server 2005 features

2005-08-28T19:35:00.000-07:00

Following 10 things you should know about SQL Server 2005 features :1. There is now an XML data type2. Distributed Management Objects (DMO) becomes SQL Server Management Objects (SMO)3. Common Table Expresssions recursive queries4. The Service Broker makes SQL Server traffic asynchronous5. Create .NET triggers6. SQL Server 2005 configuration is dynamic7. Define your own data types8. Many active

SQL Buddy

2005-08-22T11:58:00.000-07:00

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.

Excel 2003 Formulas in SQL Server

2005-08-21T21:02:00.000-07:00

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

Monitor SQL Server blocking

2005-08-18T11:08:00.000-07:00

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

Controlling Transactions and Locks

2005-08-17T08:06:00.000-07:00

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,

Stored Procedure - Error Handling

2005-08-16T12:45:00.000-07:00

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.

Different SQL implementations

2005-08-15T10:06:00.000-07:00

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 Story

2005-08-12T14:05:00.000-07:00

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

SQL Monitor

2005-08-11T07:59:00.000-07:00

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

SQL Server Clustering

2005-08-10T14:23:00.000-07:00

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

Replication Optimization Tips

2005-08-10T13:01:00.000-07:00

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

Decrypt Stored Procedure

2005-08-08T17:47:00.000-07:00

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

SQL Server Maintenance

2005-08-06T21:17:00.000-07:00

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

UDF Part II

2005-08-04T13:56:00.000-07:00

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 BEGINFunction_bodyRETURN scalar_expressionENDArticle by Baya Pavliashvili, moreRecommended Book:

UDFs in SQL Server

2005-08-03T07:09:00.000-07:00

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

Useful undocumented stored procedures

2005-08-01T07:39:00.000-07:00

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

SQL Server 2005 Reserved Keywords

2005-07-28T14:18:00.000-07:00

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

SQLCMD Utility in Yukon

2005-07-28T14:11:00.000-07:00

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. moreAnother example

Yukon - SQL Server 2005

2005-07-28T11:32:00.000-07:00

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.

Toad for SQL Server on USB

2005-07-20T12:01:00.000-07:00

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.

Simple sql server monitoring

2005-07-12T19:33:00.000-07:00

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 pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 30ANd EXISTS (SELECT * FROM master..syslockinfo lWHERE req_spid = p.spid AND rsc_type <> 2)This query shows if a process is running more

Command line with sql

2005-07-11T19:27:00.000-07:00

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 promptand send output to a file.

2005-06-24T21:41:00.000-07:00

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

2005-06-23T14:23:00.000-07:00

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 orrun directly from query analyzer.Sample Format File 7.0 [sql bcp version]305 [number of data fields]1 SQLCHAR 0 9 "," 1 MyFieldExec master..xp_cmdshell bcp "SELECT * FROM ##WrkData_LBW_Exp_2" queryout ' + [

2005-06-18T08:59:00.000-07:00

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

2005-06-17T14:33:00.000-07:00

SQL Table Definition : This sql query is used to find tabledefinition 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 aINNER JOIN

2005-06-16T14:28:00.000-07:00

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

2005-06-15T11:15:00.000-07:00

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.