Archive for the ‘SQL Server 2008’ Category
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/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 2015/07/14
When installing SQL Server 2008 Service Pack 3 related updates, some don’t like compressed directories (even if the database files themselves are uncompressed).
I found this holds at least for KB2977321 and KB2285068.
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
–jeroen
via: Can not install KB2285068 Error Code 84B40000 – Microsoft Community.
Posted in Database Development, Development, Power User, SQL Server, SQL Server 2008, SQL Server 2008 R2, Windows | 1 Comment »
Posted by jpluimers on 2014/04/08
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 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 »
The Wiert Corner - irregular stream of stuff 