sql server - datetime to number

needed to transform minutes and seconds into a decimal number... it was not that easy as I thought originally... here the snippets:

I needed to find out the current hours, seconds and minutes

DATEPART(SECOND, CURRENT_TIMESTAMP)
DATEPART(MINUTE, CURRENT_TIMESTAMP)

DATEPART(HOUR,   CURRENT_TIMESTAMP)

(works for every part of the current timestamp...)

afterwards I needed to convert it to decimal and assemble the parts into one number using convert.

something like:
select convert(double(6, 2), @hour * 100 + @min) + convert(double(6, 2), @sec);

Now to cut off seconds we just need to convert the number to int again (as in the good old days of programming).

set @hourAndMin = convert(int, @hourAndMinAndSeconds)

SQL Server - Checks

Hi,

try to start a list of most important things (not in order) to check as a DBA in operation:

• Services Running
  (see: sys.dm_server_services)
  
• FileSystem Space
  (see: sp_spaceused and sp_MSforeachtable / sys.master_files + sys.databases / msdb.sys.dm_io_virtual_file_stats)
  
• Error Log (Agent and DB)
  (see: xp_readerrorlog)
  
• Locks and Waits
  (see: sys.dm_exec_requests (wait_type), sys.dm_exec_connections, sys.dm_exec_sql_text, trace-flags 1204, 1222)
  
• Backups
  (see: msdb.dbo.backupmediafamily, msdb..backupset)
  
• expensive queries
  (see: dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan)
  
• index fragmentation
  (see: sys.dm_db_index_physical_stats)
  
• statistics
  (see: dbcc show_statistics)
  
• consistency
  (see: dbcc checkdb / progress: SYS.DM_EXEC_REQUESTS, SYS.DM_EXEC_SQL_TEXT)
  
• overview of sessions (users)
  (see: sys.dm_exec_sessions, kill)
  
