Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
goJohn,
You cannot substitute a table with a variable. Use dynamic SQL for that. The
reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.pos
ting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||thanks for the reply John.
What I'm trying to do is this:
Table 1: Contains a list of people and the current state of their
accounts which have all breached a set level.
I want to generate an individual email that notifies the person that
they have breached the limit on a certain account and show them the
details.
For example
Name Expenses Agreed Expeneses Email
----
Woody 150 100 Woody@.blah.com
Buzz 200 190 Buzz@.blah.com
Rex 60 50 rex@.blah.com
Send Email
To: Woody@.blah.com
From: DBA
Subject: Expenses Exceeded
[message] Woody, You have breached the agreed level on the following
accounts (select * from [appropriate table]
Name Expenses Agreed Expeneses
--
Woody 150 100
Please see the accounts manager
Now I could include all the people that have breached but the email will
contain that is not relevant and theres a good chance they would bother
with contact.
My idea was to create table that only contained data for one email so I
planned (I've simplied this but hopefully you'll get the idea)
create table [email]
(name, expenses, agreed_expeneses)
insert into [email] (name, expenses, agreed_expeneses)
values (@.name, @.expenses, @.agreed_expenses)
so that I could use xp_sendmail with the details of the table.
I am able to send emails to specific people, able to create tables with
the email but unable to insert data into the table and its a bit of a
bugger. As I said, as always, this is probably not the best way of doing
it but I don't know anything else as I'm still on that learning curve so
help, advise, critism would be appreciated.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi
This is just rough and untested as there is not enough information in your
postings:
declare @.rm_email_subject varchar(30)
declare @.rm_email_text varchar(100)
declare @.rm_email_content varchar(8000)
declare @.rm_email_signature varchar(100)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
declare @.rm_expenses varchar(10)
declare @.rm_agreed_expenses varchar(10)
set @.rm_email_text = ' You have breached the agreed level on the following
accounts
Name Expenses Agreed Expeneses
--
', @.rm_email_signature = '
Yours faithfully
John', @.rm_email_subject = 'Expenses Exceeded'
declare cc cursor for
select fee_earner, tkemail, mmatter, CONVERT(varchar(10),expenses),
CONVERT(varchar(10),agreed_expenses), from rm_80
where expenses > aggreed_expenses
open cc
while 1=1
begin
fetch next from cc
into @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter, @.rm_expenses,
@.rm_agreed_expenses
if @.@.fetch_status <>0
break
SELECT @.rm_email_content = @.rm_fee_earner + @.rm_email_text + @.rm_fee_earner
+ @.rm_expenses + ' ' + @.rm_agreed_expenses + @.rm_email_signature
EXEC master..xp_sendmail @.recipients =@.rm_tkemail, @.message
=@.rm_email_content ,@.subject =@.rm_email_subject
close cc
deallocate cc
go
This would work for 1 account per email, if you required more then there
would need to be a second cursor that loops through each row and appends
details to the message body.
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:upncqXQOEHA.1620@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply John.
> What I'm trying to do is this:
> Table 1: Contains a list of people and the current state of their
> accounts which have all breached a set level.
> I want to generate an individual email that notifies the person that
> they have breached the limit on a certain account and show them the
> details.
> For example
> Name Expenses Agreed Expeneses Email
> ----
> Woody 150 100 Woody@.blah.com
> Buzz 200 190 Buzz@.blah.com
> Rex 60 50 rex@.blah.com
> Send Email
> To: Woody@.blah.com
> From: DBA
> Subject: Expenses Exceeded
> [message] Woody, You have breached the agreed level on the following
> accounts (select * from [appropriate table]
> Name Expenses Agreed Expeneses
> --
> Woody 150 100
> Please see the accounts manager
> Now I could include all the people that have breached but the email will
> contain that is not relevant and theres a good chance they would bother
> with contact.
> My idea was to create table that only contained data for one email so I
> planned (I've simplied this but hopefully you'll get the idea)
> create table [email]
> (name, expenses, agreed_expeneses)
> insert into [email] (name, expenses, agreed_expeneses)
> values (@.name, @.expenses, @.agreed_expenses)
> so that I could use xp_sendmail with the details of the table.
> I am able to send emails to specific people, able to create tables with
> the email but unable to insert data into the table and its a bit of a
> bugger. As I said, as always, this is probably not the best way of doing
> it but I don't know anything else as I'm still on that learning curve so
> help, advise, critism would be appreciated.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql