Microsoft TFS Server Custom SQL Query

Microsoft's TFS 2017 has come a long ways, but is still a pretty terrible product.  That said, it is still the best option for many things as a Microsoft developer.

One thing I have seen a lot of people on the Internet asking about is various ways to get at the TFS information stored in SQL.  Microsoft obviously refuses to answer these questions just pointing people to their APIs.  However, sometimes it is just easier to run a quick SQL query.

For those people who are just wondering where Microsoft is hiding all the various fields, I have spent a good bit of time tracking some of them down.  Remember this code is for SQL 2017 and is unlikely to work for other versions, but it might point you in the right direction.  All this is is one of my queries with some comments, I will leave it up to you to go look at the tables and see how I use them to get the data.

The very last join I do is for a custom HTML field that I created in TFS, a very similar join on that table could be used to get other HTML fields as well, such as comments or description fields.

// Dashboards.tbl_Widget stores the Dashboard settings, you can hack changes here
// dbo.QueryItems has the settings for each query
// tbl_ChangeSet stores the change sets
// vw_WorkItemComments stores all the work item comments, including comments generated when changesets are submitted
// tbl_Version holds actual file references, can be linked to changesets using VersionFrom column
// WorkItemFiles are links between workitems and the changesets
// dbo.WorkItemLongTexts stores the long texts from bugs, like the main body
// dbo.tbl_TagDefinition contains the tag names themselves joins dbo.tbl_PropertyDefinition.Name on TagId
// dbo.tbl_Field contains the descriptions for the FieldId's from tbl_WorkItemCustomLatest
// dbo.tbl_PropertyDefinition string guid table join on dbo.tbl_PropertyValue on PropertyId integer value, ArtifactId is lookup key which is WorkItemId number converted to hex

select [System.Id] ID, l_title.[TextValue] [Title], l_approvedby.[StringValue] [ApprovedBy], l_valuearea.[StringValue] [ValueArea], l_priority.[IntValue] [Priority], l_waitingon.StringValue [WaitingOn]
-- Sub Select for Tags
,(select substring((select distinct ', ' + td.[Name]
from dbo.tbl_PropertyValue pv
Inner Join dbo.tbl_PropertyDefinition pd on pv.PropertyId = pd.PropertyId
Inner Join dbo.tbl_TagDefinition td on pd.[Name] = 'Microsoft.TeamFoundation.Tagging.TagDefinition.' + cast(td.TagId as varchar(100))
    Where pv.InternalKindId = 16 And pv.ArtifactId = CONVERT(VARBINARY(8), l.[System.Id])
for xml path('')), 3, 9000)) as Tags
,l_deploy.Words DeployNotes
from vw_denorm_WorkItemCoreLatest l
    inner join vw_denorm_WorkItemCustomLatest l_title on l.[System.Id] = l_title.Id And l_title.FieldId = 1
    left join vw_denorm_WorkItemCustomLatest l_approvedby on l.[System.Id] = l_approvedby.Id And l_approvedby.FieldId = 10133
left join vw_denorm_WorkItemCustomLatest l_valuearea on l.[System.Id] = l_valuearea.Id And l_valuearea.FieldId = 10055
    left join vw_denorm_WorkItemCustomLatest l_priority on l.[System.Id] = l_priority.Id And l_priority.FieldId = 10029
    left join vw_denorm_WorkItemCustomLatest l_waitingon on l.[System.Id] = l_waitingon.Id And l_waitingon.FieldId = 10134
    left join (select Max(AddedDate) AddedDate, Id from WorkItemLongTexts Where FldID = 11135 Group By Id ) l_deploy_grp on l.[System.Id] = l_deploy_grp.Id
left join WorkItemLongTexts l_deploy on l.[System.Id] = l_deploy.Id And l_deploy.FldID = 11135 And l_deploy_grp.AddedDate = l_deploy.AddedDate
Where
l.[System.IsDeleted] = 0
    And l.[System.State] = '20 Testing'
    And l.[System.AreaPath] = '\VerizonRAB'
And ( l.[System.WorkItemType] = 'Feature' Or l.[System.WorkItemType] = 'Bug' Or l.[System.WorkItemType] = 'Task' )

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

