sql rank function and complex queries

This following site very usefull for ranking for student and know about complex queries


http://www.1keydata.com/sql/sql-rank.html

create Captcha

style Menubar

http://javascript-array.com/scripts/simple_drop_down_menu/

sql server tutorial

http://msdn.microsoft.com/en-us/library/aa258864%28v=SQL.80%29.aspx

DOT net tutorial

http://www.devmanuals.com/tutorials/ms/aspdotnet/updatedataindatabase.html

SQL SERVER ALTER TABLE COLUMN

ALTER table CHILDINVENTORY ALTER COLUMN ItemDescription varchar(8000) NULL

SP_HELP CHILDINVENTORY

get count of tables from one database

1)Get all the table name from one database

SELECT table_name FROM INFORMATION_sCHEMA.TABLES

2)count how many tables from one database

Select count(table_name) FROM INFORMATION_sCHEMA.TABLES

TABLE SCHEMA GET COLUMN NAME FROM TABLE

SELECT COLUMN_NAME FROM INFORMATION_sCHEMA.COLUMNS WHERE TABLE_NAME='TABLENAME'


or

select * from sys.columns where object_id in(select object_id from sys.tables where name='tablename')

Windows Service Configuration Via CommandPrompt

STEP:1
Goto Run-->type cmd-->Command Prompt ---> type cd\
c:\> CD c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322
Give Enter

STEP:2 (For Install Windows Service)
C:\windows\Microsoft.NET\Framework\v1.14322> Installutil /i Your Exe Path

example :

C:\windows\Microsoft.NET\Framework\v1.14322> Installutil /i D:\DirectI_RenewDomainservice\bin\DirectI_RenewDomainservice.exe


Give enter Some commands run in that command prompt

Check That Window Service

Mycomputer ->right click -->manage--> Services and application-->services-->check ur Exe Name

Step:3 (For Unistall Windows Service)
C:\windows\Microsoft.NET\Framework\v1.14322> Installutil /u Your Exe Path

example :

C:\windows\Microsoft.NET\Framework\v1.14322> Installutil /u D:\DirectI_RenewDomainservice\bin\DirectI_RenewDomainservice.exe

Face Book Like Fan Box for blog

http://www.bloggerplugins.org/2011/01/facebook-like-fan-box-for-blogger.html

Custom Validations For DropDown

script type="text/javascript"
function CountryCheck(source, args)
{
var ddlCountry1 = document.getElementById ("dropweight");
var country = ddlCountry1.options[ddlCountry1.selectedIndex].value;

if (country == "0")
{
args.IsValid = false;
}
else
{
args.IsValid = true;
}

}
script

asp:CustomValidator ID="CustomValidator2" runat="server" ErrorMessage="Select Unit" Display="dynamic" ControlToValidate="dropweight" ClientValidationFunction="CountryCheck" ValidationGroup="addproduct"

PageLife Cycle

http://www.codeproject.com/KB/aspnet/ASPDOTNETPageLifecycle.aspx

DTSX execution via stored procedure

CREATE PROCEDURE [dbo].[UspEComm_StockExecutingDTSXPackage]

@IntProcessCount Int,

@dtsFileName VARCHAR(1000),

@ErrorMessage VARCHAR(250) OUTPUT

AS

BEGIN

DECLARE @decrptPassword VARCHAR(200),

@ssisstr VARCHAR(1000), @errCode INT

IF @IntProcessCount = 1

BEGIN

TRUNCATE TABLE ABEInventoryTemp

END

SET @decrptPassword = '"@r@bm0r3"'

SET @ssisstr = 'DTExec.exe /FILE "' + @dtsFileName + '" /DECRYPT ' + @decrptPassword + ' /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW '

EXEC @errCode = xp_cmdshell @ssisstr

IF @errCode=0

BEGIN

SET @ErrorMessage='The package executed successfully.'

END

ELSE IF @errCode=1

BEGIN

SET @ErrorMessage='The package failed.'

END

ELSE IF @errCode=3

BEGIN

SET @ErrorMessage='The package was canceled by the user.'

END

ELSE IF @errCode=4

BEGIN

SET @ErrorMessage='The utility was unable to locate the requested package. The package could not be found.'

END

ELSE IF @errCode=5

BEGIN

SET @ErrorMessage='The utility was unable to load the requested package. The package could not be loaded.'

END

ELSE IF @errCode=6

BEGIN

SET @ErrorMessage='The utility encountered an internal error of syntactic or semantic errors in the command line.'

END

ELSE

BEGIN

SET @ErrorMessage='The package failed.'

END

END

