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')

author: Paula DiTallo | Posted On Thursday, January 5, 2017 10:15 PM | Comments (0)

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

author: Paula DiTallo | Posted On Thursday, January 5, 2017 10:00 PM | Comments (0)

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

author: Paula DiTallo | Posted On Tuesday, March 1, 2016 5:08 PM | Comments (0)

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 servname.onyourdomain.com

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

author: Paula DiTallo | Posted On Thursday, January 7, 2016 2:33 PM | Comments (0)

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. 

author: Paula DiTallo | Posted On Wednesday, December 30, 2015 5:12 PM | Comments (0)

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;
        s.name AS 'SchemaName'
       ,o.name 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 s.name = 'Schema'
       AND o.name = 'TableName'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;

author: Paula DiTallo | Posted On Friday, December 4, 2015 8:26 AM | Comments (0)

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]

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

author: Paula DiTallo | Posted On Tuesday, December 1, 2015 12:24 PM | Comments (0)

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.,...

author: Paula DiTallo | Posted On Thursday, September 24, 2015 6:56 PM | Comments (0)

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') 

author: Paula DiTallo | Posted On Monday, September 14, 2015 7:53 AM | Comments (0)

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.

author: Paula DiTallo | Posted On Monday, September 14, 2015 7:39 AM | Comments (0)

SQL Server: Why is it taking so long for SQL Server to take my database OFFLINE?

If you are using the SSMS GUI, you may not be aware that behind the scenes, you are really issuing an ALTER DATABSE command.  It is likely that another process (or processes) were accessing the database you want to take offline.

If you're a DBA, or have sysadmin privileges, issue an sp_who2 command -- looking for the ALTER DATABSE process logged to you.  Kill the process. 

Once the process has been killed off, issue the ALTER DATABSE command yourself.

To take the database offline:

USE master


To get the database back online:

USE master


For added protection of data you do not wish to be online:


author: Paula DiTallo | Posted On Monday, September 14, 2015 7:35 AM | Comments (0)

How do I set a trace in SQL Server?

Think of this as a lightweight alternative to SQL profiler. Under the hood of SQL profiler, there exists SQL Trace  -- which provides a collection of stored procedures to generate trace info. Cut and paste the t-script below to see how it works.

-- Pay attention to what the server settings for traces look like first:

select * from sys.traces


-- you are looking to make sure there isn't already a trace file set somewhere
-- In any case you will need to create a new trace, make sure the @tracefile doesn't exist on the disk yet

declare @myTracefile nvarchar(500) set @tracefile=N'c:\temp\myTraceFile.trc'

declare @myTrace_id int

declare @maxsize bigint

set @maxsize =1

exec sp_trace_create @myTrace_id output,2,@MyTracefile ,@maxsize



---  add the result columns you care about

--  if you don't have any other traces set, you'll be set to 1, if not, run the select * sys.traces again to see
-- what got assigned. look up in sys.traces to find the @mytrace_id,
--  in this example, I will assume its 1 for now so that is why you see: @mytrace_id=1

declare @myTrace_id int

set @myTrace_id=1

declare @xon bit

set @xon=1

declare @current int

set @current =1

while(@current  <10)   -- 10 times is just an arbitrary number of times i might log something
                                               -- pick whatever you want

      -- here is where you figure out what events you want to log to the file.  
      -- Go here to decide:  https://msdn.microsoft.com/en-us/library/ms186265.aspx

      -- Try it out for now, using #14 -- that's probably not what you want, but get it to work first.

      exec sp_trace_setevent @myTrace_id,14, @current,@xon

      set @current=@current+1

-- later you'll want to look at it

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus  @myTrace_id,1

-- see the traced event

select yourlogin, dbinstancename,* from ::fn_trace_gettable(N'myTraceFile.trc',default)


-- Once you're done, you'll need to cleanup
-- stop the trace and delete the file

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus @myTrace_id,0

exec sp_trace_setstatus @myTrace_id,2  -- delete def from server


author: Paula DiTallo | Posted On Friday, May 15, 2015 7:31 AM | Comments (0)

DB2: How do I get the Column Definitions, Column Descriptions?

These general statements will get you the information that you're looking for:


 AND TABLE_NAME = 'YourTableName'

This will get you the definitions of the columns:

t.table_schema as Library
,c.character_maximum_length as Length
,c.numeric_precision as Precision
,c.numeric_scale as Scale
FROM sysibm.tables t
JOIN sysibm.columns c
   on t.table_schema = c.table_schema
  and t.table_name = c.table_name
WHERE t.table_schema = 'YourSchema'
and t.table_name = 'YourTableName'
order by t.table_name, c.ordinal_position

author: Paula DiTallo | Posted On Wednesday, April 22, 2015 4:15 PM | Comments (0)

sql server: How can I list or review the last restores for all the databases on a server?

Go to the server your are interested in gathering the information from, then issue this statement:

WITH LastRestoresOnServer AS
    sysdb.[name] as dbName,
   ROW_NUMBER() OVER (PARTITION BY sysDb.Name ORDER BY r.[restore_date] DESC) as RN
FROM master.sys.databases sysdb
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = sysDb.Name
FROM [LastRestoresOnServer]

author: Paula DiTallo | Posted On Monday, April 13, 2015 4:58 PM | Comments (0)

How do I Fix "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS"

You were probably trying to do a comparison between 2 databases with different collation settings on a WHERE clause or on a  join. Here's how you would do the comparison with a JOIN:

Select * 
from source db1.schema.table1 as t1
join source db2.schema.table2 as t2
on t1.col1
collate SQL_Latin1_General_CP1_CS_AS = t2.col1 COLLATE SQL_Latin1_General_CP1_CS_AS

author: Paula DiTallo | Posted On Thursday, April 9, 2015 7:40 PM | Comments (0)

mssql2008+ : How do I manage transactions in stored procedures?

This pattern works well:

USE [yourDatabase]


  @PersonId INT,
  @NewMoneyCollected MONEY = 0
      UPDATE OrganizationTripFunding
      SET Balance = MoneyCollectedAllYear + @NewMoneyCollected
      WHERE PersonId = @PersonID;
    IF @@TRANCOUNT > 0

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
-- use as debugging tool -- PRINT 'error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); -- PRINT 'line number: ' + CAST(@ErrorLine AS VARCHAR(10)); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; GO

author: Paula DiTallo | Posted On Monday, March 16, 2015 8:39 PM | Comments (0)

SSIS 2012: My Deployment fails with the message "query the operation_messages view"

The full error message reads as "Failed to deploy project. For more information, query the operation_messages view for the operation identifier '39212'. (Microsoft SQL Server, Error: 27203)

In a nutshell, the most common reason for this is the latency between SQL Server logging the deployment operation within the SSIS catalog database and the stored procs that actually commit the deployment.

Try the deployment again.

author: Paula DiTallo | Posted On Friday, January 23, 2015 5:49 PM | Comments (0)

How Do I Install an Eclipse Plugin from a *.zip File?

If you aren't able to install from the Help\Install New Software menu, it is probably because the zip file doesn't have the content.jar/artifacts.jar needed to do so. You will need to navigate to where you've installed Eclipse, then follow these steps:
1.  Navigate to the Dropin drectory
2. Create a new subdirectory (folder).
3. Expand the zip file to the subdirectory folder.
4. Restart Eclipse
5.  Under the Windows\Open Perspective menu, look for your plugin.

author: Paula DiTallo | Posted On Tuesday, July 15, 2014 11:29 AM | Comments (1)

Sql Server: Orphaned user name; Can't login, User name disappeared from my database

This usually happens when you restore a database. For example, you restore a copy of production database X to your QA server. In essence, you have overwritten the user info of that specific database with what exists in production. This creates an orphan user -- where you have no login associated with a user in that database on a server that once associated that user with the old copy of the database.

Here is what to do fix the issue:

1.  Validate that what you think is the problem is the  problem. Do this by listing the known orphans. You may have others show up--but be sure the id you are interested in is on the list:

use [your database instance]


EXEC sp_change_users_login 'Report'

2. Fix the broken login:

