| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-08-11 : 11:26:22
|
| Hi all,I have a cursor which looks something like thisCursor start ...Dynamic SQL 1....Dynamic SQL 2....Dynamic SQL 3... etcThe first couple of dynamic SQL statements can run without problems but the rest must all be successful otherwise I will have major problems.My question is this : If I decide to engulf the remaining dynamic SQL statements with a BEGIN TRAN... END TRAN, should that be part of the dynamic SQL statement or part of the ordinary code?------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-08-11 : 12:30:16
|
| You'll have lots of problems with this.If one of the dynamic statements encounters an error, the @@error generated will be local to the statement scope and you won't be able to get to it outside it in the procedure. You'll then have no way of knowing when you need to issue a ROLLBACK.I would try to get rid of the dynamic SQL if that's possible.Jonathan{0} |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-11 : 12:50:22
|
| I don't think I will be able to avoid using Dynamic SQL. The exercise requires the script to be run on all the databases on all our servers, i.e. live and installation servers.But basically you’re saying that all my inserts should be done in one long string so that a ROLLBACK is possible?Would that solve the problem?Thanks.------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-11 : 22:06:15
|
| Just so I understand, does the transaction have to cross databases? Meaning you need to BEGIN TRAN, run the script on one server, then go to the next database, then the next, then COMMIT or ROLLBACK? If that's the case, that's a terrible idea, and don't do it.What does this script do anyway? |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-12 : 04:25:47
|
| Hi,Well, a terrible start for me this morning. Some $%@! switched off my machine sometime last night and I’ve lost about 70% of the work I did yesterday.Anyway....The current software that the company is using has a SQL Server backend + VB front-end. The Pension Admin team have decided to make an addition which requires inserting rows into 4 tables then changing member details to reflect the new changes.Currently there are a number of servers where the software databases reside. My approach is to automate the script so that it picks out the software databases from the server the script is run on and make the changes.So if our first Server A has 20 databases of which 10 are software databases (the rest are rubbish as far as I am concerned), the script loops through the databases picking out the relevant ones and making the necessary changes.My biggest fear is that the inserts can not fail. If say the first database picked happens to fail on the 3rd insert, no further inserts and updates should be done.This script will eventually be used to update all databases on all servers (live and installation).------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-12 : 06:28:49
|
quote:
Well, a terrible start for me this morning. Some $%@! switched off my machine sometime last night and I’ve lost about 70% of the work I did yesterday.
OUCH. That sucks.I have 2 thoughts about your problem though, they might give you some inspiration (hopefully).1. Build a deployment application up a tier. In vb.net or something. You could get it to open a script file and run it against the DB server and use ADO.NET to manage the transaction. You could make it flexable enough to handle all future upgrades (making you a superstar in the process  )2. Use dynamic SQL to create a stored proc that contains the transaction stuff. Then call the stored proc.Damian |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-12 : 08:22:07
|
quote:
1. Build a deployment application up a tier. In vb.net or something. You could get it to open a script file and run it against the DB server and use ADO.NET to manage the transaction. You could make it flexable enough to handle all future upgrades (making you a superstar in the process )
Interesting idea and one that I didn't think of at all. Definitely worth a try when I have more time on my hand.quote:
2. Use dynamic SQL to create a stored proc that contains the transaction stuff. Then call the stored proc.
This sounds like the ideal choice to solve the problem as I am pressed for time and have 4 projects in the pipeline at the moment. Mind you, this is going to be one long dynamic sql statment.Also, I owe you an apology for this :http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28145I really don't know why I took personal offence at the time. I thought you were ripping me, but now it seems like it was nothing to get upset about. I was worried others might catch your drift and give me a hard time.  Anyway, I've also figured out why my machine was switched off this morning. I've remember that last night, around 8 or 9pm we had a power cut in Epsom which lasted about 2 seconds. Long enough to restart my machine. Must be the current heat wave we're enjoying over here. Final thoughts...Most Micro Soft products have some form of document recovery, i.e. Word and Excel. In fact this morning I managed to get back the word document I had left open yesterday. With other Apps like SQL Server, no such utility exists for Query Analyzer. Would make an excellent addition in my opinion.Or save your work frequently... he says  ------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-12 : 10:01:39
|
| I think you need to start using the save button a little more often. I have a temp directory where I keep saving work-in-progress scripts, code snippets, etc. And I make sure I save any current work every few minutes, can't even imagine leaving office without having saved them! Dont count on the electricity cut to make you lose your work, Dr. Watson could do that all by himself Owais |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-12 : 18:56:05
|
| I second Owais. I just had a power outage at work yesterday (3 in fact, all within 2 minutes, bad storm) and I lost a couple of SQL Script modifications. Nothing major fortunately, but 20-30 minutes of work needed to be done over.The save button IS YOUR FRIEND!!!! So is SOURCE CONTROL!!!! |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-13 : 07:34:06
|
quote:
Originally posted by mohdowaisI have a temp directory where I keep saving work-in-progress scripts, code snippets, etc.
Hala Owais... keefak ?  Yea.. I should use the save button more but Monday night was a one off really. I made an early start and didn't leave until after 6.30 so I was very tired. Did I mention my 4 hour train journey before coming to work?!  Anyway, I think I've learnt my lesson... the hard way again! ------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
|
Cursors and Dynamic SQL