Archive for the “SQL” Category

SQL upgrade from MSDE: UpgradeAdvisor returned -1 .

Cause:

The error occurs because of a problem with BPACMD.EXE attempting to load BPAClient.dll. While upgrading the instance of SQL Server the setup looks for the BPAClient.dll and it fails as it is not able to find it at C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA.

Solution :
Copy the BPAClient.dll file from the following location C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA.

May 12, 2010 Posted Under: SQL   Read More

SQL 2000 – search all tables


CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

To execute
EXEC SearchAllTables ‘hi mom’
GO

April 4, 2010 Posted Under: SQL   Read More

SQL: attached a mdf without the log file.

Missing ldf file

Solution
You can attach a database without the logfile by attaching, then clicking the logfile location, and click the remove button. After you click OK a new logfile will be created for your database

February 19, 2010 Posted Under: SQL   Read More

sQL: SQL 2000 truncate logs using DBCC

DBCC statememnts act as Database Console commands. You can use the below listed queries, sprcifically the DBCC ones to shrink your transaction log.

– To GET the name of the log and data files
select name from dbo.sysfiles

– SEE the active transactions as well as the File ID
dbcc loginfo(‘db_Name’)

– Mark transactions inactive so that they can be truncated later
– Suppose file ID is 2

DBCC SHRINKFILE (2)
– Backup the log file and truncate the inactive entries
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
– Run Shrinkfile again for maintainence.
DBCC SHRINKFILE (2)

You can create a stored procedure out of the last 3 statements and schedule it as a job on your server than can run overnight and ensure that the transaction log file size does not get out of hand.

January 22, 2010 Posted Under: SQL   Read More

SQL 2008 : Delete all tables in a db

Reference

EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

——
You won’t be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

– First disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO

EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?

GO

– Now enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

November 27, 2009 Posted Under: SQL   Read More