alias for variable [memory] table?

i have this sql Q:
a delete statement not allowed to use with "as" (delete from table as t
where..),
so when i need "as" i do it in sub q, like this:
delete from customers where 3<(select count(*) from customer t where
t.date=customer.date)
but when table in memory table, I got err:
declare @.t table([id] int, [date] smalldatetime)
delete from @.t where 3<(select count(*) from @.t t where @.t.date=t.date)
I got err:Must declare the scalar variable "@.t"
have a solution for like situations?Indeed, you need to use an alias. Try something like this:
declare @.t table([id] int, [date] smalldatetime)
delete a from @.t a where 3<(select count(*) from @.t t where
a.date=t.date)
Razvan|||Your attempt at inventing syntax makes no sense in terms of the SQL
language model. An alias is supposed to act as it materializes a new
working table with the data from the original table expression in it.
To be consistent, this syntax says that you have done nothing to the
base table.
The next question is why would you use that proprietary in memory table
in the first place? It looks like you are mimicking a scratch tape in a
1950's file system instead of writing SQL. But you did not post enough
for anyone to give you a relatioanl solution.|||Use an alias, but use it where it belongs:
delete <alias>
from <table> [as] <alias>
where <condition>
ML
http://milambda.blogspot.com/|||Here is another way, without aliases:
DELETE @.t WHERE date IN (
SELECT date FROM @.t
GROUP BY date
HAVING COUNT(*)>3
)
Razvan
PS. I hope your real columns have better names...|||to --CELKO--
I realy interest you, but i not understand at all, please explain your
approach!!
for anyone to give you a relatioanl solution.
my example is very clear, i need to delete from this table rec that
appear more
then 3 times. did you have better way?sql

'aging' tables

I'm about to create some mechanism for control both: size of given table and
age of it's entries. I wish it to delete entries if older then X , or last n
entries when table is bigger then Y .
I need opinion if it will be good to use ddl trigger mechanism (SS2005), or
maybe someone would share another solution. It is supposed to be used only
for some tables like log table, not for each table in database, I don't want
to the mechanism decrease performance too much though.On 10.01.2007 10:49, fireball wrote:

Quote:

Originally Posted by

I'm about to create some mechanism for control both: size of given table and
age of it's entries. I wish it to delete entries if older then X , or last n
entries when table is bigger then Y .
I need opinion if it will be good to use ddl trigger mechanism (SS2005), or
maybe someone would share another solution. It is supposed to be used only
for some tables like log table, not for each table in database, I don't want
to the mechanism decrease performance too much though.

In that case a batch like approach is probably better, i.e. once a day
run your cleanup job. If possible you should have a CI with the
timestamp as leading column to make deleting more efficient.

The easiest is deletion by age. Deleting the oldest n records is more
difficult. If you are on SQL 2005 you may be able to create something
with an analytic function (i.e. using "row_number").

Cheers

robert|||Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w wiadomosci

Quote:

Originally Posted by

a batch like approach is probably better, i.e. once a day

the database will be run on remote client machine and developers are not
allowed to perform scheduled tasks on it (except upgradind/servicing) - so I
suppose I need to have it contorled either from application level or
database itself..|||fireball wrote:

Quote:

Originally Posted by

Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w
wiadomosci

Quote:

Originally Posted by

>a batch like approach is probably better, i.e. once a day

>
the database will be run on remote client machine and developers are
not allowed to perform scheduled tasks on it (except
upgradind/servicing)

I dont think he wants you to run the job manually.
Create a DTS package that deletes the rows, and have it scheduled/run as a
job by the sql-server-agent whenever the client starts sql-server (since you
don't know when the database/client will be turned on, you can't use a fixed
time like "at midnight").

/jim

AFTER Trigger

