There are tables in a parent database where the data needs to be synced to child databases.  Using a simple EXCEPT statement efficiently identifies what rows are different.  Works great if the table schema is the same, otherwise the asterisk must be replaced with column names.

SELECT * FROM
(SELECT * FROM Parent.SchemaName.TableName
EXCEPT
SELECT * FROM Child.SchemaName.TableName AS A

Using column names is not a problem if the columns are known.  The issue I have, is the tables can change, and do, at any time.  The daily auditing stored procedure fails because of this.  If differences are not discovered with the audit, then no data is synced.

Step 1: Create temp tables

IF OBJECT_ID(‘tempdb..#ChildTableColumns’) IS NOT NULL
BEGIN
DROP TABLE #ChildTableColumns
END
CREATE TABLE #ChildTableColumns
( TableName VARCHAR(128)
, ColumnName VARCHAR(128))

IF OBJECT_ID(‘tempdb..#ParentTableColumns’) IS NOT NULL
BEGIN
DROP TABLE #ParentTableColumns
END
CREATE TABLE #ParentTableColumns
( TableName VARCHAR(128)
, ColumnName VARCHAR(128))

Step 2: Get Column Names for each child and parent table from system tables

INSERT INTO #ChildTableColumns
(TableName
, ColumnName)
SELECT T.name AS TableName
, C.name AS ColumnName
FROM ChildDatabase.sys.tables AS T
INNER JOIN ChildDatabase.sys.columns AS C
ON C.object_id = T.object_id
WHERE T.name IN (‘Table1′,’Table2’)

INSERT INTO #ParentTableColumns
(TableName
, ColumnName)
SELECT T.name AS TableName
, C.name AS ColumnName
FROM ParentDatabase.sys.tables AS T
INNER JOIN ParentDatabase.sys.columns AS C
ON C.object_id = T.object_id
WHERE T.name IN (‘Table1′,’Table2’)

Step 3: Get Comma Delimited list of columns that are the same

SELECT DISTINCT C.TableName
, STUFF((SELECT (‘, ‘ + CT.ColumnName + ”)
FROM #ClientTableColumns AS CT
WHERE CT.TableName = C.TableName
ORDER BY CT.ColumnName
FOR XML PATH(”)) , 1, 2 , ”) AS ColumnsList
FROM(SELECT CA.TableName
, CA.ColumnName
FROM #ChildTableColumns AS CA
INNER JOIN #ParentTableColumns AS CO
ON CO.TableName = CA.TableName AND CO.ColumnName = CA.ColumnName) AS C
ORDER BY C.TableName ASC;

Use the comma delimited list in a variable for a dynamic sql statement to replace the asterisk in the above EXCEPT statement.  For my use case the process must iterate over many Parent databases and hundreds of Child databases.

 

 

 

 

Advertisements

Created a new SQL Agent job calling an SSIS package in the local SSIS Catalog.

Error Message:  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Error: 18452, Severity: 14, State: 1.

I struggled with this error knowing that I was working in the same domain and using the same AD account for the Agent and SSIS services.  The connection string in the package was set correctly and worked fine in my individual development environment.

The fix; the server reference in the connection manager was an alias that apparently didn’t have SPN’s correctly created.  I think it was an old alias.  Changed the server reference in the connection manager to the server name as it appears in AD and it worked fine.

 

 

I have SQL Agent Operators named ‘Alerts’ and ‘MSXOperator’.  I use the ‘Alerts’ operator for SQL Server native generated fatal errors.   Didn’t realize the MSXOperator was created on Target SQL Server Agent by the process of enlisting as Target to the Master.  Changing the ‘Alerts’ Operator from my Central Management Server worked like a charm, but the MSXOperator didn’t work.  The error message is “Cannot modify or delete operator ‘MSXOperator’ while this server is a TSX.”.  Of course I googled the error, others had the error but there were no answers.

The error is generated on the target but not the master when this statement is run.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_operator @name=N’MSXOperator’,
@enabled=1,
@pager_days=0,
@email_address=N’SQLAlerts@Firm.com’,
@pager_address=N”,
@netsend_address=N”
GO

Execute the statement on the master instance to change the email address.  In my case I was changing to a distribution group from my personal account.  Look at the Target from Manage Target Servers on the master instance.  The download instruction Operation of “6(RE_ENLIST)” is created and on the next polling will download the email address change to the target.

capture

Check the properties of MSXOperator on the target server.  It’s been changed.  When you exit the properties you will still get the error message “Cannot modify or delete operator ‘MSXOperator’ while this server is a TSX.”.  That’s one of those SQL Server twitches.

Need to get the warranty type and expiration date for all Dell computers and servers in the enterprise and save it to a SQL Server staging table for down stream consumption into the Asset Data Warehouse.

The Powershell script works, but I want the iteration results of the API call to write to a hash table and then make one connection to the database for insert there.


#Initializer server, database, connection, query, and data adapter
$serverName = “Instance Name”
$databaseName = “Database Name”

$Query = “SELECT SerialNumber FROM ViewWithAListOfSerialNumbers’;”

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString =”Server=$serverName;Database=$databaseName;trusted_connection=true;”
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query,$Connection)
$DataSet = New-Object System.Data.DataSet
$DataAdapter.Fill($DataSet) | Out-Null
$Connection.Close()

# Create the function for the call to Dell
Function fnDellWarranty ($ServiceTag) {
$service = New-WebServiceProxy -Uri “http://xserv.dell.com/services/AssetService.asmx?WSDL”
$guid = [guid]::NewGuid()
$info = $service.GetAssetInformation($guid,’Dell Warranty’,$ServiceTag)

if($info -ne $Null){

$Result=@{
‘ServiceLevelDescription’=$info[0].Entitlements[0].ServiceLevelDescription.ToString()
‘EndDate’=$info[0].Entitlements[0].EndDate.ToShortDateString()
‘EntitlementType’=$info[0].Entitlements[0].EntitlementType.ToString()
‘StartDate’ = $($($info[0].Entitlements.StartDate.ToShortDateString()) | Sort-Object)[0]
‘DaysLeft’=$info[0].Entitlements[0].DaysLeft
‘ServiceProvider’=$info[0].Entitlements[0].ServiceProvider
‘ServiceTag’=$info[0].AssetHeaderData.ServiceTag
‘SystemType’=$info[0].AssetHeaderData.SystemType
‘SystemModel’=$info[0].AssetHeaderData.SystemModel
‘ShipDate’=$info[0].AssetHeaderData.SystemShipDate.ToShortDateString()
}

$obj = New-Object -TypeName psobject -Property $result

}

Return $obj
}

#add dataset to array
$SArray = $DataSet.tables[0].Rows

# Clear the Sql that will stage the data
IF ($SArray.count -gt 0) {
$Connection.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $Connection
$cmd.CommandText = “TRUNCATE TABLE stage.DellWarranty;”
$cmd.ExecuteNonQuery()

$Connection.Close()}

$SArray | ForEach-Object {

#Run function
If(!($_.SerialNumber -eq $Null)) {

$fnR = $(fnDellWarranty $_.SerialNumber)

#set variables
$StartDate = $fnR.StartDate
$ShipDate = $fnR.ShipDate
$DaysLeft = $fnR.DaysLeft
$ServiceLevelDescription = $fnR.ServiceLevelDescription
$EndDate = $fnR.EndDate
$SystemType = $fnR.SystemType
$SystemModel = $fnR.SystemModel
$EntitlementType = $fnR.EntitlementType
$ServiceProvider = $fnR.ServiceProvider
$ServiceTag = $fnR.ServiceTag
}
#my mistake was in the loop. I had Return $SerialNumber, $StartDate, then nothing would write to the table. Once that was returned, this worked

if($fnR -ne $Null){
$Connection.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $Connection
$cmd.CommandText = “SET NOCOUNT ON; INSERT INTO dbo.DellWarranty(SerialNumber,ServiceTag,StartDate,ShipDate,DaysLeft,ServiceLevelDescription,EndDate,SystemType,SystemModel,EntitlementType,ServiceProvider)`
VALUES(‘{0}’,'{1}’,'{2}’,'{3}’,'{4}’,'{5}’,'{6}’,'{7}’,'{8}’,'{9}’,'{10}’)”`
-f $_.SerialNumber, $ServiceTag,$StartDate,$ShipDate,$DaysLeft,$ServiceLevelDescription,$EndDate,$SystemType,$SystemModel,$EntitlementType,$ServiceProvide

$cmd.ExecuteNonQuery()

$Connection.Close()
}

}
$SerialNumber =$Null

Many comments praise the use of adding a linked server to AD and creating a view to capture user information.  There is a 1000 record limit, so that presents an obstacle to work around.  I struggled with permissions and never got a linked server to AD to work.  I’m not a Domain Admin and have limited exposure to AD in general.  Links to those methods are below.

Using Power shellflat filesSSIS package and Agent Job I created system to capture AD information on an hourly basis.  I experimented with the Power Shell Active Directory module a lot.  Having never had access to AD, it was a whole new world learning the structure and query syntax.  I made scripts for user group membership, computers  and here is the script to capture user information.

Import-module ActiveDirectory

#————————————————————-

# User details list

#————————————————————-
$cr = “`r” #Carriage Return
$tq = “`”” #Text Qualifier
#Column Headings
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`

(“AccountExpirationDate”+”|”),`
($tq+”AccountExpires”+$tq+”|”),`
($tq+”assistant”+$tq+”|”),`
($tq+”Deleted”+$tq+”|”),`
($tq+”Department”+$tq+”|”),`
($tq+”DistinguishedName”+$tq+”|”),`
($tq+”EmailAddress”+$tq+”|”),`
($tq+”EmployeeNumber”+$tq+”|”),`
($tq+”Enabled”+$tq+”|”),`
($tq+”extensionAttribute4″+$tq+”|”),`
($tq+”extensionAttribute5″+$tq+”|”),`
($tq+”extensionAttribute6″+$tq+”|”),`
($tq+”extensionAttribute7″+$tq+”|”),`
($tq+”extensionAttribute9″+$tq+”|”),`
($tq+”extensionAttribute11″+$tq+”|”),`
($tq+”extensionAttribute12″+$tq+”|”),`
($tq+”extensionAttribute13″+$tq+”|”),`
($tq+”extensionAttribute15″+$tq+”|”),`
($tq+”GivenName”+$tq+”|”),`
($tq+”HomePhone”+$tq+”|”),`
($tq+”Initials”+$tq+”|”),`
($tq+”LastLogonDate”+$tq+”|”),`
($tq+”Manager”+$tq+”|”),`
($tq+”MobilePhone”+$tq+”|”),`
($tq+”Modified”+$tq+”|”),`
($tq+”Name”+$tq+”|”),`
($tq+”Office”+$tq+”|”),`
($tq+”OfficePhone”+$tq+”|”),`
($tq+”SamAccountName”+$tq+”|”),`
($tq+”Surname”+$tq+”|”),`
($tq+”Title”+$tq),`
$cr|
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\PathName\Process\Spool\AdUser.txt
#Data Rows
Get-ADUser -Filter * -Properties * |
ForEach-object {
$AccountExpirationDate = $_.AccountExpirationDate
$AccountExpires = $_.AccountExpires
$assistant = $_.assistant
$Deleted = $_.Deleted
$Department = $_.Department
$DistinguishedName = $_.DistinguishedName
$EmailAddress = $_.EmailAddress
$EmployeeNumber = $_.EmployeeNumber
$Enabled = $_.Enabled
$extensionAttribute4 = $_.extensionAttribute4
$extensionAttribute5 = $_.extensionAttribute5
$extensionAttribute6 = $_.extensionAttribute6
$extensionAttribute7 = $_.extensionAttribute7
$extensionAttribute9 = $_.extensionAttribute9
$extensionAttribute11 = $_.extensionAttribute11
$extensionAttribute12 = $_.extensionAttribute12
$extensionAttribute13 = $_.extensionAttribute13
$extensionAttribute15 = $_.extensionAttribute15
$GivenName = $_.GivenName
$HomePhone = $_.HomePhone
$Initials = $_.Initials
$LastLogonDate = $_.LastLogonDate
$Manager = $_.Manager
$MobilePhone = $_.MobilePhone
$Modified = $_.Modified
$Name = $_.Name
$Office = $_.Office
$OfficePhone = $_.OfficePhone
$SamAccountName = $_.SamAccountName
$Surname = $_.Surname
$Title = $_.Title
“{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}{14}{15}{16}{17}{18}{19}{20}{21}{22}{23}{24}{25}{26}{27}{28}{29}{30}{31}”-f`
([string]$_.AccountExpirationDate+”|”),`
($tq+[string]$_.AccountExpires+$tq+”|”),`
($tq+[string]$_.assistant+$tq+”|”),`
($tq+[string]$_.Deleted+$tq+”|”),`
($tq+[string]$_.Department+$tq+”|”),`
($tq+[string]$_.DistinguishedName+$tq+”|”),`
($tq+[string]$_.EmailAddress+$tq+”|”),`
($tq+[string]$_.EmployeeNumber+$tq+”|”),`
($tq+[string]$_.Enabled+$tq+”|”),`
($tq+[string]$_.extensionAttribute4+$tq+”|”),`
($tq+[string]$_.extensionAttribute5+$tq+”|”),`
($tq+[string]$_.extensionAttribute6+$tq+”|”),`
($tq+[string]$_.extensionAttribute7+$tq+”|”),`
($tq+[string]$_.extensionAttribute9+$tq+”|”),`
($tq+[string]$_.extensionAttribute11+$tq+”|”),`
($tq+[string]$_.extensionAttribute12+$tq+”|”),`
($tq+[string]$_.extensionAttribute13+$tq+”|”),`
($tq+[string]$_.extensionAttribute15+$tq+”|”),`
($tq+[string]$_.GivenName+$tq+”|”),`
($tq+[string]$_.HomePhone+$tq+”|”),`
($tq+[string]$_.Initials+$tq+”|”),`
($tq+[string]$_.LastLogonDate+$tq+”|”),`
($tq+[string]$_.Manager+$tq+”|”),`
($tq+[string]$_.MobilePhone+$tq+”|”),`
($tq+[string]$_.Modified+$tq+”|”),`
($tq+[string]$_.Name+$tq+”|”),`
($tq+[string]$_.Office+$tq+”|”),`
($tq+[string]$_.OfficePhone+$tq+”|”),`
($tq+[string]$_.SamAccountName+$tq+”|”),`
($tq+[string]$_.Surname+$tq+”|”),`
($tq+[string]$_.Title+$tq),`
$cr
} |
Out-File -Width 2147483647 -Encoding ASCII -FilePath \\PathName\Process\Spool\AdUser.txt -append # output

The output does need to be a UNC path. Once the flat files are created then SSIS and T-sql can take it from there to update the database of users and computers.  The project is an inventory of users, computers and the software installed on their computers.  The firm users have some expensive software and the licensing must be closely tracked.

Here are the links to other discussion on pulling in AD to SQL Server.

http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users

http://dbatasks.blogspot.co.uk/2013/03/getting-data-out-of-active-directory.html

There are many scripts in blogs and forums to remove a user from all databases in a SQL Server instance.  I want to keep as simple as possible and just drop the database user account.  This script will error on the databases where the users owns a schema.  I want to know those databases and want to retain the login.  Plus having heard so many warnings about cursors I don’t use them.  Some would say a loop in a WHILE statement is the same thing as a cursor.

Simply put, get a list of databases to check for the user, loop through each, and drop the user if it exists.

DECLARE @Databasename VARCHAR(128)
, @Command VARCHAR(255)
, @UserName VARCHAR(128)

SELECT @UserName = ” — Enter a User

DECLARE @DBList TABLE
(DatabaseName VARCHAR(128))

INSERT INTO @DBList
( DatabaseName )
SELECT name
FROM master.sys.databases
WHERE name <> ‘master’

WHILE (SELECT COUNT(*) FROM @DBList) > 0
BEGIN
SELECT TOP 1 @Databasename = Databasename
FROM @DBList

SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’

PRINT @Command
EXECUTE (@Command )

DELETE FROM @DBList WHERE DAtabaseName = @Databasename
END

 

Centralize SQL Server maintenance with native tool in my goal. Scripts are saved in a database table, exported to a .bat in the file system and executed from a SQL Server Agent job.

The basic statement in the command batch file looks like this:

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH NO_INFOMSGS ” 

I build the statement using the server database ID rather than database name.  There are no naming convention rules like “database names have no spaces” or “do not begin with a number” for systems not created by me.  Just easier to reference databases from numbers than names in the Dynamic SQL code within the stored procedures than handling complex database names.

I want to have a .log file error message result output.  I can then add a step to my job that looks for error message files and forward the file in email to me as notification of errors.

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH ALL_ERRORMSGS, NO_INFOMSGS ” -o \\Lgc004\SQLBackups\Process\DbccCheckDb\Error\LG0113DBCCCheckDB.log                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Paul Randal is a guide:

http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/