August 2005 - Posts

Just found a great converter of code from vb.net to c# and vica versa on www.developerfusion.co.uk

http://www.developerfusion.co.uk/utilities/convertvbtocsharp.aspx

and

http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

-
Posted by simonsabin | with no comments

 If your going to the PDC let use know post your details below as a comment

Will try and get a wiki set up at some point.

Mike Tauly has setup a wiki for this see his post http://mtaulty.com/blog/archive/2005/08/26/8860.aspx

-
Posted by simonsabin | with no comments

So I did my presentation in the swimming pool at MS in Soho tonight and judging by the feedback it went well.

I covered a lot of stuff and so had to go pretty quick and the pub was waiting,but I was happy with what I covered. Someone mentioned that it was quite impressive to build a package in 40 minutes, I realised that I actually built 2, quite complex packages in this time an explained a load of stuff about SSIS, this is all possible due to the flexibility and ease of use of SSIS.

I remember Donald Farmer doing a quick package at the PDC, that was something daft like 60 seconds, which just goes to show how easy it is.

The content was as follows (slides will go up on www.sqlserverfaq.com shortly),

  1. High level overview of SSIS, differences to DTS
    1. Tasks
    2. Extensibility
    3. Flows
    4. Extensibility
    5. Pipelines, Input, output, transforms
    6. Extensibility
    7. Performance
    8. Extensibility
    9. Logging
    10. Extensibility
  2. Demo of a package to dedupe addresses from an access database secured with a workgroup file
    1. Access source
    2. Fuzzy grouping
    3. Raw destination output
    4. Data viewer on output
  3. Demo of processing a IIS log file to produce
    • The count of requests by type of page
    • The count of requests by page for asp pages in descending requewst count order, saved to a file
    • The browser for each request
    • Process all log files in a folder
    1. This required the use of flat file connections
    2. Derived colum to derive the page type from the uri-stem
    3. Multicast to allow parallel processing
    4. Aggregate to produce count by page type
    5. Conditional Split to allow filtering of just asp pages
    6. Aggregate for page request count
    7. Sort to sort Page request count correctly
    8. File destination to save results
    9. Custom script component to extract and derive the browser type from the cs-useragent
    10. Trash destination to allow analysis of browser extraction
    11. For each loop to allow processing of a folder of files
  4. Overview of logging
    1. Different providers
    2. Extensibility
    3. Logging to different logs

All of this in 40 minutes

 

-
Posted by simonsabin | with no comments

The Foreach Loop in SSIS is not the most intuitive of editors in the SSIS suite. There is a nice article on SQLIS.com, with pictures, explaining how to use it and how to use variables to set connection strings etc.

http://www.sqlis.com/default.aspx?55

-
Posted by simonsabin | with no comments

Erland is looking for support on the ability to be able to insert, update and delete rows in a view when there is an instead of trigger on the view.

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=ab8e717d-8087-4337-9c27-1b4aa12a47f5

I think this is a usability issue and I find it out that MS say they won't fix it. Bottom line is anything is possible and this is just not nice behaviour in a user friendly product.

-
Posted by simonsabin | with no comments

I will be doing a presentation on SSIS at the UK SQL Server Usergroup tomorrow evening.

http://www.sqlserverfaq.com/

I will be doing a brief overview of SSIS and a fes of the components in SSIS. Trying to convey is power and flexibility.

-
Posted by simonsabin | with no comments

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Collections

Public Class ScriptMain

Inherits UserComponent

Dim cols As SortedList = New SortedList()

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

' Add your code here

'

If cols.ContainsKey(Row.ErrorColumn) Then

If Convert.ToInt32(cols.GetByIndex(cols.IndexOfKey(Row.ErrorColumn))) < Row.OuputColumn.Length Then

cols.SetByIndex(cols.IndexOfKey(Row.ErrorColumn), Row.OuputColumn.Length)

End If

Else

MyBase.ComponentMetaData.FireInformation(0, "GetLengths:Column count " + cols.Count.ToString(), "Added Key", "", 0, False)

cols.Add(Row.ErrorColumn, Row.OuputColumn.Length)

End If

MyBase.ComponentMetaData.FireInformation(0, "GetLengths:", Convert.ToBase64String(Row.OuputColumn.GetBlobData(0, 1000)), "", 0, False)

End Sub

Public Overrides Sub PostExecute()

Dim i As Int32

MyBase.ComponentMetaData.FireInformation(0, "GetLengths:Column count " + cols.Count.ToString(), "", "", 0, False)

For i = 0 To cols.Count - 1

MyBase.ComponentMetaData.FireInformation(0, "GetLengths:Column " + cols.GetKey(i).ToString(), "max length " + cols.GetByIndex(i).ToString(), "", 0, False)

Next

MyBase.PostExecute()

End Sub

End Class

-
Posted by simonsabin | with no comments

Jamie has talked about finding objects reference in SPs and a bit of SQL for this http://blogs.conchango.com/jamiethomson/archive/2005/08/17/2031.aspx. I find that I put these in an procedure prefixed sp and put in master I can then use the shortcut features in Query analyser and SQL Server management studio to execute the sp against some highlighted text.

Assign it to say CTRL +5  and then when ever you can't remember the exact name select some text of the bit you know hit CTRL +5 and you get a list of all the objects that contain that text in the name.

CREATE PROCEDURE sp_likeObject
@Objectname varchar(100)
AS
SELECT type, name
FROM sysobjects
WHERE name like '%' + @ObjectName + '%'
ORDER BY type, name
-
Posted by simonsabin | with no comments

Someone  asked on the SQLServerfaq.com how to have a table that allows nulls but the remaining values are unique and then have another table reference this column as a foreign key. I initially was going to say no way not possible, but then got thinking.

With the use of partitioning or instead of triggers I can up with 2 solutions each with pros and cons, here is my response ...

Not pretty but with the use of partitioning or instead of triggers it is possible. If you change you null to a 0 or -1 you can partition these into another table, you then enforce the uniqueness on the table that doesn't allow nulls and put the foreign key to that. Downside is you will need a natural key because you can't use identities and partitioning.
The other option is to use instead of triggers. This fires and you then insert the relevant rows into the relevant table the one taking nulls or the one not. This has the benefit of allow your nulls, but has the downside of using a trigger and will require extra code for updates and deletes.

Here is an example with some very good names I hops it is self explanatory

drop table x
go
drop view fred
go
drop table frednull
drop table frednotnull
go
create table frednull (d int not null, a int not null check (a =0), b int, primary key (d,a))
create table frednotnull (d int not null, a int not null check (a >0 or a <0) unique, b int,primary key (d,a) )
go
create view fred
with schemabinding
as
select d,a,b
from dbo.frednull
union all
select d,a,b
from dbo.frednotnull

go
insert into fred (d,a,b) values (1,0, 1)
insert into fred (d,a,b) values (2,0, 3)

insert into fred (d,a,b) values (3,1, 1)
go
insert into fred (d,a,b) values (4,1, 3)
go
create table x ( aa int null, c int, foreign key (aa) references frednotnull(a))
go
insert into x values (1,2)
go
insert into x values (2,2)
go
drop table x
go
drop view fred
go
drop table frednull
drop table frednotnull
go
create table frednull (d int identity(1,1000), a int null , b int, primary key (d))
create table frednotnull (d int identity(1,1), a int not null unique, b int,primary key (d) )
go
create view fred
with schemabinding
as
select d,a,b
from dbo.frednull
union all
select d,a,b
from dbo.frednotnull
go
create trigger tr_fred_ins on fred instead of insert
as
SET NOCOUNT ON
insert into frednull (b)
select b
from inserted
where a is null

insert into frednotnull (a,b)
select a,b
from inserted
where a is not null
go
insert into fred (d,a,b) values (1,null, 1)
insert into fred (d,a,b) values (2,null, 3)

insert into fred (d,a,b) values (3,1, 1)
go
insert into fred (d,a,b) values (4,1, 3)

go
create table x ( aa int null, c int, foreign key (aa) references frednotnull(a))
go
insert into x values (1,2)
go
insert into x values (2,2)
go

-
Posted by simonsabin | with no comments

In SQL server 2005 there are a number of extra restrictions imposed on the size of keys in tables when using XML indexes.

John has a good post on what they are and why. He also does some interesting stuff with the new system tables so whow you waht has been created when you index an XML column

http://blogs.msdn.com/jgalla/archive/2005/08/18/453189.aspx

-
Posted by simonsabin | with no comments
More Posts Next page »