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

Loading XML from a remote URL

I was charged with integrating FedEX freight for a client using soap.

I built the the query string dynamically passing in values such as freight class, weight, Sender zip code, Receiver zip code etc...

The way I normally load XML failed;

XmlTextReader rssReader = new XmlTextReader(fedEx_url);

XmlDocument rssDoc = new XmlDocument();

// Load the XML content into a XmlDocument

rssDoc.Load(rssReader);


I got this error message;

Unexpected XML declaration. The XML declaration must be the first node in the document, and no white space characters are allowed to appear before it. Line 5, position 3.


There are no errors in my code the issue was that the XML produced was invalid. When i viewed the source of the XML file generated I noted that there where four blank lines at the start and two at the end and hence the error generated.

To get round this I had to return the XML to string and then remove trailing and preceding white space;

string clean_xml = cleanXml(fedEx_url);

private string cleanXml(string fedEx_url)
{
Uri url = new Uri(fedEx_url);

string result = "";

// Create the web request
HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;

// Get response
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
// Get the response stream
StreamReader reader = new StreamReader(response.GetResponseStream());

// Read the whole contents and return as a string
result = reader.ReadToEnd();
}

// Remove trailing and preceding white space
Regex regex = new Regex(@">\s*<");

result = regex.Replace(result, "><");

return result.Trim();
}


Once you have your clean XML string you can load it as follows;

XmlReader rssReader = XmlReader.Create(new StringReader(clean_xml));

XmlDocument rssDoc = new XmlDocument();

rssDoc.Load(rssReader);


To get a value from the XML document I did the following;

decimal shipping = Convert.ToDecimal(rssDoc.GetElementsByTagName("net-freight-charges")[0].InnerText.Replace("$", ""));


I was able to solve the above with help form the following postings;
Yahoo Develoepr network
Neil Kilbride's Blog

Tuesday, February 3, 2009

SQL Management Studio - Speed-up database list load

Recently, we came across a seemingly strange issue where SQL Management Studio 2008 errored-out when trying to connect to a server with a limited permission login. Studio 2005 allowed us in without a problem.

After scrawling through blogs and forums galore, I finally discovered that it was because Studio 2008 was trying to read the 'Collation' property of each and every database on the server, and was crashing on the very first one, thus not letting me view any databases at all.

In order to stop this failing, we needed to view 'Object Explorer Details' (F7) on the 'Databases' folder, and remove the column header 'Collation'. This fixed the problem and now allowed us to view all databases, and then open the one we were permitted to.

Following up on this, on a hunch, I decided to see what happen when we un-check all other columns (database properties) except for name. Up till now, refreshing (and initially loading) a server's list of databases took painfully long... those extra seconds that kill us developers!

As expected and to my delight, with all those properties not being requested the database list loaded almost instantly on click. Better still, Studio 2008 remembers the column settings so that hereon in, only looks to load the name of the database initially, and speeds up the process every time we now open SQL Management Studio.

Binding a DataList to an ArrayList

The following example shows how to databind a DataList to an ArrayList containing uppercase Alpha Characters.