EXEC sp_change_users_login 'Auto_Fix', 'orphan username'

author: Paula DiTallo | Posted On Monday, June 30, 2014 3:32 PM | Comments (0)

SQL Server: Restoring a backup generates this error: Operating system error 5(Access is denied.).

There are several reasons for this. The most common reason is that the account you are using while logged into mssql through ssms does not have access to the drive definition you are using as your source. Network drives are a good example of an access conflict.

Assuming you have access to the drive, try redirecting the network drive to a local drive letter via xp_cmdshell  (Note: be sure use of xp_cmdshell is enabled) .

exec master..xp_cmdshell 'net use Z: "\\BackupServerXX\<share>\PathWithoutTrailingSlash" YourADpasswordHere /user:domain\your.Username'
Declare @restorefile varchar(1024) = 'Z:\thebackupfile.bak'
Restore database x
From disk = @restoreFile

When you are done, or if you've made a mistake and need to reassign the drive, delete the reference.

exec master..xp_cmdshell 'net use Z: /delete'

author: Paula DiTallo | Posted On Thursday, June 26, 2014 3:32 PM | Comments (0)

SQL Server: How do I generate the table schema and populate it with inserts in a script?

In SSMS, there's a Generate Script utility (read:  only available under version 2008 and up) .

Here are the steps you would need to take to make use of the utility:

  1. Right click on the database you're interested in and go to Tasks -> Generate Scripts
  2. Select the tables and/or any other objects you'd like in order to get them into the script.
  3. Navigate to Set scripting options. Click on Advanced.
  4. Under the General category, navigate to Type of data to script
  5. Select the Schema and Data option to get the insert statements generated. Click OK.

author: Paula DiTallo | Posted On Tuesday, May 20, 2014 5:46 PM | Comments (0)

How do I find the largest files on my disk (storage)?

Here's an example using forfiles to scan your storage. This command line will look for files over 200mb on the C:\ drive after 1/1/2014.

forfiles /P C:\ /M *.* /S /D +"01/01/2014"  /C "cmd /c if @fsize gtr 209715200 echo @path @fsize @fdate @ftime"

For more information, check out Microsoft's examples and syntax page:


author: Paula DiTallo | Posted On Tuesday, May 6, 2014 10:06 AM | Comments (0)

Sql Server: Select Count(*) throws the error: Arithmetic overflow error converting expression to data type int

Try using count_big(*) instead-- this is defined as bigint instead of int.

In this example, I am looking for how many 100s of millions of rows exist by product type where each row is roughly 300 bytes wide to ultimately obtain how many gigabytes of data I need to provision.

select x.ProductType,
       count_big(*) as ProdCount,
      (count_big(*)*300) as TotalBytes,
      ((count_big(*)*300)/1073741824) as Gigabytes
 from fdwintegration.etl.fuelpriceindex x
  inner join fdwintegration.etl.FuelProducts y
   on x.ProductIndicator = y.ProductIndicator
    and x.ProductType = y.ProductType
   where x.ProductIndicator = 'D'
   group by x.ProductType
order by x.ProductType

author: Paula DiTallo | Posted On Monday, April 21, 2014 5:01 PM | Comments (1)

How Do I Alphabetize the Application Icons on my Droid?

Click on the App Button (aka: "Drawer" ) -- That's the key with the multiple squares, click on menu , select view type, select alphabetical grid.

author: Paula DiTallo | Posted On Tuesday, April 8, 2014 12:48 AM | Comments (0)

Windows: How do I find what process is locking a file?

Open up an MSDOS command window. Type :

net file

The id, path, user name, loc# for the file will show up in a text list. Once you find the file you want unlock, you'll need to remember the id. To unlock the file, go back to the MSDOS command window. Type:

net file 99/close    (where 99 = file id)

On a windows server there are 2 other ways:
1. From an MSDOS command window type: Openfiles.exe /query /s YOUR_SERVER_NAME
2. Server Manager>Roles>File Services> Share and Storage Management (right click on SaSM) >Manage Open File

author: Paula DiTallo | Posted On Friday, December 13, 2013 5:11 PM | Comments (0)