SQL Tips and Tricks Archives - Page 532 of 536 - SQL Authority with Pinal Dave

SQL SERVER – Fix: Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: 08001, Native Error: 17

March 16, 2007

SQL Error Messages, SQL Server Security, SQL Utility

Error received: Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: 08001, Native Error: 17 Error state: 1, Severity: 16 Source: Microsoft OLE DB Provider for SQL Server Error message: [DBNETLIB]SQL Server does not exist or access denied The simple fix: Microsoft SQL Server 2005 >> Configuration Tools…

SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity

March 15, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL Server DBCC, SQL Server Security

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer. DBCC CHECKIDENT (yourtable, reseed, 34) If table…

SQL SERVER – Union vs. Union All – Which is better for performance?

March 10, 2007

Pinal Dave

SQL, SQL Performance, SQL Puzzle, SQL Server, SQL Tips and Tricks

SQL Download, SQL Scripts

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article. UNION The UNION command is used to select related information from two tables, much like the JOIN…

SQL SERVER – Download 2005 SP2a

March 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

DBA, SQL Download

Microsoft released an updated SQL Server 2005 SP2 on March 5th, 2007. The build number is 9.00.3042.01. The previous build number was 9.00.3042.00.Microsoft released a SP2a patch for the second service pack for SQL Server 2005 to fix the issues with the maintenance plans.If you have upgraded to SP2, use…

SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running

March 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

To determine which version of SQL Server 2000/2005 is running, connect to SQL Server 2000/2005 by using Query Analyzer, and then run the following code: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The results are: The product version (for example, 8.00.534). The product level (for example, “RTM” or “SP2”). The edition…

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

March 5, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF? SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO SET QUOTED_IDENTIFIER OFF…

SQL SERVER – Delete Duplicate Records – Rows

March 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Duplicate Records, SQL Scripts

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3. DELETE FROM MyTable WHERE ID NOT IN…