I have run into a very interesting and very frustrating problem with Sql Server.

So far my experimenting points to a bug in sql server, though I wish it was something I was doing wrong.

I have created a scalar-valued function that uses a table variable. According to microsoft's msdn and several other sites doing inserts, updates, and deletes on local table variables in user functions is very valid.

In my case I was able to get Inserts and Deletes to work, but am having all sorts of issues with the Update statement. I copied microsofts example one and that executed fine, so then I started replacing pieces of their code with my own and saving/executing until it was a duplicate of my original function. At this point I assumed something must have been cached incorrectly in my original query window.

However, I needed to add a second update statement to the function and while the first one still works the second one won't take. So after messing with it for a bit I once again executed the microsoft example and bit by bit copied each piece of my sproc over replacing their code; and what do you know it worked again.

The only difference I can see is that their update statements are inside of a While loop, so it is still a bug in sql server, but I might be getting a little closer to a better work around.

Update: Looks like it might have been my problem after all, which is wonderful. I was using square brackets around my table variables; on a co-workers suggestion I removed those and my queries started working.

I'm guessing that all I was doing before was figuring out a way to bypass the query parser as can often happen in large stored procedures.

Cross Apply Incorrect syntax near '.'

I recently ran into an issue that had me puzzled for a bit.

I was using the new Cross Apply functionality in SQL 2005 and was getting the error:

Incorrect syntax near '.'

I checked and rechecked my syntax but couldn't figure out what I had done wrong.

After doing some googling I came accross this sqlteam post in which another poor guy had worked through the same issue.

He ended up figuring out that Cross Apply didn't work when the database was set to sql 2000 compatibility. It makes sense, though it might have been nice to get a more clear error message.

Armed with that information I looked up the code to change the db compatibility level, and what do you know my problem was solved.

Here is the code copied from the link above:

----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

T-Sql trigger doesn't always work.

I'm having a rather odd problem.  A trigger I wrote for database Inserts seems to work about 99 percent of the time, but periodically some records will get inserted that the trigger never fires for.  Or rather that the data the trigger is suppose to create is never created.

It's a rather odd delima, if I can figure it out I will try and put the answer here.  In the meantime, if anyone else has experienced this I wouldn't mind hearing about it.

Ok I figured out the answer with a little help from Google.

turns out that in SQL Server a trigger fires once per set of operation as opposed to once per affected row.

so for a standard insert statement the trigger works fine, but if the insert statement has more than one record it is inserting then the trigger will only be fired once.  Good news is that the "inserted" table contains all the records that were inserted, not just one; so you still have access to all the records, you just get them in batch form instead of individually.

joins on t-sql table valued functions

I love using functions to create re-usable code.  However I recently ran into a major issue with them.

I created one to return a simple list of ids, based on some authentication data, which I could then use to return only records that users had access to.  I read a couple of articles which said table valued functions were great for performance, especially over scalar valued functions.  They cautioned to use them in the joins though and not in the select or where clauses; the articles claimed that the select and where clauses would cause them to execute once for each record.

I followed the advice of the articles, which seemed logical, and we started having all sorts of speed issues with our application.

Through a little trial and error I discovered that my table valued function would often add minutes onto the execution time of a sproc when used in the join area of the sql statement.  I tried moving it down to the Where clause using the In keyword and the sprocs began executing in less than a second again.

Maybe I was reading the articles backwards, but I have learned to use table valued functions in my where clause and not in the join part of a sql statement.

On a side note, I have lots of really small sprocs with the function in their join clause, so it is possible that the optimizer only has problems with it when there are at least a hand full of tables it is working with.

DBCC commands to clear SQL server cache for speed tests

[quote name="Andrew Holliday"]

When tuning SQL Server applications, a certain degree of hands-on experimenting must occur. Index options, table design, and locking options are items that can be modified to increase performance. When running a test, be sure to have SQL Server start from the same state each time. The cache (sometimes referred to as the buffer) needs to be cleared out. This prevents the data and/or execution plans from being cached, thus corrupting the next test. To clear SQL Server’s cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.

[quote]

T-SQL Performance of the DatePart function

I recently have had some trouble with speed on one of my queries. Suspecting it might be a DatePart function I was applying to every row of retrieved data I wrote a little performance test and was pleasantly surprised at how efficient DatePart was:

[code]

declare @loop int, @temp varchar(10), @datestamp datetime
set @loop=1
print convert(varchar(100),getdate(),113)
set @datestamp = getdate()
while @loop < 1000000
begin
set @loop = @loop + 1
set @temp = DatePart(q, '08/27/2008')
end

print convert(varchar(100),getdate(),113)
print datediff(ms, @datestamp,getdate())

set @datestamp = getdate()
set @loop=1
while @loop < 1000000
begin
set @loop = @loop + 1
set @temp = ''
end
print convert(varchar(100),getdate(),113)
print datediff(ms, @datestamp,getdate())

[/code]

[results]

27 Aug 2008 15:23:12:293
27 Aug 2008 15:23:14:200
1906
27 Aug 2008 15:23:15:983
1783

[/results]

so the DatePart function only added a little more than 100 milliseconds to the processing time.

TSQL Datatypes

I can't ever seem to remember what the various data type ranges are. So I have linked to a page that seems to have a pretty complete list of them and how much space each takes up. I have also copied a few to this post:

bigint Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint Range: -2^15 (-32,768) to 2^15-1 (32,767)
tinyint Range: 0 to 255
Byte bit Range: 0 (FALSE) or 1 (TRUE)
bit columns in a table, will be collectively stored as: 1 Byte
9 - 16 bit columns in a table, will be collectively stored as: 2 Bytes, etc.
money Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
smallmoney Range: -214,748.3648 to 214,748.3647
float
Range: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
real Range: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
Note: Real is equivalent to float(24).
datetime Range: January 1, 1753, through December 31, 9999
smalldatetime Range: January 1, 1900, through June 6, 2079
text Maximum length is 2,147,483,647 characters.
ntext Maximum length is 1,073,741,823 characters.
binary & varbinary Maximum length is 8000 bytes.
image Maximum length is 2,147,483,647 bytes.

Restore your SQL Server database using transaction logs.

Restore your SQL Server database using transaction logs - Pr... Most DBAs dread hearing that they need to restore a database to a point in time, especially if the database is a production database. However, knowing how to do this is of the utmost importance for a DBA's skill set. I'll walk you through the steps of how to restore a SQL Server database to a point in time to recover a data table.

SQL Where clauses: Avoid Case, use Boolean logic

SQL WHERE clauses: Avoid CASE, use Boolean logic As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. ------------ Apparently Coalesce() in the Where clause removes the queries ability to use an index for evaluating that column. Makes the code a tad bit messier but really good to know when to use boolean logic.

Cast varchar to datetime

CodeGuru Forums - Convert a Varchar in a DateTime SELECT * FROM tab_val WHERE (CASE ISDATE(val) WHEN 1 THEN CAST(val As DateTime) ELSE NULL END) > CAST('2003-05-05 08:00:00.000' As DateTime) ------------------- When casting a varchar to a datetime it is critical that all the datetime pieces (such as minutes, seconds...) are in the string. Otherwise an error will be thrown.

T-SQL Sproc timeout in .NET but not Management Studio

I recently had a rather odd problem which I have been unable to solve to my satisfaction so far.

Problem:
The problem involves stored procedures which are compiled and cached. When calling the sproc from a c#.net 2.0 web application the call would timeout; however I could capture the call in SQL Profiler, run it in Management Studio and it would run great in sub second times.

Solution:
The only solution I have found so far is to re-compile the sproc manually.
You could also try adding WITH RECOMPILE to the sproc and deal with the performance loss. This was suggested in a sqlteam thread:
A similar suggestion is on SQLServerCentral.com

After reading Ken Henderson's WebLog I am guessing that the cached plan is somehow getting corrupted and that running the code manually in Management Studio somehow uses a different cached plan. Perhaps plans are different for each user? Hopefully I can find and post a better solution.

Left Join vs Left outer Join - Join vs Inner Join

Join vs Inner Join - dBforums Hi all, Can someone please describe whats the difference between: 1) Join vs Inner Join 2) Left join vs Left Outer Join TIA Falik ------------ According to this thread, the words Inner and Outer are optional in the Join syntax. This is something that wasn't ever really clear to me before so it is good to have it cleared up. The shorter code the better so I will happily stop using the non required words.

