update table and export data to file in one transaction
-
2012年7月2日 19:53
Dear All,
In my .Net web application I need to add data export feature to existing logic i.e. to export the record from table to text file and save it in the local directory. The database I'm using is SQL Server 2008 standard.
There are additional requirements:
- the data must be exported as XML with <?xml version="1.0" encoding="UTF-8"?> tag
- the whole process of record creation (currently done is SP called from .net code when user click on the save button) and data export need to be done as one transaction i.e. if file export fails record creation need to be rolled backFrom what I understand reading the MS docs only BCP or SSIS can be used to export the data from SQL Server but I don't know how to assure the above requirements are met with these methods.
Any ideas how to achieve that?Thanks in advance
Dzordz
全部回复
-
2012年7月2日 20:36
There are certainly more possibilities. For instance, you can retrieve the data to the client and create the XML file there.
There are a couple of things to consider here. You say "local directory". Which local directory? The process that creates the file must have access to that directory.
You can create XML from SQL Server with the FOR XML clause, but for some reason you cannot get it to create that initial line.
If I understand this correctly, your process should both save data into a table and create this file. Where is this data coming from in the first place?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
2012年7月2日 22:48
Yes, I know that I can return my XML formatted data from Stored procedure to asp.net function as out parameter and save it into the file using System.IO functions. The thing is that I need to be absolutely certain that the file is successfully written to the disk before I can update the table and commit this to the database and I cannot export the file before the changes are comitted otherwise it may happen that the file is exported but the update of the table fails. This is the reason why I thought about transactions but I don't know how to do it :-(
By local directory I meant server's local directory to which I have direct access and can set proper access rights so this is not an issue.
Yes, I know that I can't create the first line with for XML clause that's why I need to save the XML as string and concatanate it with the encoding tag like this:
declare @sXML as NVARCHAR(MAX)
SELECT @sXML = (select * FROM tbl1 FOR XML AUTO, ROOT)
SET @sXML = '<?xml version="1.0" encoding="UTF-8"?>' + @sXML
Is that correct? Are there any better ways to do it?The process looks like this:
1. On my aspx page the user enters some data and click on the save button
2. asp.net functon calls SP and passes the data
3. In SP there are some validation checks and finally table is updated with this data and some other updates are done to other tables
4. The next step is where this export should be called and if it fails all above sql statements should be rolled backThanx in advance!
Dzordz
-
2012年7月3日 9:08
By local directory I meant server's local directory to which I have direct access and can set proper access rights so this is not an issue.
Which server? SQL Server or the web server's?
The transaction thing will be difficult anyway, but let's get the other things right first.
If I understand this correctly, the users enters some data, but the data that is saved to the file does also include data coming from the database?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
2012年7月3日 12:43
I have access to both servers so this I need to figure out. If export will be done from .net code then of course it will be web server but if it need to be done e.g. by BCP then it will be SQL Server.
Yes, first the the data from aspx form is saved in the db and then it should be exported to the file so the file will include only the data from the table but formatted as XML. The SQL statements will look like:
1. UPDATE tbl1 Set tbl1.col1 = @SPParam1, tbl1.col2 = @SPParam2 (...)
2. declare @sXML as NVARCHAR(MAX)
SELECT @sXML = (select tbl1.*, tbl2 FROM tbl1 INNER JOIN tbl2 (...) FOR XML AUTO, ROOT)
SET @sXML = '<?xml version="1.0" encoding="UTF-8"?>' + @sXML3. "export" @sXML
Dzordz
-
2012年7月3日 21:50
OK, now I know the presumptions. I just wanted to make sure that you understood where the file gets created if you use SQL Server.
You would need a distributed transaction between SQL Server and file system, and I don't know whether this is possible. At least not in SQL 2008. In SQL 2012, the FileTable could be an alternative. (Well, in SQL 2008 you have filestream, but those files are stored off-limits, you can't really use them.) But it's possible that .Net offers transaction handling for files.
You can do:
BEGIN TRANSACTION Store data. Create file COMMIT TRANSACTION
But if the computer crashes before the COMMIT is executed, the file will exist without the data.
Better might be:
1) Store data, set filecreated = 0
2) Create the file.
3) UPDATE tbl SET filecreated = 1Note that the latter can be done by a separate processes, that first checks if the file exists, and in such case only updates the bit. If the file is missing it creates the file.
If you create the file from SQL Server, I would recommend that you use write a dedicated CLR stored procedure for the task, so that you don't have to enable xp_cmdshell.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
2012年7月4日 11:30
In this approach:
1) Store data, set filecreated = 0
2) Create the file.
3) UPDATE tbl SET filecreated = 1Note that the latter can be done by a separate processes, that first checks if the file exists, and in such case only updates the bit. If the file is missing it creates the file.
I don't think I can divide it into separate processes because at step 1 the row in tbl table must be updated and status "success" must be returned to the user (at this moment the application doesn't allow further updates to this row). This means that the file also must be created successfully otherwise the update must be rolled back and status "failed" must be returned to the user. In this case the user is still able to change the data and have the possibility to re-try the update. How can I rollback tbl update from step 1 if for some reason the file is not created?
Dzordz
-
2012年7月4日 12:03
You probably don't want to hear this, but nonetheless: What about the Output clause to a permanent, but transiently populated table, a GUID or some other failsafe identifier for each group of related rows, then the file export process as a separate task, probably via SSIS and runs either every few minutes or on a trigger. You can still have a failsafe of sorts: The data in your transient table doesn't get deleted unless the XML file is successfully created, but SSIS does that part for you, using SSIS's built-in capabilities for that sort of thing.
Your desired approach, somehow combining file creation with database transactions is doubling your chances (loosely speaking) of something going wrong, including things that technically have nothing to do with the database transaction (a permissions mixup, disk full, back key usage leading to duplicate files, all sorts of things), combined with the likelihood of making your transactions take longer.
-
2012年7月4日 22:12
How can I rollback tbl update from step 1 if for some reason the file is not created?
You would delete the row.
I realise that these limitations will affect how the application appear to the user, but I can't see how there can be two-phase commit between the file system and the database.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
2012年7月6日 8:07
Hi,
What about this idea:
BEGIN TRANSACTION
--Store data
UPDATE tbl1 WHERE tbl1ID = @ID
...
UPDATE tbl2-- save stored data as XML in additional table used for trace if the file is created
UPDATE tblTrace
SET FileContentXML = (SELECT * FROM tb11 FOR XML),
FileExported = 0,
tbl1ID = @ID --identity id from tbl1 updated row--Create file
-- call SSIS package or CLR stored procedure which will do the export and set the flag to 1-- check if file is created by testing FileExported flag
IF((SELECT COUNT(*) FROM tblTrace WHERE tbl1ID = @ID and FileExported = 1) = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1 -- error
END
COMMIT TRANSACTION
If I use SSIS then how can I run the package from my stored procedure? MS SQL will wait until the package execution is finished to continue executing following statements in my SP or will only start the package execution and jump to the next statement right away?
If I use CLR SP then are there any restrictions for the lenght of the string passed to it? Can you send me a link to some example code of such SP?
Thanks in advance
Dzordz
-
2012年7月6日 9:26
Not too bad. But keep in mind that if someone pulls the power plug after the file has been created, but before the transaction is committed, the file will be there, but not the data in the table.
Also be careful with starting an SSIS package from within a transaction. 1) The data for to export will be blocked. 2) The transaction will be too long.
So at the end of the day, I think that you are better of with accepting that data is entered preliminary and then has to be wiped out if the file fails.
Also:
IF((SELECT COUNT(*) FROM tblTrace WHERE tbl1ID = @ID and FileExported = 1) = 0)
It's usually better to write this as:
IF NOT EXISTS (SELECT *FROM tblTrace WHERE tbl1ID = @ID and FileExported = 1)
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
2012年7月11日 21:25
Thanks for your comments.
I have another idea that I would like to ask you about. I found a post by Jonathan Kehayias about CRL stored procdeure exporting the data to file here http://www.sqlservercentral.com/articles/SQLCLR/65657/ and I wonder if I can addapt it to use it in my solution.
I would run this SP (a bit changed to create XML file and return 0 if file is exorted w/o errors or 1 if not) in my transaction to check if the file was successfully exported and update Export flag if yes. The code would look like this:
BEGIN TRAN
some sql selects & inserts preparing the data to export and placing in @XML variable
DECLARE @ID INT
INSERT TrackingTable (XML, Export) VALUES (@XML,0)
SELECT @ID = SCOPE_IDENTITY()
DECLARE @CMD VARCHAR(50), @Result BIT
SET @CMD = 'SELECT XML FROM TrackingTable WHERE ID = ' + CAST(@ID AS VARCHAR)-- right now all the data is alredy in the tables and should not be blocked for the CLR sp
EXECUTE @Result = [dbo].[WriteResultsToCsvFile] @CMD , 'SomeFileName.xml'
IF(@Result = 1)
BEGIN
ROLLBACK TRAN
RETURN 1 -- error code
END
-- file is exported so update the status
UPDTE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)
COMMIT TRAN
Are there any disadvantages of this approach?
Thanks in advanceDzordz
-
2012年7月11日 21:57版主
The SQL Server will not be able to store the file on the local PC. That will need to be done from our application. You will need to do it as 2 operations: Insert into the table, save the key for the new record. Then output the XML file. On failure delete the key.
- 已标记为答案 Iric WenModerator 2012年7月12日 3:20
-
2012年7月11日 22:27
Note here that if the server crashes, or the admin kills the process after the file has been created, but before the transaction commits, the file will be there, but the data will not be in the table.
But as I've said before, this situation is almost impossible to protect yourself against, since, to my knowing, you cannot do two-phase commit between SQL Server and the file system.
As the solution looks now, it's a bit over-worked, you could just pass @XML to the CLR procedure.
However, the table solution make sense if you commit before writing the file. If the file export fails, you will see which exports that failed in TrackingTable. (Or possibly failed; again, you may be aborted between writing the file and the UPDATE.)
Note that you should have the call to the CLR procedure in TRY CATCH, so that you can trap it if raises an error; I would not rely on the return code alone.
On a final note:
UPDATE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)
As far as this statement is needed, it should of course read:
UPDATE TrackingTable SET EXPORT = 1 WHERE ID = @ID
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- 已标记为答案 Iric WenModerator 2012年7月12日 3:20
-
2012年7月12日 9:13
Thank you for your comments.
I thought that transaction assures that if server crashes or SQL Server process i terminated then the transaction will be restored and finalized or rolled back when server is restarted, isnt't it?
As for passing @XML to the CLR procedure I think it would be possible but there is limitation on passed string to 8000 characters which may be easily exceeded so it's safer to retrieve the XML in the sp.
Is this a bad practice: 'UPDATE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR) even if @ID is fetched from identity column in the same SP? I will need to pass this command as string to the CLR sp so I need to do it this way, right?
Dzordz
-
2012年7月12日 22:09
I thought that transaction assures that if server crashes or SQL Server process i terminated then the transaction will be restored and finalized or rolled back when server is restarted, isnt't it?
Correct. But the file-creatíon is not part of the transaction, so the
file will not be rolled back. Only the data into the database.As for passing @XML to the CLR procedure I think it would be possible but there is limitation on passed string to 8000 characters which may be easily exceeded so it's safer to retrieve the XML in the sp.
There is no such limitation.
Is this a bad practice: 'UPDATE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR) even if @ID is fetched from identity column in the same SP? I will need to pass this command as string to the CLR sp so I need to do it this way, right?
In your post, this was just a local statement, and just as well read:
UPDTE TrackingTable SET EXPORT = 1 WHERE ID = @ID
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
.png)
.png){kind=spacer}
