2013年12月18日 星期三

mssql-transaction



Transactions
Transactions are used when you need to perform more than one operation such
as Insert, Update, or Delete or when you want to validate that the result is what
it is expected to be. If everything is good, you commit the transaction; if something
goes wrong, you can roll back the transaction and everything goes back to what it
was before.
To start a transaction, use the following command:
Begin tran T1
Here, T1 is the name of the transaction and can be any name you want. If everything
is good with the operations and you want to commit the transaction, you use the
following command:
Commit tran T1
If you want to cancel the transaction and go back to what it was before, you use
the following command:
Rollback tran T1

mssql-cursor


DECLARE account_cursor CURSOR FOR Select name, revenue from
FilteredAccount
OPEN account_cursor
declare @name as varchar(160)
declare @revenue as money
FETCH NEXT FROM account_cursor INTO @name, @revenue
WHILE @@FETCH_STATUS = 0
BEGIN
-- do something here
print @name + ' ' + CAST( isnull(@revenue, '') as varchar(40))
FETCH NEXT FROM account_cursor INTO @name, @revenue
END
CLOSE account_cursor
DEALLOCATE account_cursor

mssql-procedure



CREATE PROCEDURE mySP
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from FilteredAccount
END

To modify a stored procedure we use the ALTER command as follows:
ALTER PROCEDURE mySP
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT name from FilteredAccount
END
To delete a stored procedure we use the DROP command as follows:
DROP PROCEDURE mySP
To execute a stored procedure we use the EXEC command as follows:
EXEC mySP


mssql-select

.

select by date

select sum([Cost Posted to G_L]) AMT ,[location code]
from dbo.[DataBase$value entry] ve
where [posting date]>='2012-07-01'  and [posting date]<='2013-11-30'
group by [location code] order by [location code]


mssql-top

Select TOP 2 name, address1_stateorprovince from FilteredAccount

Select UPPER(name), LOWER(address1_stateorprovince) from FilteredAccount

Select UPPER(name) as name , LOWER(address1_stateorprovince) as address1_
stateorprovince from FilteredAccount

Select * from FilteredAccount WHERE name like '%damian'

Select * from FilteredAccount WHERE name like '%damian%'

Select * from FilteredAccount WHERE name = 'damian' OR telephone1 =
'9999'

Select * from FilteredAccount ORDER BY name desc

Select address1_stateorprovince, count(*) from FilteredAccount group
by address1_stateorprovince

Select address1_stateorprovince, count(*) as state_count from
FilteredAccount group by address1_stateorprovince

Select FilteredContact.fullname, FilteredAccount.name from
FilteredContact inner join FilteredAccount on FilteredContact.accountid =
FilteredAccount.accountid
















Update FilteredAccount set address1_country = 'U.S.'


generator-example head and line



0.10,FILED_SERVICE_CONTRACT_HEADER.doc_no,text,5%,8,doc_no,, 
0.20,FILED_SERVICE_CONTRACT_HEADER.CONTRACT_NO,text,5%,8,CONTRACT_NO,, 
0.30,FILED_SERVICE_CONTRACT_HEADER.CUSTOMER_NO,text,20%:100px:100px,10,CUSTOMER NO,,{required}
0.40,FILED_SERVICE_CONTRACT_HEADER.YOUR_REFERENCE,text,100%:200px:400px,40,Description,,{required}
0.61,FILED_CONTRACT_LINE.item_no,text:GRID,5,100,ITEM NO
0.61,FILED_CONTRACT_LINE.DESCRIPTION,text:GRID,50,150,DESCRIPTION
0.61,FILED_CONTRACT_LINE.recordID,text:GRID,10,150,_,,

2013年12月17日 星期二

navision-tutorial -book

http://it-ebooks.info/book/2315/

asp date to string

asp date2string

CurrentDate = Date()
CurrentTime = Now()
NewDate = DatePart("yyyy", CurrentDate)
NewDate1 = DatePart("m", CurrentDate)
NewDate2 = DatePart("d", CurrentDate)
hh = DatePart("h", CurrentTime)
mm = DatePart("m", CurrentTime)
ss = DatePart("s", CurrentTime)
hh=hh+1
outFile=NewDate&"-"&NewDate1&"-"&NewDate2&"_"&hh&"_"&mm&"_"&ss