Check the User Who Started a Long Running Job Query

  

SELECT top 1  msdb.dbo.agent_datetime(run_date, run_time) as 'Job_RunDateTime',
	  sj.name
	, sh.run_date, sh.run_duration
	, sh.step_name
	,Substring(sh.message,charindex('The Job was invoked by User',sh.message)
	,charindex('The last step to run',sh.message)-charindex('The Job was invoked by User',sh.message))
	[The Job was invoked by User]
	 , sh.message 
	
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
join msdb.dbo.sysjobs_view job on job.job_id=sj.job_id
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where activity.run_Requested_date is not null and activity.stop_execution_date is null  
and  sj.name = N'my agent job name'	AND step_id = 0 
Order by   msdb.dbo.agent_datetime(sh.run_date, sh.run_time) desc ;
 

 
Advertisements

Import Excel File from SSMS with Missing Provider Error

When you try to import Excel file into your database from SSMS, you may run into one of the following errors:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.14.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.15.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered

In order to quickly get the excel data into database instead of trying to jumping around to fix the issue, we can use the 64bit wizard already installed on our machine.

You can use the wizard from any of the following:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\140\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe

You can create a shortcut on your desktop to use it next time.
The list below if for reference to the version number corresponding to SQL Server version:

110 SQL Server 2012
120 SQL Server 2014
130 SQL Server 2016
140 SQL Server 2017
150 SQL Server 2019

https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine


Scrap files from a website with powershell

  
  

$url = "https://www.nass.usda.gov/Publications/AgCensus/2017/Online_Resources/County_Profiles/Iowa/"
  
$targetDir  ="c:\temp\mypdf\"


$WebResponse = Invoke-WebRequest $url
$mySrcs=$WebResponse.AllElements | Where { ($_.href -like 'cp*.pdf*' -and $_.Tagname -eq 'a')}  


 
function DownloadFile([Object[]] $sourceFiles,[string]$targetDirectory) {            
 $wc = New-Object System.Net.WebClient            
             
 foreach ($sourceFile in $mySrcs){            
  $sourceFileName = $sourceFile.href            
  $targetFileName = $targetDirectory + $sourceFileName            
  $wc.DownloadFile($url+ $sourceFileName, $targetFileName) 
  
  Write-Host "Downloaded $sourceFile to file location $targetFileName"             
 }            
            
}            
            
DownloadFile $mySrcs $targetDir  

 

http://toreaurstad.blogspot.com/2013/03/using-powershell-to-download-multiple.html


Import Tab Delimited Text File with bcp

  
 USE [myDB]
GO
 
CREATE TABLE [dbo].[mySourceTable](
	[col1] [nvarchar](max) NULL,
	[Zip] [nvarchar](max) NULL,
	[col3] [nvarchar](max) NULL,
	[col4] [nvarchar](max) NULL,
	[col5] [nvarchar](max) NULL,
	[col6] [nvarchar](max) NULL,
	[col7] [nvarchar](max) NULL,
	[col8] [nvarchar](max) NULL,
	[col9] [nvarchar](max) NULL,
	[Latitude] [nvarchar](max) NULL,
	[Logitude] [nvarchar](max) NULL,
	[col12] [nvarchar](max) NULL
)  
GO

 
  

 
 --1.Generate XML format file 
 exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] format nul -c -x -f  C:\temp\myFormatFileZip.xml -T  -t\t -r0x0A -S MC0555\SQL2014DEV';
 --Tab delimited and Row terminator -r0x0A

---2. bcp in with all data 
exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] IN "C:\temp\US.txt" -f "C:\temp\myFormatFileZip.xml" -T -S MC0555\SQL2014DEV';

go


  

Import Tab Delimited Text File with dbaTool

  
  
  
$DataTable = Import-Csv C:\temp\mysourceZip.txt -Header "col1","Zip","col3","col4","col5","col6","col7","col8","col9","Latitude","Logitude","col12" -Delimiter  "`t"
Write-DbaDbTableData -SqlInstance MC04\myDEVserver -InputObject $DataTable -Table mydb.dbo.myStagingTable -AutoCreateTable 

  

 

https://docs.dbatools.io/#Write-DbaDbTableData

https://stackoverflow.com/questions/24751377/how-can-i-import-a-tab-delimited-text-file-with-import-csv-when-the-file-has-no


How to Change Primary Key Column Name in a Table with Spatial Index

Here is a list of steps for how modify a column (primary key) name with spatial index for geography data type.

  
  
--step 0  Optional
--drop  foreign keys if pointing to the primary key column in question

---Step 1 drop spatial index
 --Step 2 drop primary key constraint


 --Step 4:  add primary key constraint
  
--Step 3 rename column name
 sp_rename @objname = N'[dbo].[myTablewithSpatialIndexForDatatype_geography_Column].[myOldID]', @newname = N'myNewId'  
 Go

  ---Step 5: add back SPATIAL INDEX

  ---Step 6:  add foreign key back to point to the primary key after name change 
 

 

dbaTools sample Inventory Code

Andreas Schubert posted a blog about how to use dbaTools to get SQL Server information inventory in your organization.
Here is the link to the post:

building an inventory view of SQL Servers with dbatools

And the code download:
https://github.com/AndreasESchubert/Automation

You just need to create a name list for your servers.
The scan is pretty quick and generate an HTML view.