variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

variables

Author

Topic

wondering_sa
Starting Member

8 Posts

Posted - 2011-12-22 : 13:39:10

If I am not posting to the right place, please accept my sincere apologies, and move to the appropriate location. Thank you!
=============================

SQL7 and SQL2005 - can use either one of them.

I have a simple Query1 that extracts information from Table1. Would like to run this query about 40 times with different parameters passed from Table2 (RecipientNumber field), and then email results in Excel format to 40 different addresses passed from the same Table2 (RecipientEmailAddress field). Table2 consists of two columns: RecipientNumber - used for the Query1, and RecipientEmailAddress - used for the email routine.

I could extract with DTS (hard coding RecipientNumber one by one), and then email with xp_sendmail (hard coding RecipientEmailAddress one by one), but Table2 is frequently updated.

Is there something that would allow reuse of the same DTS, while passing RecipientNumber from Table2 dynamically (?looping?)? And then, maybe "looping" through all RecipientEmailAddress entries (matching Excel file name by RecipientNumber) with xp_sendmail to send out attachments? .. Or any other suggestions are greatly appreciated!

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 13:51:06

you want to JOIN the 2 tables...not run it 40 times

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wondering_sa
Starting Member

8 Posts

Posted - 2011-12-22 : 14:38:10

I am trying to send custom output (from Table1) to different users (from Table2) in Excel format. Why you think I need a JOIN?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 14:57:56

maybe if you post the query and the DDL of the 2 tables

I didn't see that you wanted a report for each person

It's still a join with perhaps a cursor

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 15:14:36

[code]
DECLARE myCursor99 CURSOR
FOR
SELECT * -- Make sure to replace with the actual Columns you want
FROM TABLE1
INNER JOIN TABLE2
ON t1.RecipientNumber = t2.RecipientNumber;

OPEN myCursor99;

FETCH NEXT FROM myCursor99
INTO @local_Variable List;

WHILE @@FETCH_STATUS = 0
BEGIN
Do your Execel magic and mailings
FETCH NEXT FROM myCursor99
INTO @local_Variable List;
END

CLOSE myCursor99;
DEALLOCATE myCursor99;
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wondering_sa
Starting Member

8 Posts

Posted - 2011-12-22 : 16:55:23

Thank you for your reply!

This is the simple query that extracts grades for every student:
-----------------------------------------
SELECT Student, Class, Grade, Comments

From Grades

WHERE
Student = "9645"
-----------------------------------------

The second table contains Student numbers (the same like in Grades table), and email address for every student.

Would like to run the query above for every student, and then send every student her/his grades in Excel format.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:41:50

[code]
SELECT g.Student, Class, Grade, Comments,sn.emailaddress,..
From Grades g
INNER JOIN StudentNumbers sn
ON sn.Student = g.Student
[/code]

and you can use bcp or OPENROWSET or export/import wizard to sent result in a excel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources