SQL - Recover System Databases

  So we had one of those unfortunate things happen that you hope never happens - our SQL server database drive died. Not the drive running SQL, the drive that held the databases. Luckily we had backups of the databases. The drive was repaired but the drive also held the SQL system tables. Without the system tables SQL server will not run. I had the backups, the BAK files for the system tables, but to restore them you need something to restore them to.
  To recover the five system databases that I needed, master, model, msdb, tempdb, and distribution, I used another database server. I created five databases and restored the backups to the newly created databases. Then I copied the database files, the MDFs and LDFs, to the location that my crashed server was expecting to find them and renamed them to the file names that I knew the server would be looking for. When everything was in place I clicked start on the sql service in the server manager and the server started!
  The next day was spent recovering around forty databases. The databases were listed in SQL Server Management Studio (SSMS) but remember because of the crash there were no database files. For each one I used SSMS to create a new database and rename the database file, the MDFs and LDFs, to the names that the server was expecting. Then using SSMS I restored the backup.

TSQL - Dynamic Update

In a database I needed to search every table that began with 'afx' and find any date field that could be set to NULL and replace any dates that were 1/1/1900 with NULL. Also, the fields couldn't be 'date_from' or 'date_to'. I did a TSQL query to search all tables and fieds and found 83 fields that needed to be changed. This was far too many to do idividualy so I wrote a script based off of my original search to do it.

Here is the TSQL I used:

Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C
On T.TABLE_NAME like 'afx%' and T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
and c.Column_Name <> 'date_from' and c.Column_Name <> 'date_to' and C.IS_NULLABLE = 'YES'
And C.Data_Type = 'datetime'
Open myCursor
Fetch Next From myCursor Into @TN, @CN
While @@Fetch_Status <> -1
Begin
Set @SQL = N'Update ' + @TN + ' set ' + @CN + ' = NULL Where [' + @CN + '] = ''1/1/1900'''
Exec sp_executesql @SQL
Fetch Next From myCursor Into @TN, @CN
End
Close myCursor
Deallocate myCursor

SQL - Splitting a Record Field Into Multiple Records

I needed to split a field with multiple lines and a field with comma delimited values into separate records.

I used this function that I found at geekswithblogs:

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

GO

Here is the SQL I used to split a comma delimited string::

select ID as fkAgreementID,  F.Item as Country
from vwSubAgreementAssociatedList T
CROSS APPLY dbo.fnSplit(T.ProjectCountryCode, CHAR(44)) F

SQL - Doing a JOIN With a Case Statement

I needed to do a join between two different tables on a text field. The problem was that the names in the text fields did not always match exactly to each other and some names were very similar to each other. The only solution I could think of match the tables was to do a join using a CASE statement.

Here is the JOIN with CASE statements that I used:

  LEFT OUTER JOIN
  test.dbo.aVend on ((SUBSTRING(test.dbo.aVend.Description, 1, 8) = SUBSTRING(dbo.SubOrganization.Organization, 1, 8))
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) = SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 0
      else 1
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) = SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) = SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) = SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 0
   END = 1)         
   )
   where test.dbo.aVend.[Comp ID] not in (501255, 501221, 500342) or test.dbo.aVend.[Comp ID] is null

Excel - Sequentialy Count The Number Of Repeat Occurances

I a column in Excel I had to put in a sequence number for each matching value in another column. So if my column of values had the series (Red, Red, Blue, Red Blue) then I would end up with (1,2,1,3,2) for the number sequence.

The values that I was sequential numbering were in column D. This is the formula that I used:

=COUNTIF($D$1:D1, D1)

I just entered the formula in the first cell and then filled the remainder of the cells using the fill handle.

Here is a sample of my sequential count in Excel:

SharePoint - Duplicate Content Error

This was a strange problem! A user tried to activate the "SharePoint Server Publishing Infrastructure" feature and received an error saying "A duplicate content type named "Report" was found." In addition to this problem there were a couple of features that were enabled for testing that I was unable to disable.

The problem was fixed by disabling the two features circled below:

Once these two feature were deactivated the "SharePoint Server Publishing Infrastructure" feature could be activated.

SQL - List Size Information for all Tables

I needed to find how many records I had in the "look-up" tables for my web application. All of my look-up tables start with "lu".

This is the query I used:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME like 'lu%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    p.rows

Thanks to stackoverflow.

SQL - Passed the Stanford Database Class

Passed the "Introduction To Databases" class at Stanford!

SQL - Search All Tables and Fields for a Value

I needed to be able to search a database to find a value in any table and field. The first listing was too slow on my large database.

From http://vyaskn.tripod.com:

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

This one runs faster but lacks the detail of the first listing

From experts-exchange:

Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
, @SN as varchar(200), @Exact_Match bit

Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)

-- Replace @myValue with the value you're searching for in the database
Set @myValue = 'Value to Search'
-- 0 for LIKE match, 1 for exact match
Set @Exact_Match = 1

Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name, T.Table_Schema
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C
On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
Where T.Table_Name <> 'dtproperties' And Table_Type = 'Base Table'
And C.Data_Type In ('varchar','char','nvarchar','nchar','sql_variant')
--And C.Data_Type In ('text','ntext')
--And C.Data_Type In ('tinyint','int','bigint','numeric','decimal','money','float','smallint','real','smallmoney')
--And C.Data_Type In ('datetime','dmalldatetime')
-- Fields not searched: image, uniqueidentifier, bit, varbinary, binary, timestamp
Open myCursor
Fetch Next From myCursor Into @TN, @CN, @SN
While @@Fetch_Status <> -1
Begin
If @Exact_Match = 0
Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] Like ''%' + @myValue + '%'''
Else
Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = ''' + @myValue + ''''
--Print @SQL
Exec sp_executesql @SQL
Fetch Next From myCursor Into @TN, @CN, @SN
End
Close myCursor
Deallocate myCursor
Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name
Drop Table #myTable 

SQL - List All Triggers

I'm working on a large database with over 2400 tables and I wanted to know what all of the triggers are and where they're located.

This is the query I ran to get that information:

SELECT
   ServerName   = @@servername,
   DatabaseName = db_name(),
   SchemaName   = t.TABLE_SCHEMA,
   TableName    = object_name( o.parent_obj ),
   TriggerName  = o.name,
   Definition   = c.text
FROM sysobjects o
   JOIN syscomments c ON o.id = c.id
   JOIN INFORMATION_SCHEMA.TABLES t ON object_name( o.parent_obj ) = t.TABLE_NAME
WHERE o.type = 'TR'
ORDER BY SchemaName, TableName, TriggerName

SQL - List Database Tables, Fields, and Other Information

There are a few SQL scripts around for listing table information. This is the one that I use. Just replace "YourDatabaseName" with the name of the database.

USE [YourDatabaseName];
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
T.[name] AS [table_name], AC.[name] AS [column_name],  
TY.[name] AS system_data_type, AC.[max_length], 
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded] 
FROM sys.[tables] AS T  
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]  
WHERE T.[is_ms_shipped] = 0 
ORDER BY T.[name], AC.[column_id]

SQL - Listing All of the Records From Multiple Tables

I was asked to provide a list of all of the records from some look up tables that I use for my project. There are almost 60 look up tables! I spent some time thinking about how to do this in the SQL database. Then I realized, the easy answer is to connect MS Access to the SQL server and then select the tables I need and import them into Access.

XML - Using XQuery to Transform an XML File

What I learned in the Introduction to Databases came in handy. I had a log file in XML that I needed to query on to get some information. Unfortunately because of the structure of the file I wasn't able to query on it to get the data I needed and I wasn't able to import it into Access. Using XQuery, I transformed the file into a format that I could import into Access.

Here is a section of the original XML file:

<BB>
 <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\lkuncan" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\cnedlin" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\cdfield" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\ograw" Content="Deleted" />

This is the format I needed it to be in:

<bRec>
   <Account>INTERNEWS\lkuncan</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\cnedlin</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\cdfield</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\ograw</Account>
   <Content>Deleted</Content>
</bRec>

This is the XQuery Expression I used to transform the XML file:

for $b in doc("Blog.xml")/BB/Content 
return
<bRec>
  <Account> { $b/data(@Account) } </Account>
  <Content>{ $b/data(@Content) }</Content>
</bRec>

I used Kernow to run the expression and get my results.

TSQL - Union, Union All, Order By

I needed to do a pick list in an application that was a combined list from two different SQL tables. One table contains the fields ID, ProjectNo, and ProjectTitle. The other table contains the fields ID, TCode, and Description.

To begin with, for the pick-list to work I needed to have distinct values for the ID fields from the two different tables. I accomplished this by having the starting value for the ID field for one of the tables start at a value that I knew would always be higher than the other table.

The next thing that was needed was an SQL view to return the list. The list had to have the values for each table sorted but not mixed with each other. In other words my list had to be table A sorted plus table B sorted, not table A + B sorted.

What finaly gave me the results I needed was:

SELECT top 10000 ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
order by Value
)Tab1
union all
select * from(
select top 10000 ID, TCode as Value, Description as DescValue
from Department
order by Value
)Tab2

There is quite a difference between doing a Union All vs. Union. Give it a try and see what you get.

I also tried these:

SELECT ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
union 
select ID, TCode as Value, Description as DescValue
from Department
order by Value

SELECT ID, ProjectNo, ProjectTitle
FROM dbo.Project
group by ID, ProjectNo, ProjectTitle
union 
select ID, TCode, Description
from Department
group by ID, TCode, Description
order by 2 asc

SQL Dynamic Pivot Tables, BIRT Cross Tab Report

The day was spent working on getting a cross tab report in BIRT. It was a series of two steps forward and one step back, but at the end of the day it was done! Here's how I did it.

Here is a sample of the data in the table:

The report needs to do a monthly sum of the amounts for each Region/Office where the documentation is not complete. I started out by doing a regular pivot table in SQL. This worked fine but then I realized that my months and years would be changing so what I needed was a dynamic pivot table. Using this example from Kodyaz, I came up with this dynamic pivot table:

DECLARE @PivotColumnHeaders VARCHAR(Max)
--Create a list for the column names
--This list will be used in our PIVOT down below
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + [Month] + ']',
    '[' + [Month] + ']'
  )
FROM (select distinct top 50  datename(month, [dtAccountingPeriod]) + ' ' + cast(YEAR([dtAccountingPeriod]) AS varchar) as [Month]
, YEAR([dtAccountingPeriod]) AS IDXY, Month([dtAccountingPeriod]) AS IDXM
From[INformV2].[dbo].[FoAccountingDocumentAudit]
where [DocumentationComplete] = 0
order by IDXY, IDXM) TTD

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from(
SELECT 
luFoOfficeRegion.Value as Region
,FoOffice.OfficeIdentifier as Office
,datename(month, [dtAccountingPeriod]) + '' '' + cast(YEAR([dtAccountingPeriod]) AS varchar)  as [Month]
,[amtUSD] 
FROM [INformV2].[dbo].[FoAccountingDocumentAudit]
join FoOffice on fkOfficeID = FoOffice.ID
join luFoOfficeRegion on [INformV2].[dbo].[FoAccountingDocumentAudit].fkRegionID = luFoOfficeRegion.ID
where [DocumentationComplete] = 0
) Datatable
PIVOT(sum([amtUSD]) for [Month] in (' + @PivotColumnHeaders + ' )
)as pvtMonth
order by Region
'
EXECUTE(@PivotTableSQL)

The result was this:

Very nice!
Now on to BIRT. I started to create the report in BIRT and realized there was a problem, BIRT likes to work with static columns. Back to SQL.
I used this in a view to return my data:

SELECT top 100    dbo.luFoOfficeRegion.Value AS Region, dbo.FoOffice.OfficeIdentifier AS Office
,datename(month, dbo.FoAccountingDocumentAudit.dtAccountingPeriod) + ' ' + cast(YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) AS varchar)  as [MonthYear]
,SUM(dbo.FoAccountingDocumentAudit.amtUSD) AS Total
,YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Year
,Month(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Month
FROM dbo.FoAccountingDocumentAudit INNER JOIN
dbo.FoOffice ON dbo.FoAccountingDocumentAudit.fkOfficeID = dbo.FoOffice.ID INNER JOIN
dbo.luFoOfficeRegion ON dbo.FoAccountingDocumentAudit.fkRegionID = dbo.luFoOfficeRegion.ID
WHERE dbo.FoAccountingDocumentAudit.DocumentationComplete = 0
GROUP BY dbo.luFoOfficeRegion.Value, dbo.FoOffice.OfficeIdentifier, dbo.FoAccountingDocumentAudit.dtAccountingPeriod

The year and month are used in BIRT to sort the columns.

The returned data:

Back to BIRT. I used the view in my report and created a data cube:

Then using the data in a BIRT "Cross Tab", applying a little formating, and here's my final result:

SharePoint 2010 - Fast Search Disk Space Problem

The organization I work for uses Fast Search and the drive where the search index is being stored was running low on disk space. I thought it was the index itself that was growing but as it turned out my problem was being caused by click-through. The folder, "...data\sprel\worker\build_proc", was being filled with files. I used the "spreladmin" utilty to stop the process and deleted the files. So far I have not been able to figure out what is causing the problem and since, in our case, there isn't much of a benefit in using click-through I just decided to leave the service off. I did discover some nice command-line tools for working with Fast Search Server. You can find them here.

TSQL - Using Like in a TSQL Join

More work importing data! This time for all of the tables I imported I needed to set a foreign key field for each record to indicate who created the record.

Each table that I imported the data from has a field called "Record Owner" that is the first and last name of the record creator plus an additional code. An example would be "Bob Smith 12345".

I have a table called "AppUser" that has primary key field "ID", and a "FirstName" and a "LastName" field. In my imported tables I have a field, "CreatedBy", that is the foreign key to the "ID" field in the "AppUser" table. Because of the additional code characters in the "Record Owner" field, I had to use a TSQL "like" to accomplish the join.

Here is my final TSQL query for populating the "CreatedBy" field:

UPDATE [INformV2].[dbo].[FoOfficeReportingPerformance]
SET [CreatedBy] = qbO.ID   
FROM [INformV2].[dbo].[FoOfficeReportingPerformance]
INNER JOIN [Importsv1].[dbo].[FoOfficeReportingPerformance] qbA
ON [INformV2].[dbo].[FoOfficeReportingPerformance].[QBRecordID] = qbA.[Office performance record ID#]
INNER JOIN [INformV2].[dbo].[AppUser] qbO
ON  qbA.[Record Owner] like (qbO.[FirstName] + ' ' +  qbO.[LastName] + '%')

Table "[Importsv1].[dbo].[FoOfficeReportingPerformance]" is the originally imported data.
Table "[INformV2].[dbo].[FoOfficeReportingPerformance]" is my final table. I imported data from "[Importsv1].[dbo].[FoOfficeReportingPerformance]" into this table. It has a field "QBRecordID" that matches the primary key, "Office performance record ID#", for the records in "[Importsv1].[dbo].[FoOfficeReportingPerformance]".

Anyway, what is interesting to me here is that you can use "like" as a comparisoin operator in a TSQL JOIN!

TSQL - Bitwise Operators

I recently had to set a field by ORing three different fields in a table. The easy solution was to use the Bitwise OR operator. So in the table design in SQL Manager, the "Computed Column Specification" for the field I set the formula to (([Field1]|[Field2])|[Field3]). You can learn more about the Bitwise Operators here.