SalesMan Account
SID AcctType AID AID AcctName
1 0 1 1 myName
2 1 2 2 hisName
I need a trigger that when the AcctType is set to 2, it will delete record 2
(AID 2)(hisName) in the Account table.
Hi
create trigger my_tr on SalesMan for update
as
begin
if update(Acctype)
begin
delete Account where id in (select id from deleted where
deleted.aid=Account.aid)
end
end
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.
|||I think this trigger does not specify that AcctType has been changed to 2,
so may delete more than expected.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hi
> create trigger my_tr on SalesMan for update
> as
> begin
> if update(Acctype)
> begin
> delete Account where id in (select id from deleted where
> deleted.aid=Account.aid)
> end
> end
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
>
|||> when the AcctType is set to 2
When which AcctType is set to 2? The row with SID 1? The row with SID 2?
If SID 1, why should it delete SID 2? What shows me that rows 1 and 2 are
related in any way?
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.
|||Good catch Aaron, I have just missed it :-))
create table t1 (c int not null primary key,c2 int)
insert into t1 values (1,1)
insert into t1 values (2,10)
insert into t1 values (3,20)
insert into t1 values (4,30)
create table t2 (c int ,c2 char(1))
insert into t2 values (1,'a')
insert into t2 values (1,'b')
insert into t2 values (2,'c')
insert into t2 values (3,'d')
insert into t2 values (4,'f')
alter trigger my_tr on t1 for update
as
begin
if update(c2)
begin
delete t2 where exists (select * from
inserted i where i.c=t2.c and i.c2=2)
end
end
update t1 set c2=20 where c=1
--did not delete
select * from t2
update t1 set c2=2 where c=1
--does
select * from t2
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uJgw9Q$kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I think this trigger does not specify that AcctType has been changed to 2,
>so may delete more than expected.
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>

AFTER Trigger

SalesMan Account
SID AcctType AID AID AcctName
1 0 1 1 myName
2 1 2 2 hisName
I need a trigger that when the AcctType is set to 2, it will delete record 2
(AID 2)(hisName) in the Account table.Hi
create trigger my_tr on SalesMan for update
as
begin
if update(Acctype)
begin
delete Account where id in (select id from deleted where
deleted.aid=Account.aid)
end
end
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.|||I think this trigger does not specify that AcctType has been changed to 2,
so may delete more than expected.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hi
> create trigger my_tr on SalesMan for update
> as
> begin
> if update(Acctype)
> begin
> delete Account where id in (select id from deleted where
> deleted.aid=Account.aid)
> end
> end
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
>> SalesMan Account
>> SID AcctType AID AID AcctName
>> 1 0 1 1 myName
>> 2 1 2 2 hisName
>> I need a trigger that when the AcctType is set to 2, it will delete
>> record 2
>> (AID 2)(hisName) in the Account table.
>|||> when the AcctType is set to 2
When which AcctType is set to 2? The row with SID 1? The row with SID 2?
If SID 1, why should it delete SID 2? What shows me that rows 1 and 2 are
related in any way?
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
> SalesMan Account
> SID AcctType AID AID AcctName
> 1 0 1 1 myName
> 2 1 2 2 hisName
> I need a trigger that when the AcctType is set to 2, it will delete record
> 2
> (AID 2)(hisName) in the Account table.|||Good catch Aaron, I have just missed it :-))
create table t1 (c int not null primary key,c2 int)
insert into t1 values (1,1)
insert into t1 values (2,10)
insert into t1 values (3,20)
insert into t1 values (4,30)
create table t2 (c int ,c2 char(1))
insert into t2 values (1,'a')
insert into t2 values (1,'b')
insert into t2 values (2,'c')
insert into t2 values (3,'d')
insert into t2 values (4,'f')
alter trigger my_tr on t1 for update
as
begin
if update(c2)
begin
delete t2 where exists (select * from
inserted i where i.c=t2.c and i.c2=2)
end
end
update t1 set c2=20 where c=1
--did not delete
select * from t2
update t1 set c2=2 where c=1
--does
select * from t2
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uJgw9Q$kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I think this trigger does not specify that AcctType has been changed to 2,
>so may delete more than expected.
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ewomg08kIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> create trigger my_tr on SalesMan for update
>> as
>> begin
>> if update(Acctype)
>> begin
>> delete Account where id in (select id from deleted where
>> deleted.aid=Account.aid)
>> end
>> end
>>
>> "morphius" <morphius@.discussions.microsoft.com> wrote in message
>> news:76172D5C-A425-40D3-B48B-BD0A78CCEA61@.microsoft.com...
>> SalesMan Account
>> SID AcctType AID AID AcctName
>> 1 0 1 1 myName
>> 2 1 2 2 hisName
>> I need a trigger that when the AcctType is set to 2, it will delete
>> record 2
>> (AID 2)(hisName) in the Account table.
>>
>

