-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'
Monday, June 24, 2019
Linked Server Refering to Self
Likely a problem with DNS tables, this will fix it with a valid SQL user.
-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'
-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'
Friday, June 21, 2019
sp_send_dbmail solution for attachments
When using the @query parameter with sp_send_dbmail via the SQL
Agent to send an attachment in the email you may get the following error:
“Failed to initialize sqlcmd library
with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.”
Apparently this is the sp_send_dbmail
equivalent of the nearly useless Visual Basic “Object variable or With block
variable not set.” error.
Note that the syntax was
correct and everything worked fine when operating from a query session.
One way to get around it is
to preface the execution context with a power user:
EXECUTE AS LOGIN = 'ElmerFudd'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alert
Stuff',
@recipients = 'bugz@bunny.org; ',
@subject = 'Favorite vacations',
@query = N'
use msdb;
go
select top 10 ...
This took quite an effort to solve so I decided to share …
2¢
Thursday, May 30, 2019
FYI: SS2016 Requires .Net FW 3.5 for DBMail
This was painful enough to figure out that I figured I would share the information with the entire group.
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.
Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++
Tools:
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.
Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++
Tools:
This was painful enough to figure out that I figured I would
share the information with the entire group.
Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard
(will not install from a exe).
Detailz …
DatabaseMail.exe (in the instance Binn directory) which is
invoked by the Service Broker which is part of the Core database engine since
2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency.
Why it will not work with a more recent version of .Net is a mystery, perhaps
they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).
Essentially there was no error information available, mail
items would just sit in the queue.
Starting the exe itself on the server or invoking dbmail via
a sql interface puts this in the application log of the server:
“The activated proc
'[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue'
output the following: 'Could not create DatabaseMail.exe process.
Executing API 'CreateProcess' failed with error number 14001.'”
~
Which, after a lot of time with The Google, led to the
answer. Don’t fall for missing DatabaseMail.exe.config threads as that is a red
herring.
2¢, J++
Tools:
SELECT
CASE sent_status
WHEN 0 THEN 'Unsent'
WHEN 1 THEN 'Sent'
WHEN 2 THEN 'Failed'
WHEN 3 THEN 'Retrying'
END AS sent_status_desc
FROM msdb..sysmail_mailitems
SELECT *
FROM msdb..sysmail_event_log
ORDER BY log_id DESC
SELECT @@VERSION
SELECT sent_status, send_request_date a, *
FROM msdb.dbo.sysmail_mailitems
ORDER BY a DESC
SELECT *
FROM msdb.dbo.sysmail_sentitems
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
SELECT is_broker_enabled
FROM sys.databases
WHERE [name] = 'msdb';
EXECUTE msdb.dbo.sysmail_help_status_sp
-- sp_configure
'Database Mail XPs'
SELECT *
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC
-- select @@VERSION
SELECT *
FROM Msdb.dbo.sysmail_profile
SELECT *
FROM Msdb.dbo.sysmail_event_log order by log_date desc
SELECT *
FROM Msdb.dbo.sysmail_faileditems
EXEC Msdb.dbo.sysmail_help_queue_sp
Thursday, May 16, 2019
SCRIPTING SQL SERVER OBJECTS WITH DBATOOLS – BEYOND DEFAULT OPTIONS
https://claudioessilva.eu/2019/05/15/scripting-sql-server-objects-with-dbatools-beyond-default-options/
Probably you had the need to script out some objects from a SQL Server instance/database and this is quite easy. You just need to right click on the object (well…not on every single one, try it with an Availability Group :-), no script option available) select “Script XXXX as” and you have it.
But have you realized that this option doesn’t bring all the stuff?
Let’s say you are scripting a table and you have a Non-Clustered index or a trigger…using this option some of the objects under the table will not be scripted out.
I understand this as each one of this is a different object, which means if you go to each one you can right click and script just that one.
SSMS – “Generate Scripts…” option. This is a tedious work and you can easily miss some objects. What can we do on SSMS to make this task easier?
You can accomplish this task by using the “Generate Scripts…” option under “Tasks” when you right-click on the database:
This will open the wizard and at the “Set Scripting Options” you can just click on the “Advanced” button and there you can change the properties.
Here you can see that some defaults are in place, and “Script Indexes” is one of them.
This is much easier right? All-in-one in a single scripting operation.
What about automating this task? You want to script out multiple objects from different instances/databases.
Enter dbatools’ “Export-” commands
To search for commands within dbatools we can use the Find-DbaCommand.
1
Find-DbaCommand -Tag Export
This command give to us a nice Synopsis text that help to find which command is the one we want. From the output we can see that we have (as of v0.9.824) 5 commands tagged as Export.
To replicate our SSMS example using PowerShell we will use the Export-DbaScript.
Don’t forget to use Get-Help cmdlet to find the available parameters and get some examples on how you can use the command.
1
Get-Help Export-DbaScript -Detailed
Tip: Try other switches like -Examples or even -ShowWindow (won’t work on PSCore but dbatools does!) switches.
Example using our “MyTable” object
Here is how MyTable looks like:
3 Columns
1 Default constraint
1 Non-Clustered Index
1
Get-DbaDbTable -SqlInstance SQL1 -Database DB1 -Table MyTable | Export-DbaScript -Passthru
Note: I’m using -PassThru parameter to output the script to the console, by default it will create a SQL file.
The output of this execution is even more incomplete when comparing with SSMS. Here, we dont even get the default constraint scripted.
Using “New-DbaScriptingOption” command
dbatools has a command that makes it possible to create an object of type ScriptingOptions.
Then we can change the properties like we have done before on the “Generate Scripts…” option on SSMS.
1
2
$options = New-DbaScriptingOption
$options | Get-Member
Use Get-Member so you can see what properties the object offers.
Here we start seeing what we need.
By default what are the values of properties like NonClusteredIndexes and DriDefaults
1
2
3
$options = New-DbaScriptingOption
$options.NonClusteredIndexes
$options.DriDefaults
False! That explains why they are “missing” from our default Export-DbaScript output.
NOTE: Export-DbaUser comamnd can also leverage on this object. Try it.
Let’s change this options to $true and pass our $options object as the value of the -ScriptingOptionsObject parameter and run the command again.
1
2
3
4
$options = New-DbaScriptingOption
$options.NonClusteredIndexes = $true
$options.DriDefaults = $true
Get-DbaDbTable -SqlInstance SQL1 -Database DB1 -Table MyTable | Export-DbaScript -Passthru -ScriptingOptionsObject $options
Nice! Now we can see all the stuff.
Try it yourself
See the other options available, change the values, rerun and analyse the output.
Do you need to export it to run on a lower SQL Server version? Change the TargetServerVersion option
1
2
#Will script the code like SQL Server 2014
$options.TargetServerVersion = "Version120"
You want to include the “IF NOT EXISTS” statement? Change the “IncludeIfNotExists” option.
Here is an example to script out a list of tables (add more to the $TableName variable):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$SourceServer = "SQL1";
$SourceDB = "DB1";
$TableName = "MyTable", 'YourTable';
$options = New-DbaScriptingOption
$options.DriPrimaryKey = $true
$options.DriForeignKeys = $true
$options.DriNonClustered = $true
$options.DriChecks = $true
$options.DriDefaults = $true
$options.Indexes = $true
$options.IncludeIfNotExists = $true
$TableName | Foreach-Object {
Get-DbaDbTable -ServerInstance $SourceServer -Database $SourceDB -Table $_ | Export-DbaScript -ScriptingOptionsObject $options -Passthru;
}
Availability Groups example using dbatools
Try it yourself:
1
Get-DbaAvailabilityGroup -SqlInstance SQL1 -AvailabilityGroup SQL1_AG1 | Export-DbaScript -Passthru
Summary
We have seen how we can leverage on some dbatools commands to generate T-SQL scripts from objects. This way we can versioning or just run them on other instace/database. We have seen what default options it (doesn’t) brings and how to add more options to it.
We also saw that, for some objects, the script option is not available. As example Availability Groups, but dbatools can help you here too.
Thanks for reading!
Wednesday, October 17, 2018
A modified and hardened process that updates statistics
SET NOCOUNT ON
DECLARE @name VARCHAR(128) = '' -- database name
, @update_stats varchar(max) = 'EXECUTE {DBNAME}..sp_updatestats'
, @sql_script varchar(max) = ''
, @errorMessage varchar(max) = ''
, @Recips VARCHAR (1024) = 'foo@foo.org; '
-- , @Subject VARCHAR (1024) = 'TESTING: Failure of maintenance plan for updating statistics '
, @Subject VARCHAR (1024) = 'Failure of maintenance plan for updating statistics '
-- DEV
-- , @mailProfile VARCHAR (64) = 'Mail'
-- QA
, @mailProfile VARCHAR (64) = 'Mail'
-- Prod
-- , @mailProfile VARCHAR (64) = 'Mail'
/*
SELECT @Recips += ISNULL (ap.pValue, 'Elmer; ')
FROM dbo.params ap
WHERE ap.pName = 'Elmer'
*/
DECLARE db_cursor CURSOR FOR
-- Force an error for testing ...
-- SELECT name
SELECT QUOTENAME (name)
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','AspNetSqlSessionState')
OPEN db_cursor
BEGIN TRY
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @name
SELECT GETDATE () [Start]
SET @sql_script = REPLACE(@update_stats, '{DBNAME}', @name)
EXEC (@sql_script)
FETCH NEXT FROM db_cursor INTO @name
SELECT GETDATE () [End]
END
END TRY
BEGIN CATCH
SELECT @errorMessage += @name + ': ' + someDB.dbo.fnGetErrorTryCatch ()
END CATCH
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT @errorMessage
IF ISNULL (@errorMessage, '') != ''
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @mailProfile
, @recipients = @Recips
, @body_format = 'HTML'
, @body = @errorMessage
, @subject = @Subject
END
Tuesday, October 16, 2018
How to tell if statistics are actually being updated
Even if everything 'looks' good:
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects o
JOIN sys.indexes i ON o.name = 'ORDERs' AND o.object_id = i.object_id;
GO
Monday, March 19, 2018
Deceptively simple ... and potentially evil
drop table tNUM
CREATE TABLE dbo.tNUM
(
NUM INT PRIMARY KEY
);
WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), -- 2
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 4
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 16
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 256
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296
L6 AS (SELECT 1 AS c FROM L5 AS A CROSS JOIN L5 AS B), -- 18,446,744,073,709,551,616
...
---- Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L2)
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5) --L6)
INSERT INTO dbo.tNUM ( NUM )
SELECT -- TOP (1000)
n
FROM Nums
--SELECT TOP (1000)
-- n
--FROM Nums
ORDER BY n;
SELECT COUNT (*) FROM tNum
Friday, March 2, 2018
FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server
Context: FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server
FT_MON, FT_SEARCH, FT_CRAWL
Resolution:
a.) Verify that FT is the issue with sp_who2
b.) Put the database in single user mode and drop the index.
ALTER DATABASE [foobar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP FULLTEXT INDEX ON foo;
GO
ALTER DATABASE [foobar] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
USE [Shared]
GO
ALTER
-- CREATE
PROC dbo. NoBotFindFullTextIndices (
@TableName VARCHAR (256) = ''
, @DatabaseName VARCHAR (256) = ''
, @found INT = 0 OUTPUT
)
AS
BEGIN
/***************************************************************
Author
2018-02-28 15:42:50.917 1968.95
Source:
https://stackoverflow.com/questions/16280918/how-to-find-full-text-indexing-on-database-in-sql-server-2008
Execution:
DECLARE @foundIt INT = 0
EXEC [Shared].dbo.sp_NoBotFindFullTextIndices
'OLS_PRODUCT_SEARCH'
, 'SMCO-PERSONIFY-DEV'
, @found = @foundIt OUTPUT
Note - a simpler way but with less utility ...
SELECT DISTINCT
object_name(fic.[object_id])as table_name
, [name]
FROM [foobar].sys.fulltext_index_columns fic
JOIN [foobar].sys.columns c
on c.[object_id] = fic.[object_id]
and c.[column_id] = fic.[column_id]
****************************************************************/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @execMe NVARCHAR (2048) = ''
SET @execMe = '
SELECT COUNT (t.name)
FROM ['
+ @DatabaseName
+ '].sys.tables t
JOIN ['
+ @DatabaseName
+ '].sys.fulltext_indexes fi
ON t.[object_id] = fi.[object_id]
JOIN ['
+ @DatabaseName
+ '].sys.fulltext_index_columns ic
ON ic.[object_id] = t.[object_id]
JOIN ['
+ @DatabaseName
+ '].sys.columns cl
ON ic.column_id = cl.column_id
AND ic.[object_id] = cl.[object_id]
JOIN ['
+ @DatabaseName
+ '].sys.fulltext_catalogs c
ON fi.fulltext_catalog_id = c.fulltext_catalog_id
JOIN ['
+ @DatabaseName
+ ']. sys.indexes i
ON fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id]
WHERE t.name = '''
+ @TableName
+ ''';'
EXEC @found = sp_executeSQL @execMe
END
Monday, December 12, 2016
Power Shell - Get UNC Path Directories
http://stackoverflow.com/questions/23574653/running-get-childitem-on-unc-path-works-in-powershell-but-not-in-powershell-run
cd env:
$foo = @{Name = "Foo"}
$foo.Path = "\\serverPath\someDir\"
$bar = @{Name = "Bar"}
$bar.Path = "\\serverPath\someDir\"
cd c: #THIS IS THE CRITICAL LINE
@( $foo, $bar ) | ForEach-Object {
$item = Get-ChildItem $_.Path
# Do things with item
Write-Host $item
}
Monday, October 3, 2016
Could not load file or assembly or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
If all the subprojects are on the right version check the app pool.
Wednesday, July 20, 2016
Monday, May 16, 2016
SQL Server Last Restore Time
-- Last Restore time
WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1
Tuesday, March 15, 2016
ASPState DB
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
aspnet_regsql.exe -ssadd -sstype p -S server\instance -U sa -P somePass
Wednesday, January 6, 2016
Entity Framework (EF) 6 (or at least 6.13) Error - Missing Primary Keys
In the past EF would blow up when trying to make an *.edmx file on a table with no PK. As of EF 6 (or at least 6.13) it allows them but the result set returned when performing a join on a table will this version can/will be incorrect.
A deadly, but silent, error.
A deadly, but silent, error.
SQL Server Finding the Object Alter Date
select TOP 1 * from sys.procedures order by create_date desc
select TOP 1 * from sys.tables order by create_date desc
select TOP 1 * from sys.views order by create_date desc
/*
CREATE PROC jkAlterProcTest
AS
SELECT 1
CREATE TABLE jkAlterTableTest (a int)
CREATE VIEW jkAlterViewTest
AS
SELECT a
FROM jkAlterTableTest
ALTER PROC jkAlterProcTest
AS
SELECT 2
ALTER TABLE jkAlterTableTest ADD b int
ALTER VIEW jkAlterViewTest
AS
SELECT a, b
FROM jkAlterTableTest
*/
select TOP 1 * from sys.tables order by create_date desc
select TOP 1 * from sys.views order by create_date desc
/*
CREATE PROC jkAlterProcTest
AS
SELECT 1
CREATE TABLE jkAlterTableTest (a int)
CREATE VIEW jkAlterViewTest
AS
SELECT a
FROM jkAlterTableTest
ALTER PROC jkAlterProcTest
AS
SELECT 2
ALTER TABLE jkAlterTableTest ADD b int
ALTER VIEW jkAlterViewTest
AS
SELECT a, b
FROM jkAlterTableTest
*/
Wednesday, October 14, 2015
Enable CLR for db / user
MAKE SURE YOU ARE TARGETING THE CORRECT DB
For instance - DNN and not Person
USE dbFoo;
GO;
EXEC
sp_changedbowner 'sa'
GO;
ALTER DATABASE dbFoo
SET TRUSTWORTHY ON;
GO;
USE MASTER;
GO;
GRANT EXTERNAL
ACCESS ASSEMBLY TO [domain\userBar]; -- note: server-wide
GO;
USE dbFoo;
GO;
sp_configure 'show advanced options',
1;
GO;
RECONFIGURE WITH OVERRIDE;
GO;
sp_configure 'clr enabled', 1;
GO;
RECONFIGURE WITH OVERRIDE;
GO;
Thursday, September 3, 2015
Disable Aero Shake, Win 7; All Windows Automagically Minimizing
http://www.howtogeek.com/howto/windows-7/disable-aero-shake-in-windows-7/
Disable Aero Shake in Windows 7
One of the interesting new features in Windows 7 is the way you can grab a window by the title bar and “shake” it back and forth to minimize everything else. It’s a fun feature, but just in case you want to disable it we’ve got the solution for you.
Disable Aero Shake Manual Registry Hack
Open up regedit.exe through the start menu search or run box, and then navigate down to the following key:
HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows
Once you are there, right-click on the Windows key and create a new key called Explorer.
Now right-click on the right-hand side, create a new 32-bit DWORD with the following settings:
- Name: NoWindowMinimizingShortcuts
- Value: 1
Once you’ve created this, you should log off and back on for the change to take effect.
Downloadable Registry Hack
Simply download, extract, and double-click on DisableAeroShake.reg to enter the information into the registry. To re-enable use the other file.
Prevent RDC from Logging Out Your Active Session When Logging In from Another Box
Credit: Brian Trexler
"I think I found the incredibly annoying setting that logs
you out (and closes your active work) when disconnecting a Remote Desktop
Session on Windows 7! I’ve been determined to slay this dragon.
I’m going to keep an eye on this to ensure it sticks and
never logs me out. Let me know what you guys find.
Here is the maximum setting for “MaxDisconnectionTime”…"
Back up the registry, navigate to:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\Terminal Services\
Change (decimal) value for MaxDisconnectionTime to 4294967295
Wednesday, August 26, 2015
VBA Pattern Matching with the Levenshtein Algorithm
Public Sub match()
'MsgBox ("asdfs")
For i = 3 To 200
If (Cells(i, 2).Value <> "") Then
If (Trim(UCase(Cells(i, 2).Value)) = Trim(UCase(Cells(i, 3).Value))) _
And (Trim(UCase(Cells(i, 4).Value)) = Trim(UCase(Cells(i, 5).Value))) _
And (Trim(UCase(Cells(i, 6).Value)) = Trim(UCase(Cells(i, 7).Value))) _
And (Trim(UCase(Cells(i, 8).Value)) = Trim(UCase(Cells(i, 9).Value))) _
Then
Cells(i, 13).Value = "Y"
Else
Cells(i, 13).Value = "N"
End If
Cells(i, 14).Value = Levenshtein(Cells(i, 2).Value, Cells(i, 3).Value)
Cells(i, 15).Value = Levenshtein(Cells(i, 4).Value, Cells(i, 5).Value)
Cells(i, 16).Value = Levenshtein(Cells(i, 6).Value, Cells(i, 7).Value)
Cells(i, 17).Value = Levenshtein(Cells(i, 8).Value, Cells(i, 9).Value)
Cells(i, 18).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value + Cells(i, 17).Value
Else
Cells(i, 13).Value = ""
Cells(i, 14).Value = ""
Cells(i, 15).Value = ""
Cells(i, 16).Value = ""
Cells(i, 17).Value = ""
Cells(i, 18).Value = ""
End If
' Write sql
' Good ones
If ((Cells(i, 13).Value = "Y") Or (Cells(i, 18).Value < 5)) And (Cells(i, 2).Value <> "") Then
' Likely good
' If (Cells(i, 18).Value > 4) (Cells(i, 18).Value < 10) Then
writeSQL (i)
' the names are an exact match but there is not city/state/zip information
' this is the case with a lot of the SMO_2015_Voting_Member_List ones
ElseIf (UCase(Trim(Cells(i, 2).Value)) = UCase(Trim(Cells(i, 3).Value))) _
And UCase(Trim((Cells(i, 4).Value)) = UCase(Trim(Cells(i, 5).Value))) _
And UCase(Trim((Cells(i, 6).Value)) = "NULL") _
And UCase(Trim((Cells(i, 8).Value)) = "NULL") _
And UCase(Trim((Cells(i, 10).Value)) = "NULL") Then
writeSQLNULL (i)
Else
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
End If
Next i
End Sub
Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
'https://en.wikipedia.org/wiki/Levenshtein_distance
'http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel
string1 = UCase(Trim(string1))
string2 = UCase(Trim(string2))
Dim i As Long, j As Long
Dim string1_length As Long
Dim string2_length As Long
Dim distance() As Long
string1_length = Len(string1)
string2_length = Len(string2)
ReDim distance(string1_length, string2_length)
For i = 0 To string1_length
distance(i, 0) = i
Next
For j = 0 To string2_length
distance(0, j) = j
Next
For i = 1 To string1_length
For j = 1 To string2_length
If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
distance(i, j) = distance(i - 1, j - 1)
Else
distance(i, j) = Application.WorksheetFunction.Min _
(distance(i - 1, j) + 1, _
distance(i, j - 1) + 1, _
distance(i - 1, j - 1) + 1)
End If
Next
Next
Levenshtein = distance(string1_length, string2_length)
End Function
Function writeSQL(ByVal i As Integer)
Dim customer As String, firstName As String, lastName As String, _
city As String, state As String, zip As String, _
_id As String, sqlSelect As String, sqlUpdate As String
customer = RTrim(Cells(i, 1).Value)
firstName = RTrim(Cells(i, 3).Value)
lastName = RTrim(Cells(i, 5).Value)
city = RTrim(Cells(i, 6).Value)
state = RTrim(Cells(i, 8).Value)
zip = RTrim(Cells(i, 10).Value)
_id = RTrim(Right("0000000000000" & Cells(i, 12).Value, 12))
sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
"' AND [state] = '" + state + "' AND zip = '" + zip + "'"
sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca. = '" + _id + _
"' FROM ca WHERE ca.firstName = '" + firstName + "' AND " + _
"ca.lastName = '" + lastName + "' AND city = '" + city + "' AND [state] = '" + state + _
"' AND zip = '" + zip + "' AND customer = '" + customer + "'"
If (firstName = "firstName") Then
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
Rows(i).Interior.Color = 45535
Else
'Cells(i, 20).Value = sqlSelect
Cells(i, 20).Value = ""
Cells(i, 21).Value = sqlUpdate
Rows(i).Interior.Color = 65535
End If
End Function
Function writeSQLNULL(ByVal i As Integer)
Dim customer As String, firstName As String, lastName As String, _
city As String, state As String, zip As String, _
ship_master_customer_id As String, sqlSelect As String, sqlUpdate As String
customer = Cells(i, 1).Value
firstName = Cells(i, 3).Value
lastName = Cells(i, 5).Value
city = Cells(i, 6).Value
state = Cells(i, 8).Value
zip = Cells(i, 10).Value
_id = Right("0000000000000" & Cells(i, 12).Value, 12)
sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
"' AND [state] = '" + state + "' AND zip = '" + zip + "'"
sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca.= '" + _id + _
"' FROM ca ca WHERE ca.firstName = '" + firstName + "' AND " + _
"ca.lastName = '" + lastName + "' AND city IS NULL AND [state] IS NULL AND zip IS NULL AND customer = '" + customer + "'"
If (firstName = "firstName") Then
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
Rows(i).Interior.Color = 45535
Else
'Cells(i, 20).Value = sqlSelect
Cells(i, 20).Value = ""
Cells(i, 21).Value = sqlUpdate
Rows(i).Interior.Color = 65535
End If
End Function
'MsgBox ("asdfs")
For i = 3 To 200
If (Cells(i, 2).Value <> "") Then
If (Trim(UCase(Cells(i, 2).Value)) = Trim(UCase(Cells(i, 3).Value))) _
And (Trim(UCase(Cells(i, 4).Value)) = Trim(UCase(Cells(i, 5).Value))) _
And (Trim(UCase(Cells(i, 6).Value)) = Trim(UCase(Cells(i, 7).Value))) _
And (Trim(UCase(Cells(i, 8).Value)) = Trim(UCase(Cells(i, 9).Value))) _
Then
Cells(i, 13).Value = "Y"
Else
Cells(i, 13).Value = "N"
End If
Cells(i, 14).Value = Levenshtein(Cells(i, 2).Value, Cells(i, 3).Value)
Cells(i, 15).Value = Levenshtein(Cells(i, 4).Value, Cells(i, 5).Value)
Cells(i, 16).Value = Levenshtein(Cells(i, 6).Value, Cells(i, 7).Value)
Cells(i, 17).Value = Levenshtein(Cells(i, 8).Value, Cells(i, 9).Value)
Cells(i, 18).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value + Cells(i, 17).Value
Else
Cells(i, 13).Value = ""
Cells(i, 14).Value = ""
Cells(i, 15).Value = ""
Cells(i, 16).Value = ""
Cells(i, 17).Value = ""
Cells(i, 18).Value = ""
End If
' Write sql
' Good ones
If ((Cells(i, 13).Value = "Y") Or (Cells(i, 18).Value < 5)) And (Cells(i, 2).Value <> "") Then
' Likely good
' If (Cells(i, 18).Value > 4) (Cells(i, 18).Value < 10) Then
writeSQL (i)
' the names are an exact match but there is not city/state/zip information
' this is the case with a lot of the SMO_2015_Voting_Member_List ones
ElseIf (UCase(Trim(Cells(i, 2).Value)) = UCase(Trim(Cells(i, 3).Value))) _
And UCase(Trim((Cells(i, 4).Value)) = UCase(Trim(Cells(i, 5).Value))) _
And UCase(Trim((Cells(i, 6).Value)) = "NULL") _
And UCase(Trim((Cells(i, 8).Value)) = "NULL") _
And UCase(Trim((Cells(i, 10).Value)) = "NULL") Then
writeSQLNULL (i)
Else
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
End If
Next i
End Sub
Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
'https://en.wikipedia.org/wiki/Levenshtein_distance
'http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel
string1 = UCase(Trim(string1))
string2 = UCase(Trim(string2))
Dim i As Long, j As Long
Dim string1_length As Long
Dim string2_length As Long
Dim distance() As Long
string1_length = Len(string1)
string2_length = Len(string2)
ReDim distance(string1_length, string2_length)
For i = 0 To string1_length
distance(i, 0) = i
Next
For j = 0 To string2_length
distance(0, j) = j
Next
For i = 1 To string1_length
For j = 1 To string2_length
If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
distance(i, j) = distance(i - 1, j - 1)
Else
distance(i, j) = Application.WorksheetFunction.Min _
(distance(i - 1, j) + 1, _
distance(i, j - 1) + 1, _
distance(i - 1, j - 1) + 1)
End If
Next
Next
Levenshtein = distance(string1_length, string2_length)
End Function
Function writeSQL(ByVal i As Integer)
Dim customer As String, firstName As String, lastName As String, _
city As String, state As String, zip As String, _
_id As String, sqlSelect As String, sqlUpdate As String
customer = RTrim(Cells(i, 1).Value)
firstName = RTrim(Cells(i, 3).Value)
lastName = RTrim(Cells(i, 5).Value)
city = RTrim(Cells(i, 6).Value)
state = RTrim(Cells(i, 8).Value)
zip = RTrim(Cells(i, 10).Value)
_id = RTrim(Right("0000000000000" & Cells(i, 12).Value, 12))
sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
"' AND [state] = '" + state + "' AND zip = '" + zip + "'"
sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca. = '" + _id + _
"' FROM ca WHERE ca.firstName = '" + firstName + "' AND " + _
"ca.lastName = '" + lastName + "' AND city = '" + city + "' AND [state] = '" + state + _
"' AND zip = '" + zip + "' AND customer = '" + customer + "'"
If (firstName = "firstName") Then
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
Rows(i).Interior.Color = 45535
Else
'Cells(i, 20).Value = sqlSelect
Cells(i, 20).Value = ""
Cells(i, 21).Value = sqlUpdate
Rows(i).Interior.Color = 65535
End If
End Function
Function writeSQLNULL(ByVal i As Integer)
Dim customer As String, firstName As String, lastName As String, _
city As String, state As String, zip As String, _
ship_master_customer_id As String, sqlSelect As String, sqlUpdate As String
customer = Cells(i, 1).Value
firstName = Cells(i, 3).Value
lastName = Cells(i, 5).Value
city = Cells(i, 6).Value
state = Cells(i, 8).Value
zip = Cells(i, 10).Value
_id = Right("0000000000000" & Cells(i, 12).Value, 12)
sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
"' AND [state] = '" + state + "' AND zip = '" + zip + "'"
sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca.= '" + _id + _
"' FROM ca ca WHERE ca.firstName = '" + firstName + "' AND " + _
"ca.lastName = '" + lastName + "' AND city IS NULL AND [state] IS NULL AND zip IS NULL AND customer = '" + customer + "'"
If (firstName = "firstName") Then
Cells(i, 20).Value = ""
Cells(i, 21).Value = ""
Rows(i).Interior.Color = 45535
Else
'Cells(i, 20).Value = sqlSelect
Cells(i, 20).Value = ""
Cells(i, 21).Value = sqlUpdate
Rows(i).Interior.Color = 65535
End If
End Function
Thursday, August 6, 2015
FYI: Resolution - Build / Compile Issues - The XML namespace for the conceptual model's Schema element is not supported on this project's target framework version
Apparently when compiled w/ a 2012 vs. a 2013 it IDE
(minimum known is “Premium”) it will no longer play nicely yielding many rabbit
holes and a rather generic error of “The XML namespace for the conceptual
model's Schema element is not supported on this project's target framework
version”.
Wednesday, August 5, 2015
Force Restart Over RDC
http://www.fixedbyvonnie.com/2013/11/shutdown-restart-windows-remote-desktop-connection/
How to shutdown or restart Windows over a Remote Desktop connection
I would love the world to think of me as an industrious, prolific IT director and blogger; however, I’ve finally avowed this one truth:
I’m lazy.
Yep. Why hide it. It’s true.
I like to take the easy way out, the path of least resistance, and I have a propensity to be dormant unless someone lights a flame under my butt.
My indolence is precisely what makes programs like Remote Desktop such a joy to use. Why should I get up and walk over to a server when I can remotely connect to it from the comfort of my desk? Remote Desktop lets me access almost any Windows computer on my network and gives me the feeling of sitting right there in front of the box.
A bunch of smart people at Microsoft concocted the Remote Desktop Protocol (RDP), and with the advent ofWindows XP, Microsoft began bundling the Remote Desktop Connection (RDC) with ever OS since.
The RDC client is great because it supports cool features like 24-bit color, audio, and file system redirection. So, for example, you can open a Youtube video on the remote computer and hear the audio on your local machine. Or you can access the files on your local machine from the remote machine. All this magic is made possible through the RDP and what Microsoft calls the Terminal Services client (mstsc.exe).
Whenever you connect to a Microsoft Server, the Shutdown and Restart buttons are visible in the little arrow immediately to the right of the lock icon.
But you might be wondering how to do the same thing when opening a remote connection on a Windows 7 box.
As you can see, there’s only a Lock and Disconnect option but no Shutdown or Restart function.
To restart the remote computer, click the Start button and enter this command in the search box:
shutdown /r /t 0
The /r switch tells the machine to reboot and the /t option sets the number of seconds to execute the command. Since we set /t to zero, the restart should initiate immediately.
To shutdown the machine just enter:
shutdown /s
The other option is to just to click the Desktop and press Alt + F4 to bring up the shutdown menu.
Finally, if you execute the shutdown command but then suddenly realize you made a mistake type:
shutdown /a
This will attempt to abort the shutdown sequence.
Tuesday, August 4, 2015
Find the BAK, MDF and LDF Files
-- Find the BAK files:
SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC
-- Find the MDF's and LDF's:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT * FROM sys.database_files'
EXEC sp_MSforeachdb @command
-- Or ... easier
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC
-- Find the MDF's and LDF's:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT * FROM sys.database_files'
EXEC sp_MSforeachdb @command
-- Or ... easier
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
-- Or ... even easier
SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'
FROM master.dbo.sysdatabases Thursday, July 30, 2015
Dropping Global Temp Tables
-- A better question is why are you using global temp tables in the first place???
IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt
IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt
Wednesday, July 29, 2015
TFS Hidden Workspace Solution
-
Q:
When creating a new build in Team
Foundation Server, I get the following error when attempting to run the new
build:
The path
C:\Build\ProductReleases\FullBuildv5.4.2x\Sources is already mapped to
workspace BuildServer_23.
I am unable to see a workspace by that
name in the workspaces dialog.
|
|
|
|
Subscribe to:
Posts (Atom)