Friday, March 8, 2013

If you wish to select the Date of the first Day of This Week, simply use the following:
SELECT CAST(DATEDIFF(DAY, 0, DATEADD(DAY, 1- DATEPART(DW, @InputDate), @InputDate)) AS datetime) AS OutputDate
Similarly, if you wish to select the Date of the last Day of This Week, use the following:
SELECT CAST(DATEDIFF(DAY, 0, DATEADD(DAY, 7- DATEPART(DW, @InputDate), @InputDate)) AS datetime) AS OutputDate

Tuesday, October 5, 2010

SQL Server Number of Database Connections

The following piece of SQL can be used to find the number of connections currently active on all databases in SQL Server:
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Thursday, February 18, 2010

Setting Page.Title dynamically in C#.net

Recently I have been creating pages with titles in the format: 'Site Name | Page Name'.

Doing so is a relatively painless process, simply place a Key and Value pair for your site name in web.config appSettings as follows:
<appSettings>
<add key="site_name" value="My Website"/>
</appSettings>

Then set the site name to the format required (otherwise it will default to the Title attribute as is in your aspx page directive) by placing the following code in Page_Load of your MasterPage:
protected void Page_Load(object sender, EventArgs e)
{
string site_name = ConfigurationManager.AppSettings["site_name"];

if (!Page.Title.Contains(site_name))
Page.Title = site_name + " | " + Page.Title;
}


However, I came across a situation where I was using a single page (product.aspx) to display any one of the products in my database and so I wanted to set the PageTitle dynamically to include the product name. To do so I added the following lines to Page_Load !IsPostBack on my inner page to set the Page.Title and save it in a ViewState to avoid it reverting to the default on postback:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string site_name = ConfigurationManager.AppSettings["site_name"];

string product_name = Request.QueryString.ToString(); //Or taken from my database
Page.Title = site_name + " | " + product_name;
ViewState["PageTitle"] = Page.Title;
}
}

And replace the code on your MasterPage to check for the ViewState as follows:
protected void Page_Load(object sender, EventArgs e)
{
string site_name = ConfigurationManager.AppSettings["site_name"];

if (ViewState["PageTitle"] != null)
Page.Title = ViewState["PageTitle"].ToString();
else if (!Page.Title.Contains(site_name))
Page.Title = site_name + " | " + Page.Title;
}

Friday, August 21, 2009

Importing .xlsx to SQL Server 2008 on Windows Server 2008

After a frustrating morning of scrawling through blog posts and forums, I finally got a solution thanks to this post: http://sqlserver.posterous.com/excel-connections-in-ssis-64-b

Unfortunately, it's still a ridiculous round-about way to do a simple task but at least it works:

1. Create a DTS Package and store it on the SQL Server with you required mappings and the correct credentials (via Studio Managements 'Import Data' tool, I used Windows Authentication throughout and choosing 'Rely on server storage and roles for access controls')

2. Create a Job in with the SQL Server Agent

3. Add the step of executing the DTS package created ('SQL Server Integration Services Package')

4. 'Edit the command line manually' to include ' \86' - this ensures it runs the 32bit driver

This caused a hell of a lot of frustration, and I'm glad to say that for now at least, it's put to rest.


Wednesday, June 17, 2009

PHP on IIS 7

Here's a great resource for installing PHP/MySQL on 2008/IIS7:
http://edge.technet.com/Media/Installing-PHP-Applications-on-IIS7/

Also, very important - to get detailed error reporting, run this from the command prompt:
%windir%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpErrors -errorMode:Detailed

Monday, April 27, 2009

ASP.Net Ajax Calender Extender

I’ve been having some trouble with the display of the AJAX calendar extender.

Basically Friday was cut off a bit and Saturday completely.

Here is why this happens;
The calendar renders the days/months/years in a table, but does not specify the padding for the table cells. As a result, if you place the calendar within a table cell with padding, then the calendar table will inherit the padding from the parent table cell.


To fix this add this css;
.ajax__calendar_body table tr td { padding: 0px; }


Solution found here;
http://forums.asp.net/t/1189452.aspx

Wednesday, April 15, 2009

Mapping SQL user to a single database, hiding all others

It's straightforward to setup a user to have access to a single database, but the default problem is that although they may not be able to access the other databases, the user can still see the full list of databases on the server.

In order to get around this we need to do the following:

1. Ensure the user is setup but is not mapped to the database yet.

2. Remove the ability to view all databases for this user (or role)

In Management Studio, right click the server then click "Properties".  Click on "Permissions" and then select the particular user and select "Deny" on "View Any Database".

3. Change the owner of the database to this user

USE db_name
GO
EXEC sp_changedbowner 'user_name'
GO