Can't truncate table with Foreign Key

When trying to truncate a table that is referenced by a Foreign Key I always get this message, even if the table referencing the primary table is empty.

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'actTouches' because it is being referenced by a FOREIGN KEY constraint.

To get around this problem I simply delete all rows from the table then re seed the identity column with 1.

Delete From [table]
Go
DBCC CheckIdent ([table], RESEED, 1)
Go

How to Insert Values into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But what if you want to insert your own value into the column? It's actually very easy to do.

The trick is to enable IDENTITY_INSERT for the table. That looks like this:


SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF


Here are some key points about IDENTITY_INSERT

* It can only be enabled on one table at a time. If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
* When it is enabled on a table you must specify a value for the identity column.
* The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

Read the full article for more information. For instance the fact that this can modify the identity properties of your column depending on the values you update the table with.

SQL Server linked servers by IP

I struggled for quite a little while trying to execute this:
Select top 1 * From [xxx.xxx.xxx.xxx].[db].[owner].[table]

In SQL2005 Management Studio against a SQL2000 box.

And receiving this:
An error occurred while executing batch. Error message is: Processing of results from SQL Server failed because of an invalid multipart name "xxx.xxx.xxx.xxx.db.owner.table", the current limit of "4" is insufficient.

I was unable to link the servers by name since there was no DNS to resolve it.

I finally discovered that I didn't receive the error if I simply used SQL 2000's Query analyzer. I was a little surprised to find that this issue in backwards compatibility existed in the Management Studio. I have not had a chance to test a SQL 2005 linked server to see if an IP address causes the same issue on it, though I would hope that it wouldn't.

Note: I just discovered that I was able to use OPENQUERY in Management Studio to run my query from one SQL2000 server to a linked SQL2000 server.

SELECT * FROM OPENQUERY([xxx.xxx.xxx.xxx], 'SELECT * FROM [db].owner.[table]') AS tablename

CAST and CONVERT (T-SQL) - datetime syntax

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy). ** Input when converting to datetime; Output when converting to character data. CONVERT (data_type[(length)], expression [, style])

using sp_executesql

T-SQL Programming Part 4 - Setting Variables in Calling T-SQ... use

use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' +
         ' where OrderId < @ORDERID' print @CMD exec sp_executesql @CMD,                   N'@RECORDCNT int out, @ORDERID int',                   @RECCNT out,                   @ORDID  print 'The number of records that have an OrderId' +       ' greater than ' + @ORDID + ' is ' +      cast(@RECCNT as char(5))

SQL Case - Syntax error converting the varchar value

variable order by when mixing datatypes? [Archive] - dBforum...

An excerpt from the above link that helped me figure out a problem I was having when using a case statement to compare strings and ints and was getting unexpected results:

" In T-SQL, a CASE expression has its data type determined before the query is processed, and the type of the CASE expression is the lowest precedence type that is at least as high a precedence of each of the CASE alternatives. This means that a CASE expression with both varchar and int alternatives will be typed as int. The result of this is that whenever any of the alternatives is evaluated, it will be interpreted as an int, which in the case of a non-numeric varchar string, can cause a run-time error. If the value of a parameter is such that the non-numeric varchar is never accessed, this won't cause an error. You might think it would be better if the CASE expression weren't typed, but that would leave undetermined the question of how something like MAX(CASE when ColumnA = 0 then ColumnB else ColumnC end) should be evaluated. There is another alternative to handle this, and that is to cause the CASE expression to be of type sql_variant (needs SQL Server 2000), to which any numeric or varchar value can be cast, and which will preserve the correct ordering of each column according to its base type: "

Temporarily Changing sa password

Temporarily Changing an Unknown Password of the sa Account What if you need to log into a SQL server using a specific sql account? What if you don't know the password to said account, but you can't permanentally change the password because it is used other other processes? I can't really think of a reason this would be needed, however when I saw the article my first thought was "what a hacker hole". I haven't read the article in its entirety but it is a good thing to keep in the back of my mind as a possible security hole.