Posts

Showing posts from August, 2013

Troubleshooting Database Mail: General steps

Image
SQL Server 2008 R2 Other Versions SQL Server "Denali"SQL Server 2008SQL Server 2005
Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabledIn SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code:
Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1.
If the run_value is not 1, Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration.If you decide that it is appropriate to enable Database Mail, execute the following code:
Copy sp_configure 'Database Mail XPs&…

Method of grouping strings with XML

The following are some fine articles I found on  Brads blog page.
http://bradsruminations.blogspot.com.au/2009/10/making-list-and-checking-it-twice.html

In my previous blog entry, I described in detail how you could use the FOR XML clause to create a column with a comma-separated list of entries acquired from rows in a table somewhere.

In this article, we will do the opposite… We will take a comma-separated list and “shred” the individual items into rows.

Let’s start by creating and populating a test table:

createtable #CSVLists ( ID int,Descriptionvarchar(30),CSVList varchar(200) )insert #CSVLists select 1,'Stooges','Moe,Larry,Curly'union allselect 2,'Castaways','Gilligan,Skipper,Thurston,Lovey,'+'Ginger,Professor,MaryAnne'union allselect 3,'Monkees','Davy,Micky,Peter,Mike' (Okay, I know you Gilligan’s Island fanatics are chomping at the bit to tell me that the Skipper’s name was Jonas and the Professor’s name was Roy…

Alternative method of querying the calendar using the CTE method

Image
I have also developed a solution of mine, but using simple t-sql CTE instead of t-sql PIVOT statements. Both methods are actually ending with the same result set.
Here is the sample t-sql code for the t-sql calendar listing.
WITH CTE AS
(
SELECT
wd = DATEPART(WEEK, [date]),
[date],
Sunday = CASE WHEN [days] = 'Sunday' THEN [date] ELSE NULL END,
Monday = CASE WHEN [days] = 'Monday' THEN [date] ELSE NULL END,
Tuesday = CASE WHEN [days] = 'Tuesday' THEN [date] ELSE NULL END,
Wednesday = CASE WHEN [days] = 'Wednesday' THEN [date] ELSE NULL END,
Thursday = CASE WHEN [days] = 'Thursday' THEN [date] ELSE NULL END,
Friday = CASE WHEN [days] = 'Friday' THEN [date] ELSE NULL END,
Saturday = CASE WHEN [days] = 'Saturday' THEN [date] ELSE NULL END
FROM dbo.getFullmonth ('20080229')
)
SELECT
MAX(Sunday) Sunday,
MAX(Monday) Monday,
MAX(Tuesday) Tuesday,
MAX(Wednesday) Wednesday,
MAX(Thursday) Thursday,
MAX…

How to Query SQL server for T-SQL Calendar using GetFullMonth using Pivot

Image
After listing sql calendar date values in a month as rows, we need to display them now in a format similar to frequently used in printed calendars.
The day names are listed as column names and dates are shown under the related columns.
The first solution is supplied by Plamen Ratchev from SQLStudio.com using the t-sql PIVOT. Since I liked the t-sql code he coded much I want to share it with you here.
SELECT
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
FROM
(
SELECT
DATEPART(WEEK, [date]) AS week_number,
[date],
[days]
FROM dbo.getFullmonth('20080229')
) AS Dates
PIVOT
(
MIN([date])
FOR
[days] IN (
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
)
) AS Pivots And the output of this sql pivot statement will return the following result set as output which is displaying sql dates in a calendar format.

Build a T-SQL Calendar using SQL Function GetFullMonth on MS SQL Server

Image
SQL Server t-sql developers or administrators can use the below user defined function in order to list the calendar dates within a month.
We can pass the date as a parameter to the t-sql calendar date function and list the sql dates which belongs to the month of the given parameter date value.
You can alter the following t-sql function code as you wish in order to your requirements. For example, you can only pass the month.
You can further alter the function to pass two date parameter values and list the calendar dates within the given sql date range instead of listing only dates in a given month. For this t-sql task you can use the dbo.DateTable function which is demonstrated at How to Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code titled article.
CREATE FUNCTION GetFullMonth
(
@d datetime
) RETURNS @days TABLE
(
[date] datetime,
[days] varchar(20)
)
BEGIN

DECLARE @d1 datetime, @d2 datetime, @d3 datetime

SELECT
@d1 = DATEADD(mm, DATEDIFF(mm, 0, @d), 0),
@d2 = DATEADD(dd, -1,…

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it.
First things first you will need a merchant account with ANZ who will give you the following ·Merchant Account ·User name ·password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details.
Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide.
What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps

The site has numerous examples of the PHP, HTTP post and Asp examples. The class was based on …