All Articles - SQL Authority with Pinal Dave

SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

April 14, 2007

SQL Error Messages, SQL Scripts, SQL Server Security

Some errors never got old. I have seen many new DBA or Developers struggling with this errors. Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection. Fix/Solution/Workaround: Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”…

SQL SERVER – Stored Procedures Advantages and Best Advantage

April 13, 2007

Pinal Dave

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

Best Practices, SQL Stored Procedure

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question. I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached…

SQL SERVER – Script to Find SQL Server on Network

April 13, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server Security, SQL Stored Procedure, SQL Utility

I manage lots of SQL Servers. Many times I forget how many server I have and what are their names. New servers are added frequently and old servers are replaced with powerful servers. I run following script to check if server is properly set up and announcing itself. This script…

SQL SERVER – 2005 – Disable Triggers – Drop Triggers

April 13, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server Security, SQL Trigger

There are two ways to prevent trigger from firing. 1) Drop Trigger Example: DROP TRIGGER TriggerName GO 2) Disable Trigger DML trigger can be disabled two ways. Using ALETER TABLE statement or use DISABLE TRIGGER. I prefer DISABLE TRIGGER statement. Syntax: DISABLE TRIGGER { [ schema . ] trigger_name […

SQL SERVER – Fix : Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns

April 12, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Best Practices, SQL Error Messages, SQL Server DBCC

Error Received: Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns SQL Server 2000 supports table with maximum 1024 columns. This errors happens when we try to create table with 1024 columns or try to add columns to table which exceeds more than 1024. Fix/Solution/WorkAround:…

SQL SERVER – Fix : Error: 3902, Severity: 16; State: 1 : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

April 12, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Server Security, SQL Transactions

SQL Server Integration Services Error : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (Microsoft OLE DB Provider for SQL Server) Fix/Workaround/Solution: Option 1: To work around this problem, do not call the stored procedure by using ODBC Call syntax. You can call the stored procedure in may ways…

SQL SERVER – Running 64 bit SQL SERVER 2005 on 32 bit Operating System

April 12, 2007

Pinal Dave

SQL Tips and Tricks

SQL Server, SQL Server Security

Few days ago, I have received email from users asking question :How to run 64 bit SQL SERVER 2005 on 32 bit operating system?

SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String

April 11, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Following SQL User Defined Function will extract/parse numbers from the string. CREATE FUNCTION ExtractInteger(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers =…

SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling

April 11, 2007

Pinal Dave

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

SQL Error Messages, SQL Scripts, SQL Server Security

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can…

SQL SERVER – 2005 – Silent Installation – Unattended Installation

April 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server Security

Silent SQL Server 2005 Installation is possible in two steps. 1) Creating an .ini file The SQL Server CD contains a template file called template.ini . Based on that create another required .ini file which includes a single [Options] section containing multiple parameters, each relating to a different feature or…