Copyright © 2008-2018 Paula DiTallo

Tag Cloud

Practical Answers

Answers to problems/questions across all topics.

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

use [databaseName]goSELECT 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 * from tempdb.INFORMATION_SCHEMA.C... where table_name like '#MyTempTable%'select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytempt... ......

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]goSELECT 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) BEGIN SELECT CHAR(ASCII(SUBSTRING(@ascii... ......

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

Go to your project properties, either by right-clicking on the project and picking "Properties" or by picking Properties from the Project menu.Click on Debug, then enter your arguments into the "Script Arguments" field.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 FROM sys.database_files AS dbfile INNER JOIN sys.filegroups AS sysFG ON 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 FROM sys.database_files ......

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:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

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:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

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.Example:SELECT count(*) FROM (SELECT DISTINCT ColumnA, ColumnB, ColumnC FROM YourTable ) x ......

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 SELECT Id, PARSENAME(REPLACE(Element,'... Name, PARSENAME(REPLACE(Element,'... Surname FROM @Tmp-- 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 ......

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.Go to the folder you are interested 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).Click on the underlined text that says "Select all 999 conversations in YourFileFolderName". The text line will change to something ......

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] ,[name] ,[description] ,[last_mod_datetime] ,[last_mod_user] 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, ......

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

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', 1GO-- this updates whatever the currently configured value for advanced optionsRECONFIGUREGO-- Now enable the command shellEXEC sp_configure 'xp_cmdshell', 1GO--update the currently configured value with xp_cmdshell setting update.RECONFIGUREGO ......

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 masterGOALTER DATABASE ......

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(SELECT sysdb.[name] as dbName, sysdb.[create_date], sysdb.[compatibility_level], sysdb.[collation_name], r.*, ROW_NUMBER() OVER (PARTITION BY sysDb.Name ORDER BY r.[restore_date] DESC) as RNFROM master.sys.databases sysdbLEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = sysDb.Name)SELECT *FROM [LastRestoresOnServer]WHERE RN = 1 ......

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: Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 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 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; ......

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

This pattern works well:USE [yourDatabase] GO IF OBJECT_ID('ThisSproc', 'P') IS NOT NULL DROP PROCEDURE ThisSproc; GO CREATE PROCEDURE ThisSproc @PersonId INT, @NewMoneyCollected MONEY = 0 AS BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE OrganizationTripFunding SET Balance = MoneyCollectedAllYear + @NewMoneyCollected WHERE PersonId = @PersonID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorLine INT = ERROR_LINE(); ......

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 drectory2. Create a new subdirectory (folder).3. Expand the zip file to the subdirectory folder. 4. Restart Eclipse5. Under the Windows\Open Perspective menu, look for your plugin ......

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

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

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: Right click on the database you're interested in and go to Tasks -> Generate ScriptsSelect the tables and/or any other objects you'd like in order to get them into the script.Navigate to Set scripting options. Click on Advanced.Under the General category, navigate to Type of data to scriptSelect the Schema and Data option to get the ......

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:

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 x.ProductType, count_big(*) as ProdCount, (count_big(*)*300) as TotalBytes, ((count_big(*)*300)/1073741... as Gigabytes from fdwintegration.etl.fuelpric... x inner join fdwintegration.etl.FuelProd... y on x.ProductIndicator ......

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.

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

Open up an MSDOS command window. Type :net fileThe 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_NAME2. Server Manager>Roles>File Services> Share and Storage Management (right click ......

Linux/Unix: How do I find any partial file name on any directory?

We've all been there-- like -- where's that offline htm file i bothered to save last week ... oh I think it was blue something... or was it something blue?

find ../ -name '*blue*.htm*'  -print

If you want to keep it in an output file to sort through later while you do something else:

find ../ -name '*blue*.htm*'  -print > $HOME/ foundBlue.txt


My old (generation 2) nano iPOD won't shut off.

Try holding down the Menu and Selector (push-button circle in the center) down at the same time for at least 2 seconds.

My iPOD keeps saying "Do Not Disconnect" while charging. I can't listen to anything.

The simplest answer is because the iPOD software detects that it is unsafe (read: data loss) for your iPOD, Computer or both if you were to disconnect the cable without ejecting the device first.Here's what to do:On the Mac-- Click on the Finder icon Pick the iPOD icon.Navigate to the File menu--select Eject iPOD On a Windows OpSys device-- On the Start menu, click Computer (e.g. Windows 7).Right-click on the drive/device letter or iPOD icon (read: the icon doesn't always appear)Select Eject.The ......

How do I save frequently used SQL Statements in SQL Server?

One convenient way is to make use of the template feature (aka: Template Explorere) in SSMS.

1. Navigate to the View menu in SSMS
2. Select Template Explorer->right click on SQL Server Tempaltes.
3. Click New and create folder; use to add/edit templates.

How do I switch montor postions? Switching cables didn't help.

For Windows 7:1. Right click any blank area on your desktop. 2. Left click Personalize->Display Settings. A window will open that shows a monitor. 3. Navigate to Change Display Settings. A new window will appear showing 2 monitors. 4. Click Identify Monitors. When the monitor you are on is the main monitor, you will see "This is currently your main display" under Multiple displays. To change this, click on the other monitor and check the box that says "Make this my main display". Click Apply. ......

Which DMVs are most helpful? Is there a list of them?

As with most questions asked about monitoring/assessing/admini... MS SQL Server installations, the answer is "it depends".There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (Dynamic Management Views) category, type this:-- This will show you the views and the functions of all dmv objectsSELECT name, type, type_descFROM sys.system_objects WHERE name LIKE 'dm_%'-- ......

How can I tell when a stored proc (or function) in sql server was last modified?

You're really looking of the modify_date in the sys.objects table where the type is either a stored proc or a function. This works on a database by database level -- to elevate this search to the server level, you'll have to use the loop sproc sp_msforeachdb SELECT name, create_date, modify_date FROM sys.objectsWHERE type = 'P' -- change 'P' to 'FN' if you're looking for a function and modify_date between cast('2013-09-10' as date) and cast('2013-09-20' as date) ......

How do I change the value of a column in a CASE statement and eliminate NULLs?

In t-sql/mssql, if you have a known NULL value you can issue a statement like:SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDateFROM DrviersThe ISNULL function is replacing any nulls found in the BirthDate column to a default value of 1/1/1970.In other situations, you may not have just NULL values, but other possibilities as well. SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate, CASE WHEN BusinessPhone IS NULL OR BusinessPhone = '' THEN 'Unknown' ELSE BusinessPhone END as BusinessPhoneFROM ......

I get the error "Can't connect to SSISServer" . How do I fix this?

If you're seeing this error, or a similar access denied error you'll need to have your windows account given DCOM permissions. DBAs generally have the ability to add you.If you're a DBA and want to add a developer to be able to connect to the SSIS services to deploy packages here's a quick punch list:Logon to the target SQL Server. Go to Start->Run type lusrmgr.mscIn the Local Users and Groups dialog box, click Groups->Ditributed COM usersOnce in the Distributed COM users properties dialog ......

SQL Server Management Studio: Why do I get "Saving changes is not permitted" ?

This happens when you're in the SSMS gui making changes to the table that either affect the behavior of an index, cause existing data to be truncated/nulled, etc. This can also happen you try to change the order of columns, or change datatypes. If your credentials allow it, you can get past this message in order to truly shoot yourself in the foot! :)Navigate to Tools --> OptionsExpand DesingersClick on Table and Database DesignersCheck the box Prevent Saving changes that require table to be re-created ......

Why can't I connect to SSIS through management studio?

The microsoft integration service is secure by default. The DBA has to grant access to the service in order for it to connect successfully through ssms. To grant access to the Integration Services service Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node. Right-click Microsoft SQL Server Integration Services 11.0, and then click ......

How do I Install IBM Data Studio 4.1 ?

The download for this workbench is large--somewhere around 1.2 gb. Finding the install exe for linux or windows isn't clear. Basically this download appears to be from a 2 disk distributable set of *.ISO files. Ignore all the distraction scripts in the core directory. Delve into \ibm_ds410_lin64 (I downloaded the 64 bit version) . Look for a directory called Disk1. There you will find the installer images for Windows and Linux.Here's a visual image of what the directory will look like ......

SSIS: My Toolbox is Missing!

I work with BizTalk (BTS) and SSIS frequently. In one of my environments, whenever I've programmed in BTS, then jump back to SSIS, the SSIS toolbox is missing in action. Here's what I do to solve the problem in that environment:Save the application and close Visual Studio.Go your AppData\Local\Microsoft\Vis... version]Rename the *.tdb files there to something else like *.tdb.xOpen Visual Studio (Note: You will see the progress status bar re-initializing all available toolboxes.)Open ......

How do I know what Version of BizTalk is on my server?

There are 2 ways to do this, the first is to query the BizTalkDBVersion table:use [BizTalkMgmtDb]goSELECT DatabaseMajor, DatabaseMinor,ProductBuildN... ProductRevision FROM dbo.BizTalkDBVersion; Here is a list of possible BizTalk versions (CUP = cumulative update package, SP = service pack) :BTS20043.0.4902.0BTS2004SP... 3.0.7405.0BTS2006 3.5.1602.0BTS2006R23.6.1404... 3.9.469.0BTS2010CUP13.9.522... ......

SQL Server: I need to get to a remote server and management studio is broken. How can I login without ssms?

Try using osql . It can be a life saver. Here's an example of the most common usage:osql -E -S myMSSQLServerName /* This uses windows authentication to connect to a server */osql -? /* lists all the switches available to use with osql */Once you see the SQL prompt (1> ) , you can interact with the server.1> USE myDatabaseName2> GO3> SELECT col1, col24> FROM myTable5> GO6> 7> EXIT Here's a link or two to get you started. ......

SQL Server: Is there a query I can use that generates similar output to Activity Monitor?

Yes! This query will return very similar information to what activity monitor returns--including the text of the query the process is running (when applicable). -- Query version of Activity Monitor SELECT SessionId = s.session_id, UserProcess = CONVERT(CHAR(1), s.is_user_process), LoginInfo = s.login_name, DbInstance = ISNULL(db_name(r.database_id), N''), TaskState = ISNULL(t.task_state, N''), Command = ISNULL(r.command, N''), App = ISNULL(s.program_name, N''), WaitTime_ms = ISNULL(w.wait_duration_ms, ......

SQL Server Agent:: I Can't Edit a Job Step because of the error "Creating an instance of the COM component with CLSID from the IClassFactory failed"

The immediate workaround to this problem is to open up a new SSMS session, then navigate to the SQL Agent Job you want to edit. At that point, you will be able to make the edit and save the changes.For more information on why this error occurs and the possible release(s) fixes, check out this url:http://connect.microsof... ......

SSIS: I get the error The column data for column "Column x" overflowed the disk I/O buffer

If you are reading a flat file, you are missing a delimiter somewhere and SSIS is trying to parse for the next value--so it thows an i/o buffer error. Look in the file for the missing delimiter. If the file is too big for that, try chopping up the file up into smaller versions until you find the line with the missing delimiter.

Eclipse Project:: How do I add an external *.jar file to my project?

1. Navigate to the project. Right-click, go to the Properties menu.
2. Go to the Java Build Path. Choose Libraries. Click the Add External JARs button.
3. Select the jar file/library to add.

NOTE: For projects you own, that you would like to add, use the Add JARs button instead.

Full Practical Answers Archive