/*

SYMITAR..ACCOUNT

select top 5 * from FTPDownloadReport order by idpk desc

truncate table inventorytemp

select count(*) from inventorytemp

Declare @ErrorMessage varchar(100)

EXEC UspEComm_ExecutingDTSXPackage 'D:\NewBooks\SQL Packages\I2FULLINVPackage.dtsx',@ErrorMessage

Print @ErrorMessage

*/

SET QUOTED_IDENTIFIER OFF

sys is undifined and type is undifiend

http://www.kodyaz.com/blogs/software_development_blog/archive/2008/01/03/768.aspx

Get column name from one table

SELECT [name] AS [Column name]

FROM syscolumns

WHERE id = (SELECT id

FROM sysobjects

WHERE type = 'U'

AND [NAME] = 'product')

Get Filename from Directories and store in Datatable

private void button1_Click(object sender, EventArgs e)

{

string[] filename=Directory.GetFiles("D:\\Deepa\\AgentAgroFarms\\");

for (int i = 0; i <> {

string a=(filename[i]);

file += a.Replace("D:\\Deepa\\AgentAgroFarms\\","")+",";

}Publish Post

file = file.Substring(0, file.LastIndexOf(",") - 1);

DataTable tbl_filename = new DataTable();

tbl_filename.Columns.Add("filename",typeof(string));

DataRow dr=tbl_filename.NewRow();

dr[0]=file;

}

Get value from Row Command C#

//get value from linkbutton commandArgument isbn id

string isbn =Convert.ToString( e.CommandArgument);

Session["Inventory_ISBN"].ToString = isbn;

//get value from hidden filed

LinkButton btnedit = (LinkButton )(e.CommandSource);

HiddenField hf = (btnedit.Parent.FindControl("vendorid_hidden")) as HiddenField;

Session["Inventory_vendorid"] = Int32.Parse(hf.Value);///Datekey value

Update data in Gridview

http://www.webswapp.com/categories/ASPNET2/DropDownList_GridView/default.aspx

GET VALUE FROM DROPDOWNLIST

string ContentName = e.Values["ContentName"].ToString();         
         
// Get Value from DropDown
int TopicID = 0;
foreach (DetailsViewRow row in ItemsDetailsView.Rows)
{
    if (row.Cells[0].Text == "Topic")
    {
         DropDownList TopicDropDown = (DropDownList)row.Cells[1].FindControl("Topic_TopicIDList");
         TopicID = int.Parse(TopicDropDown.SelectedValue.ToString());
    }

}

xml format

private bool fnBulkInsFilterISBN(DataTable dt, string sPID, ref string sErrorMessage)

{

bool blnResult = false;

DataSet ds = new DataSet();

if (dt != null)

{

ds.Tables.Clear();

ds.Tables.Add(dt.Copy());

ds.Tables[0].TableName = "InventoryDetails";

dt = ds.Tables[ds.Tables[0].TableName];

if (dt.Rows.Count > 0)

{

System.Text.StringBuilder sb = new System.Text.StringBuilder(1000);

System.IO.StringWriter sw = new System.IO.StringWriter(sb);

foreach (DataColumn col in dt.Columns)

{

col.ColumnMapping = MappingType.Attribute;

}

ds.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);

SqlConnection ConVMSServer;

SqlCommand cmdVMSInsertlog;

ConVMSServer = new SqlConnection(strServerConnection);

if (ConVMSServer.State == ConnectionState.Closed) { ConVMSServer.Open(); }

int intRecordsAffected = 0;

try

{

cmdVMSInsertlog = new SqlCommand("Usp_VMSInvSearchBulkInsert", ConVMSServer);

cmdVMSInsertlog.CommandTimeout = 0;

cmdVMSInsertlog.CommandType = CommandType.StoredProcedure;

cmdVMSInsertlog.Parameters.AddWithValue("@ID", sPID);

cmdVMSInsertlog.Parameters.AddWithValue("@empdata", SqlDbType.NText).Value = sb.ToString();

intRecordsAffected = cmdVMSInsertlog.ExecuteNonQuery();

if (intRecordsAffected > 0) { blnResult = true; }

}

catch (Exception ex)

{

SetErrorMsg(ex.Message);

}

finally

{

if (ConVMSServer.State == ConnectionState.Open) { ConVMSServer.Close(); }

ConVMSServer.Dispose();

}

}

}

return blnResult;

}

sql Factorial

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 10

SELECT * FROM @Fact
--Solution :
DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 10

DECLARE @no INT,@f BIGINT,@nums INT

