Copyright © 2008-2019 Paula DiTallo

Tag Cloud

SQL Server: How can I tell which service account SQL Server is running under?

This query will provide the basics

SELECT  DSS.servicename,
FROM    sys.dm_server_services AS DSS;

How can I tell which node is the primary, or 'active' node in a SQL Server Cluster

Issue this command from the master db 

Select ServerProperty('ComputerNamePhysicalNetBIOS')

SQL Server error: "Failed to create AppDomain "master.sys[runtime].224". Exception has been thrown by the target of an invocation"

In a nutshell this is due to an incompatibility between .NET and Windows.  The most likely candidate for the cause would be an upgrade to either Windows or .NET.  Overall, check the Windows and SQL Server patch levels. The CLR/.NET that is affected is Microsoft.SqlServer.Types. 

To poke around to see what else might be at risk, issue these queries from master:

SELECT * FROM sys.dm_clr_appdomains;
SELECT * FROM sys.dm_clr_loaded_assemblies;

SELECT  * FROM sys.assemblies WHERE principal_id <> 4 AND is_user_defined = 1
SELECT * FROM sys.assemblies;

-- look at which .net assemblies are active on server.
select sum(pages_in_bytes) from sys.dm_os_memory_objects where type like '%CLR%'

-- Force type assembly errors to appear to ascertain nature of .net assembly issue.
DECLARE @Test [HierarchyID];
SET @Test = '/1/';

Once you've done the necessary upgrades to Windows and/or SQL Server, reboot the server.

SQL Server: How do I get the size of a database?

use [databaseName]

      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

SQL Server: Find existing temp tables on a server.

-- query the server to examine all the existing temp tables

select name, object_name(object_id) As ObjName,*
 from tempdb.sys.objects
 where name like '#%'

SQL Server: How do look at the dataypes/lengths of columns in a temp table?

To view that information, there are 2 primary ways:

select * 
where table_name like '#MyTempTable%'
select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytemptable');

SQL SERVER: How do I find all the tables that have had an update in a database?

This query will bring back the last user update to every table in the database you're connected to.

use [dbname]

SELECT last_user_update, 
FROM sys.dm_db_index_usage_stats us 
JOIN sys.tables t 
ON t.object_id = us.object_id 
WHERE database_id = db_id()
and cast(last_user_update as date) >= cast('2018-05-09' as date)
order by last_user_update desc

SQL Server: How do I pull the ASCII value for each character in a column name?

This is a handy script to cycle through every character in a column to determine what each ascii value is.  This is especially useful when a string match isn't matching.  Often times, there is a hidden space, etc.

 DECLARE @counter int = 1;
--DECLARE @asciiString varchar(10) = 'AA%#&    ';
 DECLARE @asciiString varchar(100) 
 SELECT @asciiString = [ColumnName]
  FROM schema.TableName
  where ColumnName like '%Something%'

WHILE @counter <= DATALENGTH(@asciiString)
   SELECT CHAR(ASCII(SUBSTRING(@asciiString, @counter, 1))) as [Character],
   ASCII(SUBSTRING(@asciiString, @counter, 1)) as [ASCIIValue]
   SET @counter = @counter + 1

In Visual Studio, how do I pass arguments in debug to a console program?

  1. Go to your project properties, either by right-clicking on the project and picking "Properties" or by picking Properties from the Project menu.

  2. Click on Debug, then enter your arguments into the "Script Arguments" field.

  3. Save.

Sql Server: How do I get the filegroup, data file name, size and path of a database?

-- start with this:
SELECT AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB, AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
sys.database_files AS dbfile
sys.filegroups AS sysFG
dbfile.data_space_id = sysFG.data_space_id

-- for a more general look by filegroup, try this:

with fileConfig as
(SELECT AS DatabaseFileName,
(dbfile.size/128)  AS FileSizeInMB, AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
sys.database_files AS dbfile
sys.filegroups AS sysFG
dbfile.data_space_id = sysFG.data_space_id
select FileGroupName,
       sum(FileSizeInMB) as TotalFilegroupInMB
 from fileConfig
  group by FileGroupName
  order by FileGroupName

SQL Server: Why is it taking so long to take a database offline?

There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. 

Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s).  

In the future, use this command:


To bring the db back online:


SQL Server: How can I get a distinct count(*) with multiple columns?

To get a count(*) of distinct column combinations, do the count(*) over the distinct select statement.


SELECT count(*)
              FROM YourTable 
             ) x

Can't Start NiFi from Ambari, How do I spin up NiFi service in Hortonworks sandbox?

Ambari will still show NiFi as down, however, you will be able to work with the canvas from the URL

How Do I Open a Port in Windows 10 (refused connection) ?

If you are seeing the "connection refused" message when attempting to set up a localhost access port, the chances are good that the port is blocked from allowing connections through Windows. To open the port, follow these instructions:

1. Navigate to Control Panel, System and Security and Windows Firewall.
2. Select Advanced settings and highlight Inbound Rules in the left pane.
3. Right click Inbound Rules and select New Rule.
4. Add the port you need to open and click Next.
5. Add the protocol (TCP or UDP) and the port number into the next window and click Next.
6. Select Allow the connection in the next window and hit Next.
7. Select the network type as you see fit and click Next.
8. Name the rule something meaningful and click Finish.

You can also use  netsh (example):
netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80

SQL Server: How do I split a comma delimited column into multiple columns or rows?

-- converting column with commas to multiple columns --

declare @col1 varchar(500)
set @col1 = 'I,Hate,Broccoli'

DECLARE @Tmp TABLE ( Id int, Element VARCHAR(20)) 
INSERT @Tmp SELECT 1,@col1
       PARSENAME(REPLACE(Element,',','.'),2) Name, 
       PARSENAME(REPLACE(Element,',','.'),1) Surname 

-- converting column with commas to multiple rows --

declare @col1 varchar(500)
set @col1 = 'I,am,really,a,smart person, one of the smartest'

DECLARE @Tmp TABLE ( Id int, Element VARCHAR(200)) 
INSERT @Tmp SELECT 1,@col1

SELECT A.[id],
    Split.a.value('.', 'VARCHAR(100)') AS String
  FROM (SELECT [id],
      CAST ('<M>' + REPLACE(Element, ',', '</M><M>') + '</M>' AS XML) AS String
    FROM @Tmp) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

SQL Server: How can I tell if a table is being used?

This statement will display the datetime stamp of the last user scan and the last user update. It will also include the # of user updates on that table.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'yourDatabaseName')

SQL Server: How do I test for the existence of a temp (#temp) table before dropping it?

This statement will work 

if object_id('tempdb..#mytempTbl') is not null
  drop table #mytempTbl

How can I get a list of what windows patches were installed?

For an effective, simple HTML formatted static list go into an MSDOS command window and type: wmic qfe list full /format:htable > c:\winpatches.htm

I can't remote into my Windows server. How do I do remote shutdown or remote restart?

Open up a windows command shell. Type in:
shutdown -r -t360 -m

If you are looking for more information about this, check out this link

GMAIL: How do I get rid of all the emails in a folder?

This took me longer than it should have to figure out. In just a few simple steps, all those emails can easily be dealt with in sets.

  1. Go to the folder you are interested in.
  2. click the top check box--or the "select all" .  You'll see a text line appearing just above the trash can icon that says something like "All 999 conversations on this page are selected." (see below).
  3. Click on the underlined text that says "Select all 999 conversations in YourFileFolderName". The text line will change to something like "All 999 conversations in 'YourFileFolderName' are selected."
  4. Click the Delete button.  It will pop up with a 'bulk action' message about the delete you're about to do. Agree to delete. 

SQL Server: Alternative to Count(*) for VLDB

If you have a very large data base (VLDB) a select count(*) can take a long time to resolve. Try this statement as an alternative:

USE YourDatabasename;
SELECT AS 'SchemaName'
       , AS 'TableName'
       ,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
       JOIN sys.objects o ON o.object_id = p.object_id
       JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id < 2 AND o.type = 'U'
       AND = 'Schema'
       AND = 'TableName'

SQL: Locate stored procs, views, etc. by text

This will locate specific text within an object on a given database. In this case, it will find all views, stored procs which have the text string "Department" somewhere in the body.

use [databaseInstance]

FROM sys.sql_modules m        
  INNER JOIN    sys.objects o 
     ON m.object_id = o.object_id 
WHERE m.definition Like '%Department%';

I get this message: profile name is not valid [SQLSTATE 42000] (Error 14607) using sp_send_dbmail. Why?

The most likely reason is that your profile has not been configured, or you are using an incorrect name under the @profile_name parameter.  To determine your profile settings, use this query:

SELECT [profile_id]
FROM [msdb].[dbo].[sysmail_profile] 

If after you have checked the results of this query and you are using the correct profile name, check how you are passing the parameter values.

Don't do this:
exec msdb.dbo.sp_send_dbmail @subject, @body, @profile_name, @recipients 

...  it will still fail because you need to explicitly define the parameter values ...

For example,
@subject = 'Some topic or another'

So you would either have to do this:

exec msdb.dbo.sp_send_dbmail @subject = 'Some topic or another', @body = 'read this!' ...etc.,


declare @subj varchar(100)
set @subj 'Some topic or another'

exec msdb.dbo.sp_send_dbmail @subject=@subj ... etc.,...

SQL Server: How do I start an agent job on a remote server?

This sql script will start an agent job on a remote server.  If you're running this as a step in another agent job, keep in mind that the job you are running it from will be determined to be successful, even if the remote job fails--as this is an asynchronous kick off only.

declare @returnCode int 
declare @JobName varchar(300) 
declare @ServerName varchar(200) 
declare @query varchar(8000) 
declare @cmd varchar(8000) 

set @JobName = 'TheJobNameYouWantToRun' 
set @ServerName = 'TheRemoteServerWhereTheJobIs' 

set @query = 'exec msdb.dbo.sp_start_job @JobName = ''' + @JobName + '''' 
set @cmd = 'osql -E -S ' + @ServerName + ' -Q "' + @query + '"' 

print ' @JobName = ' +isnull(@JobName,'NULL @JobName') 
print ' @ServerName = ' +isnull(@ServerName,'NULL @ServerName') 
print ' @query = ' +isnull(@query,'NULL @query') 
print ' @cmd = ' +isnull(@cmd,'NULL @cmd') 

exec @returnCode = master.dbo.xp_cmdshell @cmd 

if @returnCode <> 0 or @returnCode is null 
print 'xp_cmdshell @returnCode = '+isnull(convert(varchar(20),@returnCode),'NULL @returnCode') 

sql server: Why can't I use the xp_cmdshell in SSMS?

You will need to enable the feature first.

Follow these steps:

EXEC sp_configure 'show advanced options', 1
-- this updates whatever the currently configured value for advanced options
-- Now enable the command shell
EXEC sp_configure 'xp_cmdshell', 1
--update the currently configured value with xp_cmdshell setting update.