Snapshot Isolation Problem, How To Do It?

Hello all
I´m having some trouble with a query I´m using to BCP all rows in a certain table to a flat file.
The query looks like this:




Code Snippet
USE AVK
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
SELECT *
FROM tempProducts
GO
IF @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
DECLARE @date char(8)
DECLARE @time char(8)
DECLARE @sql VARCHAR(150)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')

DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql

WAITFOR DELAY '0:00:10';
SELECT *
FROM tempProducts

DELETE
FROM tempProducts

COMMIT TRANSACTION
END






What I´m trying to do here is copy all rows in tempProducts to my flat file, and the delete ONLY the rows that existed when I entered the transaction. I tried inserting some rows into tempProducts, start this query, insert som more rows (which I can do because of the WAITFOR DELAY), and this is the result.

--Output from first select
S3 Plastpall trippelkrage 40 6

--BCP Output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (3000.00 rows per sec.)
NULL

--Select before delete statement
S3 Plastpall trippelkrage 40 6
S3 Plastpall trippelkrage 40 7

So even if only 1 row existed when I entered the transaction, any rows inserted during the transaction are also deleted.
What am I doing wrong here? The EXPORT_ORDERS is a view, looking like this:




Code SnippetSELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
UNION ALL
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1