loop within a stored a procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

loop within a stored a procedure

Author

Topic

JNatUK
Starting Member

6 Posts

Posted - 2003-09-24 : 07:45:41

Hi,

Can i have a FOR loop within a stored proc.
I want 2 send out an automatic email for each user using Sql Server Agent.
so FOR each user with a criteria....send mail?

Thanks
J.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-24 : 09:14:55

T-SQL supports several Control-Of-Flow constructs. FOR is not one of them. Consult Books Online.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 09:38:22

It's a "WHILE" construct

something like:


DECLARE USER_MSG CURSOR READ_ONLY FOR 
	select distinct user_id from user_work_profile(NoLock) 
 		Where Work_Type = 'A'


OPEN USER_MSG

FETCH NEXT FROM USER_MSG
	INTO	@User_Id

WHILE @@FETCH_STATUS = 0

	BEGIN
		Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Quarter Load Beginning", no_output '
		Select @Command_String
		Exec (@Command_String)

		FETCH NEXT FROM USER_MSG
			INTO	@User_Id
	END

CLOSE USER_MSG
DEALLOCATE USER_MSG

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JNatUK
Starting Member

6 Posts

Posted - 2003-09-24 : 11:17:59

Hi,

IS the stored procedure to sendmail this :
master..xp_cmdshell
I tried that & did not receive anymail yet.
IS there any other stored procedure 2 send mail.

Thanks
J

quote:

Originally posted by X002548

It's a "WHILE" construct

something like:


DECLARE USER_MSG CURSOR READ_ONLY FOR 
	select distinct user_id from user_work_profile(NoLock) 
 		Where Work_Type = 'A'


OPEN USER_MSG

FETCH NEXT FROM USER_MSG
	INTO	@User_Id

WHILE @@FETCH_STATUS = 0

	BEGIN
		Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Quarter Load Beginning", no_output '
		Select @Command_String
		Exec (@Command_String)

		FETCH NEXT FROM USER_MSG
			INTO	@User_Id
	END

CLOSE USER_MSG
DEALLOCATE USER_MSG

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 11:41:44

Well tahts a differnt problem than your loop question...did you get the loop thing?

The sample I posted does a NET SEND...do you know what that is?

Sorry, can't help with the email piece...could never figure out lotus notes config...would be nice someday though...

What type of Email do you want to send? Did you configure MAPI?

Tara? Help...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JNatUK
Starting Member

6 Posts

Posted - 2003-09-24 : 12:49:39

I jst want 2 send a normal mail..to apear in the users inbox...we r using MS Exchange.....(Outlook)
I assumed SQL.......Master.already had a stored procedure 4 this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 13:07:41

You need to setup SQL Mail in order to be able to e-mail with SQL Server and Exchange.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 13:08:33

Here's the article from MS:

[url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q263/5/56.asp&NoWebContent=1[/url]

I don't know why SQLTeam can't show URLs correctly sometimes.

Just google this and you'll see the MS article: how to configure sql mail.

Tara

JNatUK
Starting Member

6 Posts

Posted - 2003-09-26 : 10:57:59

I configured my SQL MAIL with SQL Server agent as per instructions.How do I test whether xp_sendmail works.....I try 2 open that stored procedure but get the box with the fol. sqlmap70.dll.
How do I execute this stored proc.and what do I pass 2 it.

Thanks

JNatUK
Starting Member

6 Posts

Posted - 2003-09-26 : 11:11:01

EXEC xp_sendmail 'smithl', 'The master database is full.'
I try 2 test execute the sp but get the fol error

xp_sendmail: Procedure expects parameter @user, which was not supplied.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-09-26 : 11:36:03

quote:
Originally posted by tduggan
I don't know why SQLTeam can't show URLs correctly sometimes.

Tara, for long ugly urls, I like to use the
[ url = "myurl" ] This text appears in the message [ / url ]
syntax. That seems to work pretty consistently.

------------------------------------------------------
The more you know, the more you know you don't know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 12:26:12

JNatUK, look up xp_sendmail in SQL Server Books Online for what you need to pass it. It is already documented there, but here is an example:

EXEC master.dbo.xp_sendmail @recipients = 'jnatuk@somecompany.com', @message = 'Test e-mail'

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 12:27:04

quote:
Originally posted by AjarnMark

quote:
Originally posted by tduggan
I don't know why SQLTeam can't show URLs correctly sometimes.
Tara, for long ugly urls, I like to use the
[ url = "myurl" ] This text appears in the message [ / url ]
syntax. That seems to work pretty consistently.

I thought the problem was with the software not being able to display semi colons.

Tara

JNatUK
Starting Member

6 Posts

Posted - 2003-09-26 : 12:32:05

Now I get the fol. error:

I think it has to do with my configurations

Server: Msg 18035, Level 16, State 1, Line 0
xp_sendmail: Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account ro run under a domain account in order to complete this operation

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 12:36:09

Did you read the article yet? You can not run the MSSQLSERVER service with the local system account. This is documented in the article that I provided.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources