SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Loops & Variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/23/2008 :  10:42:00  Show Profile  Reply with Quote
When populating a variable, does it retain its value for the next loop? It seems when outputting a variable's contents, these are compounded and keep adding a single row over and over. I can't see any logic which is making this happen. This should be fetching detail for each row, one by one and outputting seperately. It looks like each file is 100s of copies of itself.

Seen any of this before? I was thinking of emptying the variable at the end of each iteration. How do you do it?

SELECT @thisvar = ''

First instruction at the start of the iteration is:

SELECT @PDF = '<HTML> ........... @var1..., @var2, etc'

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/23/2008 :  11:34:46  Show Profile  Reply with Quote
I think we're going to need more details. Perhaps an example? Better still, take a step back and explain the bigger picture - you may well not need a loop at all.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/23/2008 :  11:44:10  Show Profile  Reply with Quote
If you really need to do looping, which I suspect you don't, it might be better to do it in the application layer.

What exactly are you trying to accomplish? There is likely a much better way than trying to do looping in SQL.

Edited by - cat_jesus on 04/23/2008 11:44:34
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/23/2008 :  12:26:44  Show Profile  Reply with Quote
It has been complex. Now it's simple

Producing HTML riddled with variables out in emails. Looping so each goes to a different recipient with different details in each row. Perfect. Each batch can have 0, 1 or any number of rows in the temp table. All works a treat. Using xp_smtp_sendmail

Now I just have to copy the stored procedure and adjust it slightly for a new task. Same HTML with variables in it. But not sent as an email. Exported to individual .txt files to be converted from HTML code to a individual PDFs. Using sp_appendToFile

The loop is valid and what matters is that all that changes is sp_appendToFile from xp_smtp_sendmail.

But in the mailing there was a spool of emails. In this one, a spool of .txt. The good .txts are 6k. Some bad ones are 5Mb, repeating themselves thousands over and over...
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/23/2008 :  14:00:46  Show Profile  Reply with Quote
I wasn't saying that the loop was invalid but that it was likely the hard and inefficient way to do it. Of course without details we are only left to speculate using our past experience as a guide.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 04/23/2008 :  14:09:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have a look at this topic and find out his frustration
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101379



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000