One of my published articles at the SQL Server Central that was written in 2005 has been published again. This article was written about UDTs (at the very first stage when it was introduced) and the sample I had taken become very controversial.

If you interest, read it.

Wrote the first article to the SQL Server Universe, regarding SSIS. This is the first article of the series of articles. We will be adding more different types of articles soon but currently focusing on SSIS and SSAS. Read it and see whether it is worthwhile.

I found this problem while I was trying to produce some sample codes for the question published in the dotnetforum.lk. Before continuing, run the below codes and examine the result.

declare @sql varchar(500)

if object_id('tempdb..#temptable1', 'U') is not null
begin
   drop table #temptable1
end

create table #temptable1 (id int)
insert into #temptable1 values (1)

set @sql = 'create table #temptable1 (id int); insert into #temptable1 values (2);select id from #temptable1;'
set @sql = @sql + 'select * from tempdb.sys.objects where name like ' + quotename('#temptable1%', '''') + ';'
exec (@sql)

select * from #temptable1
select * from tempdb.sys.objects where name like '#temptable1%'

Simple code. It creates a temp table called temptable1 and inserts a record. Secondly, it executes a dynamice sql statement that contains same code. It creates a temp table with same name and inserts a different value. As you expected, SQL Server will create two tables with same name but different suffixes. The result of the last two select statements shows that the table created inside the dynamic query is not avaiable and only one temp table exist in the  tempdb database.

Everything is okay. Now change the first set statement like below.

set @sql = 'create table #temptable1 (id int, id2 int); insert into #temptable1 values (2,2);select id from #temptable1;'

Now it creates the same table with different structure. But the result is not like before. You get an error saying "Insert Error: Column name or number of supplied values does not match table definition.". I went trhough few things, found some explanation in Itzik Ben-Gan's T-SQL Programming book (Believe me, this books has solved my many TSQL issues.). The reason must be related to the naming resolution. SQL Server resolves the temptable in the dynamic query to the table created in main code. I am not sure about the exact behavior but this become a good lesson for me. Hope it will for you too.

Yes, it is the song about databases by Roy Osherove. You must listen guys. Here is  the link.

Thanks for Chandana publising it in the SQL Server Universe.

I am pretty sure that this solves many problems that were exist with applications deployed in mobile devices, laptop, etc.. This Synchronization service provides features to synchronize data from data services to local store and it handles everything you need.

Read more and download from here.

The second SQL Server Sri Lanka user group meeting held yesterday. Dinesh Asanka started the first session regarding replication. I did a level 300 presentation regarding indexes, not about how to create indexes but analyze whether they are useful. I showed the audience about the usage of DTA, index related DMVs and DMFs and some Performance Dashboard reports. I made this session as level 300 because I wanted to cover the related topics without going to details. Since some were not familiar some areas, we had to go to detail level that took some time from the planned special session that was set regarding SQLRefactor tool. Time went up and had no time do the special segment and put off to next meeting. Presentations and sample codes will be published soon.

We were able to give some cool give away items; Visual Studio DVD packs and Windows Server 2003 DVD pack sponsored by Microsoft Sri Lanka and a book (MS SQL Server 2005 Administrator's Pocket Consultant) sponsored by SQL Server Universe. Visit the next meeting, learn something, and win something, not only from Microsoft and SQL Server Universe, from red-gate too.

Gogula pointed out me an interesting issue with SSIS, that removes the ScriptTask code completely :(. He had been developing a package that accesses a text file and loads data into SQL Server. One of the ScriptTasks contained some codes for validation processes that check for junk characters. The issues he had was, whenever he closes and reopens the application, part of the ScriptTask code is missing. Believe me or not, the below code caused this weird behavior.



I was able to get this matter checked by a person who is in MS SSIS development team to make sure that the problem is not from our-end. It seems that it is a kind of issue with VSA environment, unfortunately will not be fixed soon but with next version of SQL Server (Nothing is officially confirmed). So, make sure you do not include this type of odd characters in your code, use ASCII values instead.

The 2nd SSUG meeting is scheduled on 16th May, 2007. As usual, two sessions will be done; regarding replication and indexing. I'll be speaking about indexing, not about how to create them, but how to analyze and diagnosis. During my presentation, I'll be going covering some of new DMVs and dashboard facility too. Dinesh Asanka will be delivering the first session regarding replication. If you are in Sri Lanka, and interest, visit and see how useful these sessions are. For more info, visit http://www.sqlserveruniverse.com.

I am glad to announce that SQL Server Universe did its first SQL training session for one of leading marketing companies in Sri Lanka. Gogula and myself ran four interactive sessions regarding BI, SSIS, SSAS and data analyzing with Office 2007 (including hands-on training). The goal of all these training sessions was implementing business intelligence with SQL Server 2005 product suite. The sessions went okay and seemed that the data mining part was the most interest for them.

We have some plans to launch SQL Server hands-on training for developers, will be announcing dates and places soon.