After restore db, transactional replication can't delete.

Hi,
Can someone help me with the following situation?
1) I did dump for database test with transactional replication set up on it,
push data from test to test1 db on the same server.
2) Drop the transactional replication of publication for test database.
3) Drop the subscriber db test1.
4) Restore the dump to test db. After that I can't delete the Subscription
at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server Error:
20032.
Could not delete Publication 'test'. Could not update the distribution db
subscription table. The Subscription status could not be chaned.
How can I do right now? Please help. I want drop them even this is test
server.
The whole steps are for the DR test.
Regards!
Chen
did you try sp_removedbreplication on both the publisher and subscriber in
the publication and subscription dbs?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
> Hi,
> Can someone help me with the following situation?
> 1) I did dump for database test with transactional replication set up on
> it,
> push data from test to test1 db on the same server.
> 2) Drop the transactional replication of publication for test database.
> 3) Drop the subscriber db test1.
> 4) Restore the dump to test db. After that I can't delete the Subscription
> at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server
> Error:
> 20032.
> Could not delete Publication 'test'. Could not update the distribution db
> subscription table. The Subscription status could not be chaned.
> How can I do right now? Please help. I want drop them even this is test
> server.
> The whole steps are for the DR test.
> Regards!
> Chen
|||Hi Hilary,
I did following steps:
use master
go
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'test1'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
use distribution
go
exec sp_removedistpublisherdbreplication @.publisher = 'SQMA-AT01'
, @.publisher_db = 'test'
But I still see the test:test
[SQMA-AT01].[test1]
under Local Publications and when I try to delete the same errors occur. How
can I do? Actually this is the SQL Server 2005 with SP1.
Regards!
Chen
"Hilary Cotter" wrote:

> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
|||Hi Hilary,
Problem has been solved after I did sp_removedbreplication on both the
publisher and subscriber.
Thank you so much!
Chen
"Hilary Cotter" wrote:

> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>

After restore database, what happy with full-text catelogs?

Hi,
I have CRM database that contains full-text catalogs. Someone delete rows by
mistake. I need restore this database to same server from last night backup,
after database restore, do I need manually active full-text indexes or it
will automatically enable since I restore database at same server?
If I need do manually active full-text indexes, where and what kind steps I
need perform?
Regards,
Chen
You should run an incremental population.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:07AD58B8-128E-4726-8293-6E60198E5D3C@.microsoft.com...
> Hi,
> I have CRM database that contains full-text catalogs. Someone delete rows
> by
> mistake. I need restore this database to same server from last night
> backup,
> after database restore, do I need manually active full-text indexes or it
> will automatically enable since I restore database at same server?
> If I need do manually active full-text indexes, where and what kind steps
> I
> need perform?
> Regards,
> Chen
|||Chen,
I would recommend that you run a Full Population as the restored database,
may not be current with your existing database in addition to the rows that
were deleted.
Also, if your table does not have a timestamp column and you start an
Incremental Population, then in fact a Full Population is executed and
furthermore, an Incremental Population often takes as long to run as a Full
Population as it needs to determine if any rows are deleted and this causes
the Incremental Population to take as long as the Full Population, even if
no changes have occurred.
Finally, depending upon your SQL Server version ( SELECT @.@.version ) and if
you are using SQL Server 2000, you can use "Change Tracking" with "Update
Index in Background" and once your database is restored, you may never have
to run an Incremental Population again!
Thanks,
John
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:07AD58B8-128E-4726-8293-6E60198E5D3C@.microsoft.com...
> Hi,
> I have CRM database that contains full-text catalogs. Someone delete rows
by
> mistake. I need restore this database to same server from last night
backup,
> after database restore, do I need manually active full-text indexes or it
> will automatically enable since I restore database at same server?
> If I need do manually active full-text indexes, where and what kind steps
I
> need perform?
> Regards,
> Chen

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
Mike
Set the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>
|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the table
s
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tabl
es
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore m
y
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreat
e
> the whole database by running the dts package (this only takes 10 minutes)
.
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:
>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:
> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>