• Shrink
  (see: dbcc shrinkfile / don't do that if not edge case)
  
• User Handling
  (see: create user/login, sp_change_users_login)
  
• Update Process
  
• Logging Data-Changes
  (see: Change Data Capture (msdn), Trigger)
  
• Firewall Security
  
• Automation
  (see: SQL Server Agent, Powershell, SSIS, ETL)

Supporting Tools: Management Studio, Profiler, SSDT / BIDS, Configuration Manager, PowerShell, Nagios, IIS Crypto, sqlcmd, bcp, tsqlt 

kr,

Daniel

monitor sql server table to track changes in .net c# projects

Today I found a class in the .net framework which makes it possible to get events about data changes of a sql server table (idea of a trigger, but outside the db in a higher layer) what is perfect for caches: SqlDependency. Internally it uses the service broker of the sql server what sounds quite reliable in comparison to watch dogs. (About 2 years ago I made such a watch dog framework,... my experience: don't do that...)

kr,
Daniel

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

http://stackoverflow.com/questions/5288434/how-to-monitor-sql-server-table-changes-by-using-c

https://github.com/dyatchenko/ServiceBrokerListener

sqlcmd nowadays

Hi,

it is quite uncommon for MS-SQL Server-DBAs to work in command line (or much more uncommon in comparison to oracle or pg-sql DBAs). Nevertheless there is a tool from the stone-age called sqlcmd which has very nice features and which is actively developed by Microsoft up to now. See here: http://www.sqlserverspecialists.com/2012/10/sqlcmd-commands-sql-server-tool.html and https://msdn.microsoft.com/en-us/library/ms162773.aspx .

Main thing is:
- connect to different db
- call dos commands (mixing up OS and DB)
- set and use variables
- load sql files for execution
- redirect output to filestreams (file, stdout, stderr)
- change output format to XML

... even if this sounds quite limited it is still helpful for many purposes like e.g.: scheduled tasks. Especially the OS / DB combination is often very useful if you need to wait for the execution of A to start db-job B.

In SQL Server Management Studio the so called sqlcmd-mode to be used inside the query window is implemented which allows a limited feature set in comparison to the command line features seen above. See: https://msdn.microsoft.com/en-us/library/ms174187.aspx

Using SQL Server Agent you need to use an Operating-System Job-Step to work with sqlcmd (like with any other OS application). See an article about differences between TSQL-JobSteps and CmdExec using sqlcmd here: http://www.travisgan.com/2014/04/sql-server-agent-job-and-sqlcmd.html

So finally: with sqlcmd scripting can be improved by using variables, call different scripts and call the OS. In the original command line version it has some possibilities (like opening a dedicated admin connection) which are nice, but another very helpful thing is that it is kept small and performant.

Kr,
Daniel

SQL Server - Providing Demo Data (temporary)

Today I found a new solution to provide temporary data! Additionally to creating a table (not really temporary), creating a temp table (create table #xy) and declare a table variable (declare @xy table(...)) there is also the possibility to put values directly into a select statement:

select * from

(

  values 

    (1,2,3),

    (2,3,4),

    (3,4,5)

) as myValues (nr1, nr2, nr3);

this looks quite easy and can be very useful in a with block, because you can use this adhoc-values like a table and if it is necessary you can move them very easily into a table.

with config as (

  select * from ( values ('dataFolder', '/home/...') as config (key, val)

)

select 

  dataFolder.Value + data.FileName, data.Content

from 

  data, config dataFolder

where 

   dataFolder.Key = 'dataFolder'

... but this is probably not the best show case... I think demonstration / training about SQL would be a better use-case (e.g.: showing how join statements work or stuff like this... would be the first training explaining "with" before "join" :-) ... nevertheless I was fascinated and love this feature...).

kr,

Daniel

SQL Server: GO with count | delete table in chunks

Hi,

I was asked to delete parts of a table. Unfortunately the amount of rows was to big to delete them all in a single shot (TLog would exceed the file limit). I tried a different approach deleting the table in chunks using a loop, but this still executes as a single transaction.

Then I thought I could solve the problem by using a top statement and using go... afterwards I would copy these lines some thousand times and go for a coffee... but than the msdn article about go ( https://msdn.microsoft.com/en-us/library/ms188037.aspx ) opened up a very nice alternative.

Go can have an argument "count" with the following description:

count
Is a positive integer. The batch preceding GO will execute the specified number of times.

... nice, but how will it be executed? Statement-Block COUNT times and then one go (same problem like in the loop scenario) or is it a COUNT times series of statement, go, statement, go,...

I was not able to find the answer so I checked it myself with the following code:

waitfor delay '00:01:00'
go 3

the session-details of the activity monitor showed the single waitfor - statement. Proof enough that go with count creates a statement-go-statement-go series which perfectly solves my problem of the first section (see https://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx ).

delete top (5000) from table where x = 123;
go 10000

kind regards,
Daniel

comma separated values in a single cell (T-SQL)

Hi,

I reviewed my solution back from 2015-05-27 ( http://itados.blogspot.co.at/2015/05/sql-recursive-functions-linked-lists-in.html ). I thought it would be easy to explain a colleague how to put multiple values into a cell based on this article, but it took me over 5 minutes of staring at the code to remember how this all works...

Here an easier example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

declare @x as table (id int, val int) insert into @x values (1,1) insert into @x values (1,2) insert into @x values (1,3) insert into @x values (2,1) insert into @x values (2,2) insert into @x values (2,4) select o.id, stuff( ( select ', ' + cast(i.val as nvarchar) from @x i where o.id = i.id for xml path('') ), 1, 2, '' ) from @x o group by id

kind regards,
Daniel

TableVariables vs. TempTables

Hi,

in short:
- TableVariables don't change the schema, but are also executed in tempdb
- TempTables have statistics in comparison to TableVariables (query fast, costs after change)

I personally use TableVariables more often than TempTables, because I can't forget to drop them :-)


long version:

https://www.youtube.com/watch?v=iYEeLBC_cwA

kind regards,
Daniel

get a random and unique id in a table

Today I created a random ID. The random ID must still be primary key what means that it must be unique. This is a code walk-through to solve this problem:

First of all I created a play-ground to make tests (with 4 test-entries).

declare @tbl TABLE(ID int)
insert into @tbl values (3), (7), (9), (10) 

Then I introduced a parameter to define the scope. Here we see 0 as an error case (no more IDs available) and start with ID 1 to @maxValue.

declare @maxValue int = 10

 Then we create a loop to test the development for example 20 times:

declare @i int = 0
while (@i < 20) begin

The idea now is to create a random value (=myRequestID) and to check whether the ID currently in use (if so, it will be copied to targetID).

    declare @myRequestID int = (cast(   (RAND()*(@maxValue-1))    as int) + 1)
    declare @myTargetID int = 0
    if not exists(select * from @tbl where ID = @myRequestID) begin
            set @myTargetID = @myRequestID
            insert into @tbl values (@myTargetID)

if we have already used this value (it exists in @tbl) then we need to search for an ID after the random value. Therefore we can choose all requested entries after the random value which have no next sibling and return the smallest id + 1

    end else begin      
        select @myTargetID = isnull(MIN(id) + 1, 0) from @tbl t
        where id between @myRequestID and (@maxValue - 1)
        and not exists(select * from @tbl innerT where innerT.ID = t.ID + 1)

if the targetID has a value we found a valid entry, else if the value is zero, we have not found an entry after the random value, so we jump over the maxValue border and restart at the beginning. Here we need to consider that the value 1 is a special case which needs to be handled extraordinary. 

        if @myTargetID <> 0 begin
            insert into @tbl values (@myTargetID)
        end else if not exists(select * from @tbl where ID = 1) begin
            set @myTargetID = 1;
            insert into @tbl values (@myTargetID)

Finally if 1 is already in use we can search for an ID hole from the start to the random value

        end else begin
            select @myTargetID = isnull(MIN(id) + 1, 0) from @tbl t
            where id < @myRequestID
            and not exists(select * from @tbl innerT where innerT.ID = t.ID + 1)
            if @myTargetID <> 0 begin
                insert into @tbl values (@myTargetID)
            end
        end

If no value was found in the end there is probably no hole available and we have no value to return.

        if @myTargetID = 0 begin
            print 'no value'
        end
    end

(end of the loop)

    set @i = @i + 1;
end

kr, Daniel

sql recursive functions (linked lists in sql)

In modern programming languages it makes sense to work with lists, stacks, dictionaries and other collections. This can be crucial when you want to persist the data into the database, because some considerations have to be made about sorting and stuff like that.

Now implementing a linked list in a table is easy:

table: mylist

• ID: int (not null)
• data: nvarchar(50)
• nextID: int (null)

... more tricky is to walk through the list and find e.g.: the leaf of a corresponding root (seeing a list as a special sort of tree). 

the solution is described at: 

http://stackoverflow.com/questions/16749095/sql-recursive-function-that-gets-all-ancestors-of-an-item

...but the other way round using parent_id instead of a next_id

data_table:

ID       parent_id   name

---------------------

1        2            first 

2        4            second

3        3            third

4        5            fourth

5        -            fifth

the solution looks as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

with parents as ( select ID, parent_ID from data_table t where parent_ID is not null union all select p.ID, t.parent_ID from parents p inner join data_table t on p.parent_ID = t.ID and t.parent_ID is not null and t.ID <> t.parent_ID ) select * , parents = '(' + stuff ( ( select ', ' + cast(p.parent_ID as varchar(100)) from parents p where t.ID = p.ID for xml path('') ), 1, 2, '' ) + ')' from data_table t order by ID

• line 1 gives the inner statement a name to call it e.g.: more often like in
  with y as (select * from x where id > 12) select * from y, y
  ... here you have a smaller set of data to cross join
• line 3 to 5 makes a select as a starting point for the recursion (the original records)
• union all in line 6 adds to the starting point a data set defined below
• line 7 to 11 was at first sight simply magical to me
• line 7 selects the columns corresponding to line 2 (required by the union and makes sense with the recursive algorithm) ... the own id and the parent of parent
• line 8 requests data from the data table itself (the recursion using the name), but
• line 9 joins parent data to the original data
• line 13 to 24 uses the "expanded" data evaluated and shows it nicely in an own column using "for xml path".

the expanded result is:

| ID | parent_ID |
|----|-----------|
|  1 |         2 |
|  2 |         4 |
|  3 |         3 |
|  4 |         5 |
|  2 |         5 |
|  1 |         4 |
|  1 |         5 |

this will be transformed to

| ID | parent_id |   name |   parents |
|----|-----------|--------|-----------|
|  1 |         2 |  first | (2, 4, 5) |
|  2 |         4 | second |    (4, 5) |
|  3 |         3 |  third |       (3) |
|  4 |         5 | fourth |       (5) |
|  5 |    (null) |  fifth |    (null) |

...

There is an addition for select statements to overwrite the max recursion depth (in sql server it is default set to 100) to work with "deep" lists or trees.

    OPTION (MAXRECURSION 0)

kind regards,

Daniel

DB diff tools

made some research on database tools for comparing databases about changes and differences...

free

http://opendbiff.codeplex.com/

https://www.simego.com/Products/SQL-Admin-Studio

http://dbcomparer.com/

non-free

http://www.innovartis.co.uk

http://www.red-gate.com/products/sql-development/sql-data-compare/

http://www.sqldelta.com

http://www.devart.com/de/dbforge/sql/

The solution of simego seems to be one of these hidden heroes in the universe nobody knows but saves life all day... I really really like this product... 

it supports the following features in the current version/release 3.4.000 (copy from the original page):

• Fast access to your database without the bloat
• Edit Table Data
• Execute Ad-Hoc SQL Queries
• Create and Edit Stored Procedures, Views, Functions and Triggers
• Compare Database Schema and Synchronise the Changes or generate a Change Script.
• Compare SQL Data and Synchronise the Changes or generate a Change Script.
• Export Data to Xml, CSV and Excel File Formats
• Automatic Code Generation from SQL Schema
• Multi Window Layout
• Cached database registration for fast start up time
• 64 Bit and 32 Bit versions
  
• Export data (CSV File, Excel File, XML File and SQL Script)
• import
• compare schema
• compare data
• generate code (VB.NET, C#)
• automation abilities (NANT Build process integration)

kind regards, 

Daniel

find non-printable characters in nvarchar string (T-SQL)

Yesterday I needed to find all characters in an nvarchar which are non-alpha-numeric... long story short... I found no better way than writing my own stored function and walk over the string char by char.

Good example is shown here: http://jamesveitch.com/t-sql-function-replace-non-printable-ascii-chars/

other solutions based on PatIndex: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server

... the only adaption needed to the function of the first link is in the if clause.

First you don't have to write hard coded numbers... there is a tsql-function called "ascii" which can be used instead (e.g.: ascii('A') => 65) AND is even used in the function to convert the current char to a number... a second function "unicode" does the same, but for unicode (n)chars.

Second: use begin and end statement and check explicitly for null

Third: adapt solution for language (e.g.: german öäüß,...) ...

kr Daniel

sql server complex update

Today, I was asked how complex update statements can be executed; so I built the following sample:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

declare @t1 TABLE (a int, b int) declare @t2 TABLE (a int, b int) insert into @t1 values (1,2) insert into @t1 values (2,3) insert into @t2 values (1,200) insert into @t2 values (2,300) update t2 set t2.b = t1.b from @t1 as t1 inner join @t2 as t2 on t1.a = t2.a select * from @t1 select * from @t2

in line 1,2 I create a temporary table variable and add some values in the following lines. In line 10 it starts become interesting. Here we update the value b from table @t2 aliased as t2 and overwrite the values with the joined values of table @t1 aliased as t1. This syntax with froms and joins is much more common in select statements, but can be used in update statements as well.

The much worse solution is to create a cursor over @t1 and overwrite @t2 line by line. This solution is officially bad.

kr, Daniel

kill only connections of a concrete DB

Today I had the problem that in a dead-lock situation a colleague (responsible for a database on my db server) wanted to fix the situation himself. While using activity monitor he found out which process (more or less equals to connection) was responsible for the fault-situation and wanted to set up a kill statement.

For activity monitor he needed the rights ( http://msdn.microsoft.com/en-us/library/ms175518.aspx ):

• VIEW SERVER STATE
  
  and
• CREATE DATABASE, 
• ALTER ANY DATABASE, or 
• VIEW ANY DEFINITION 

Long story short he was not allowed to call kill and I was quite happy about it, because it makes sense that a data owner is not allowed to kill processes of other unrelated services. In fact to kill his own processes could also be accomplished by restarting the corresponding server application, so he seems to have some kind of power anyway. I liked to hear, that I hadn't have to get in touch with the process search, so I needed to enable process killing. My problem was how to secure the whole system against other kill requests and still offer the functionality. 

Some research later I found the concept of impersonation (execute as), but I couldn't imagine how. I tried to allow the user (who failed to kill processes) to switch its user context to a more privileged user. Nothing won, because he is still able to do what he likes. So I created a stored procedure which encapsulates this logic (-> security by obscurity... if you don't know you can switch, you wouldn't try it). Better, but not good enough, because the source of the stored procedure definition was readable, so it is easy to understand which execute as statements has to be executed to become privileged and a security issue (as good as worst case). I tried a lot using master DB, other schemas and so on and found in the end the following link.

http://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/

... with 5 golden rules which in fact really worked for me. 

1. We create a new database and set the TRUSTWORTHY flag on
2. We create a login with the permissions we want
3. Set the login as the owner of the new database
4. We create a stored procedure that does the work we want within the new database.
5. We add the EXECUTE AS OWNER clause to the SP

These were really good tips and finally it worked to impersonate the execution! I just had to hard code the database name which the executor is allowed to kill (in the following code snippet DBNAME1) and check whether the - now - reachable sysprocesses table has an entry with passed in spid and hard coded database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

if( exists (select * from master.sys.sysprocesses sproc where db_name(dbid) = 'DBNAME1' and sproc.spid = @kill_spid)) begin print 'resolved as DBNAME1 connection ... connection will be killed'; SET @KillStmt = 'kill ' + CAST(@kill_spid AS VARCHAR(50)) + '' PRINT @KillStmt EXECUTE(@KillStmt) end else begin print 'connection was not established to DBNAME1 ... request will be ignored'; end

kind regards,

Daniel

Regex in sql server

The like statement is sometimes a bit frustrating, because it has nothing to do with the regular expression you know from development in e.g. .NET. Similar to like is patindex.

The proprietary placeholders are:

• % for 0-n characters
• _ for 1 character
• [] ranges
• e.g. [a-f] for any single character between a and f inclusively
• e.g. [abcdef] for any single character between a and f inclusively
• e.g. [[] for [, - can be set as first item in list to use it, []], [_], [%]
• [^] ranges (any character which is not in the range.

Really using Regex:

• VBScript, COM
  http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx
• Project for native regex
  http://www.codeproject.com/Articles/4733/xp-pcre-Regular-Expressions-in-T-SQL
• CLR function
  http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
  http://msdn.microsoft.com/en-us/library/w2kae45k%28v=vs.80%29.aspx

kind regards,

Daniel


EDIT: in contrary to mysql ... there you can use it in the where statement "native". E.g.:

select * from xyTable where name regexp 'asdf$' 

fill gaps of an identity column

I found out that it is possible to fill gaps in an identity table without reseeding the table ( http://msdn.microsoft.com/en-us/library/ms176057.aspx ).

You can "set identity_insert" option to on to set the values directly ( http://msdn.microsoft.com/en-us/library/ms188059.aspx ).

But:

• only 1 Table can have this setting on
• If value inserted > the current identity value for the table -> automatically uses the new inserted value as the current identity value.

kind regards,

Daniel

SSIS Repository

Hi,

today I am working with SSIS and tried to find some 3rd party tasks.

I found the page:

   http://ssisctc.codeplex.com/

It is also a good reference how to build your own SSIS task because some are open source. I will try to build my own soon...

kind regards,
Daniel

xp_cmdshell in sql server

Hi,

today I got a request to enable xp_cmdshell on my sql server because an application needed the functionality to list the content of a directory out of a t-sql stored procedure.

The functionality is pretty old (already supported by sql server 2000), but evil referring to sql server security blog entry: http://blogs.msdn.com/b/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx

If security context is not set with caution enabling this setting enables a user to access a command line to the server with administrator rights. This is bad. (Default is to access the command line with the security context of the sql server service account, but it can be changed to other credentials using: sp_xp_cmdshell_proxy_account )

Syntax:
xp_cmdshell { 'command_string' } [ , no_output ]

Enabled the feature:

1 2 3 4 5 6 7 8 9 10 11 12

-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO

reference: http://msdn.microsoft.com/en-us/library/ms190693.aspx

Check if it is turned on or off:

1 2 3 4 5 6 7

SELECT name AS [Configuration], CONVERT(INT, ISNULL(value, value_in_use)) AS [IsEnabled] FROM master.sys.configurations WHERE name = 'xp_cmdshell'

reference: http://sqltidbits.com/scripts/check-if-xpcmdshell-enabled-across-multiple-servers

... and here an example how it can be used (found in the web):

1 2 3 4 5 6 7 8 9

create table #tmp(result varchar(255)) insert into #tmp exec master.dbo.xp_cmdshell 'ping yahoo.com' if exists(select * from #tmp where result like '%request timed out%') print 'timeout' else print 'reply' drop table #tmp

references: http://www.nullskull.com/q/11008/xpcmdshell-output.aspx

and even better:

1 2 3 4 5 6

declare @results table(result varchar(255)) insert into @results exec sp_executesql N'xp_cmdshell ''ping www.yahoo.com''' select * from @results

reference: http://stackoverflow.com/questions/1842126/call-tracert-and-ping-from-sql-and-put-the-result-in-sql-table

kind regards,
Daniel

disconnect all users from db

Hi,

it is often necessary to disconnect all open connections from a database (e.g.: if you want to delete it or take it offline). Management Studio has a checkbox to kill all connections in most of the cases where it makes sense. This is not so helpful if we want to script our stuff, so the following code suggests a way to kill all connections. (I found 90% of this piece of code on the web as a code snippet, but I forgot where... sorry for not mentioning the original source)

1 2 3 4 5 6 7 8 9 10 11 12 13

Declare @dbname sysname Set @dbname = 'db' Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) and spid > @spid End

the only open task to execute this script is to enter the name of the db you want to disconnect the user from and enter it instead of the value db in the @dbname variable.

Kind regards,
Daniel

get inserted id of identity column

Hi,

a situation I am always confronted with is: insert into a table, get this damn auto generated value to work with it. I don't know any database independent solution (except of using the max id inserted, what can be ok in some cases, but can fail too).

In sql server I found a solution for this problem at technet: http://technet.microsoft.com/de-de/library/ms190315.aspx ... this page describes a stored function which returns exactly the value we need (some kind of magic happens here).

The (filtered) example of technet:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE TZ ( Z_id int IDENTITY(1,1)PRIMARY KEY, Z_name varchar(20) NOT NULL); INSERT TZ VALUES ('Lisa'),('Mike'),('Carla'); -- inserts 1,2,3 INSERT TZ VALUES ('Rosalie'); SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]; -- returns 4

Kind regards,
Daniel