Bob Mihada

Tasty note to self

noreply@blogger.com (Bob Mihada) — Wed, 09 May 2012 21:43:00 +0000

SELECT DISTINCT
MAX(EdiStcID_Grp)EdiStcID_Grp,EdiStcID, DENSE_RANK() OVER(ORDER BY
MAX(EdiStcID_Grp)) [Grp#]
FROM
#tmpSRT tsrt
GROUP BY tsrt.EdiStcID

DarkMagic With RedGate Virtual Restore and a folder full of Backups

noreply@blogger.com (Bob Mihada) — Wed, 05 Oct 2011 09:00:00 +0000

Sorry, blog, for puking all over on this one. I got to excited and had to put it somewhere for my own amazement. This came about cause I got a ton of DBs to restore for our Staging environment and our EMC SnapShot/Clone not completely dialed in yet. So exciting.

Basically, I created a backup folder on the staging server and copied a full set of backups to it. Then using the script below, with minor tweaks to environment specific paths, I can generate a script to do a virtual restore of all my backups. The GUI is great, but I got 50+ DB to get through, and I need to get this to a less manual process for weekly refreshes. Anywho... Enjoy! Sharing is caring.

The "--#region" stuff is from SSMS PACK, get yours.

IF OBJECT_ID('tempdb..#tmp_VirtualRestore') IS NOT NULL DROP PROCEDURE #tmp_VirtualRestore
GO
CREATE PROCEDURE #tmp_VirtualRestore
 @FilePath VARCHAR(256)
 ,@DataFilePath VARCHAR(256) = 'D:\SQL_DATA\'
 ,@LogsFilePath VARCHAR(256) = 'L:\SQL_LOGS\'
 ,@AppendSuffix bit = 1
AS 
BEGIN
SET NOCOUNT ON 

DECLARE  
 @SQL VARCHAR(4000)

IF LEN(ISNULL(@FilePath,'')) < 5 
BEGIN
 PRINT 'Eh, Invalid FilePath?  Please review: ' + ISNULL(@FilePath,'')
 RETURN                                 
END
 
--#region Interrogate Backup file for meta info

 --//Get/Store Header info from backup file(s)
 IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
 CREATE TABLE #header
 (
  IDX INT identity(1,1)
  , BackupName nvarchar(128)
  , BackupDescription nvarchar(255)
  , BackupType smallint
  , ExpirationDate datetime
  , Compressed tinyint
  , Position smallint
  , DeviceType tinyint
  , UserName nvarchar(128)
  , ServerName nvarchar(128)
  , DatabaseName nvarchar(128)
  , DatabaseVersion int
  , DatabaseCreationDate datetime
  , BackupSize numeric(20, 0)
  , FirstLSN numeric(25, 0)
  , LastLSN numeric(25,0)
  , CheckpointLSN numeric(25,0)
  , DatabaseBackupLSN numeric(25, 0)
  , BackupStartDate datetime
  , BackupFinishDate datetime
  , SortOrder smallint
  , CodePage smallint
  , UnicodeLocaleId int
  , UnicodeComparisonStyle int
  , CompatibilityLevel tinyint
  , SoftwareVendorId int
  , SoftwareVersionMajor int
  , SoftwareVersionMinor int
  , SoftwareVersionBuild int
  , MachineName nvarchar(128)
  , Flags int
  , BindingID uniqueidentifier
  , RecoveryForkID uniqueidentifier
  , Collation nvarchar(128)
  , FamilyGUID uniqueidentifier
  , HasBulkLoggedData bit
  , IsSnapshot bit
  , IsReadOnly bit
  , IsSingleUser bit
  , HasBackupChecksums bit
  , IsDamaged bit
  , BeginsLogChain bit
  , HasIncompleteMetaData bit
  , IsForceOffline bit
  , IsCopyOnly bit
  , FirstRecoveryForkID uniqueidentifier
  , ForkPointLSN numeric(25, 0) null
  , RecoveryModel nvarchar(60)
  , DifferentialBaseLSN numeric(25, 0) null
  , DifferentialBaseGUID uniqueidentifier
  , BackupTypeDescription nvarchar(60)
  , BackupSetGUID uniqueidentifier null
 );

 INSERT #header EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @FilePath + '''');
 
 IF OBJECT_ID('tempdb..#filelist') IS NOT NULL DROP TABLE #filelist
  CREATE TABLE #fileList
  (
   LogicalName NVARCHAR(128)
    , PhysicalName NVARCHAR(260)
    , [Type] CHAR(1)
    , FileGroupName NVARCHAR(128)
    , Size NUMERIC(20, 0)
    , MaxSize NUMERIC(20, 0)
    , FileID BIGINT
    , CreateLSN NUMERIC(25, 0)
    , DropLSN NUMERIC(25, 0)
    , UniqueID UNIQUEIDENTIFIER
    , ReadOnlyLSN NUMERIC(25, 0)
    , ReadWriteLSN NUMERIC(25, 0)
    , BackupSizeInBytes BIGINT
    , SourceBlockSize INT
    , FileGroupID INT
    , LogGroupGUID UNIQUEIDENTIFIER
    , DifferentialBaseLSN NUMERIC(25, 0)
    , DifferentialBaseGUID UNIQUEIDENTIFIER
    , IsReadOnly BIT
    , IsPresent BIT)
  
 INSERT #fileList EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @FilePath + '''');

--#endregion  

--#region Clean DataFileName

IF OBJECT_ID('tempdb..#kissfilelist') IS NOT NULL DROP TABLE #kissfilelist
  CREATE TABLE #kissfilelist
  (
  FileID BIGINT
  , LogicalName NVARCHAR(128)
    , DataFileName NVARCHAR(260)
    , [Type] CHAR(1))
INSERT INTO #kissfilelist
 SELECT 
  FileID
  ,LogicalName
  ,LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(PhysicalName),0,CHARINDEX('\', REVERSE(PhysicalName),0)))))
  ,Type
 FROM #fileList

--#endregion

IF EXISTS(SELECT DatafileName FROM #kissfilelist WHERE
 RIGHT(DataFileName,4) NOT IN ('.ldf','.mdf','.ndf'))
BEGIN
 PRINT 'ERROR, Unexpected extention in DataFileName '
 SELECT  'ERROR, Unexpected extention in DataFileName ' + DatafileName 
  FROM #kissfilelist WHERE RIGHT(DatafileName,4) NOT IN ('.ldf','.mdf','.ndf')

 RETURN
END 

 UPDATE #kissfilelist
  SET DataFileName = 
   REPLACE(REPLACE(REPLACE(DataFileName,'.ldf','.vldf'),'.mdf','.vmdf'),'.ndf','.vndf')

 SELECT @SQL = ( 
 SELECT '
--// [' + DatabaseName + '] '  + CONVERT(VARCHAR(50),GETDATE(),109) + ' 
RESTORE DATABASE [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] FROM
DISK=N' + QUOTENAME(@FilePath,CHAR(39)) + '
WITH' 
 FROM #header
  FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
 SELECT '
 ' +
  CASE WHEN ROW_NUMBER() OVER(ORDER BY FileID ASC) > 1 THEN  ',' ELSE '' END
  + 'MOVE N' + QUOTENAME(LogicalName,CHAR(39)) + 
   ' TO N' + QUOTENAME(CASE WHEN Type ='L' THEN @LogsFilePath ELSE @DataFilePath END 
    + DataFileName
    ,CHAR(39))    
 FROM 
  #kissfilelist
 FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
  SELECT '
 ,NORECOVERY, STATS=10, REPLACE
 RESTORE DATABASE  [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] WITH RECOVERY'

FROM #header
  FOR XML PATH('')
 )
 SELECT @SQL = REPLACE(@SQL,'&#x0D;','') 
 
 PRINT @SQL
END 
GO
SET NOCOUNT ON
DECLARE 
 @BackUpFolder VarChar(512)
 ,@CMD VarChar(1028)
 ,@FileCount INT
 ,@Counter INT
 ,@DBFileName VARCHAR(256)
 
 ,@DataFilePath VARCHAR(256)
 ,@LogsFilePath VARCHAR(256)
 ,@AppendSuffix bit

SELECT  
 @BackUpFolder = 'D:\SQL_BACKUPS\SQLa\'
 ,@DataFilePath = 'D:\SQL_DATA\'
 ,@LogsFilePath = 'L:\SQL_LOGS\'
 ,@AppendSuffix = 0
--#region Meat and Potaters
 
--//Temp table to store cmd outputs
IF object_id('tempdb..#tmpDetails') IS NOT NULL drop table #tmpDetails
 create table #tmpDetails(details varchar(4000), DateLogged DateTime Default(getDate()))

--// basic cmd line  
SELECT
 @cmd = 'dir /A-D /ON ' + @BackUpFolder

--// Capture files 
IF object_id('tempdb..#tmpFiles') IS NOT NULL drop table #tmpFiles
CREATE TABLE #tmpFiles (Files VARCHAR(500))
INSERT  INTO #tmpFiles
  EXEC xp_cmdshell @cmd

DECLARE  @tmpDate TABLE (IDX INT IDENTITY(1,1),fileDate DateTime, FileName VarChar(500))

INSERT INTO @tmpDate
   SELECT
  CONVERT(DATETIME, LEFT(files, 20))
  ,LTRIM(RIGHT(files, CHARINDEX(' ', REVERSE(files)))) 
   FROM
  #tmpFiles
   WHERE
  ISDATE(LEFT(files, 20)) = 1
SELECT @FileCount = @@ROWCOUNT, @Counter = 1

--#endregion
WHILE @Counter < @FileCount
BEGIN
 SELECT 
  @DBFileName = @BackUpFolder + FILENAME 
 FROM @tmpDate WHERE IDX = @Counter
 --//PRINT @DBFileName
 
 EXEC #tmp_VirtualRestore @DBFileName,@DataFilePath,@LogsFilePath,  @AppendSuffix
 
 SELECT @Counter = @Counter + 1
END

I just cared all over you :-P

VS2010 DB Compare fails.. Reverse Engineer my @$$

noreply@blogger.com (Bob Mihada) — Thu, 29 Sep 2011 16:18:00 +0000

This gave me much grief. In the end we ran a trace to find the last sql command and found this nugget of joy.

If you get hung up on:

"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database."

Run the following, and grant accordingly.

/*
VS2010 DB Compare fails with:
"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database."

User is in a role that has view definition on sys and dbo, but thats not enough, apparently

GOOD
HasBaselinePermissions IsDeniedObjectPermissions
1      0

BAD
HasBaselinePermissions IsDeniedObjectPermissions
0      0

To fix:
USE master
GRANT VIEW ANY DEFINITION TO [randomSQLUser]
--REVOKE VIEW ANY DEFINITION TO [randomSQLUser]
*/
USE master
EXECUTE AS LOGIN = 'randomSQLUser' ;
select * From fn_my_permissions(NULL, NULL)
SELECT
    CAST(CASE WHEN HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DEFINITION') = 1
              THEN 1
              ELSE 0
         END AS BIT) AS 'HasBaselinePermissions'
  , CAST(CASE WHEN 1 = IS_MEMBER('db_owner')
                   OR 1 = IS_SRVROLEMEMBER('sysadmin')
                   OR 0 = (
                           SELECT
                            COUNT(ALL [p].[state])
                           FROM
                            [sys].[database_permissions] [p]
                            LEFT JOIN [sys].[database_principals] [pr] ON [pr].[principal_id] = [p].[grantee_principal_id]
                           WHERE
                            ([p].[type] IN ('VW', 'CL'))
                            AND [p].[state] = 'D'
                            AND ([p].[grantee_principal_id] = DATABASE_PRINCIPAL_ID()
                                 OR IS_MEMBER([pr].[name]) = 1)
                          ) THEN 0
              ELSE 1
         END AS BIT) AS 'IsDeniedObjectPermissions'  
         
REVERT;

SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS

noreply@blogger.com (Bob Mihada) — Thu, 08 Sep 2011 01:57:00 +0000

/*
 SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS
 Moving DBs, prompted me to conjure this up. 
 Situation: You moved dbs to a new server, do your best to sync
 previous account to new server, with SIDs intact, yet every other
 user that tries to connect ends up at your desk, WTM?
 
 Better safe than sorry. Autofix all orphaned account, and tell them to 
 "TRY NOW!" :-P
 
 Note: one error that came up was when a corresponding principal account
   does not exists at server level
*/

EXEC sp_MSForEachDB '
USE [?]
DECLARE @FixDude Varchar(8000)
SELECT 
 @FixDude = REPLACE((
SELECT DISTINCT 
    ''    EXEC sp_change_users_login ''''Auto_Fix'''' ,'' + QUOTENAME(name,char(39)) + ''
    ''
    FROM
    sys.database_principals
WHERE
    type = ''s''
    AND default_schema_name = ''dbo'' 
    AND name <> ''dbo'' 
FOR XML PATH('''')),''&#x0D;'','''')

IF LEN(FixDude) > 15

BEGIN
 SELECT @FixDude =''USE [?]
'' + @FixDude

 PRINT @FixDude
 EXEC (@FixDude)
END'
   
RETURN

Migrating to win2003 x64 with 32bit COM+ Application..TY Yigeulruseu

noreply@blogger.com (Bob Mihada) — Wed, 24 Aug 2011 08:20:00 +0000

Cheese and Rice, I knew this was out there but didn't think I really had reach out overseas. Spent quite a few hours trying nail down an easy replicatable process to install legacy vb6 COM+ app on to new win2003 x64. First had to battle the 64 vs 32 bit hell. SYTEMWOW64 made me crossed-eyed. Got past that and got hung up trying automate the install of an pre-existing Com+ App, on a win2003x32, in the form of a app.msi + app.msi.cab. Darn thing just didn't want to install. I could re-create manually but the number of steps involved and the icky feeling I was getting forced me to press on looking for a better solution. I found snippets here and there, but nothing quite as complete as what I found here: http://nadobest.egloos.com/9589057, THANK YOU KOREA and GOOGLE TRANSLATE! I would like to give honorable mention to : http://www.carlosag.net/articles/configure-com-plus. This looked solid, but, I'm sure due to the twilight hour, I could not get it to work as I wanted. Anyway, I hack this together and GIVE FULL CREDIT to my new hero overseas, Yigeulruseu.

'--// THANK YOU KOREA: http://nadobest.egloos.com/9589057

'--// Modded to fit my needs



'************************************************* *****************************

'Registers / unregisters a COM / .NET DLL as a COM + app

'************************************************* *****************************

Option Explicit



Dim ApplicationName, ApplicationDescription, Identity, Password



ApplicationName = "MyHappyApp"

ApplicationDescription = "My vb6 dlls"

Identity = InputBox("Enter UserName: ", "[Domain\UserName]")

password = InputBox("Enter Password: ", "Password")



UnInstallApplication ApplicationName



InstallApplication ApplicationName, ApplicationDescription, 2, Identity, password, 3



InstallComponent ApplicationName, "D:\Components\HappyDAL.dll", "Data Acess Layer", 2, 4, -1, 4, 0, 0

InstallComponent ApplicationName, "D:\Components\HappyBUS.dll", "Business Objects/Logic", 2, 4, -1, 4, 0, 0

InstallComponent ApplicationName, "D:\Components\HappySecure.dll", "Security Functionality", 2, 4, -1, 4, 0, 0



'************************************************* *****************************

'Installs the Application

'************************************************* *****************************

'Activation (Activation type): 0 - The library, one-server

'AccessChecksLevel (Click the Security tab of the security level): 0 - Perform access checks only at the process level, one-process and component level access checks

'ApplicationAccessChecksEnabled (Click the Security tab of the authorization): 1-check (perform access checks for this application), 0 - eonchekeu (access checks for this application, None)

'Authentication (call level of authentication, the server type is only valid for): 1 - No 2 - connection, 3 - Call 4-packet, a 5-packet integrity, 6 - Packet Privacy

'ImpersonationLevel (the level): 1 - Anonymous, 2 - ID, 3 - the 4-delegate

'Identity: Interactive User - an interactive user, NT AUTHORITYLocalService - Local Service, NT AUTHORITYNetworkService - Network Services, then the user - ID type directly

' Reference: Application Level In all of the attribute

Sub InstallApplication (ApplicationName, ApplicationDescription, Authentication, Identity, Password, ImpersonationLevel)	

	Dim cat, collApps, app

	

	Set cat = CreateObject("COMAdmin.COMAdminCatalog")

	Set collApps = cat.GetCollection("Applications")

	collApps.Populate

	Set app = collApps.Add

	app.Value("Name") = ApplicationName

	app.Value("Description") = ApplicationDescription

	app.Value("Authentication") = 4

	app.Value("Identity") = Identity

	app.Value("Password") = Password

	'Activation-type setting (0 - library, 1-Server)

	app.Value ("Activation") = 1

	app.Value ("AccessChecksLevel") = 0

	app.Value ("ApplicationAccessChecksEnabled") = 0

	app.Value ("ImpersonationLevel") = ImpersonationLevel

	collApps.SaveChanges



End Sub

'************************************************* *****************************

'Uninstalls the Application

'************************************************* *****************************

Sub UninstallApplication (ApplicationName)

	Wscript.Echo "Unregistering the existing application ..."

	

	Dim cat, collApps, numApps, i

	

	Set cat = CreateObject ("COMAdmin.COMAdminCatalog")

	Set collApps = cat.GetCollection ("Applications")

	collApps.Populate

	numApps = collApps.Count

	

	For i = numApps - 1 To 0 Step -1

		If collApps.Item (i). Value ("Name") = ApplicationName Then

			collApps.Remove (i)

			WScript.echo "- Application" & ApplicationName & "removed!"

		End If

	Next

	collApps.SaveChanges

	

End Sub



'************************************************* *****************************

'Installs the Component

'************************************************* *****************************

'ApplicationDll: DLL path, people, including

'Transaction: 0 - Disabled, 1 - Not Supported, 2 - Supported, 3 - Required, 4 - Requires New

'TxIsolationLevel (transaction isolation level): 0 - All 1 - read uncommitted, 2-read-committed, and 3 - that you can repeat the reading, a 4-yeonsokdoem

'JIT: -1 - JIT enabled, use, 0 - JIT does not activated

'Synchonization: 0 - Disabled 1 - No 2 - Yes 3 - Required 4 - need saeteuraenjaeksyeon

' See also: component-level attribute that all

Sub InstallComponent (ApplicationName, ApplicationDLL, Description, Transaction, TxIsolationLevel, JIT, SYN, ComponentAccessChecksEnabled, ObjectPoolingEnabled)

	Dim cat, collApps, App, numApps, numComponents, i, j

	Dim components, component

	

	Set cat = CreateObject("COMAdmin.COMAdminCatalog")

	Set collApps = cat.GetCollection("Applications")

	collApps.Populate

	numApps = collApps.Count

	For i = numApps -1 To 0 Step -1

		'Wscript.Echo collApps.Item (i). Value ("Name")

		If collApps.Item (i).Value("Name") = ApplicationName Then

		Wscript.Echo App.Value("ID")

			Set App = collApps.Item(i)

			cat.InstallComponent App.Value("ID"), ApplicationDLL, "", ""			

		End If

	Next

End Sub

Sharing is caring

P.S. this wasn't the end all to getting the 32bit to work on x64. There was more but this was the best find.

NOTE TO SELF: SQL Find inactive DBs

noreply@blogger.com (Bob Mihada) — Wed, 24 Aug 2011 02:35:00 +0000

/*

Find DB activity for known list of DBs



ALL CREDIT goes to:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx



I merely tweaked for my own needs



-- You can adjust the script to select from sys.databases WHERE Name in (....). But 

-- as for me I like to keep a second listing in a management DB 



  CREATE TABLE [DBs]

    (

     [UID] [int] NOT NULL

                 IDENTITY(1, 1) NOT FOR REPLICATION

   , [DBID] [int] NULL

   , [DBName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS

                              NOT NULL

   , [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS

                          NOT NULL

   , [DateCreated] [datetime]

        NOT NULL

        CONSTRAINT [DF_DBs_DateCreated] DEFAULT (GETDATE()))

  ON

    [PRIMARY]



*/

DECLARE

		@DBName VARCHAR(128) --Database name	  

	  , @SQL VARCHAR(8000)	 

	  , @DBCount INT

	  , @DBCounter INT



 IF OBJECT_ID('tempdb..#tmpDBs') IS NOT NULL 

		DROP TABLE #tmpDBs



IF OBJECT_ID('tempdb..##tmpDBActivity') IS NOT NULL 

		DROP TABLE ##tmpDBActivity



CREATE TABLE ##tmpDBActivity(DBName sysname,last_read DATETIME, last_write DateTime)



SELECT

		ROW_NUMBER() OVER (ORDER BY DBName ASC) IDX

	  , DBName 

	 INTO

		#tmpDBs

	 FROM

		ManageDBs.dbo.DBs

				

SELECT @DBCount = @@ROWCOUNT, @DBCounter = 1

WHILE @DBCounter <= @DBCount

	BEGIN

		SELECT @DBName = DBName FROM #tmpDBs Where IDX = @DBCounter	

		

		SELECT @SQL = '

USE ' + QUOTENAME(@DBName,'[') + '

;WITH  agg AS

(

    SELECT

        last_user_seek,

        last_user_scan,

        last_user_lookup,

        last_user_update

    FROM

        sys.dm_db_index_usage_stats

    WHERE

        database_id = DB_ID()

        AND OBJECT_SCHEMA_NAME([object_id]) = ' + QUOTENAME('dbo',CHAR(39)) + '

)

INSERT INTO ##tmpDBActivity

SELECT

	DB_NAME(DB_ID()) DBName

    ,last_read = MAX(last_read)

    ,last_write = MAX(last_write)

FROM

(

    SELECT  last_user_seek, NULL  FROM  agg

    UNION  ALL

    SELECT  last_user_scan, NULL  FROM  agg

    UNION  ALL

    SELECT  last_user_lookup, NULL  FROM  agg

    UNION  ALL

    SELECT  NULL, last_user_update FROM  agg

) AS  x (last_read, last_write);'

	EXEC (@SQL)

	SELECT @DBCounter = @DBCounter+1

	

	END



SELECT     

	DBName

  , last_read

  , last_write 

  , CASE WHEN last_read IS NULL AND last_write IS NULL 

		THEN 'INACTIVE' ELSE '' END STATUS

FROM 

	##tmpDBActivity	

ORDER BY last_read ASC



DROP TABLE ##tmpDBActivity

Results:

DBName	last_read	last_write	STATUS
CheeseAndRiceCo	NULL	NULL	INACTIVE
ChickenButtLLC	NULL	NULL	INACTIVE
FranksNBeansLTD	2011-05-11 10:48:28.197	NULL
BobCo	2011-06-09 10:46:30.923	NULL
BadSeafood	2011-07-07 12:01:21.137	2011-07-06 16:39:07.253
ManageDBs	2011-08-23 19:01:46.510	2011-08-23 19:01:46.510

SQL Warm Standby Pulse, synced via LogShipping

noreply@blogger.com (Bob Mihada) — Sat, 20 Aug 2011 20:44:00 +0000

--// SQL Warm Standby Pulse, synced via LogShipping

--// TODO: Create job to monitor age and send email alerts

--// ACKNOWLEDGED: yeah there's other built-in ways to accomplish this. 

DECLARE @THRESHOLD_HOURS INT

	

SELECT

    @THRESHOLD_HOURS = 8 ;

WITH    CTE

          AS (

              SELECT

                ROW_NUMBER() OVER (PARTITION BY rh.destination_database_name ORDER BY rh.restore_history_id DESC) Seq

              , rh.destination_database_name DBName

              , rh.restore_date LstRestoreDate

              , REVERSE(LEFT(REVERSE(physical_device_name),

                             CHARINDEX('\', REVERSE(physical_device_name)) - 1)) LogFileName

              , bs.backup_start_date LogFileDateCreated

              , bs.last_lsn

              FROM

                msdb.dbo.restorehistory rh (NOLOCK)

                LEFT JOIN msdb.dbo.backupset bs (NOLOCK) ON bs.backup_set_id = rh.backup_set_id

                LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id

             )

    SELECT

        DBName

      , CASE WHEN LogFileDateCreated < DATEADD(hh, @THRESHOLD_HOURS * -1,

                                           GETDATE())

             THEN '> ' + LTRIM(STR(@THRESHOLD_HOURS)) + ' HOUR !!'

             ELSE ''

        END [ACHTUNG]

      , DATEDIFF(hh, LogFileDateCreated, GETDATE()) [AGE(HOURS)]

      , LstRestoreDate

      , LogFileName

      , LogFileDateCreated

      , last_lsn

    FROM

        CTE

    WHERE

        Seq = 1

    ORDER BY

        DBName

SQL install woes, service failed to start, retry???

noreply@blogger.com (Bob Mihada) — Fri, 17 Jun 2011 08:55:00 +0000

Scenario: New server in colo, 500 miles away. Network Admin inadvertently installed SQL 2005 developer edition. Hands me the keys.

Upon discovering the sql version, I informed him of the issue and began uninstalling, with dismay.

Last time something like this happened we had to wipe HD and reinstall the OS and then intall correct SQL version. But, this time I found the nugget that escaped me prior.

Towards the tail end of the new install, MSSQL x64 Enterprise, I got a popup saying service failed to start, retry or cancel. Battle then begins. Unfortunately this install was put on back burner till we hours of night, due to productions issue during the day. So it took me 3 night to finally resolve. Picking through the logs I found, (sqlservr!DmpGetClientExport) and googled and found: My savior

I did as advised, overwrite sqlserver.exe and SQLOS.dll, from another sql install, pulled from another server already running, and clicked retry.

So random, yet that WORKED. What a headache, but thank you Satay, for posting. Orz

~enjoy

SQL install woes, service failed to start, retry???

noreply@blogger.com (Bob Mihada) — Fri, 17 Jun 2011 08:54:00 +0000

More notes to self: network bucket too small and a funny quote

noreply@blogger.com (Bob Mihada) — Thu, 28 Apr 2011 08:20:00 +0000

The bucket effect: When bucket gets full, everything is dumped out and bucket begins refilling, where the bucket is the network connection/transmission. In effect you are able to reach the destination, but before the round-trip can occur, all communications appear to get severed. In this scenario, if other short requests(small packets) are getting through, like a web request for data, yet longer requests such as file transfers, SSH/RDP sessions, are dropping out, look for recent network. Don't assume something like a firewall or managed switch couldn't possibly be the culprit, just because you, yourself or team, didn't initiate a change request(Active/Ative? WTM?). Meh. Also don't assume everyone has already checked on these or any other obvious things. Force an acknowledgment by the group.

Funny quote during vendor site-visit:

"Before we make any changes we'll need to take a dump."

I almost lost it :-P. Vendor was actually referring to a snapshot/backup of a database before acting upon it, in case a rollback is required.

note to self: SQL log shipping stats/graphs

noreply@blogger.com (Bob Mihada) — Thu, 28 Apr 2011 06:10:00 +0000

Results of query coupled with google spreadsheets w/ timeline graph applied, equate to pretty data worth sharing.

--// View last log backups per half hour, total size(MB), with number of logs
SELECT TOP 500
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DT30
  , SUM(backup_size / 1024 / 1024) SizeMB
  , COUNT(DISTINCT media_set_id) TrnCount
FROM
    msdb..backupset (NOLOCK)
WHERE
    type = 'L'
GROUP BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0)
ORDER BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DESC

Note: can easily work for Data backups too, just change "type = 'L'" to "type = 'D'"

~Enjoy

note to self: SQL Disk summary

noreply@blogger.com (Bob Mihada) — Thu, 28 Apr 2011 03:22:00 +0000

Script to show current Disk space free, used, by drive. And then a summary break down of DB files, folders and space used.

SET NOCOUNT ON
EXEC master.dbo.xp_cmdshell 'wmic LOGICALDISK LIST BRIEF';

IF OBJECT_ID('tempDB..##tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles
CREATE TABLE ##tmpDatabaseFiles
    (
 [DBName] [sys].[sysname]
   ,  [file_id] [int]
   , [file_guid] [uniqueidentifier]
   , [type] [tinyint]
   , [type_desc] [nvarchar](60)
   , [data_space_id] [int]
   , [name] [sys].[sysname]
   , [physical_name] [nvarchar](260)
   , [state] [tinyint]
   , [state_desc] [nvarchar](60)
   , [size] [int]
   , [max_size] [int]
   , [growth] [int]
   , [is_media_read_only] [bit]
   , [is_read_only] [bit]
   , [is_sparse] [bit]
   , [is_percent_growth] [bit]
   , [is_name_reserved] [bit]
   , [create_lsn] [numeric](25, 0)
   , [drop_lsn] [numeric](25, 0)
   , [read_only_lsn] [numeric](25, 0)
   , [read_write_lsn] [numeric](25, 0)
   , [differential_base_lsn] [numeric](25, 0)
   , [differential_base_guid] [uniqueidentifier]
   , [differential_base_time] [datetime]
   , [redo_start_lsn] [numeric](25, 0)
   , [redo_start_fork_guid] [uniqueidentifier]
   , [redo_target_lsn] [numeric](25, 0)
   , [redo_target_fork_guid] [uniqueidentifier]
   , [backup_lsn] [numeric](25, 0)
   , [CreationDate] [datetime])
 
 EXECUTE sp_msforeachdb 'INSERT INTO ##tmpDatabaseFiles SELECT ''[?]'', *, GETDATE() FROM [?].sys.database_files'
  
SELECT COUNT(DISTINCT dbname) NonSystemDBCount from ##tmpDatabaseFiles WHERE dbname NOT IN ('[master]','[model]','[tempdb]', '[msdb]')

 ;WITH CTE AS (
 SELECT
 DISTINCT
   name
 , LEFT(physical_name, LEN(physical_name)-CHARINDEX ('\', REVERSE(physical_name)) ) Folder
 ,(size * 8)/1024.0 size
 ,type_desc
  FROM ##tmpDatabaseFiles 
)
SELECT 
 LEFT(Folder,60) Folder
 ,LEFT(LTRIM(STR(COUNT(Folder))),5) Files
 ,LEFT(LTRIM(STR(SUM(size)/1024.0)),10) [GB TotalSize]
 ,LEFT(type_desc,10) FileType
FROM CTE
GROUP BY Folder,type_desc
ORDER BY Folder

IF OBJECT_ID('tempDB..#tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles

Set compatibility to SQL 2005

noreply@blogger.com (Bob Mihada) — Fri, 08 Apr 2011 06:32:00 +0000

--// Note To Self

--// Set compatibility to SQL 2005
SELECT
    ' ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE
  exec sp_dbcmptlevel ' + name + ', 90'
FROM
    sys.databases
WHERE
    compatibility_level < 90

Trying to make time to get at root performance issues with SQL

noreply@blogger.com (Bob Mihada) — Wed, 30 Mar 2011 04:34:00 +0000

Poor DB design coupled with poor queries, dynamic sql even can lead to crippling performance issue. Things aren't "crippling", for me at the moment, but theres still a lot that can be done. So where to start?

Well we need something to work with first. So go get some, PerfStatsScripts, SQLDiag. Then feed it to SQL NEXUS. That should eventually lead to managing your indexes, whether missing, suggested or redundant. Another nugget, comes from K.Tripp Orz, sp_helpIndex. Visibility into what your tables have on them now. Hopefully your get into a grove where things will start to just fall into place. Said query is sucking life out of SQL. Its referencing, Table A and Table B. Review suggestions and view what there now. Make a decision and review the results. Rinse wash repeat. I don't expect to end up with a clear glass, but hopefully this would have scrapped the scum off the surface and removed all the big particles and hair, eww.

Ready...Break!

Quick perf toolkit:
http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
~ Kimberly Tripp (so you know its legit)

SQL Nexus Tool + SQLDiag(already included) + SQL Server 2005 Performance Statistics Script

Commercial approach

Idera SQL Doctor

~Throw me a bone so I may chase something worth chasing

-----

Goober: WHY IS IT DOING A TABLE SCAN? No matter what I do it just keeps doing a table scan? Update Stats? Restart SQL?

Bob: Eh, what index are you expecting to see used?

Goober: ...

Goober: .......

Goober: I hate you!!!

Goober: There was no index. I need to go talk to some people.

Bob: Super. Take care now, bye-bye then.

Mini Heart attack moment..Transaction log full then mirror busy

noreply@blogger.com (Bob Mihada) — Mon, 28 Mar 2011 07:39:00 +0000

--// During index maintenance

The statement has been terminated.

Msg 9002, Level 17, State 5, Line 2

The transaction log for database 'VeryBIGDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

log_reuse_wait_desc: database_mirroring

Quick what to do what to do.....

ALTER DATBASE [VeryBIGDB] SET PARTNER OFF

BACKUP LOG [VeryBIGDB] TO DISK='Wherever.hbc'

Lucky for me I have a job already configured and part of log shipping strategy, so I just ran the job.

SHRINK LOG Files

Deep breathes WUSAaaaaaa.

But now....

:-(

Msg 1404, Level 16, State 5, Line 1

The command failed because the database mirror is busy. Reissue the command later.

C'mon, give me a frickenfarackenchikenmonkey break!!!!

Google said, sorry just restart the mirror from scratch.

MAN-UP.

Gotta go, running dark now.

UPDATE:1:26AM

Miraculously the mirrored DB stopped complaining about being busy.

Shot of adrenealine when i saw that.... DING DING DING... ROUND 3, FIGHT

Dug through the Log Shipping backups for said DB, with cmdline nugget:

C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2*

C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_*

Add a dash of Excel, cut-n-paste, and a nifty formula:

="RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\" & A1 & "' WITH NORECOVERY,STATS=10;"

RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2000_LOG.hbc' WITH NORECOVERY,STATS=10;

RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2015_LOG.hbc' WITH NORECOVERY,STATS=10;

RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2030_LOG.hbc' WITH NORECOVERY,STATS=10;

...........

RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_0115_LOG.hbc' WITH NORECOVERY,STATS=10;

......

Ran it with error cause not all were needed, too early to apply to the database....

BUT then:

10 percent processed.

20 percent processed.

30 percent processed.

......

....

;-(( Sniff sniff, tears of joy...

Resync mirror looking very promising... knock on wood...

WHAT IS THE WORD TO DESCRIBE this.....

noreply@blogger.com (Bob Mihada) — Sat, 26 Mar 2011 05:30:00 +0000

I'm in a highly charged positive state of mind and feel like the energy and excitement is just swelling up inside. Like I want to go run as fast as I can till I drop. Waves of goosebumps keep crashing down on me. I wanna share the excitement yet I'm isolated in bottle.

BOTTLE IS FOREVER HALF FULL!!!

I can't hear your cries of "but but but", "that can't...", "you're such a kid", "you're so full of yourself"

I'm sorry you can't handle the awsomeness!!!!

11) MAN-UP

160 Day straight Windows Server 2003x64, SQL 2005x64, 90+ DBS, 1.5TB

noreply@blogger.com (Bob Mihada) — Sat, 26 Mar 2011 04:37:00 +0000

Sometimes you gotta smell the roses. Having to performs Maintenance to add in PCI-E SSD cards to SQL and just thought to check how long server been serving that masses.

And the uptime before that.....

I'm so happy, sniff, sniff

~Enjoy

Hyperbac to the rescue, again....syslnklgns

noreply@blogger.com (Bob Mihada) — Fri, 25 Mar 2011 03:48:00 +0000

Another great feature of Hyperbac ver. 4.1.73.0.

I specify the version because they were aquired by Reg-gate who, seeminly severed this functionality and has relabeled, enhanced :-|, this former built in feature as SQL virtual restore.

EXEC master..sp_addlinkedserver

@server = 'masterbk',

@srvproduct = '',

@provider = 'HyperBac.oledbmtf',

@datasrc = 'C:\master_20110105_FULL.hbc,1'

SELECT * FROM masterbk..sys.syslnklgns

...

srvid	lgnid	name	status	modate	pwdhash
1	0	srvTreasureLnk	0	2007-12-01 11:00:03.000	0x01000000BOBSMAGICKEYOFHAPPYNESS11011001TESTACULARTOOLSTYHYPERBAC000

Now I can re-create the linked server object without needing to know the forgotten magic word.

~Enjoy

A few wasted hours, sob.....SQL 2k5 x86 sneaker

noreply@blogger.com (Bob Mihada) — Mon, 21 Mar 2011 08:15:00 +0000

Fricken Fracken Chicken Chackers!!!

Admin(s) relayed to me server ready for my magic hands. Little did I know they had their hands all up in it. RDP'd in check the partition layout, corrected as needed. Gathered install files. Ready...break...literally. SQL x64 kept failing to install.

"Error 29549. Failed to install and configure assemblies E:\Program Files_64bit\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087"

???

This a fresh install WTM??

What a pain, I finally discovered the Admin installed 2k5 32-bit, prior to handing off to me. Now why would I assume someone would have done that?? Grumble grumble...to make matters worse, I went to control panel --> Add remove, to try to start from scratch, by uninstalling all sql components, thinking the install somehow got corrupt. That somehow left me with an orphaned 32-bit install of SQL that I now can't remove with Add/Remove, BOOOOOOOOooooo. Now trying to clean up this mess.

DELETE COM+ Utilities (32 bit)

"The selected item cannot be deleted. This item is currently locked against deleting."

Craptacular!

:-(

Stable Virtual Environment? SvSAN HA + ESXI + OPENFILER + MISC HARDWARE

noreply@blogger.com (Bob Mihada) — Fri, 18 Mar 2011 07:44:00 +0000

"Double, double toil and trouble; Fire burn, and caldron bubble." ~Shakespeare

For what ever reason that seems to capture my current state in time. Its been nothing short of another example of extreme IT engineering with this project. With adequate time and dedicated effort/focus this would seem like a cake walk. But with the sky falling, projects piling, fires burning, and only 24hours in a day, this is more of a cluster...k. But if Macgvyer can construct an airplane out of bamboo, what do I have to complain about. With so many ways to skin a cat, I offer up this small nugget of joy.....

This was done with everything that was already on-hand.

But just for laughs here is a list of items to build your our bamboo airplane, THAT REALLY FLYS, in theory ;-P

Software	SRC	Price	QTY	Total
SVSAN with HA 2TB	Stormagic.com	$1,990	1	$1,990
VMWARE Essentials plus	vmware.com	$4,229	1	$4,229
OpenFiler	OpenFiler.com	$0	1	$0
Microsoft Windows Server 2008 R2 Standard - 5 CALs, 1 server (1-4 CPU) 2008	Goolge Products	$660	1	$660

Hardware
HP ProCurve J9450A	NewEgg.com	$330	2	$660
2U Supermicro X7DB3 Storage Server SAS 8GB 64bits *Refurb/Used	Ebay.com	$700	4	$2,800
Western Digital Caviar Blue WD10EALX-20PK 1TB	NewEgg.com	$1,299	1	$1,299
SuperMicro 1U server 2x Quad Core 3.16GHz 32gb 2TB *Refurb/Used	Ebay.com	$1,600	2	$3,200
BELKIN A3L791-14-BLU 14 ft. Cat 5E Blue Network Cable 20pk	NewEgg.com	$68	1	$68
APC Smart-UPS 3000 Rack Mount XL 3U *Refurb/Used	RefurbUps.com	$550	2	$1,100

			GRAND	$16,006

Disclaimer: These number, as of 2011-03-17 , are very very rough and just what I could find in less than 20 minutes. I'll also concede it may be missing additional components, but most can be correlated to the diagram above.

~Enjoy

SQL does UNC and local disk paths for FileName, FilePath, FolderPath

noreply@blogger.com (Bob Mihada) — Fri, 18 Mar 2011 04:13:00 +0000

I frequently find my self needing to ensure my config tables are in sync with a Folder some network share or local drive. Whipped up a clean script to break it down simply

SET NOCOUNT ON
DECLARE
   @CMD VARCHAR(500)
 , @FolderPath VARCHAR(256)
 , @FileExt VARCHAR(5) --// 'txt'
 , @Subdirectories VARCHAR(3)

SELECT
 --// UNC path may work too
   @FolderPath = 'C:\Program Files\7-zip\' --// *include trailing back slash
 , @FileExt = 'exe'
 , @Subdirectories = 'YES' --// 'YES' | '' / 'NO'

SELECT
   @CMD = 'dir /B /Aa'
   + CASE WHEN ISNULL(@Subdirectories, '') = 'YES' THEN '/S'
          ELSE ''
     END + ' ' + '"' + @FolderPath + CASE WHEN LEN(@FileExt) >= 1
                                          THEN '*.' + @FileExt
                                          ELSE ''
                                     END + '"'
PRINT @CMD   
 
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE  #tmp

CREATE TABLE #tmp (filePath VARCHAR(2000))

INSERT  INTO #tmp
       EXECUTE master.dbo.xp_cmdshell @CMD ;
--//Used CTE to provide some flexibility. Typically when looking for files
--// I need to compare configuration info like folder path or files in a 
--// Config table. This allows for clean Joins.
WITH    CTE
         AS (
             SELECT
               CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
                    THEN @FolderPath + filePath
                    ELSE filePath
               END [FilePath]
             , CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
                    THEN @FolderPath
                    ELSE LEFT(filePath,
                              LEN(filepath) - CHARINDEX('\',
                                                        REVERSE(filePath))
                              + 1)
               END [FolderPath]
             , CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
                    THEN filePath
                    ELSE  RIGHT(filePath,
                               CHARINDEX('\', REVERSE(filePath)) - 1)
               END [FileName]
             FROM
               #tmp
             WHERE
               LEN(ISNULL(filePath, '')) > 0
            )
   SELECT
       *
   FROM
       CTE

~Enjoy

a little nugget to clean up a raw data import.

noreply@blogger.com (Bob Mihada) — Mon, 14 Mar 2011 08:49:00 +0000

I may be reinventing the wheel here, but it was late and was having a hard time googling for some reason. Needed something that I could hand off to someone to go crazy with in-loading data from raw text files, so I whipped this up.

/* 
Typically you would right click a db > Task > Import Data. 
After data is imported you can use this script to create a cleaner base table.
Biggest headache is typically trying to identify the proper data type and lengths
This script attempts to handle the most common denominator, INT, DateTime, VarChar, and Decimal.

 @LastColumn quirk, if importing CSV and last column contains "," the data is bad, so make best 
 effort to load and then go to source and work it out.

End result is Print statement that will create table x(base table) and 
 an insert into statement

*/
SET NOCOUNT ON
DECLARE
  @ROWSAFFECTED INT
, @Count INT
, @Counter INT
, @ColumName VARCHAR(128)
, @LastColumn VARCHAR(128)
, @TableName VARCHAR(128)
, @dSQL VARCHAR(4000)
, @SaidTABLE VARCHAR(128)

SELECT
  @SaidTABLE = 'CampaignResults_raw'
,@LastColumn = 'DateCreated'

IF OBJECT_ID('tempdb..#tCol') IS NOT NULL
  DROP TABLE #tCol

SELECT
  ORDINAL_POSITION IDX
, TABLE_NAME
, COLUMN_NAME
, CASE WHEN RIGHT(COLUMN_NAME, 2) = 'id' THEN 'INT'
       WHEN COLUMN_NAME LIKE '%date%'
            OR COLUMN_NAME LIKE '%time%' THEN 'DateTime'
       ELSE 'VARCHAR'
  END DT
, 0 MaxLen
INTO
  #tCOL
FROM
  INFORMATION_SCHEMA.Columns
WHERE
  TABLE_NAME = @SaidTABLE
ORDER BY ORDINAL_POSITION

SELECT
  @ROWSAFFECTED = @@ROWCOUNT
, @Counter = 1

IF @ROWSAFFECTED = 0
  RETURN

SELECT
  @Count = @ROWSAFFECTED
WHILE @Counter <= @Count
  BEGIN
      SELECT
          @ColumName = COLUMN_NAME
        , @TableName = TABLE_NAME
      FROM
          #tCOL
      WHERE
          IDX = @Counter
 
      SELECT
          @dSQL = 'UPDATE t
  SET DT = CASE WHEN xINT = 0 THEN ''INT''
   WHEN xDecimal =0 THEN ''Decimal''
   WHEN xDate <0 THEN ''DATE''
   ELSE ''VARCHAR'' END
  ,MaxLen = c.MaxLen
  FROM 
  #tCOL t
  JOIN
  (
  SELECT 
   ' + LTRIM(STR(@counter)) + ' IDX
   ,SUM(CASE WHEN ISNUMERIC(LTRIM(RTRIM( [' + @ColumName
          + '] + ''e0'' ))) = 1 AND [' + @ColumName + '] NOT LIKE ''%.%'' THEN 0 ELSE 1 END) xINT
   ,SUM(CASE WHEN ISNUMERIC(LTRIM(RTRIM( [' + @ColumName
          + '] + ''e0'' ))) = 1 AND [' + @ColumName + '] LIKE ''%.%'' THEN 0 ELSE 1 END) xDecimal
   ,SUM(CASE WHEN ISDATE(LTRIM(RTRIM([' + @ColumName
          + ']))) = 1 THEN -1 ELSE 1 END) xDate
   ,MAX(LEN(RTRIM(LTRIM([' + @ColumName + '])))) MaxLen
  FROM 
   ' + @TableName + '
  WHERE 
    [' + @LastColumn + '] NOT LIKE ''%,%''
   AND [' + @ColumName
          + '] IS NOT NULL) c ON c.IDX = t.IDX AND t.IDX = '
          + LTRIM(STR(@counter))  
      --PRINT @dSQL
      EXEC (@dSQL)
      SELECT
          @Counter = @Counter + 1
  END

DECLARE @tSTR VARCHAR(8000)

SELECT
  @tSTR = REPLACE('CREATE TABLE x('
                  + (
                     SELECT
                      '
' + CASE WHEN IDX = 1 THEN ''
       ELSE ','
  END + '[' + COLUMN_NAME + ']' + CASE WHEN DT = 'INT' THEN ' [INT]'
         WHEN DT = 'Decimal' THEN ' [Decimal]'
                                       WHEN DT = 'DATE' THEN ' [DateTime]'
                                       ELSE ' VarChar(' + LTRIM(STR(MaxLen))
                                            + ')'
                                  END
                     FROM
                      #tCOL
    ORDER BY IDX                 
                    FOR
                     XML PATH('')
                    ), '&#x0D;', '') + ')'
PRINT @tSTR

--DECLARE @tSTR VARCHAR(8000)

SELECT
  @tSTR = REPLACE('INSERT INTO X SELECT '
                  + (
                     SELECT
                      '
' + CASE WHEN IDX = 1 THEN ''
       ELSE ','
  END + 'NULLIF(' + CASE WHEN DT IN ('INT','Decimal','Date') THEN '[' + COLUMN_NAME + ']'
          ELSE 'LTRIM(RTRIM([' + COLUMN_NAME + ']))'
                                  END + '  ,''NULL'')'
                     FROM
                      #tCOL
--where DT = 'INT'
    ORDER BY IDX                 
                    FOR
                     XML PATH('')
                    ), '&#x0D;', '') + ' FROM ' + @SaidTable + '
 WHERE  [' + @LastColumn + '] NOT LIKE ''%,%'''

PRINT @tSTR

~Enjoy

DRBD Openfiler FINALLY

noreply@blogger.com (Bob Mihada) — Fri, 11 Mar 2011 03:59:00 +0000

Great Stuff

Wee hours never good when have to follow instructions to the T. I keep fighting with this, if not the partitions, then zero out thing. If not zero out, then its oversight on glaring message that just seemed to blend into screen. If not that then the drbd.conf was off. If not that then....one or the other would constantly be "Unconfigured"

m:res	cs	st	ds	p	mounted	fstype
0:cluster_metadata	Connected	Secondary/Secondary	Inconsistent/Inconsistent	C
1:vg0drbd	Connected	Secondary/Secondary	Inconsistent/Inconsistent	C

m:res	cs	st	ds	p
0:cluster_metadata	Connected	Primary/Secondary	UpToDate/Inconsistent	C
...	sync'ed:	0.30%	(475751/476820)M
1:vg0drbd	SyncSource	Primary/Secondary	UpToDate/Inconsistent	C

Well on to HA. haresource what?

Man this was a chore... Many THANKS to google and freinds...this one did it for me.

drove me nutz

noreply@blogger.com (Bob Mihada) — Wed, 09 Mar 2011 07:29:00 +0000

Thanks for the clarification... Drove me nuts trying to follow HA instructions, and I proofing this with local disk(IDE) and main volume SCSI. My partition name not matching, booo, why, google found mOrpheuS and I took the RED Pill.

http://www.frihost.com/forums/vt-36885.html Orz

"Yeah, IDE hard disks (well, devices, actually) are hd. SCSI devices (and kernel-level emulation of SCSI devices, like USB devices or, in some cases, CD-RW drives) are sd.
The standard partitioning scheme nowadays is (assuming your drive is hda): "

note to self

noreply@blogger.com (Bob Mihada) — Sun, 06 Mar 2011 11:06:00 +0000

Config ESXI for HA,VA, HAPPY. Still working on the network throughput.

setup Esxi
enable iscsi
config second nic to support jumbo frames.
install svsan. fiddle till you're comfortable with it. (Luke and Mark rock, TY)
if possible config RDM
stand up second rig, repeat 1-5
More to come.....