DECLARE C1 CURSOR FOR SELECT nums FROM @fact
OPEN C1
FETCH NEXT FROM C1 INTO @no
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @f=1,@nums=@no
WHILE @no>0
BEGIN
SET @f=@f*@no
SET @no=@no-1
END
DECLARE @t TABLE(nums INT,Fact BIGINT)
INSERT INTO @T SELECT @Nums,@F
FETCH NEXT FROM C1 INTO @no
END
CLOSE C1
DEALLOCATE C1

SELECT * FROM @T ORDER BY 1

Abstract and interface

An Interface cannot implement methods.
An abstract class can implement methods.

An Interface can only inherit from another Interface.
An abstract class can inherit from a class and one or more interfaces.

An Interface cannot contain fields.
An abstract class can contain fields.

An Interface can contain property definitions.
An abstract class can implement a property.

An Interface cannot contain constructors or destructors.
An abstract class can contain constructors or destructors.

An Interface can be inherited from by structures.
An abstract class cannot be inherited from by structures.

An Interface can support multiple inheritance.
An abstract class cannot support multiple inheritance.

different button same function

head
body
form id="form1" runat="server">
div>
LinkButton
LinkButton
LinkButton

Private Sub Confirrmshipment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
While True

DispatchOrders()
FnUpdateEbayStatus()
FnUpdateBuyComStatus()
UnshipedReports()
toCheckManualOrders()

Try

Dim sleepTimeCal As Integer = 0
sleepTimeCal = 1000 * 60 * Convert.ToInt32(ConfigurationManager.AppSettings.Get("SleepInterval"))
Thread.Sleep(sleepTimeCal)
Catch ex As Exception

Finally

End Try

End While


End Sub

Different types of date show

different types of show date

SELECT CONVERT(VARCHAR(100),GETDATE(),100) --Feb 8 2011 5:43PM
SELECT CONVERT(VARCHAR(100),GETDATE(),101) --02/08/2011
SELECT CONVERT(VARCHAR(100),GETDATE(),102) --2011.02.08
SELECT CONVERT(VARCHAR(100),GETDATE(),103) --08/02/2011
SELECT CONVERT(VARCHAR(100),GETDATE(),104)--08.02.2011
SELECT CONVERT(VARCHAR(100),GETDATE(),105)-- 08-02-2011
SELECT CONVERT(VARCHAR(100),GETDATE(),106) --08 Feb 2011
SELECT CONVERT(VARCHAR(100),GETDATE(),107) --Feb 08, 2011
SELECT CONVERT(VARCHAR(100),GETDATE(),108) --17:41:36
SELECT CONVERT(VARCHAR(100),GETDATE(),109) --Feb 8 2011 5:41:57:530PM
SELECT CONVERT(VARCHAR(100),GETDATE(),112) --20110208
SELECT CONVERT(VARCHAR(100), GETDATE(),113) --08 Feb 2011 17:43:44:903
SELECT CONVERT(VARCHAR(100), GETDATE(),114) --17:44:04:450
SELECT CONVERT(VARCHAR(100), GETDATE(),110) --ANSWER :02-08-2011

SELECT CONVERT(VARCHAR(100),GETDATE(),120) --201102082011-02-08 17:45:17

SELECT CONVERT(VARCHAR(100),GETDATE(),111) --2011/02/08

SELECT CONVERT(VARCHAR(100),GETDATE(),121)--2011-02-08 17:45:43.513

Read Xml format

« SQL SERVER – Simple Example of Creating XML File Using T-SQL
SQL SERVER – Restore Master Database – An Easy Solution »
SQL SERVER – Simple Example of Reading XML File Using T-SQL
February 13, 2009 by pinaldave
In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.
Following is the XML which we will read using T-SQL:
Following is the T-SQL script which we will be used to read the XML:
DECLARE @MyXML XMLSET @MyXML = 'WhiteBlueBlackGreenRedApplePineappleGrapesMelon'
SELECTa.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’++a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4FROM @MyXML.nodes(‘SampleXML’) a(b)

INSERT DATA FROM TEXTFILE TO DATABASE

Private Sub readfile()
'Dim delimiter As String() = {vbTab}
'Dim headers As String() = Split(lines(1), delimiter(0))

Dim tabSize As Integer = 0
Dim arInfo As String()
Dim objstreamreader As StreamReader
objstreamreader = File.OpenText(Ge_Filename + filename)
' ReadAllText("E:\vengatesh\USADD@12!20110130163507-1_4.txt")
Dim a As String()
a = File.ReadAllLines(Ge_Filename + filename)
' contents = a.Replace(("").PadRight(tabSize, " "c), vbTab)
Dim textdelimiter As String() = {vbTab}
For count As Integer = 1 To 12
arInfo = Split(a(count), textdelimiter(0))
For i As Integer = 0 To arInfo.Length
' row = Table.NewRow()
If i < arInfo.Length Then
vendor = arInfo(i).ToString()
End If
If i + 1 < arInfo.Length Then
user = arInfo(i + 1).ToString()
End If
If i + 2 < arInfo.Length Then
product = arInfo(i + 2).ToString()
End If
If i + 3 < arInfo.Length Then
itemcondition = arInfo(i + 3).ToString()
'Table.Rows.Add(row)
End If
If i + 4 < arInfo.Length Then
itemnode = arInfo(i + 4).ToString()
'Table.Rows.Add(row)
End If
If i + 5 < arInfo.Length Then
price = arInfo(i + 5).ToString()
End If
If i + 6 < arInfo.Length Then
seller = arInfo(i + 6).ToString()
End If
If i + 7 < arInfo.Length Then
quantity = arInfo(i + 7).ToString()
End If
If i + 8 < arInfo.Length Then
adddelete = arInfo(i + 8).ToString()
End If
If i + 9 < arInfo.Length Then
shipinternelly = arInfo(i + 9).ToString()
End If
If i + 10 < arInfo.Length Then
exiptedship = arInfo(i + 10).ToString()
End If
If i + 11 < arInfo.Length Then
mrp = arInfo(i + 11).ToString()
Dim get_Value = obj.Insert_data(vendor, user, product, itemcondition, itemnode, price, seller, quantity, adddelete, shipinternelly, exiptedship, mrp).ToString()
End If
i = i + 12
Next

Next










End Sub

CSV file format tab delimiter

Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE PROCEDURE venkattext @FILENAME varchar(200) AS DECLARE @cmd varchar(5000) DECLARE @F_Name varchar(8000) DECLARE @tempCOUNT int BEGIN CREATE TABLE ##TempFileGen2(isbn varchar(500) ,title varchar(300),authorname varchar(200),manufacturerid bigint,imageurl varchar(300),binding varchar(200),sku varchar(300),price int,name varchar(300))
Insert into ##TempFileGen2(isbn,title,authorname,manufacturerid,imageurl,binding,sku,price,name)
select top 100 P.ISBN,P.title,P.AuthorName,p.manufacturerid,P.ImageURL,p.binding,s.sku,pr.usprice as Price,m.namefrom product p join stock s on p.productid=s.productid join price pr on pr.sku=s.sku joinmanufacturer m on p.manufacturerid=m.manufacturerid and p.AmazonSync=1 and s.stockInHand>0 and pr.active=1 and s.active=1
SET @F_Name = 'E:\vengatesh\'+@FILENAME+'.txt' SET @Cmd= 'bcp "SELECT * FROM gmimagedb..[##TempFileGen2] " queryout "' -- SET @Cmd = @Cmd + @F_Name + '" -U -P -c -U sa -P @r@bm0r3' SET @Cmd = @Cmd + @F_Name + '" -c -T -S TESTSERVER\SQL2008TEST' print @cmd EXEC master..xp_cmdshell @Cmd END

sql server

2) Write small procedure for automatic backup

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor


3)HOW TO COVERT DATE TO VARCHAR


SET @VARIABLE NAME=CONVERT(VARCHAR(100),GETDATE(),112)

SQL QUERES

CURSOR EXAMPLES:

1) Write the cursor program examples for select top most 100 author name from product table and print with serial no

Answer:

SET NOCOUNT ON
DECLARE @name VARCHAR(100),@COUNT INT=0
DECLARE OPENCURSOR CURSOR FOR
SELECT TOP 100 AUTHORNAME FROM PRODUCT
OPEN OPENCURSOR

FETCH NEXT FROM OPENCURSOR INTO @name

WHILE @@FETCH_STATUS=0
BEGIN
SET @COUNT=@COUNT+1
PRINT LTRIM(STR(@COUNT)+')'+ @NAME)+','
FETCH NEXT FROM OPENCURSOR INTO @name
END
CLOSE OPENCURSOR
DEALLOCATE OPENCURSOR

Output:

1)Frances Hodgson Burnett,
2)Rudyard Kipling,
3)Kenneth Grahame,
4)Colin Hawkins,Jacqui Hawkins,
5)Jane Kemp,Clare Walters,
6)Jane Kemp,Clare Walters,
7)Enid Blyton,
8)Enid Blyton,
9)Nick Butterworth,
10)Judith Kerr,
11)Judith Kerr,
12)Dr. Seuss,
13)Dr. Seuss,
14)Dr. Seuss,
15)Dr. Seuss,
16)Elizabeth Laird,John Richardson,
17)Enid Blyton,
18)Enid Blyton,