Following on from Leo Pasta's lead, I've created two stored procedures to make printing out a message without waiting for a batch or transaction to completed. InstantPrint and InstantPrintTime
While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated.
The script was similar to:
DECLARE @appconfig XML
SET @appconfig = '<configuration></configuration>'
... Insert configuration nodes
-- Output the config
SELECT @appconfig as ApplicationConfiguration
The default options for SQLCMD produced output like:
(the value was a little longer, but cut off at the end)
Clearly the xml was not valid. There's header text included, corrupted tags, and missing closing tags. So I hunted through books online and google for an answer.
Turns out I needed to use the -y 0 option, which will output xml columns up to 1MB in size.
Also the -h-1 (without any spaces) will remove the header from the output.
If you are planning to use SQLCMD to output XML, remember: Hey Yo! -h-1 -y 0!
SQL injection attacks are nothing new and hopefully everyone is protecting
themselves from it.
An interesting by-product of the standard method of protection from the basic
sql injection attack (replace single quotes with two single quotes) is
increasing the size of the original value. When building a command in a
variable, its possible to truncate the original WHERE conditions allowing the
attacker to affect much more data than the developer intended.
Bala Neerumalla has written a detailed article for MSDN Magazine with
background on the attack methods, some excellent examples, and methods for
detecting and preventing SQL Truncation attacks. Definately worth reading
and implementing.
Michael Jones has a neat script for shrinking databases in small chunks to
avoid having the server locked up for a long period.
declare @sql varchar(8000)
declare @name sysname
declare @sizeMB int
declare @UsedMB int
declare @FreeMB int
declare @ShrinkMB int
-- Desired free space in MB after shrink
set @FreeMB = 1000
-- Increment to shrink file by in MB
set @ShrinkMB = 50
-- Name of Database file to shrink
set @name = 'MyDatabaseFileName'
-- Get current file size in MB
select @sizeMB = size/128. from sysfiles where name = @name
-- Get current space used in MB
select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
select [StartFileSize] = @sizeMB, [StartUsedSpace] = @UsedMB, [File] = @name
-- Loop until file at desired size
while @sizeMB > @UsedMB+@FreeMB+@ShrinkMB
begin
set @sql =
'dbcc shrinkfile ( ' + @name + ', '+convert(varchar(20),@sizeMB-@ShrinkMB)+' ) '
print 'Start ' + @sql
exec ( @sql )
print 'Done ' + @sql
-- Get current file size in MB
select @sizeMB = size/128. from sysfiles where name = @name
-- Get current space used in MB
select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
end
select [EndFileSize] = @sizeMB, [EndUsedSpace] = @UsedMB, [File] = @name
I just came across a good article by Keith Brown on a common trap when
setting up
SQL Server with kerberos delegation. As he noted, the port number for the
Sql Server Service should be specified when setting the service principal name
(SPN).
Delegation is a powerful feature of Active Directory but can be difficult to
setup. It can be used to allow SQL Server to impersonate the user when
connecting to other services: such as BULK LOAD connecting to a network file
server. Or for middle tier applications to impersonate users on other servers. A
detailed
explanation of delegation can be found here.
Microsoft's whitepaper on
troubleshooting kerberos delegation has more detail on setting up SQL
Server, including linking servers with delegation.
I've been beta testing SQL Backup 5 and
came across an old problem. If you iterate through all the databases on a server
and issue a BACKUP DATABASE for Database Snapshot you will receive an error message
similar to:
Msg 3002, Level 16, State 1, Line 1
Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
You may also see in your server logs:
Msg 3041, Severity 16, State 1.
BACKUP failed to complete the command BACKUP DATABASE databasename.
Check the backup application log for detailed message.
Snapshot databases should not need backing up, as they are read-only
representations of another database. Amongst the
limitations of Database Snapshots are that you cannot backup or restore a
snapshot database. Instead if you need, you can
revert the original database back to the Database Snapshot version. Make
sure you backup the original database first!
How do you know if a database is a snapshot?
Snapshot databases will reference their original database using the
source_database_id column in sys.databases. The following SQL displays which
databases are real and which are snapshots:
-- real databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NULL
-- snapshot databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NOT NULL
There's been some
interesting questions by users on the SQL Server Engine Tips blog. While
almost all of them seem to be off topic, there are some interesting ideas. One
of the fun ones was from Dave asking "Can we have some unsigned int data
types?".
As a basic background for those of us who spend most of their time in T-SQL;
an
unsigned integer does not hold negative values. The value range is from 0 to
the maximum of the datatype.
/* Create the unsigned integer data
types */
CREATE TYPE dbo.unsignedTinyint
FROM tinyint NULL
CREATE TYPE dbo.unsignedSmallint
FROM smallint NULL
CREATE TYPE dbo.unsignedInt
FROM int NULL
CREATE TYPE dbo.unsignedBigint
FROM bigint NULL
GO
/* unsigned integer (positive values only) rule */
CREATE RULE dbo.unsignedSmallIntRule
AS @value >= 0;
GO
CREATE RULE dbo.unsignedIntRule
AS @value >= 0;
GO
CREATE RULE dbo.unsignedBigintRule
AS @value >= 0;
GO
/* Bind the unsigned integer rules to their datatypes
*/
EXEC sp_bindrule
'dbo.unsignedSmallIntRule', 'dbo.unsignedSmallint'
EXEC sp_bindrule
'dbo.unsignedIntRule', 'dbo.unsignedInt'
EXEC sp_bindrule
'dbo.unsignedBigintRule', 'dbo.unsignedBigint'
GO
If you attempt to insert an negative value into an unsigned column, you will
receive an error message like:
Msg 612, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE
RULE statement. The statement was terminated.
or:
Msg 220, Level 16, State 2, Line 1
Aritmetic overflow error for data type tinyint, value =-1.
