MS SQL Search within programmable objects (sp, trigger, view,...)

Below script searches keywords within programmable objects in all databases.

declare @keyword1 varchar(50) = 'SomeTable'
declare @keyword2 varchar(50) = 'SomeColumn'

create table #t
(
 [ReferencingDatabase] varchar(100),
 [ReferencingSchema] varchar(100),
 [ReferencingObject] varchar(100),
 [ReferencingObjectType] varchar(100),
 [HelpText] varchar(300)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
use ?
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
''?'' as [ReferencingDatabase],
s.name as [ReferencingSchema],
o.name as [ReferencingObject],
o.type_desc as [ReferencingObjectType],
''sp_helptext '''''' + s.Name + ''.'' + o.name + '''''''' as [Script]
from sys.syscomments c
left outer join sys.objects o on o.object_id = c.id
left outer join sys.schemas s on s.schema_id = o.schema_id
where
c.text like ''%' + @keyword1 + '%''
and c.text like ''%' + @keyword2 + '%''
end
'
exec sp_msforeachdb @cmd1
select * from #t
drop table #t

Find dependant objects in MS SQL

Below script finds dependant objects for a given object.


declare @server varchar(50) = 'sql server name'
declare @db varchar(50) = 'database name'
declare @schema varchar(50) = 'schema name'
declare @object varchar(50) = 'object name'

create table #t
(
[ReferencingDatabase] varchar(100),
[ReferencingSchema] varchar(100),
[ReferencingObject] varchar(100),
[ReferencingObjectType] varchar(100)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
 ''?'' as [ReferencingObjectDatabase],
 s.name as [ReferencingObjectSchema],
 o.name as [ReferencingObjectName],
 o.type_desc as [ReferencingObjectType]
from ?.sys.sql_expression_dependencies d
inner join ?.sys.objects o on o.object_id = d.referencing_id
inner join ?.sys.schemas s on s.schema_id = o.schema_id
where d.referenced_entity_name = ''' + @object + '''
and (d.referenced_server_name = ''' + @server + ''' or d.referenced_server_name is null)
and (d.referenced_database_name = ''' + @db + ''' or d.referenced_database_name is null)
and (d.referenced_schema_name = ''' + @schema + ''' or d.referenced_schema_name = '''' or d.referenced_schema_name is null)
end
'

exec sp_msforeachdb @cmd1
select * from #t
drop table #t

SOA Tips

I learned some tips about implementing service oriented architecture by experiencing the difficulties that emerged because we didn't care about them.

A. SOA does not mean that you're not going to do object oriented design.

In order to avoid complexity, you should design the objects in your business domains.

If you do not, SOA eventually becomes procedural programming. You call methods from the client one by one.

B. Each client should call only one service method to do one job. Otherwise:

Round trip increases: cost of opening a connection from client and sending data through network is much much more than you think. Doing it multiple times for a single job makes your software incredibly slow.

Because services run stateless (at least our implementation does so) every time you get a call at the service, you instantiate all the data to do your job again and again. Take all the data you need to do a job, then do it at once.

TFS vNext needs audit log reports

Because of some governmental regulations, we need to report the administrative actions over permissions on TFS. Auditors frequently request such reports from us.

I requested the audit logs of administrative actions over permissions on TFS from Microsoft in the VS vNext Wish List.

We'll see what Microsoft thinks about adding audit reports to TFS in the next version. What do you think, is it worth?

TFS Case Study of Microsoft Turkiye

Microsoft Turkiye examined our TFS implementation and published a case study document:
https://blog.microsoft.com.tr/turkiye-finans-katilim-bankasi.html

Unfortunately the document is prepared only for Turkish audience, it has no English translation.

Count lines of code in a database

Here is a small script to count lines of code in a database:

select
    o.type_desc as ObjectType
    ,count(*) as [ObjectCount]
    ,sum(len(m.definition) - len(replace(m.definition, char(13), ''))) as [TotalLineCount]
from sys.objects o
inner join sys.sql_modules m on o.object_id = m.object_id
where o.[type] in ('P', 'TR', 'FN', 'IF', 'V', 'TF')
and o.name not like 'aspnet[_]%'
and o.name not like 'vw[_]aspnet[_]%'
group by o.type_desc
order by o.type_desc

Sign a .Net assembly without the source code

Two different versions of an old .Net assembly caused some deployment problems. We wanted to deploy the assembly to the GAC but neither of the two versions were signed. We needed to sign the assemblies but we did not have the source code.

After a small search I found a great tool named ILMerge.

Main purpose of ILMerge is to combine more than one .Net assemblies into one assembly. The side benefit is that you can also sign the resulting assembly. It works while working with only one assembly too. This way you can sign an assembly without having the source code.

Sample command line:

ilmerge /t:library /keyfile:"c:\SomeKeyFile.snk" /out:"c:\signed\assembly.dll" "c:\unsigned\assembly.dll"

Entity framework class generator

I wrote a sql script to generate entity framework POCO classes: https://gist.github.com/954916

Script considers primary and foreign keys including both "one to one" and "one to many" relations.

It also writes sql object descriptions saved as extended properties to the class and property comments of POCO classes.

It is working for MS SQL 2008 and 2008 R2. I did not try to run the script on other versions.

C#: Call DynamicMethod within another DynamicMethod

Today I tried to call a dynamic method within another dynamic method.

First I built a dynamic method, m1dyn:

DynamicMethod m1dyn = new DynamicMethod("m1", typeof(string), new[] { typeof(string) });
var il = m1dyn.GetILGenerator();
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Ret);

Then I created a delegate and assigned it to a delegate variable named m1:

Func<string, string> m1 = (Func<string, string>)m1dyn.CreateDelegate(typeof(Func<string, string>));

Then I built a second dynamic method, m2dyn:

DynamicMethod m2dyn = new DynamicMethod("m1", typeof(string), new[] { typeof(string) });
var il2 = m2dyn.GetILGenerator();
il2.Emit(OpCodes.Ldarg_0);
il2.Emit(OpCodes.Call, m1.Method);


I simply emitted a code which calls the method info of the first delegate variable, m1.
When I ran the code, I got the following error at the last line above:
"MethodInfo must be a RuntimeMethodInfo. Parameter name: meth"

What I had to do was to emit a call instruction with the first dynamic method variable itself, not the resulting delegate:
il2.Emit(OpCodes.Call, m1dyn);

Complete code is below:

public static void Test()
{
DynamicMethod m1dyn = new DynamicMethod("m1", typeof(string), new[] { typeof(string) });
var il = m1dyn.GetILGenerator();
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Ret);
Func<string, string> m1 = (Func<string, string>)m1dyn.CreateDelegate(typeof(Func<string, string>));

DynamicMethod m2dyn = new DynamicMethod("m1", typeof(string), new[] { typeof(string) });
var il2 = m2dyn.GetILGenerator();
il2.Emit(OpCodes.Ldarg_0);
il2.Emit(OpCodes.Call, m1dyn);
il2.Emit(OpCodes.Ret);
Func<string, string> m2 = (Func<string, string>)m2dyn.CreateDelegate(typeof(Func<string, string>));

string a = m2("a");
Console.WriteLine(a);
}

WCF BasicHttpBinding and windows authentication on IIS 6.0

Recently I tried to host a WCF service on IIS 6.0 with basic http binding.
I needed to authenticate users with windows security mode.

I configured the WCF binding like below in the web.config file:

<bindings>
<basicHttpBinding>
<binding name="BasicHttpWindowsBinding">
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Windows" />
</security>
</binding>
</basicHttpBinding>
</bindings>


Although I configured IIS so that anonymous authentication is not allowed and windows authentication is required, I got this error when I opened the service page with internet explorer:

System.NotSupportedException: security settings for this service require windows authentication but it is not enabled for the iis application that hosts this service

Well, it was disappointing because both service configuration and the IIS configuration was configured for windows authentication mode.

After a little struggle I found that basicHttpBinding's windows authentication is not supported by IIS 6.0.
Instead security mode of the binding should be set to Ntlm and you have to allow anonymous authentication in the IIS site settings.

Here is the complete config file:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

<system.web>
<compilation debug="true" />
<customErrors mode="Off" />
<authentication mode="Windows">
</authentication>
<authorization>
<allow users="somedomain\someuser"/>
<deny users="*"/>
</authorization>
</system.web>

<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v3.5" />
<providerOption name="WarnAsError" value="false" />
</compiler>
</compilers>
</system.codedom>

<system.webServer>
<validation validateIntegratedModeConfiguration="false" />
<handlers>
<add name="SvcFile" path="*.svc" verb="*" type="System.ServiceModel.Activation.HttpHandler" resourceType="Unspecified" preCondition="integratedMode" />
</handlers>
<directoryBrowse enabled="true" showFlags="Date, Time, Size, Extension, LongDate" />
</system.webServer>

<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="BasicHttpWindowsBinding">
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm" />
</security>
</binding>
</basicHttpBinding>
</bindings>
<services>
<service behaviorConfiguration="SomeServiceBehavior" name="SomeAssembly.SomeService">
<endpoint binding="basicHttpBinding" bindingConfiguration="BasicHttpWindowsBinding" contract="SomeAssembly.ISomeService">
<identity>
<dns value="localhost" />
</identity>
</endpoint>
<endpoint address="mex" binding="mexHttpsBinding" contract="IMetadataExchange" />
<host>
<baseAddresses>
<add baseAddress="https://localhost/SomeAssembly/ISomeService.svc" />
</baseAddresses>
</host>
</service>
</services>
<behaviors>
<serviceBehaviors>
<behavior name="SomeServiceBehavior">
<serviceMetadata httpGetEnabled="True"/>
<serviceDebug includeExceptionDetailInFaults="True" />
</behavior>
</serviceBehaviors>
</behaviors>
</system.serviceModel>

</configuration>

No history entries were found for the item and version combination specified

This is a message when I tried to view the history of a folder on TFS.

I googled the message and only a few exact matches came out. First two was useless. I was terrified for an instance, but this blog entry saved me :

http://team-foundation-server.blogspot.com/2007/07/no-history-entries-were-found-for-item.html

The problem was that I had no permissions on the folder although I was an administrator. I could never remember to check the securities.

When I checked it I saw that the folder was not inheriting permissions. After inheriting permissions I could view the history of the folder.

Migrating from VSS to TFS

Beware!!! This is not a technical article about how to migrate VSS to TFS.
This is a story about my experience of migrating to TFS.

When we first met with TFS we were using Visual Source Safe 6.0.

Advisors from Microsoft came and introduced the brand new source version control system of Mirosoft, TFS. Future of the source version controls was TFS. Moreover TFS was not just another source version control system. We could even use TFS to manage projects instead of our own project management system. It also had the shelve option when you are leaving work at night. TFS brought everything we needed. They urged us to migrate to TFS because Microsoft itself was migrating to TFS. Because it was newer and better, we should quit VSS and use TFS instead.

Get Database Schema

If you want to list the tables of a database and columns of a table you may use the "GetSchema" method of System.Data.Common.DbConnection class.
It works on both MS Sql Server 200 and 2005. I did not try on other servers.

Sample Code

DbConnection con = new OleDbConnection(connetionString);
DataTable tables =
con.GetSchema("TABLES", new string[] {null, null, null, "BASE TABLE" });
DataTable columns =
con.GetSchema("COLUMNS", new string[] {"CustomerTable", null, null });

Dictionary Performance

I tested performances of these classes :

A) Key-value keepers
- Dictionary
- SortedDictionary
- SortedList
B) Value keepers
- List
- HashSet
- List (Sorted and binary searched)

I inserted a number of random numbers to these classes and searched for numbers afterwards.
Below results show the average of 100 tests.