Thursday, May 28, 2009

SQL

ALTER TABLE Table_1 ALTER COLUMN test1 nchar(20)
------------------------------
Declare @NewInt
intexec DebugA 5,@NewInt OUTPUT
Select @NewInt
-------------------
CREATE TABLE tt (EntryID uniqueidentifier DEFAULT NewID())
select EntryID from ttt
CREATE TABLE ttt (EntryID uniqueidentifier DEFAULT NewSequentialID())
INSERT INTO tt DEFAULT VALUES
-------------------------------------------------
--Table structure

CREATE TABLE dbo.GUIDTable2
(
GUIDCol uniqueidentifier NOT NULL PRIMARY KEY DEFAULT (NEWID()),
OtherColumn varchar(25) NOT NULL
)
GO
INSERT INTO dbo.GUIDTable2 (OtherColumn) VALUES ('Performance tuning')
select * from GUIDTable2
------------------------------------------------
Auto Increment
-----------------
Create TABLE tbl_Processes
(
ProcessID int IDENTITY(1,1),
ProcessName nvarchar (100),
Active bit,
CONSTRAINT pk_PersonID PRIMARY KEY (ProcessID)
)
-----------------------------------------------------

Create TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20)
)
--------------------------------
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
----------------------------
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
----------------------------------------------
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

-----------------------------------
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

--------------------------------------------------------

SELECT *
FROM TableName E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.sal))
FROM TableName E2
WHERE E2.sal >= E1.sal))


-------------------------------------------------------------------------

sp_helptext GetUniqueAppID-------We can view the sp
---------------------------------
SQL Server Installation
-----------------------
http://www.functionx.com/sqlserver/Lesson01.htm
http://www.exforsys.com/tutorials/sql-server-2005/getting-started-with-sql-server-2005/1.html



http://www.ss64.com/sql/

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard

http://blog.sqlauthority.com/

Reporting Services:
--------------------
http://msdn.microsoft.com/en-us/magazine/cc188691.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

http://www.dotnetheaven.com/UploadFile/ursbgiri/106012007065515AM/1.aspx

Reporting
------------
http://www.codeproject.com/KB/database/MergeReplication.aspx
http://www.codeproject.com/KB/reporting-services/RecursiveData.aspx



GOOD:
=====
SELECT max( mark ) FROM `student`
--------------------
SELECT * FROM Product where Product_price = (
SELECT MAX(Product_price)FROM Product WHERE Product_price < (SELECT MAX(Product_price) FROM Product) ) -------------------- SELECT * FROM Product E1 WHERE(3 =(SELECT COUNT(DISTINCT (E2.Product_price))FROM Product E2 WHERE E2.Product_price >= E1.Product_price))
----------------------------------------------------------------------------------------------------

Renaming a Column
------------------
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

Deleting a Column
-----------------
ALTER TABLE TableName
DROP COLUMN ColumnName

DATE TIME
---------
http://www.sqljunkies.ddj.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Retrieve Current Date Time in SQL Server
----------------------------------------
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO


SELECT CONVERT(VARCHAR(10),GETDATE(),111)
--------------------------------
DateTime dtDOB = Convert.ToDateTime(Request.Form["TextBox1"].ToString());
DateTime dtNow = DateTime.Now;
//TimeSpan k=dtNow.Subtract(dtDOB);

if ((dtNow.Subtract(dtDOB).TotalDays / 365) < 18)
{
Response.Write("Sorry");
}
else
{
Response.Write("Success");
}
-------------------------------------------------
CREATE TABLE MyTable
(
ID TINYINT NOT NULL IDENTITY (1, 1),
FirstCol TINYINT NOT NULL,
SecondCol TINYINT NOT NULL,
ThirdCol TINYINT NOT NULL,
ComputedCol AS (FirstCol+SecondCol)*ThirdCol
) ON [PRIMARY]
GO

INSERT INTO MyTable
([FirstCol],[SecondCol] ,[ThirdCol]) values (1,2,3)
GO

SELECT *
FROM MyTable
GO
----------------------------------------------------\\

http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg67.htm
-----------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[VALIDATEUSER]
@UserName VARCHAR(100),
@Password VARCHAR(100),
@Result INT OUT
AS
BEGIN
DECLARE @Cnt AS INT

SELECT @Cnt = COUNT(*) FROM USERS WHERE USERNAME=@UserName AND PASSWORD=@Password
IF(@Cnt=0)
BEGIN

SELECT @Cnt = COUNT(*) FROM USERS WHERE USERNAME=@UserName
IF(@Cnt=0)
BEGIN
SET @Result = -1
END
ELSE
BEGIN
SET @Result = 0
END

END
ELSE
BEGIN
SELECT @Result=USERID FROM USERS WHERE USERNAME=@UserName AND PASSWORD=@Password
END
END

Upload Document To MOSS 2007


SPSite siteCollection = null;
SPWeb topLevelSite = null;
string strDocLibraryLink = System.Configuration.ConfigurationManager.AppSettings["SPDocumentLibrary"].ToString();
//"http://#/Corporate/DealDesk/Documents/";
siteCollection = new SPSite(strDocLibraryLink);
topLevelSite = siteCollection.AllWebs[ConfigurationManager.AppSettings["TopLevelSite"].ToString()];
// siteCollection.AllWebs["Corporate/DealDesk"];


topLevelSite.AllowUnsafeUpdates = true;
SPList list = topLevelSite.Lists["Documents"];
//foreach (SPListItem item in list.Items)
//{
// if (Convert.ToString(item["ID"]).Equals(strUARID))
// {
SPFolder objFolder = topLevelSite.GetFolder("documents");
byte[] bufDoc = null;
string strFileName = Path.GetFileName(filename.FileName);
strMossFileName = strFileName;
int nLen = filename.ContentLength;
bufDoc = new byte[nLen];
Stream oStream = filename.InputStream;
oStream.Read(bufDoc, 0, nLen);
// SPFile file = objFolder.Files.Add(strFileName, bufDoc, true);
System.GC.AddMemoryPressure(200024);
SPFile file = objFolder.Files.Add(strFileName, oStream, true);
Response.Write("File Name:" + file);
Response.Write("File ID:" + file.Item["ID"].ToString());
//strFileName = file;
file.Update();


Note: In K2 we should not upload document, tr .net only we have upload and then in k2 we have update Metadata only tr file name as a identity.