A-Z about SQL Joins

Are you a person who always wanted to learn SQL JOINS in detail but never got a resource for the same. Craig Freedman has an explained in detail about joins.

Introduction to Joins
Nested Loops Join
Merge Join
Hash Join
Summary of Join Properties
Subqueries in CASE Expressions
Subqueries: ANDs and ORs

Thanks to Craig for this excellent post

Get Techie Here....

SQL: Top Six ACE Security Best Practice to Secured Applications

1. Follow Principle of Least Privilege
a. Utilize access controls and code access security to minimize the privilege level of application user and service accounts.
b. SQL and .NET services should be configured to run with least privilege.

2. Input Validation for All User Input

Do:
a. Constrain input in length, character set and syntax.
b. Execute validation on the server-side.

Do Not:
a. Use only a black list (i.e., a list of unacceptable characters, phrases or words).
b. Perform client-side validation only.

3. Encode all client-supplied data when displaying it as output

a. Utilize HTMLEncode and URLEncode
b. Pay special attention to hidden variables and Response.Redirect

4. Protect Sensitive Information in Transit and Storage—(Passwords, Credit Card Numbers, Product Keys, Encryption Keys and Connection Strings)

a. Utilize IPSec or SSL for transmission.
b. Utilize Rijndael or TripleDES symmetric algorithms with minimum 128-bit key length or Asymmetric algorithms such as RSA with a minimum 1024-bit key length for storage.
c. Encrypt secrets stored in registry as demonstrated in Building Secure ASP.NET Applications.

5. Prevent SQL Injection

a. Employ parametized stored procedures, as well as input validation.
b. Never use dynamic SQL unless the application is required to generate it.
c. If required, dynamic SQL can be written using Sp_Executesql with parameterized variables

6. Restrict Information in Error Messages

a. Always turn off detailed error messages to the clients.
b. Exercise customized (generic) error messages if possible.
c. Maintain an error log.

GetTechieHere...

SQL: How to check for a parallel run of a DTS package

What will happen if the DTS/ SSIS package is running as per schedule, and if some one else manually tries to execute the same package. In the normal scenario, the package will be executed in two threads.

Here is a script by which we can check if the DTS package is already running or not. Have this check as the first step within the package. So even if some one executes a second session of the package, the package is intelligent enough not to run.

Prerequisites: Have a global variable declared as string: Variable1
Whenever the DTS package is executed, make an entry to a table in the database. This can be the table used to track the execution of the DTS packages (In the below example the table name is 'dtsLog' and column 'DTSStatus' has the value related to DTS status). dtsParent is the name of the package for which we are putting this check condition.

'**********************************************************************'
Visual Basic ActiveX Script'
************************************************************************
Function Main()
Dim oAdoConn
Set oAdoConn = CreateObject("adodb.Connection")
oAdoConn.ConnectionString = "Driver={SQL Server};Server=" & DTSGlobalVariables("Variable1").Value & ";Database=<database name>;Trusted_Connection=Yes"
oAdoConn.Open

Set rsCount = oAdoConn.Execute("SELECT DTSStatus FROM dtsLog WHERE (dtsName= 'dtsParent')")

If rsCount.Fields("DTSStatus") = "Started" then
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_SuccessEnd If
End Function

GetTechieHere...

C#: How do download files from a http location to a file share

In my previous project, we had to move files from one domain to other. The files on the source domain will be uploaded to a http location and we have to move it to another location. We couldnt do a simple DTS packages here as DTS/ SSIS supports data transfer using FTP only (I guess...If you know some method, pls let me know)

You can use the below code to download a file from a http location

string remoteUri = "http://i.microsoft.com/h/en-us/i/HP_13.5/";
string fileName = "MediaCenter_S.jpg";
string myStringWebResource = null;

// Create a new WebClient instance.
WebClient myWebClient = new WebClient();

// Concatenate the domain with the Web resource filename.
myStringWebResource = remoteUri+fileName;
Console.WriteLine("Downloading File \"{0}\" from \"{1}\" .......\n\n", fileName, myStringWebResource);

// Download the Web resource and save it into the current filesystem folder.
myWebClient.DownloadFile(myStringWebResource, "D:\\SANGOM\\" + fileName);
Console.WriteLine("Successfully Downloaded File \"{0}\" from \"{1}\"", fileName, myStringWebResource);

Remember you need to include the namespace system.net

GetTechieHere...

SQL: Using the TOP command

The TOP keyword allwos you to return the first 'n' number of rows from a query. In SQL 2005, TOP can be used to return percentage of the rows for the query.

Here is an example:
DECLARE @Percentage float
SET @Percentage = 10

SELECT TOP(@Percentage) PERCENT
Name
FROM Company.Employee
ORDER BY Name

GetTechieHere..

IIS Snap-in failed to initialize. Name: Indexing service

I keep getting this error “snap-in failed to initialize. Name: Indexing service” when I try to expand the node ‘Services and Applications’ in computer management.

I thought this has something to do with the IIS installation and tried re-installing. No luck. I came across this link http://dotnettemplar.net/CommentView,guid,dc0ab5e1-8c6f-4837-8e27-b023e0d14ee5.aspx which talks about the same issue. Yes, For me also, it was the windows desktop search that was creating this issue. You get prompted to install Windows desktop search when you install Outlook and One Note 2007.I uninstalled windows desktop search and my indexing service just works perfect

GetTechieHere ..

Getting Started

Sankar and I are gearing up to start writing technical blogs here. We will be writing on any technology from .Net to Java, from Pascal to tomorrow's technology.

Keep watching this site every morning.. this is going to be an interesting place..

GetTechieHere..