SQL database (In Recovery) mode

You are doing some transaction and suddenly there is blackout due to power failure. When everything resumes then the database shows “In Recovery” mode refusing to entertain any connection. You don’t know what to do. Yes you can do, you can wait and watch the recovery time :)

DECLARE @DBName VARCHAR(64) 
set @DBName = 'eIntuit_DW_X'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
  [LogDate]
 ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
 ,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

Courtesy: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

Update SQL Table column with number range

There might be scenario you might want to update the column with number range. Here is the simple solution for doing so. 

For example from this (Figure 1) to this (Figure 2)

Figure 1

 

Figure 2

Below is the SQL code for above result.

--//PURPOSE: This sample is to demonstrate the trick 

--// of updating the SQL table column with sequence numbering.

--//Decalre the table variable

declare @TempTable as table(

[ID] INT,

[Name] varchar(50),

[Range] INT

)

--//Insert rows in the Table variable

insert into @TempTable values(1, 'Mumbai', null)

insert into @TempTable values(2, 'Navi Mumbai', null)

insert into @TempTable values(3, 'Delhi', null)

insert into @TempTable values(4, 'Kolkata', null)

insert into @TempTable values(5, 'Chennai', null)

insert into @TempTable values(6, 'Assam', null)

--//Do select to check the result of table variable with range column equals to null.

select * from @TempTable

--//Decalre the variable

declare @cnt int 

--//Set the variable to the value you want to keep as start.

set @cnt = 100

--//HERE IS TRICK. The single update statement to update entire table

update @TempTable set @cnt = [Range] = (@cnt + 1)

--//Check the result of the updated column [Range]

select * from @TempTable

Happy SQL Scripting !!!!

Show Desktop Icon deleted from Quick Lunch

If accidentally your ‘Show Desktop’ icon is deleted from quick lunch toolbar then do the following step 

1. Open new notepad

2. Paste the following code in the notepad
[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

3.Go to file menu and select Save As and in the file name write name as “Show Desktop.scf” and save it on desktop (you can save anywhere you want). Make sure that ‘Save as file’ should have all files selected. 

4.Drag & drop the file to the quick lunch bar .. and you are DONE!!

-Ravi Kumar V.

SQL BCP command to export full database data to text/csv file

There are times when you need to automate the exporting of table data to text file. In that command line BCP command comes handy.

SQL CODE TO EXPORT FULL DATABASE DATA:

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(2048)

DECLARE @tableName NVARCHAR(2048)

DECLARE @schemaID NVARCHAR(2048)

DECLARE@schemaName NVARCHAR(2048)

DECLARE @outPath NVARCHAR(2048)  

SET @outPath = 'd:\test\'

DECLAREtableCursor CURSOR

FOR SELECT[name],[uid] FROMsysobjects WHEREtype='U' FOR READ ONLY

OPENtableCursor

FETCH NEXT FROM tableCursor INTO @tableName,@schemaID;

WHILE @@FETCH_STATUS = 0

BEGIN

      SET @schemaName = SCHEMA_NAME(@schemaID)

      SET @cmd = 'bcp [' + db_name() + '].['+ @schemaName +'].[' + @tableName + '] out ' + @outPath + @tableName + '.txt -t"," -T -c -S <SERVERNAME\INSTANCE>'

      PRINT @cmd

      EXEC xp_cmdshell @cmd

      FETCH NEXTFROM tableCursor INTO@tableName,@schemaID;

END;

CLOSEtableCursor;

DEALLOCATE tableCursor;

Happy Exporting !!

Ravi Kumar V.

Get list of measure in SSAS cube using PowerShell

There are times you are told to compare measure on two servers. Manually doing the task would be tedious. So I have prepared scripts which will generate the list of all measures in all cube on the given server. The script works for both SQL 2005 & SQL 2008.

Dimension can also be fetched in the same way.

The script will generate comma separated file named ‘MeasureList.txt’ in the same folder from where the power shell script running.

CODE:

$OLAPServer = Read-Host "`nEnter the name of the OLAP server"

$OutputFileName = "MeasureList.txt"

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | out-null

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null

$ObjServer = New-Object "Microsoft.AnalysisServices.Server"

Trap{"[ERROR] No server found !";break} 

$ObjServer.Connect($OLAPServer)

New-Item $OutputFileName -type File -Force | out-null

#Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME,EXPRESSION"

Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME"

#--NOTE: Open "EXPRESSION" column if you need calculated members formula but it will spread in multiple line in excel.

foreach($OLAP_DB in $ObjServer.Databases)

{

#foreach($Cube in $OLAP_DB.Cubes){

# Write-Host -f Yellow "CUBE:" $Cube.Name

#}

Write-Host "Database: " $OLAP_DB

$AdomdClient = New-Object "Microsoft.AnalysisServices.AdomdClient.AdomdConnection"

$AdomdClient.ConnectionString = "data source=$OLAPServer;initial catalog=$OLAP_DB"

$AdomdClient.Open()

Write-Host "Status:"$AdomdClient.State

$DataSet = $AdomdClient.GetSchemaDataSet("MDSCHEMA_MEASURES", $null, $true)

Write-Host -f yellow "Number of Measures:"$DataSet.Tables["rowsetTable"].Rows.Count

ForEach($Cube in  $OLAP_DB.Cubes)

{

ForEach($row in $DataSet.Tables["rowsetTable"].Rows)

{

#Write-Host $Cube.Name "#" $row["CUBE_NAME"].ToString()

If($Cube.Name -eq $row["CUBE_NAME"].ToString())

{

$op = ""

$op = $op + $row["CATALOG_NAME"]

$op = $op + "," + $row["CUBE_NAME"]

$op = $op + "," + $row["MEASURE_NAME"]

$op = $op + "," + $row["MEASURE_UNIQUE_NAME"]

$op = $op + "," + $row["MEASURE_AGGREGATOR"]

$op = $op + "," + $row["MEASURE_IS_VISIBLE"]

$op = $op + "," + $row["MEASURE_NAME_SQL_COLUMN_NAME"]

#$op = $op + "," + $row["EXPRESSION"]

Add-Content $OutputFileName $op

}

}

}

$AdomdClient.Close();

}

$ObjServer.Disconnect();

Happy Power shelling!! 

Powershell: Check is Analysis Server / SQL Server is running!

If you are planning automation then it becomes necessary to check if the server is up and running before passing query to it.

Below is the Power-shell script which quickly checks if the SQL Server Analysis Server / SQL Server is running or not.

Checking Analysis Server (SSAS)

# Load AMO assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null

$assembly = New-Object "Microsoft.AnalysisServices.Server" 

#Trap is since the connect method will throw error if no server is found.

Trap{$null;continue}

#Try connecting to server 

$assembly.Connect(".\SQL2008R2")

if($assembly.Version -eq $null){

Write-Host -f Red "Not connected"

}else{

Write-Host -f Yellow "Connected!"

}

Checking SQL Server (SQL)

# Load assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$s = New-Object "Microsoft.SqlServer.Management.Smo.Server" "SQL2008R2"

if($s.Version -eq $Null){

Write-Host -f Red "SQL not connected!"

}else{

Write-Host -f Yellow "SQL Server connected!"

}

Happy Powershelling!!

Ravikumar Vishwakarma.

SSAS, Property not accessible because 'Parent Columns and Child Columns don't have type-matching columns'

Hi friends, if you are on this page then I can imagine how much you are frustrated with this silly error and more frustrated because cannot find the cause. And even google cannot help you much.  

I am sharing my experience on this hope this will be helpful to somebody.

I got this error when i refreshed the Data Source View (DSV) in SQL Server Analysis Service (SSAS) project open in visual studio. I was really clueless what was it talking about, which realtion have been changed? Even the change report generated was no showing any problem. 

Stucked!! right? Even I was :) !! After spending couple of days I decided to review changed done in last week. After reviewing I got the hint of the problem.

I was having 'Employee' table with 'EmployeeID' column of type INTEGER (primary key), which was linked to their 4 tables as foreign key. And one of developer has changed the datatype of foreign keys to VARCHAR. 

This should have broken the relationship with other tables but it did not and error was all about that. In DSV I deleted all the faulty relationship manually and refreshed the DSV. Strange enough the report was showing the relationship as CHANGED and not DELETED and then resulting in same error.

I reloaded the project, since it was not allowing me to save. Deleted the relations again, but this time I did one extra step. I refreshed  the all the other 4 tables as well. 

Refreshing the DSV tables. 

1. My table was not a query, DSV was directly referring to table.

2. I replaced the tables with named query

3. Saved the DSV and the again bought it to normal and saved. 

4. Repeated this for all the related table.

5. The problem was solved.

I could have deleted the tables and re-added but that will delete all other relations as well and I have to manually do everything again.

I Hope VS could have handled this mess !! 

Happy Coding!!!

-Ravi Kumar V.

Bing Tool-bar on Windows 7, 64-bit version

Hi Friendz,

Let me share one small experience first. I have window 7 64-bit with 64-bit IE installed on it. 

Just since there is Facebook chat feature on bing bar I added tool-bar and it was really nice. I was over joyed!! that I can chat without log-in into Facebook website. I chatted, closed the IE, after sometime reopened and to my surprise there was no BING tool-bar. I checked everything, everything was fine but there was no bing bar :( !!

Then after few trail I realised that I have 64-bit version of IE running. So just to check I browse to path "C:\Program Files (x86)\Internet Explorer" which is 32-bit version of IE. 

I clicked on "iexplore.exe" and hurry !!! the IE opened with BING bar.

Later I created short-cut for 32-bit IE. Till now I have not found any performance different 64-bit & 32-bit.

Happy BINGing !!!!