Archive for the ‘SQL Server 2014’ Category
Posted by jpluimers on 2024/08/29
A friend of mine needed to figure out SQL Server deadlocks a while ago.
I hadn’t been doing stuff like this for quite some time, but remembered that unlike old SQL Server days, it had become relatively easy.
These links show you how:
Query:
[Wayback/Archive] sql server find deadlock history – Google Search
--jeroen
Posted in Database Development, Development, Software Development, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2020/09/03
Some links for my research (when writing this a few years back, I had not done a lot of SQL Server BLOB work for a while, so I missed a lot of interesting progress).
As of SQL Server 2008, one can use FileStream, which SQL Server installs on an NTFS file system local to the SQL Server instance, but is accessible over the SQL Server API. It benefits from a lot of SQL Server features (including transactional and backup related).
As of SQL Server 2012, FileTable has extended FileStream, which makes it even easier to access the files from Windows applications.
Over the years, FileStream and FileTable has improved a lot.
Some links to get a feel:
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2018/01/24
The subtle difference between -q and -Q: the latter will exit after executing the command (regardless of the SQL server version; I think this was introduced in SQL Server 2005 or 2000).
Inside the command, you can use single ' quotes for strings.
C:\Users\jeroenp>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 10.50.2500.0 NT x64
Copyright (c) Microsoft Corporation. All rights reserved.
usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
–jeroen
via: [WayBack] c# – How to terminate sqlcmd immediately after execution completed? – Stack Overflow
Posted in Database Development, Development, Software Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | 1 Comment »
Posted by jpluimers on 2017/03/16
A long time ago I wrote about Fixing 84b40000 error on SQL Server 2008 updates (like KB2977321 and KB2285068).
The same holds for error 0x858C001E errors when updating SQL Server 2012 and 2014:
For x86 systems, ensure these directories are not compressed:
C:\Program Files\Microsoft SQL Server
C:\Program Files\Microsoft SQL Server Compact Edition
For x64 systems, ensure these directories are not compressed:
C:\Program Files\Microsoft SQL Server
C:\Program Files x86\Microsoft SQL Server
C:\Program Files x86\Microsoft SQL Server Compact Edition
Sources:
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2017/01/12
Static Code Analyzer for T-SQL – MS SQL Server.
Plugs into MS SSMS and can also be run from command line.It reports useful clues which you can turn/on off to your liking. http://sqlcodeguard.com/index-database-issues.html
It will spot declared but unused variables, but it appears it doesn’t do code coverage or execution path to spot stuff like variables being used without being initialized.
http://sqlcodeguard.com/ Price: Free
Source: Lars Fosdal on G+: Static Code Analyzer for T-SQL – MS SQL Server. Plugs into MS SSMS and can al…
–jeroen
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2016/12/08
SQL server % (modulo, not mod) operator doesn’t like floats (with reason).
You should get rid of the floats as they will give inaccurate results.
As a workaround, cast either through an integer or through a decimal: sql server modulo float – Google Search
CAST(CAST(TheInaccurateFloatValue AS decimal(38,19)) % ModuloValue AS float)
The decimal(38,19) is the maximum non-float precision you get.
( cast(dividend as integer) % divisor ) + ( dividend - cast(dividend as integer))
–jeroen
Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2016/09/28
Common table expressions are awesome. They work in at least Oracle and SQL Server.
You cannot nest them, but you can use them consecutively. Thanks spender for explaining that:
WITH
x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y
–jeroen
via: sql – Can you create nested WITH clauses for Common Table Expressions? – Stack Overflow.
Posted in Database Development, Development, OracleDB, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »
Posted by jpluimers on 2014/03/31
With SQL Server, when your database is in “Recovery Pending” mode don’t just start blindingly search google, but sit down as you might be causing more damage doing so.
After sitting down, read these two posts by Paul Randal | SQLskills.com from his SQL Server Corruption series:
- Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database.
- SQL Server EMERGENCY mode repair.
Then think about it before acting.
Though the simplest cause for “Recovery Pending” might be that a disk spin-up was slow, or a disk became full (and everything might just be dandy after the disk is available and there is enough room on it), make sure you read the above posts first before relying on the simple causes.
–jeroen
Posted in Development, Software Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Tagged: EMERGENCY mode, Recovery Pending, SQL Server | Leave a Comment »
Posted by jpluimers on 2014/03/19
Interesting: SQL Server 2014 is done: Hekaton, Azure integration « Tim Anderson’s ITWriting.
Most important feature is the in-memory database engine (OLTP).
Because the in-memory OLTP it is so highly optimized there are quite a few T-SQL features missing, but usually it is a magnitude faster than the disk based database engine.
For limitations, see Transact-SQL Constructs Not Supported by In-Memory OLTP.
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2014 | Leave a Comment »
The Wiert Corner - irregular stream of stuff 