store procedure call store procedure
<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
store procedure for return value string format
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
sql datetime format
select
Company, PIN, Type, convert(varchar,convert(datetime, DateFrom, 103),112) as df
from sheet1
where Type in (‘01′,’02′,’03′,’04′,’05′)
MSSQL 2000 Log file full
- backup log DATABSENAME with no_log
- shink thik true on top checkbox and set value = 20%
Paging with Store procedure
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(8)
)
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
DataSet Paging with Store procedure
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
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
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:
| 1. | Start SQL Query Analyzer, and then connect to the instance of SQL Server 7.0 as a system administrator. |
| 2. | On the File menu, click Open. |
| 3. | In the Open Query File dialog box, locate and then click the Instcat.sql SQL script file from the SQL Server 2000 SP3 setup files.Note By default, the Instcat.sql script file exists in the Full path of the setup files for SQL Server 2000 SP3\install folder. |
| 4. | Click Open. |
| 5. | In SQL Query Analyzer, click Execute on the Query menu. |
ref: http://support.microsoft.com/default.aspx?scid=kb;en-us;834849
