Clear Log Files In SQL Server 2008 and Beyond

Sometimes your recovery mode prevents you from clearing your log files. Change the recovery mode and then do SHRINKFILE


:SETVAR DB Datamart
:SETVAR __IsSqlCmdEnabled "True"GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'

BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';SET NOEXEC ON;END
GO
USE $(DB)
ALTER DATABASE $(DB) SET RECOVERY SIMPLE WITH NO_WAIT--SELECT name ,size/128.0 - CAST(FILEPROPERTY($(DB), 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
--FROM sys.database_files;DBCC SHRINKFILE ($(DB)_log,TRUNCATEONLY)--SELECT name ,size/128.0 - CAST(FILEPROPERTY([$(DB)], 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
--FROM sys.database_files;ALTER DATABASE [$(DB)] SET RECOVERY FULL WITH NO_WAIT

Dynmic SQL - List Space Allocation Across Dbs

DECLARE @Tbl Table
(ID INT IDENTITY(1,1),name    VARCHAR(100))


INSERT INTO @Tbl
(name)
VALUES
('model'),
('master')

DECLARE
@StartCnt INT = 1,
@MaxCnt INT = (SELECT COUNT (1) FROM @tbl),
@Db nVarchar(100),
@sql nVarchar(MAX)

WHILE @StartCnt <= @MaxCnt

BEGIN
SET @Db = (SELECT Name FROM @Tbl WHERE ID = @StartCnt)
PRINT @db
SET @sql = '
USE '
+@Db
+' SELECT '''
+ @DB
+''' DB, SUM(Size)/128 Used_MB, SUM(maxsize)/128 Allocated_MB
FROM sysfiles'


  exec sp_executesql @sql OUTPUT
  SET @StartCnt = @StartCnt+1
  END

SQL Tips - Cross Apply vs Cross Join

I have a query at work with a CROSS APPLY that I found hard to read and incorrectly assumed was under performing. I tried rewriting it using an inner join to a table variable and realized I had decreased the performance four fold. My miscalculation came from my confusing CROSS APPLY with CROSS JOIN. I found a nice definition of the two provided by Pandian Sathappan.

CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.

2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)

CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

2.The table-valued function acts as the right input and the outer table expression acts as the left input.

3.The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

Reference:
Pandian Sathappan

www.SQLServerbuddy.blogspot.com  

Some Ruby Rapid Prototyping Notes

How to articles
http://www.akitaonrails.com/2009/1/13/the-best-environment-for-rails-on-windows/#.UZW4q8ogacs

http://rubyonrails.org/download

IDE
http://www.aptana.com/downloads/start

Language Installer
http://rubyforge.org/projects/rubyinstaller/


Package Manager.
This is ruby gems. It seems to be included in ruby 2. You can install from rubygems.org/pages/download

A Text Editor
ftp://ftp.vim.org/pub/vim/pc/gvim72.exe

Rails
Using the command line package manager, issue $> gem install rails

MVC for Ruby, built into rails 3.
http://rubyonrails.org/merb

Add on management is done through Git. You need a tool to get through git. Use the IDE above.
Git Book
http://git-scm.com/book

ORM STUFF
https://www.ruby-toolbox.com/categories/orm
http://stackoverflow.com/questions/3609482/activerecord-talk-to-two-databases

Active Record may not be the best, but it is the best supported and widely used. Sequel may be the best functionally, with Datamapper being in the top three. Below is a basic note from the Datamapper dude on when yo use SQL vs ORM to code modeling.

accepted

I'm the DataMapper maintainer, and I think for complex reporting you should use SQL. While I do think someday we'll have a DSL that provides the power and conciseness of SQL, everything I've seen so far requires you to write more Ruby code than SQL for complex queries. I would much rather maintain a 5 line SQL query than 10-15 lines of Ruby code to describe the same complex operation. Please note I say complex.. if you have something simple, use the ORM's build-in finders. However, I do believe there is a line you can cross where SQL becomes simpler. Now, most apps aren't just reporting. You may have alot of CRUD type operations, for which an ORM is perfectly suited and far better than doing those things by hand. One thing that an ORM will usually provide is some sort of organization to your application logic. You can group code based around each model in the same file. It's usually there that I'll put the complex SQL query, rather than embedding it in the controller, eg: class User include DataMapper::Resource property :id, Serial property :name, String, :length => 1..100, :required => true property :age, Integer, :min => 1, :max => 130 def self.some_complex_query repository.adapter.select <<-SQL SELECT ... FROM ... WHERE ... ... more complex stuff here ... SQL endend Then I can just generate the report using User.some_complex_query. You could also push the SQL query into a view if you wanted to further cleanup this code. EDIT: By "view" in the above sentence I meant RDBMS view, rather than view in the MVC context. Just wanted to clear up any potential confusion. TERMS ruby - The interpreter itself. Run Ruby scripts or statements. gem - Ruby Package Manager. Great for automatically downloading or updating small Ruby modules like XML libraries, web servers, or even whole Ruby programs. irb - Interactive Ruby Prompt. This is an entire Ruby shell that will let you execute any Ruby code you want. You can load libraries, test code directly, anything you can do with Ruby you can do in this shell. Believe me, there is quite a lot that you can do with it to improve your Ruby development workflow [1]. ri - Quick shell access to Ruby documentation. You can find the RDoc information on nearly any Ruby Class or method. The same kind of documentation that you would find on the online ruby-docs. erb - Evaluates embedded Ruby in Ruby Templated documents. Embedded Ruby is just like embedding php into a document, and this is an interpreter for that kind of document. This is really more for the rails crowd. An alternative would be haml. rdoc - Generate the standard Ruby documentation for one of your Ruby classes. Its like Javadocs. It parses the Ruby source files and generates the standard documentation from special comments. testrb and rake. I'm not familiar enough with these. I'd love it if someone could fill these in!

Other DB stuff
http://guides.rubyonrails.org/migrations.html
http://lostechies.com/rayhouston/2008/05/03/connecting-activerecord-to-sql-server/

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds
gem install activerecord
gem install activerecord-sqlserver-adapter

T-SQL - Converting Time in SQL Server to Ticks

We were trying to determine why the start date field in a tool we use was stored as a bigint. I thought it may be ticks.

To figure this out we tried to do the following. It did not get our answer, but it was a very cool use of the binary data type.

DECLARE @date datetime = '1/22/2013'

,@ticksPerDay BIGINT = 864000000000declare @date2 datetime2 = @datedeclare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9))declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks]


Detailed reference.
http://stackoverflow.com/questions/7386634/convert-sql-server-datetime-object-to-bigint-net-ticks

Turned out the value was a json formatted date, which is defined as number of milliseconds in Universal Coordinated Time (UTC) since midnight January 1, 1970 (UTC). Again, this is a mere convention and not part of the JSON standard. If you are exchanging data with another application, you will need to check its documentation to see how it encodes date and time values within a JSON literal. This is based on the face that javascript does not have a datetime datatype, so you can pass the time as a big int (which is what we had) or a string that is formatted like a date. The below test transform targets the date

DECLARE @EpochDate
DateTime2 = '1/1/1970',@TargetDate DateTime2
--change this date for your transformation,@JsonDate BIGINT = 628318530718
,@Milli INT,@Seconds BIGINT
SET @Milli = RIGHT(@JsonDate,3)SET @Seconds = LEFT(@JsonDate,LEN(@JsonDate)-3)SELECT @EpochDate, @JsonDate, @Milli, @Seconds SET @TargetDate = dateadd(ms, @Milli,dateadd(second,@Seconds,@EpochDate))IF ((MONTH(@TargetDate) >= 3 AND DAY(@TargetDate) >= 11 AND HOUR(@TargetDate)AND
(
MONTH(@TargetDate) <= 11 AND DAY(@TargetDate) <= 2))SELECT DATEADD(HOUR,-8, @TargetDate)ELSE SELECT DATEADD(HOUR,-7, @TargetDate)

T-SQL MERGE INTO With Replace Substring Manipulation - When You are Thinking About things the Hard Way

Today I was told that we needed to change the ULRs for some sites to remove the subdomain element of the URL and instead insert an environment value before the domain. 

I opted to do a MERGE INTO rather than a simple update. The reasons for this are technical and complicated. Well really, I had never used REPLACE in an UPDATE and assumed it would not work. I also was more interested in something fancy, rather than the simple solution. Oops.

So instead of doing this

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

UPDATE PartnerConfig USING (
        SET ConfigString = REPLACE(ConfigString,@prodDomainStr, @qaDomainStr)
WHERE ConfigTypeID = @configType


I used this. Both did the job. It is an ad hoc infrequent update of a limited record set (<100 rows). It took me longer to write the merge, and I am sure the query plan is worse for me doing so. Lesson learned.

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

MERGE INTO PartnerConfig CCTarget
USING (
        SELECT PartnerConfigID, REPLACE(ConfigString,@prodDomainStr, @qaDomainStr) VALUE
        FROM PartnerConfig
        WHERE ConfigTypeID = @configType
      ) AS CCSource
        ON CCTarget.PartnerConfigID = CCSource.PartnerConfig
WHEN MATCHED THEN
UPDATE
SET CCTarget.ConfigString = CCSource.ConfigString;

As always, the example has been sanitized to use generic names. This can lead to malformed code.

T-SQL - XQuery - Delete Nodes From XML Document

When working with XML in SQL server you are using a sublanguage called XQuery inside of T-SQL. You have access to XML methods when using an XML typed value.  This query was written to be run by support, so it has some built in tests that assist support in knowing we are targeting the correct data. The keys here are:
Practice this in a test environment before you do it on prod. XML is tricky.

Understand the layout of your XML document. The one I was targeting was simple. We have some duplicate data and I wan to remove it.

<ResponseRoot attrib1='1' attrib2='2'>
<Answer id='77' value='work it out'>32</Answer>
<Answer id='78' value='work it out'>22</Answer>
<Answer id='79' value='work it out'>1112</Answer>
<Answer id='80' value='work it out'>112</Answer>

<Answer id='81' value='work it out'>12</Answer>
<Answer id='82' value='work it out'>42</Answer>
<Answer id='83' value='work it out'>552</Answer>
<Answer id='84' value='work it out'>92</Answer>
<Answer id='85' value='work it out'>2</Answer>

<Answer id='86' value='work it out'>12</Answer>
<Answer id='87' value='work it out'>42</Answer>
<Answer id='88' value='work it out'>552</Answer>
<Answer id='89' value='work it out'>92</Answer>
<Answer id='90' value='work it out'>201</Answer>


</ResponseRoot>

The [13] is part of XPath, which is used by XQuery to locate items in an XML document. Unlike C# (which enumerates nodes from 0), XQuery in T-SQL enumerates the nodes from 1.  Below I am looking for the 9th through 13th child nodes of the root to delete. I deleted them from bottom to top to prevent changing the absolute position of the nodes as I deleted them. Note that the 13th node is not determined by the data or the attributes of the node. Just by the ordinal position from the root.

I build the XML value in a variable by selecting the XML data from the table, updating it before I run the update, and then updating the row with the variable.
 

BEGIN TRAN

DECLARE

  @UserId INT = (SELECT UserId FROM User WITH (NOLOCK)  WHERE FIRSTNAME = 'Person' AND LASTNAME = 'Someone')

, @ObjectID varchar(99) = (SELECT ObjectID From Object WITH (NOLOCK) WHERE Name = 'Object name')

, @ResponseId INT

SET @ResponseId = (SELECT MAX(ObjectResponseID) From ObjectResponse WITH (NOLOCK) WHERE UserId = @UserId AND ObjectID = @ObjectID)

--Verify correct response is targeted

IF @UserId IS NOT NULL

BEGIN

IF @ResponseId = 123456

BEGIN

SELECT ObjectResponseXML Before FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

DECLARE @XDoc XML

SET @XDoc = (SELECT ObjectResponseXML FROM ObjectResponse WHERE ObjectResponseID = @ResponseId)

--Remove 13 before 12 to retain absloute order of preceding nodes you will delete

SET @XDoc.modify('delete (/ResponseRoot/Answer[13])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[12])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[11])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[10])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[9])')

UPDATE ObjectResponse

SET ObjectResponseXML = @XDoc

WHERE ObjectResponseID = @ResponseId

SELECT ObjectResponseXML After FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

END

ELSE Print 'Object response not found.'

END

ELSE Print 'User not found.'

ROLLBACK