Get the Basic Idea of Cursor from Given Written Cursor

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO

CREATE proc GET_BIN_RES_A --123--1024--123--668--1363--1128--777--255--33--1137--1386--1137--33--1061 --138--1137--289--99--255--1061--1373 --Get_Bin_cursor @Item int as Declare @rv_no varchar(30) Declare @rv_no1 varchar(30) Declare @rv_no2 varchar(30) Declare @rv_no3 varchar(30) Declare @iv_no varchar(30) Declare @iv_no1 varchar(30) Declare @iv_no2 varchar(30) Declare @rv_date smalldatetime Declare @iv_date1 smalldatetime Declare @iv_date2 smalldatetime Declare @iv_date smalldatetime Declare @rv3 smalldatetime Declare @vendor_name varchar(200) Declare @client_name varchar(200) Declare @client_name1 varchar(200) Declare @bill_no int Declare @podate smalldatetime Declare @bin_no int Declare @itemdesc varchar(250) Declare @pono varchar(100) --Declare @pono varchar(100), declare @recd_qty int declare @issue_qty int declare @issue_qty1 int declare @item_receipt int Declare @Balance int Declare @potrate int Declare @Bal int Declare @po1 varchar(100) declare @aa int Declare @Bal1 int Declare @Bal11 int Declare @Bal12 int Declare @Bal2 int Declare @Bal3 int Declare @Bal4 int Declare @Bal5 int Declare @Bal16 int Declare @Bal17 int Declare @Bal18 int Declare @item_id int Declare @aa12 int Declare @aa1 int Declare @mes varchar(100)Declare @Pack_Size_G varchar(200)declare @aa51 intdeclare @sr1 intdeclare @sr2 intdeclare @cnt intDECLARE bin_cursor CURSOR FOR Select rv_no,rv_date,vendor_name,podate,grnitemid as bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,grnitemid as item_id,mes,Pack_Size_G from Get_BIN_RV_A where grnitemid=@Item order by rv_date delete from tem_bin open bin_cursor FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G set @po1=@pono set @rv_no1=@rv_no -- WHILE @@FETCH_STATUS = 0 BEGIN
if @rv_no=@rv_no1 begin set @Balance=@recd_qty +@item_receipt insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@Balance,@item_id,@mes,@Pack_Size_G) end
if @rv_no<>@rv_no1 begin select @bal4=balance from tem_bin where sr in (select max(sr) as sr from tem_bin)--item_id=@item set @bal3=@Bal4+@recd_qty print 'BaliR' + ' ' + convert(varchar(10),@bal3) insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@bal3,@item_id,@mes,@Pack_Size_G) --select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date -- print @rv3--set @sr1=0select @sr1=max(sr) from tem_bin where item_id=@item --sr in (select max(sr) as sr from tem_bin)select distinct @rv3=max(rv_date) from Get_BIN_RV_A where grnitemid=@item --and rv_date<@rv_date -- in (select rv_no as rv_no from tem_bin)
print @rv3 print @sr1 end
DECLARE binA_cursor CURSOR FOR Select iv_no,iv_date,issue_qty,clientname,itemdesc,item_id from GET_BIN_IV where item_id=@Item --order by iv_date open binA_cursor FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id set @iv_no2=@iv_no WHILE @@FETCH_STATUS =0 begin if @iv_no=@iv_no2 --break begin select top 1 @iv_no=iv_no,@issue_qty=issue_qty,@iv_date=iv_date,@client_name=clientname from GET_BIN_IV where item_id=@item --order by iv_date--and grn_no=@rv_no set @aa12=(Select count(rv_no) as rv_no from Get_BIN_RV_A where grnitemid=@Item ) -- select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date -- print @rv3 if @aa12=1 begin select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date print 'aa5' if (@item_receipt =@issue_qty">+@recd_qty)>=@issue_qty -- if 41>=@issue_qty print'check' -- if 41>=@issue_qty begin set @bal=@balance-(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) -- update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date=@rv3-- sr in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no end end if @aa12>1 begin print 'ajay' set @bal=@bal3 -(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) if @rv_date=@rv3 begin update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where sr=@sr1--rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no endelse begin update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no end end end
------iv no not same---------------------------------------------------------------------------------if @iv_no<>@iv_no2 begin print 'ajay1' select @bal3=balance from tem_bin where sr in (select max(sr) as sr from tem_bin) set @Bal5=@bal3-@issue_qty set @cnt=(select count(iv_no) as iv_no from get_bin_iv where item_id=@item and iv_no in (select iv_no from tem_bin where isnull(iv_no,'')!='' ) and item_id=@item) -- set @cnt=(select count(iv_no) as iv_no from get_bin_iv where iv_no=@iv_no2)-- in (select iv_no from tem_bin where item_id=@item and isnull(iv_no,'')!='')) if @bal5<0>=1 begin print 'ajayaaaaaaa' insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) update tem_bin set item_receipt=@item_receipt end if @cnt=0 begin print 'break1' break end
--else-- begin -- print'aa' -- if @cnt>=2 -- begin -- insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) -- update tem_bin set item_receipt=@item_receipt -- end
-- end

