use master go declare @table table(logdate datetime,processinfo varchar(max),text varchar(max)) insert into @table exec xp_readerrorlog select * from @table where processinfo like ‘spid%’ order by 1
Category: SQL Server
Getting SQL Server table size with Powershell
$conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = “Server=********\NCRWO;database=NCRWO_TransactionLog;Integrated Security=true;MultipleActiveResultSets=true” $conn.Open() $cmd = New-Object System.Data.SqlClient.SqlCommand $cmdInner = New-Object System.Data.SqlClient.SqlCommand $cmd.Connection = $conn $cmdInner.Connection = $conn $cmd.CommandText = “SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE'” $swReader = $cmd.ExecuteReader() while ($swReader.Read()) { $cmdInner.CommandText =…
C# – Write file on client and read same file from inside the database
We had a job that an enterprise software vendor was running that failed on permissions denied. They asked us to open the file share to anyone without a password. Our awesome SQL Server DBA pushed back, thankfully. We came up…
Powershell – Getting tables from a SQL Server database
c:\Users\adm-showard>powershell Windows PowerShell Copyright (C) 2009 Microsoft Corporation. All rights reserved. PS C:\Users\adm-showard> $sqlConn = New-Object System.Data.SqlClient.SqlConnection PS C:\Users\adm-showard> $sqlConn.ConnectionString = “Server=CMHWPSWSQL1201;Integrated Security=true;Initial Catalog=SWNetPerfMon” PS C:\Users\adm-showard> $sqlConn.Open() PS C:\Users\adm-showard> $sqlcmd = $sqlConn.CreateCommand() PS C:\Users\adm-showard> $sqlcmd = New-Object System.Data.SqlClient.SqlCommand PS C:\Users\adm-showard>…
Query SQL Server XML column for value
Just an example of how to pull this type of value from an embedded XML document in a SQL Server database table column… select top 100 * from Transactions with (nolock) where trn_xml.value(‘(/Transaction/RetailStoreId)[1]’, ‘int’) = 2401
SQL Server – querying active users by database
use master go declare @table table( spid int, status varchar(max), login varchar(max), hostname varchar(max), blkby varchar(max), dbname varchar(max), command varchar(max), cputime int, diskio int, lastbatch varchar(max), programname varchar(max), …
Deleting in a loop in SQL Server
While discussing a way to delete many rows, but without deleting all of them at the same time (too much transaction logging), we came up with the following… This works pretty well. It’s faster to do it all at once,…
Crawling all SQL Server tables and columns for a specific value
We had a quick need to search all columns in every table during a data discovery exercise. This is by no means the most efficient, but it does work. It pulls 10,000 rows at a time from each table, and…
AWR like jobs in SQL Server
This morning when I arrived at the office, there were several emails related to a high CPU utilization issue on a SQL Server that occurred around 4:45AM this morning. Since SQL Server 2005 does not have the anticipated performance repository…
SQL Server example of a C# stored procedure
Below is a simple example of using CLR. Source code for procedure… using System.IO; using Microsoft.SqlServer.Server; public partial class spaceProc { [Microsoft.SqlServer.Server.SqlProcedure] public static void freeSpace() { foreach (DriveInfo drive in DriveInfo.GetDrives()) { if (drive.IsReady) { SqlPipe p = SqlContext.Pipe;…