Archive

Archive for March, 2009

Use APPLY

Use APPLY
The apply statement was created for the situation where you put multiple inline nested queries in the one statement. For example, take the following statement:
SELECT soh.SalesOrderID,
Quantity=(SELECT TOP 1 (Quantity)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID),
UnitPrice=(SELECT TOP 1 (UnitPrice)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID)
FROM Sales.SalesOrderHeader soh
This performs an extra query, retrieving data from another table using the same criterion. This can now be replaced with the following:

SELECT soh.SalesOrderID, soh.OrderDate, a.*
FROM Sales.SalesOrderHeader soh
CROSS APPLY (
SELECT TOP (1) sod.UnitPrice, sod.Quantity
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderId = soh.SalesOrderId
ORDER BY sod.Quantity DESC
) as a

Categories: SQL Server Tags:

URL Rewriting in the IIS 7.0

follow the below mention steps for URL Rewriting in the IIS 7.0.

  1. Open Properties of the Virtual Directory “website1” from IIS.
  2. Click on “Configuration”, from Virtual Directory Tab.
  3. Click on “Insert” from Mappings Tab.
  4. Add the following path,

a. “X:\WINDOWS\Microsoft.NET\Framework\v2.0.507\aspnet_isapi.dll” in Executable. (X is the Windows Drive. i.e. where the framework is installed”)
b. Uncheck the Option “Check that file Exist”.

URL Rewriting in the IIS 6

Follow the below mention steps for URL Rewriting in the IIS.

1) Open Properties of the Virtual Directory  from IIS.

2) Click on “Configuration”, from Virtual Directory Tab.

3) Click on “Add” from Mapping Tab.

4) Add the following path,

a. “X:WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_isapi.dll”  in Executable.  (X is the Windows Drive. i.e. where the framework is installed”)

b. Type “.*” in Extension.

c. Uncheck the Option “Check that file Exist”.

Add the following code in Global.asax file

string[] arrPath;
string strPath = string.Empty;
string strUrl = string.Empty;
string strQS = string.Empty;
string strQrystr = string.Empty;

string strLocal = ConfigurationManager.AppSettings[“Local”];

protected void Application_BeginRequest(object sender, EventArgs e)
{
string str1 = “UrlRequest”;
string str2 = str1;

strQrystr = string.Empty;
strUrl = string.Empty;
setPath();

if (strPath.IndexOf(“.”) > 0)
{
return;
}
try
{
// ### Start – If main page(like http://localhost/websiteName/) then exit sub
if (strPath == “” || strPath == “/”)
{
HttpContext.Current.RewritePath(strLocal + “/Default.aspx”, “”, “”);
return;
}

strQS = Request.QueryString.ToString();

if (System.IO.File.Exists(Server.MapPath(“~/” + arrPath[1] + “.aspx”)))
{
strUrl = ConfigurationManager.AppSettings[“Local”] + “/” + arrPath[1] + “.aspx”;

if (arrPath.Length > 0)
{
for (int i = 0; i < arrPath.Length; i++)
{
if (arrPath[i] != null && arrPath[i] != “”)
{
strQrystr = strQrystr + “q” + i + “=” + arrPath[i] + “&”;
}
}
}
if (strQrystr.Length > 0)
{
strQrystr = strQrystr.Substring(0, strQrystr.Length – 1);
}
}
else
{
Response.Redirect(strLocal + “/Default.aspx”);
}

//### Start – Actual Url Rewrite

if (strQS == “”)
HttpContext.Current.RewritePath(strUrl, “”, strQrystr + strQS);
else
{
if (strQrystr != “”)
{
HttpContext.Current.RewritePath(strUrl, “”, strQrystr + “&” + strQS);
}
else
{
HttpContext.Current.RewritePath(strUrl, “”, strQS);
}
}
}
catch (IndexOutOfRangeException ex)
{
ex.Message.ToString();

}
catch (Exception ex)
{
ex.Message.ToString();
}
}
private void setPath()
{
strPath = HttpContext.Current.Request.Path.ToString().ToLower();

if (HttpContext.Current.Request.Url.AbsoluteUri.ToLower().IndexOf(“localhost”) != -1 || strLocal != “”)
{
strPath = strPath.Replace(strLocal.ToLower() + “/”, “”);
}
else
{
strPath = strPath.Substring(1);
}
arrPath = strPath.Split(‘/’);
}

Add following lines in web.config

<add key=”Local” value=”/VirtualdirectoryName”/>

Making Store Procedure Generic

Making Store Procedure Generic

Consider creating stored procedures in the master database.

Use the special “sp_” prefix for these procedures, and use the undocumented system stored procedure “sp_MS_marksystemobject” to mark the stored procedure as a system stored procedure.

System stored procedures, although residing in the master database, operate on the database objects from where the stored procedure is executed.

All the usual caveats about using undocumented procedures and adding stored procedures to a system database apply.

Example:
***********
CREATE PROCEDURE sp_counts AS

DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQL VARCHAR(200)
create table #temp
(
tablename varchar(300),
noofrows int
)

DECLARE C1 CURSOR FOR
select table_name from information_schema.tables where table_type=’base table’

OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQL = ‘select isnull(null,”’+@TABLENAME+”’),rows from sysindexes where indid<2 and id = object_id(‘+ ”” + @TABLENAME +”” +’)’

insert into #temp exec(@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
close C1
deallocate C1
delete from #temp where tablename = ‘dtproperties’
select * from #temp order by noofrows desc
drop table #temp
GO

********
exec sp_MS_marksystemobject ‘sp_counts’
********

Now from any database you can give EXEC SP_COUNTS which gives you the tablename and total rows in the table.