C# Function to create and attach ArrayList:
private void bindAlphabetList()
{
ArrayList al_az = new ArrayList();
for (int i = 65; i < datasource =" al_az;">

Source Code for the DataList:
<asp:DataList ID="dl_az" runat="server" RepeatDirection="Horizontal" >
<ItemTemplate>
<asp:LinkButton ID="lb_letter" runat="server" Text='<%# Container.DataItem %>'></asp:LinkButton>
</ItemTemplate>
<SeparatorTemplate> | </SeparatorTemplate>
</asp:DataList>
Note how an attribute is bound to the ArrayList value:
Text='<%# Container.DataItem %>'

Alternatively an attribute can be bound to the Index of the DataListItem in which it is enclosed, as follows:
Text='<%# Container.ItemIndex %>'

or the Index of the GridViewRow if working with a GridView:
Text='<%# Container.DataItemIndex %>'

Wednesday, January 28, 2009

Setup 301 Redirect handler for all domains on an IIS Server

There are lots of different ways out there to redirect non-www domains to their correct www.domain.com equivalent.

For SEO purposes this is of the utmost importance because without redirects Google (and other search engines) assume data is being duplicated on two domains, and thus importance (and hence ranking) is distributed between them.

I wanted to create a way to make one single fix so that all domains setup on the server with the "www." only and not the non-www domain, would have their non-www equivilent redirected with a recommended 301 redirect.

This would be done with the Default Website in IIS, making use of UrlRewriter.net and a redirect.ashx handler. Setting up IIS to handle all extensions using these instructions we ensured that all requests are handled in the following way in the web.config:
<rewrite to="~/redirect.ashx?$1" url="^/(.*)$">
Here's the snippet from the handler itself:
public void ProcessRequest (HttpContext context) {
   string url = context.Request.ServerVariables["HTTP_HOST"];

   context.Response.Clear();

    if (!url.Contains("www."))    {
        url = "http://www." + url + "/" + context.Server.UrlDecode(context.Request.QueryString.ToString());

        context.Response.Status = "301 Moved Permanently"; 
        context.Response.AddHeader("Location", url);
    }
    else
    {
        context.Response.Status = "404 Not Found";
    }

    context.Response.End();

}

Now all the host headers without a binding to a website in IIS will find it's way here if the DNS is setup similarly, so domain.com (and any requests within) redirects successfully to www.domain.com.

Google is happy. So I am happy.

Compiling ASP.Net 1.1 Projects without VS 2003

So we've moved on from many of our early projects, some we have not converted to 2.0 because very little maintenance is ever needed. The problem is that we still need to recompile these projects on minor updates. Here's how, using CSC.exe, inside of a bat file in the parent folder, assuming that the project exists in domain.com sub folder.

"C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\csc.exe" /t:library /debug /recurse:domain.com\*.cs /out:domain.com\bin\domain.com.dll /r:domain.com\bin\a_ref_library.dll /r:domain.com\bin\another_ref_library.dll
pause

Friday, January 23, 2009

Find String in Stored Procedures/Triggers/Jobs

The following SQL samples are very useful when searching your Database for a certain string. For example if you change a column name you may want to search Tables, Views, Procedures and Triggers throughout the Database for any references to the old column name.

1. Use the following SQL when searching for *Tables which contain the old column name:
use dbname
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARINDEX('OldColumnName', COLUMN_NAME) > 0
*Note: The above example also returns Views which reference the old column.

2. Use the following SQL when searching for Stored Procedures which contain the old column name:
use dbname
SELECT ROUTINE_NAME, OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE CHARINDEX('OldColumnName', OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) > 0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME

3. Use the following SQL when searching for Triggers which contain the old column name:
use dbname
SELECT DISTINCT sysobjects.name AS [Object Name],syscomments.text
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'TR'
AND sysobjects.category = 0
AND CHARINDEX('OldColumnName',syscomments.text) > 0

4. Use the following SQL when searching for Jobs which contain a certain procedure name:
SELECT DISTINCT msdb.dbo.sysjobs.name AS [Object Name], msdb.dbo.sysjobsteps.command
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id
WHERE msdb.dbo.sysjobsteps.command LIKE '%ProcName%'

Monday, January 5, 2009

Creating refCount for new Databases

Found I was tired copying table structure and then content for refCount to new databases. Below is a simple piece of SQL to create a large refCount quickly and easily.

USE [dbname]
GO

/****** Object: Table [dbo].[refCount] Script Date: 01/05/2009 12:25:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refCount](
[Count] [int] NOT NULL,
CONSTRAINT [PK_refCount] PRIMARY KEY CLUSTERED
(
[Count] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--0 entered
INSERT INTO refCount ([Count])
SELECT 0

--1 entered
INSERT INTO refCount ([Count])
SELECT 1

DECLARE @MaxCount int
SELECT @MaxCount = MAX([Count]) FROM refCount
--3 entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

DECLARE @Y int
--IMPORTANT!!
--Change this to suit your requirements where MaxCount will be [2 to the power of (Y + 2)]
SET @Y = 10 --MaxCount of your refCount after running will be 4096 ie [2 to the power of (10 + 2)]

DECLARE @Count int
DECLARE c1 CURSOR FOR SELECT * FROM refCount WHERE [Count] < @Y
OPEN c1
FETCH NEXT FROM c1 INTO @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @MaxCount = MAX([Count]) FROM refCount
--entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

FETCH NEXT FROM c1 INTO @Count
END

CLOSE c1
DEALLOCATE c1 --End Vendor Due