Tuesday, October 15, 2013

Move Database Files MDF and LDF to Another Location

USE MASTER
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database

--e.g
--If the old location are
--V:\SQLDB\DATA\Test_DB123.MDF
-- L:\SQLDB\LOG\Test_DB_log123.LDF


ALTER DATABASE Test_DB123
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO


-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'Test_DB123'
GO


-- Move MDF and LDF Files from Old to New
-- Re-Attached DB

CREATE DATABASE [Test_DB123] ON
( FILENAME = N'D:\MSSQL\DATA\Test_DB123.mdf' ),
( FILENAME = N':E\MSSQL\LOG\Test_DB_log123.ldf' )
FOR ATTACH
GO

* Try if it works for you.

Tuesday, July 2, 2013

Query timeout expired; HYT00 in processing ssas 2008 cube

Some time while cube process this error comes 

"Query timeout expired; HYT00" 

After the Cube deployment:--

Right click on Analysis Services in Management Studio -> Properties. -> Show Advanced (all) Properties 
Set  ExternalCommandTimeout 0 , ExternalConnectionTimeout 0, DatabaseConnectionPoolMax = 0, DatabaseConnectionPoolTimeout = 0, DatabaseConnectionPoolConnectTimeout = 0 


Thursday, May 30, 2013

Delete ASCII Characters from the string

Create FUNCTION dbo.Remove_ASCII_Char (@CharData varchar(200)) 
RETURNS varchar(200)
AS 
BEGIN
DECLARE @Index INT,
@strASCII varchar(8000),
@strASCIIChar INT
 
SET @Index = 1
SET @strASCII = ''

WHILE @Index < LEN(@CharData)+1
BEGIN
SET @strASCIIChar = ASCII(SUBSTRING(@CharData, @Index, 1))
IF @strASCIIChar not BETWEEN 1 and 32 
BEGIN
SET @strASCII = @strASCII + CHAR(@strASCIIChar)
END
SET @Index = @Index + 1
END
RETURN @strASCII
END

Saturday, April 13, 2013

SQL Server Agent(Agent XPs disabled)

If SQL Server Agent stopped with the following message in SSMS "SQL Server Agent (Agent XPs disabled)"

So here is the solution for this:

Run Following code

Step 1. 


EXEC SP_CONFIGURE 'Agent XPs'

Step 2.

EXEC SP_CONFIGURE 'show advanced options',1 

GO 

RECONFIGURE 
GO 

EXEC SP_CONFIGURE 'show advanced options'

Step 3.

EXEC SP_CONFIGURE 'Agent XPs',1 

GO 

RECONFIGURE


Now you are good to go.

Wednesday, April 3, 2013