Private: MY Note

http://www.sql-server-performance.com/

<script type=”text/javascript”><!–
google_ad_client = “pub-2910472071369479″;
//728×90, created 11/20/07
google_ad_slot = “7313632958″;
google_ad_width = 728;
google_ad_height = 90;
//–></script>
<script type=”text/javascript”
src=”http://pagead2.googlesyndication.com/pagead/show_ads.js“>
</script>

src=”http://pagead2.googlesyndication.com/pagead/show_ads.js” mce_src=”http://pagead2.googlesyndication.com/pagead/show_ads.js”>

set nocount on
declare @sql nvarchar(2000)
declare @comp_code nvarchar(255), @pin nvarchar(255), @LoanType nvarchar(255), @datefrom nvarchar(255)
declare @temp_id int, @n int
declare @docNo nvarchar(20)

create table #temp
(
 _id int IDENTITY,
 Company nvarchar(255),
 PIN nvarchar(255),
 LoanType nvarchar(255),
 df nvarchar(255)
)

insert into #temp
select Company, PIN, LoanType, convert(varchar,convert(datetime, DateFrom, 103),112) as df
from sheet1 where LoanType in (’01′,’02′,’03′,’04′,’05′) and convert(datetime, DateTo, 103) >= getdate()
order by pin

set @n = 0

– loop –
declare cur cursor for
select _id from #temp
open cur
fetch next from cur into @temp_id   
 while @@fetch_status=0
 begin  
  set @n = @n + 1
  
  exec get_running_number @n, @docNo out
  
  select @comp_code=Company, @pin=PIN, @LoanType=LoanType, @datefrom=df from #temp where _id=@temp_id
  
  set @sql = ‘insert into welfare.WF2_TXN_CREMATION (Comp_Code,Pin,Doc_Date,Relat_Pers_Type, Doc_No) values ‘
  set @sql = @sql + ‘(”’ + @comp_code + ”’,”’+ @pin + ”’,”’ + @datefrom + ”’,”’
+ @LoanType + ”’,”’ + @docNo + ”’)’
  print (@sql)
  
  fetch next from cur into @temp_id  
 end
 
 print ‘– total record: ‘ + cast(@n as varchar)

close cur
deallocate cur

– drop table –
drop table #temp    

set nocount off

CREATE PROCEDURE get_running_number
@id int,
@docNO varchar(20)  out
AS
begin
 declare @_id nvarchar(20)
 declare @i int
 set @_id = cast(@id as nvarchar)
 set @i = len(@_id)
 while (@i<5)
 begin
  set @_id = ‘0′ + @_id
  set @i = @i +1
 end
 set @_id = ‘WF0711′ + @_id
      select @docNO =  @_id
end
GO

select
Company, PIN, Type, convert(varchar,convert(datetime, DateFrom, 103),112) as df
from sheet1
where Type in (’01′,’02′,’03′,’04′,’05′)

- backup log DATABSENAME with no_log

- shink  thik true on top checkbox and set value = 20%

set nocount on

declare @FirstRec int, @LastRec int
declare @Page int, @RecsPerPage int
set @Page = 4
set @RecsPerPage = 3

CREATE TABLE #TempTable1
(
 ID int IDENTITY,
 Comp_Code varchar(3),
 Org_id varchar(
)

INSERT INTO #TempTable1 (Comp_Code, Org_id)
SELECT Comp_Code, Org_id FROM WF_MST_VENDOR

SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

SELECT *,MoreRecords = ( SELECT COUNT(*)  FROM #TempTable1 TI  WHERE TI.ID >= @LastRec)
FROM #TempTable1
WHERE ID > @FirstRec AND ID < @LastRec

drop TABLE #TempTable1

set nocount off

CREATE PROCEDURE sp_PagedItems
 (
  @Page int,
  @RecsPerPage int
 )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
 ID int IDENTITY,
 Name varchar(50),
 Price currency
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
 (
  SELECT COUNT(*)
  FROM #TempItems TI
  WHERE TI.ID >= @LastRec
 )
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

ref: http://www.aspfaqs.com/webtech/062899-1.shtml

ref: http://technet.microsoft.com/en-us/library/ms174998.aspx

RESOLUTION

loadTOCNode(1, ‘resolution’);

To resolve this problem, you must upgrade the catalog stored procedures on each instance of SQL Server 7.0 that is configured as a linked server and that is participating in the distributed transaction. You can upgrade the catalog stored procedures on your instance of SQL Server 7.0 by running the Instcat.sql SQL script file that is included with the SQL Server 2000 SP3 setup files.

Note To upgrade the catalog stored procedures, you must have access to the setup files for SQL Server 2000 SP3.

To upgrade the catalog stored procedures, follow these steps for each instance of SQL Server 7.0:

ref: http://support.microsoft.com/default.aspx?scid=kb;en-us;834849

In our Table Store_Information example,

Table Store_Information

if we want to multiply the sales amount from ‘Los Angeles’ by 2 and the sales amount from ‘San Diego’ by 1.5, we key in,

SELECT store_name, CASE store_name
  WHEN ‘Los Angeles’ THEN Sales * 2
  WHEN ‘San Diego’ THEN Sales * 1.5
  ELSE Sales
  END
“New Sales”,
Date
FROM Store_Information

“New Sales” is the name given to the column with the CASE statement.

Result:

ref: http://www.1keydata.com/sql/sql-case.html

Default system tables and stored procedures are visible along with user-defined ones in MS SQL Enterprise Manager. This is fairly inconvenient when you’re trying to walk thorugh your own tables and stored procedures. To hide these system objects, try the following:

1. Right click on the “(Local)(WindowsNT)” in the Enterprise Manager.
2. Select “Edit Sql Server Registration Properties,” which opens up a property panel.
3. Uncheck the option “Show system databases and system obejcts.”

ref: http://www.devx.com/tips/vtBrowser/20528 (Tip bank)