end------------------------------------------------------------------------------------------------------
FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id end CLOSE binA_cursor DEALLOCATE binA_cursor
FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G
end --update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where sr =@sr1--@sr1 --in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no CLOSE bin_cursor DEALLOCATE bin_cursor

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Table Row Count Easily in Sql server 2000/@005

Sql Server 2000
SELECT o.NAME, i.rowcnt FROM sysindexes AS i INNER JOIN sysobjects AS o ON i.id = o.id WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 ORDER BY o.NAME

Sql Server 2005
SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME

Get File Statistics Using fn_virtualfilestats

From below given Query -
you will get many valuable information like what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes. Due to IO if there has been any stall (delay) in read or write, you can know that as well.

SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
vfs.NumberReads, vfs.NumberWrites,
(Size*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId

SSIS in sql server 2005

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW; e.x. when you create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created. At a high level, SSIS provides the ability to:
retrieve data from just about any source
perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
load data into just about any source
define a workflow
The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.
In this tutorial we will step through a number of topics that you need to understand in order to successfully build an SSIS package. Our high level outline is as follows:
Creating SSIS packages with SQL Server Management Studio (SSMS)
Business Intelligence Development Studio (BIDS)
Creating a simple SSIS package in BIDS
Deploying SSIS packages
Executing SSIS packages

Different Way of Alias in column

There are different ways to alias a column. Here are some possible ways that work in SQL Server.
01. select
02. 1 as number,
03. 1 number,
04. 1 "number",
05. 1 'number',
06. 1number,
07. 1"number",
08. 1'number',
09. 1 as "number",
10. 1 as 'number',
11. 1.number,
12. 1."number",
13. 1.'number',
14. 1 as [number],
15. 1 [number],
16. 1.[number],
17. 1[number]

Bulk Insert in sql server 2000

Bulk Insert-Copies a data file into a database table or view in a user-specified format.
Eg-
BULK INSERT test
FROM 'd:\testabc.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)

Break vs Return

Suppose you have a set of codes and when a particular condition is true, you want to terminate or switch to another set of code, you can use Break commnad. I see sometimes people use return commnad for this. But there is a significant difference between these two. Consider this example
Usage of BREAK (select will be executed)
declare @j int
set @j=1
while 1=1
begin
print @i
if @j>10 break -It Terminate the Current Block
set @j=@j+1
end
select @j

declare @i int
set @i=1
while 1=1
begin
print @i
if @i>10 return It Terminate the Entire Block
set @i=@i+1
end
.select @i
Break Command-it just terminate the current block (created by if clause, while loop etc), but Return Command- will terminate the entire block

Retrieving all the table Name and view name of specific Database

use ABC
select * from information_schema.tables-Retrieve Tables

select * from information_schema.view --retrieve Views

OPENROWSET

OPENROWSET-Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Eg-SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=F:\', testing#txt)

List of datatypes supported in SQL Server

List of datatypes supported in SQL Server-
exec sp_datatype_info in sql server 2000
exec sp_datatype_info_90 in sql server 2005
exec sp_datatype_info_100 in sql server2008