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
John,
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@.posting.google.c om...
> 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.c om...
> 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!
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Wednesday, March 28, 2012
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@.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|||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
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@.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|||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
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
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
killing the duplicates from a table using sql
hi, what is a good way to kill the duplicates from a table. when i say killing duplicated, i mean killing all the rows for the repeated row.
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
WorkTempID ItemNo Seq
100196 RTP-22 1
100197 RTP-22 2
100198 RTP-22 3
100199 RTP-22 3
100200 RTP-22 4
100201 RTP-22 4
100202 RTP-22 5
100203 RTP-22 5
********************************************************
see how Seq 3, 4 and 5 are repeated? so for the output i want.
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
********************************************************
i DO NOT want this as the output. i already know how to achive this using DISTINCT keyword
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
100198RTP-223
100200RTP-224
100203RTP-225How big is the table?|||not that big.. few dozen rows. i am basically getting the job done by reading through the whole table, doing a count based on seq. if the count is more than 1, i update that row with "delete" as the ItemNo. at the end just deleting everyting that has "delete" for ItemNo. gets the job done but i think there is a better way to do this.|||Assuming that you actually want to remove the duplicates from the underlying table...
If the table isn't that big then consider something like this:
Declare @.tblTemp table (WorkTemplID int, Item char(10), Seq int)insert into @.tblTemp
select distinct * from Itemstruncate Items
insert into Items
select * from @.tblTemp
Not the most elegant code but very easy to understand|||thanks for the reply but it does not give me what i need. remember, i not only need to kill the duplicates but also the orginal row that is duplicated. if seq 3 is repeated 5 times DISTINCT keyword will give me 1 row that has seq 3 in it. but i dont want to get ANY rows with seq 3.|||create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
select * from #t1
group by c1
having count(c1) < 2|||aaah. Ok you want something like this...
|||create table #t1 (c1 int)
delete <table>
from <table> ORG
inner join
(select <col1>, <col2>,etc from <table> group by <col1>, <col2>,etc
having count(*) > 1) <some table alias STA> on
STA.<col1> = ORG.<col1> and etc (for all cols)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
select *
into #t2
from #t1
group by c1
having count(c1) < 2
select * from #t2|||ok ok, here's my last go at a perfect template ...
create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
delete from #t1
where c1 in
(
select c1
from #t1
group by c1
having count(c1) > 1
)|||ok, my last attempt at making the perfect template for this ...
create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
delete from #t1
where c1 in (
select c1
from #t1
group by c1
having count(c1) > 1
)
select * from #t1
Richard101|||Richard101 that only works if you've got a single unique column. The original example has no unique key columns...wouldn't have a problem if it did.
I just want to see you write out a few more templates :)|||ok, although my idea of a template is something that works, reduced to it's minimum, that you can build up.
right, using your data...
--
create table #t1 (c1 varchar(10), c2 varchar(10), c3 int)
insert into #t1 values ('100196', 'RTP-22', 1)
insert into #t1 values ('100197', 'RTP-22', 2)
insert into #t1 values ('100198', 'RTP-22', 3)
insert into #t1 values ('100199', 'RTP-22', 3)
insert into #t1 values ('100200', 'RTP-22', 4)
insert into #t1 values ('100201', 'RTP-22', 4)
insert into #t1 values ('100202', 'RTP-22', 5)
insert into #t1 values ('100203', 'RTP-22', 5)
delete from #t1
where c3 in
(
select c3
from #t1
group by c3
having count(c3) > 1
)
select * from #t1
--
Richard101|||Teehee. I was assuming that that a duplicates had to be c1 AND c2 AND c3. My fault. So how would you write that one Richard? ;)
Monday, March 12, 2012
KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud|||hi Alejandro,
My table doesnt have a primary key...so this procedure doesnt fir here...any
other ways to accompliish this?
Thanks
"Alejandro Mesa" wrote:
> INF: How to Remove Duplicate Rows From a Table
> http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
>
> AMB
>
> "pmud" wrote:
> > Hi,
> >
> > My sql table contains duplicate rows & I am trying to delete those but when
> > i try to delete or when i try to edit & save the duplicate rows i get the
> > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > WERE AFFTECTE DBY UPDATE.
> >
> > how can i delete these dupliacte rows? I cant even make any column a primary
> > key coz there are duplicates...
> >
> > Plz help.
> >
> > Thanks
> > --
> > pmud|||You can alter the table and add a primary key column to it first
ALTER TABLE tablename
ADD id INT IDENTITY (1, 1)
"pmud" wrote:
> hi Alejandro,
> My table doesnt have a primary key...so this procedure doesnt fir here...any
> other ways to accompliish this?
> Thanks
> "Alejandro Mesa" wrote:
> > INF: How to Remove Duplicate Rows From a Table
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
> >
> >
> > AMB
> >
> >
> > "pmud" wrote:
> >
> > > Hi,
> > >
> > > My sql table contains duplicate rows & I am trying to delete those but when
> > > i try to delete or when i try to edit & save the duplicate rows i get the
> > > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > > WERE AFFTECTE DBY UPDATE.
> > >
> > > how can i delete these dupliacte rows? I cant even make any column a primary
> > > key coz there are duplicates...
> > >
> > > Plz help.
> > >
> > > Thanks
> > > --
> > > pmud
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud|||hi Alejandro,
My table doesnt have a primary key...so this procedure doesnt fir here...any
other ways to accompliish this?
Thanks
"Alejandro Mesa" wrote:
> INF: How to Remove Duplicate Rows From a Table
> http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
>
> AMB
>
> "pmud" wrote:
> > Hi,
> >
> > My sql table contains duplicate rows & I am trying to delete those but when
> > i try to delete or when i try to edit & save the duplicate rows i get the
> > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > WERE AFFTECTE DBY UPDATE.
> >
> > how can i delete these dupliacte rows? I cant even make any column a primary
> > key coz there are duplicates...
> >
> > Plz help.
> >
> > Thanks
> > --
> > pmud|||You can alter the table and add a primary key column to it first
ALTER TABLE tablename
ADD id INT IDENTITY (1, 1)
"pmud" wrote:
> hi Alejandro,
> My table doesnt have a primary key...so this procedure doesnt fir here...any
> other ways to accompliish this?
> Thanks
> "Alejandro Mesa" wrote:
> > INF: How to Remove Duplicate Rows From a Table
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
> >
> >
> > AMB
> >
> >
> > "pmud" wrote:
> >
> > > Hi,
> > >
> > > My sql table contains duplicate rows & I am trying to delete those but when
> > > i try to delete or when i try to edit & save the duplicate rows i get the
> > > error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> > > WERE AFFTECTE DBY UPDATE.
> > >
> > > how can i delete these dupliacte rows? I cant even make any column a primary
> > > key coz there are duplicates...
> > >
> > > Plz help.
> > >
> > > Thanks
> > > --
> > > pmud
KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
pmud
INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...b;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
pmud
INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...b;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but when
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a primary
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud
KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
Hi,
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...kb;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but whe
n
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROW
S
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a prima
ry
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud
My sql table contains duplicate rows & I am trying to delete those but when
i try to delete or when i try to edit & save the duplicate rows i get the
error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROWS
WERE AFFTECTE DBY UPDATE.
how can i delete these dupliacte rows? I cant even make any column a primary
key coz there are duplicates...
Plz help.
Thanks
--
pmudINF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...kb;en-us;139444
AMB
"pmud" wrote:
> Hi,
> My sql table contains duplicate rows & I am trying to delete those but whe
n
> i try to delete or when i try to edit & save the duplicate rows i get the
> error ::" KEY COULMN INFORMATION IS INSUFFICIENT OR INCORERCT.TOO MANY ROW
S
> WERE AFFTECTE DBY UPDATE.
> how can i delete these dupliacte rows? I cant even make any column a prima
ry
> key coz there are duplicates...
> Plz help.
> Thanks
> --
> pmud
Key column is insufficient or incorrect. too many rows were affect
Hi,
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !
Key column information is insufficient or incorrect. Too many rows were affected by u
I have edit sql server 2000 table record by vb or direct enterprise mangafer and i have recived one message "Key column information is insufficient or incorrect. Too many rows were affected by update." any guys know this problem pl send the solution
Manish jain
ThanksThe Name Of Allah
hi
In the following table, for example, the error message appears if you attempt to delete one or both of the rows containing "abc" by using SQL Enterprise Manager with the following steps:
1. Right-click on the table.
2. Click on Open table, and then click on Return All Rows.
3. Highlight the rows, and then press the Delete button.
Character_Column1 Character_Column2
abc abc
abc abc
zxy zxy
fgt art
Eng. Maged
Friday, March 9, 2012
Keeping track of last time of extract
I am building an SSIS package in which I extract data from a number of transaction tables. The rows in each transaction table contain a timestamp. I would like to do the following.
* Every time I extract rows from a table I want to extract only the records added since last extract (no rows are modified or deleted - only added)
* In the extract process I want to find the maximum timestamp in a given table. This timestamp should be stored and updated in a table on the SQL Server. This way, the next time the extraction process is run only "new" rows are extracted
How would you go about doing this in SSIS? I have thought of the following approach, but I am unsure whether it is too cumbersome.
* A package variable is defined for each of the transaction tables
* A series of Execute SQL Tasks populates each of these variables with the timestamps from the table in SQL Server
* A series of data flow tasks extract the data (using the variables populated above in the WHERE-condition). Contained in the data flow tasks is a script component which records the maximum timestamp extracted and places this in yet another package variable (I am unsure how to do this, by the way...)
* A series of Execute SQL Tasks updates the table in SQL Server with the new timestamps
How would you do this?
Thanx! 
Have 2 values stored in a table called (e.g.) tblConfig. They are:
LastExtractDate
ThisExtractDate
Step 1) UPDATE tblConfig SET LastExtractDate=ThisExtractDate, ThisExtractDate = GETDATE()
Step 2) SELECT * FROM <source_table> WHERE <some_tstamp_value> >= LastExtractDate AND <some_tstamp_value> < ThisExtractDate> (this would be inside a data-flow source adapter)
OK, column names may change etc...but you get the idea!!!
-Jamie|||Very nice... And simple.
/Michael|||
Reckless wrote:
Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?
/Michael
Yeah. Good idea!
-Jamie|||Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.|||
Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.
Hi Al,
Step 1 I would do in an Exec SQL Task.
Step 2 most likely in a data flow - yes, using an OLE DB Data Source.
-Jamie
Wednesday, March 7, 2012
keeping last 10 entries by ID
Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helpingdelete
from Report
where R_id not in
(select top 10
R_Id
from Report Report2
where Report2.RName = Report.RName
order by RDate desc)
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helpingdelete
from Report
where R_id not in
(select top 10
R_Id
from Report Report2
where Report2.RName = Report.RName
order by RDate desc)
Keep Together Table Rows
I have a report that uses a table where each data record is displayed using
three table rows. When the report pages it sometimes breaks after the first
or second row of the three. I would like to have the three rows always on
the same page. I can not find a keep together property where I can bind the
three rows together and have them display as a unit.
Does anyone have any thoughts on the best approach to do this within the
table structure?
Thanks,
GeorgeSorry, the current version is not so good for controlling page breaks. You
can do some stuff using rectangles, but within tables, you don't have much
control. You can do a page break at the start or end of groups, but keep
together is a problem.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"George Vessels" <georgev@.pobox.com> wrote in message
news:%23cIGugQ7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a report that uses a table where each data record is displayed using
> three table rows. When the report pages it sometimes breaks after the
> first
> or second row of the three. I would like to have the three rows always on
> the same page. I can not find a keep together property where I can bind
> the
> three rows together and have them display as a unit.
> Does anyone have any thoughts on the best approach to do this within the
> table structure?
> Thanks,
> George
>
three table rows. When the report pages it sometimes breaks after the first
or second row of the three. I would like to have the three rows always on
the same page. I can not find a keep together property where I can bind the
three rows together and have them display as a unit.
Does anyone have any thoughts on the best approach to do this within the
table structure?
Thanks,
GeorgeSorry, the current version is not so good for controlling page breaks. You
can do some stuff using rectangles, but within tables, you don't have much
control. You can do a page break at the start or end of groups, but keep
together is a problem.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"George Vessels" <georgev@.pobox.com> wrote in message
news:%23cIGugQ7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a report that uses a table where each data record is displayed using
> three table rows. When the report pages it sometimes breaks after the
> first
> or second row of the three. I would like to have the three rows always on
> the same page. I can not find a keep together property where I can bind
> the
> three rows together and have them display as a unit.
> Does anyone have any thoughts on the best approach to do this within the
> table structure?
> Thanks,
> George
>
Friday, February 24, 2012
Keep group together
Is there any way to keep a group header and detail rows together so I don't get the header on the bottom of a page and the details orphaned at the top of the next page?
Table Keep Together property only works at the table level (though not sure how that works anyway). And I don't want a page break before each group.
Thanks in advance.
The only solution to this problem as of now is to repeat the group headers on new page.
Shyam
|||Any idea if this is going to be addressed in a future version?|||Probably yes, but not sure.
Shyam
Keep group rows together
Hi,
Is it possible to keep a group in a table report on the one page if this group could be fitted into the rest of the page and start new page otherwise?
Thanks,
Igor
This is taken from here http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx
Using Rectangles to Keep Objects Together
Rectangles in Reporting Services can be used either as graphical elements or as containers of objects. As object containers, they keep objects together on a page and control how objects move and push each other.
To keep multiple objects together on a page, put the objects within a rectangle. You can then put a page break before or after the rectangle by using the PageBreakAtStart or PageBreakAtEnd properties for the rectangle.
Using Rectangles to Control Item Growth and Displacement
Items within a rectangle become peers of each other and are governed by the rules of how peer items are positioned on the page as they move or grow. For example:
? Items will push or displace each other within the rectangle.
? Items will not push or displace items outside the rectangle, because they are not their peers.
? If necessary, a rectangle will grow to accommodate the items it contains.
You can use this logic to your advantage when dealing with objects that expand. For example:
? If you want to leave a blank space in your report for a table to expand into, group the blank space and the table in the same rectangle. When the table grows, it will push the blank space.
? If you want to prevent a matrix from pushing items off the right edge of the page, put the matrix within a rectangle with blank space to its right. Now, the matrix is no longer a peer to the other item on the page and will not be able to push it until the matrix can no longer be contained within its rectangle.
Subscribe to:
Posts (Atom)