Archive

Archive for the ‘SQL Server’ Category

Group by Months/Weeks

May 13, 2013 Leave a comment

Beautiful bit of code for counting valid fields and grouping the results in month/week chunks – simply replace the %%INTERVAL%% with either WEEK or MONTH (or any other valid DATEADD identifier, for that matter) and specify the table and %%DateField%% you are interested in:

    ;WITH d(d) AS 
    (
      SELECT DATEADD(%%INTERVAL%%, n, DATEADD(%%INTERVAL%%, 
DATEDIFF(%%INTERVAL%%, 0, @minDate), 0)) FROM ( SELECT TOP (DATEDIFF(%%INTERVAL%%, @minDate, @maxDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects ORDER BY [object_id] ) AS n ) SELECT --COALESCE(op.Id, @ID) AS ID, d.d AS Date, count(op.%%DateField%%) AS CountOfItems FROM d LEFT OUTER JOIN (select * FROM Ops o WHERE
o.Operation_Project = @Project_ID or @Project_ID = 1) op
ON op.%%DateField%% >= d.d AND
op.%%DateField%% < DATEADD(%%INTERVAL%%, 1, d.d) GROUP BY --op.Operation_Project, d.d HAVING d.d < (SELECT MAX(o.%%DateField%%) FROM Ops o
WHERE o.Id = @Project_ID or @Project_ID = 1) ORDER BY d.d

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Advertisements
Categories: SQL Server

The delights of SqlMetal: Multiple ResultSets from a stored procedure

May 17, 2011 5 comments

Ever wanted to return multiple result-sets from a stored procedure using Linq to SQL? Well I have and, like you if you’re reading this, I found that the default settings in Visual Studio generated code did not allow it. The problem was that the code generated only returned the first result set from the stored procedure. I.e:

SELECT * FROM USERS
SELECT * FROM ROLES

Gave me an IEnumerable return value. Now this just wasn’t good enough; so without further ado here’s the simple code to create a database entities ‘later’ using SqlMetal (a Microsoft authored tool supplied with both Visual Studio 2008 and 2010). First of all, the easiest way to start this tool (as with all Visual Studio tools) is to open the command window within the Tools sub-menu in the Visual Studio section of the Start menu.

Once in try this:

SqlMetal /server:. /database:MyDb /code:”DalEntities.cs” /sprocs /namespace:DalEntities /pluralize /serialization:Unidirectional

…and you’ll find that magically the usual dbml style generated code is produced and placed in DalEntities.cs – in this particular case with all the Stored Procedures included also.

What’s more, as previously said, the tool supports multiple result-sets to be returned from stored procedures. As shown below:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.TestStoredProcedure1")]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result1))]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result2))]
public IMultipleResults TestStoredProcedure1()
{
	IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
	return ((IMultipleResults)(result.ReturnValue));
}

It’s like magic, I tell ye!  Have fun!

MS Reporting Services Error

October 4, 2010 Leave a comment

I’ve had an issue, a couple of times now, with Reporting Services when emailing attachments (this can occur with automatic distribution of Team Foundation Server system reports). It is a known issue with RS. The fix requires administrative access to Active Directory – which I rarely have as a contract developer.

Here’s the MS help on it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;842423

…and here’s the log file entry they refer to (from problem logs I have seen in the past):

Read more…

Quick and Easy T-SQL Split Statement

June 25, 2009 Leave a comment

T-SQL

Here’s a quick and easy Split function to split a comma separated list (for example, although it could be separated with any character) into a table that can be easily joined in the calling statement.

The only bad news here is that you will need SQL 2005 or above for this to work (if my memory serves me correctly) – but then if you’re still running SQL 2000 one has to beg the question, ‘Why?’. 

Anyway – over to the code:

create function Split ( @StringToSplit varchar(2048), @Separator varchar(128))
returns 
table as return
with 
indices as
(
    select 0 S, 1 E
    union all
    select E, charindex(@Separator, @StringToSplit, E) + len(@Separator)
    from indices
    where E > S
)
    select substring(@StringToSplit,S,case 
        when E > len(@Separator) then e-s-len(@Separator) 
        else len(@StringToSplit) - s + 1 end) String, S StartIndex 
    from
        indices where S >0

Categories: SQL Server Tags: ,

Comma separated list from mapping table as an embedded SELECT statement

June 19, 2009 Leave a comment

T-SQLEver needed to get a comma seperated list instead of another mapping table? Well in these days of Linq this may be old hat – but it’s still nice to remember how to do it. Here’s a quick and easy way to get a list from a linked table constructing the list field using the XML PATH features of SQL Server 2005:

 

(SELECT CAST(CountryId AS NVARCHAR(10)) + ‘,’ AS [text()]
     FROM UserToCountryMapping AS utcm
    WHERE utcm.UserId = externalSelectTable.UserId
     FOR XML PATH(”)) as Regions

Categories: SQL Server

Location of SQLCMD and SQLPubWiz Executables

June 18, 2009 Leave a comment

T-SQLEver needed the SQL Publishing Wizard as a stand-alone install for a build server – I did and finally found it here:

SQL Publishing Wizard: http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

You might find you want a copy of SQLCMD.EXE too, it’s here: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=e

Categories: SQL Server Tags: ,
%d bloggers like this: