sqlserver

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

Posted On Thursday, May 17, 2018 1:45 PM | Comments (0)

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

Posted On Wednesday, May 9, 2018 11:27 AM | Comments (0)

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

Posted On Tuesday, February 27, 2018 5:22 PM | Comments (1)

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

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

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

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

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

Posted On Wednesday, August 9, 2017 12:45 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] ,[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, ......

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

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

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

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

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