| Author |
Topic |
|
Valentino99
Starting Member
5 Posts |
 Posted - 2003-05-21 : 13:09:29
|
| Hi all...How can i force the stored procedure to do the prints immediately?My Problem now is, that my sp do the prints not immediately!!Does it have a function like DoEvents (VB)? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-21 : 13:19:07
|
| Anyone know what this even means?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-21 : 13:31:27
|
| I don't have a clue.Valentino99, please explain further because we aren't understanding. Why are you issuing PRINT statements in a stored procedure in the first place? If the stored procedure is being used by an application and it's production, then I would recommend removing the PRINT statements.Tara |
|
|
|
Valentino99
Starting Member
5 Posts |
Posted - 2003-05-21 : 13:42:01
|
| I have a procedure that implement several procedures and all takes over 3 hours.In order to see, what implements the procedure straight, I inserted prints. These are however only lastingly indicated! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-21 : 13:49:06
|
| 3 hours! Have you tried optimizing it yet? PRINT statements should be the least of your worries.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-21 : 14:00:20
|
Your trying to troubleshoot reactivley instead of proactivley.Anyway, where you have your prints, replace them with this:SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Starting Quarterly Load Process'SET @cmd = 'echo ' + '"|' + @var + '|"' + ' > d:\Data\Tax\log_out.txt'SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT' __________That's for the first one. It will replace the existing text file. subsequent ones will require you to change the ">" to ">>" to do an append.I do this to track the activities of a user load process in case they mess something up I can check. I then bcp the file on exit of the sproc...but you don't have to.But 3 hours? Also note the code I gave you records the date and time.It is also "|" delimited to make my bcp even easier.Brett8-) |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-22 : 03:42:20
|
| What he wants to do is to flush the output directly, so he can see what happens "real time" instead of after the proc completes. There is a way to do this, but I can't remember right now... Someone else who can maybe? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-05-22 : 03:59:43
|
| PutGoAfter the prints?But it will not make the core stuff any faster. He's still sat around for 3 hours.-------Moo. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-22 : 04:10:21
|
| You can't put GO in a proc... That will make things a bit crazy :D |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-05-22 : 04:17:30
|
| Oh it's a procedure. I didn't read it properly.:|-------Moo. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-22 : 07:16:54
|
| OK I remembered now how to do it... You have to use RAISERROR with the NOWAIT option, which will be outputted directly along with all statements before it.Just put this every time you want to flush the output:RAISERROR ('Flushing output...', 0, 1) WITH NOWAITEdited by - andraax on 05/22/2003 07:17:45 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-23 : 15:42:53
|
| Why not write to a log file? Or better yet, just add rows to a "Log Table" and check out the table now and then to see how the procedure is going.- JeffEdited by - jsmith8858 on 05/23/2003 15:43:34 |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-23 : 17:43:38
|
| I like jeff's idea the best and just to add you could also trace it using the Query Analyzer debugger. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-05-25 : 01:22:18
|
| RAISERROR WITH NOWAIT is very handy for having a long-running stored procedure do status updates; if something's going to take 3 hours, it can be nice to know that it's still working and not deadlocked / rolling back / doing the wrong thing. Sure, you can use all sorts of monitoring or logging approaches, but RAISERROR WITH NOWAIT is one line and very appropriate for that kind of thing.Cheers-b |
|
|
|
|
Forced Prints