Sql Auto grow: delete

Showing posts with label delete. Show all posts

Thursday, March 22, 2012

Automatically Delete Snapshots

Does anyone know of a way to set up the Report Server to automatically delete
snapshots based on age.
I know I could write code to do this. I was hoping there might be a setting
you could manage that said to remove all snapshots in this folder older then
30 days everyday at midnight.
DaveDo you mean execution snapshots? Why do you want to delete them? Or are you
taking about aging of history snapshots? If so, this one is on the feature
list for a future version but in the meantime you can write some code to do
it yourself.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> Does anyone know of a way to set up the Report Server to automatically
> delete
> snapshots based on age.
> I know I could write code to do this. I was hoping there might be a
> setting
> you could manage that said to remove all snapshots in this folder older
> then
> 30 days everyday at midnight.
> Dave|||Thanks Brian...
I know I can write code to do it. I was hoping there might be a way to
configure the Report Server itself to remove reports older than a configured
number of days.
I'll do it myself and wait for the feature in another release.
Dave
"Brian Welcker [MS]" wrote:
> Do you mean execution snapshots? Why do you want to delete them? Or are you
> taking about aging of history snapshots? If so, this one is on the feature
> list for a future version but in the meantime you can write some code to do
> it yourself.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
> news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> > Does anyone know of a way to set up the Report Server to automatically
> > delete
> > snapshots based on age.
> >
> > I know I could write code to do this. I was hoping there might be a
> > setting
> > you could manage that said to remove all snapshots in this folder older
> > then
> > 30 days everyday at midnight.
> >
> > Dave
>
>sql

Automatically Create Linked Server?

I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Automatically Create Linked Server?

Sunday, March 11, 2012

Automatic delete from conflict tables

Is sql-server automatically deleting records from conflict tables?
Some records in the conflict tables seem to disappear.
If so, what is the definition of the time to retain data and how to
change it?
ThanksNo, SQL Server does not automatically delete records
unless its programmed in.
Try running a trace and see what is deleting them.
Peter
"Happiness is nothing more than good health and a bad
memory."
Albert Schweitzer
>--Original Message--
>Is sql-server automatically deleting records from
conflict tables?
>Some records in the conflict tables seem to disappear.
>If so, what is the definition of the time to retain data
and how to
>change it?
>Thanks
>.
>

Thursday, February 16, 2012

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***
Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl.. .
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.developersdex.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.developersdex.com ***

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete or update infomation on a table, the information will insert, delete or update on another table ( we don't have to create any trigger to do that ).
Please tell me the way to do. Thanks !
TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison
|||I think that you want to create a trigger that will allow you to log =
data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main=20
FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main=20
FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =
AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo'=20
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
--=20
Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =
news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =
insert, delete or update infomation on a table, the information will =
insert, delete or update on another table ( we don't have to create any =
trigger to do that ).
> Please tell me the way to do. Thanks !

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete or update infomation on a table, the information will insert, delete or update on another table ( we don't have to create any trigger to do that )
Please tell me the way to do. Thanks !TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison|||I think that you want to create a trigger that will allow you to log =data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo' SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
-- Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =insert, delete or update infomation on a table, the information will =insert, delete or update on another table ( we don't have to create any =trigger to do that ).
> Please tell me the way to do. Thanks !

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete
or update infomation on a table, the information will insert, delete or upda
te on another table ( we don't have to create any trigger to do that ).
Please tell me the way to do. Thanks !TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison|||I think that you want to create a trigger that will allow you to log =
data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main=20
FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main=20
FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =
AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo'=20
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
--=20
Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =
news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =
insert, delete or update infomation on a table, the information will =
insert, delete or update on another table ( we don't have to create any =
trigger to do that ).
> Please tell me the way to do. Thanks !

Sunday, February 12, 2012

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me...
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
fill
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
David Portas
SQL Server MVP
--

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> > I have a table that has teh primary key set so it auto increments.
> >
> > How can I make it to where if I delete a record, the next entry will
fill
> > the spot that was vacated?
> >
> > For example;
> >
> > I have 5 records,
> > 1
> > 2
> > 3
> > 4
> > 5
> >
> > If I delete record 3 I will have
> >
> > 1
> > 2
> > 4
> > 5
> >
> > Then if I add another reocrd, I will have
> > 1
> > 2
> > 4
> > 5
> > 6
> >
> > I would like 6 to actually be 3 to fill in the space.
> > or, when I delete a record, have everythign shift locations, or at least
> > have teh last record fill the spot of the deleted one.
> >
> >
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
--
David Portas
SQL Server MVP
--

Friday, February 10, 2012

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?

Thursday, March 22, 2012

Automatically Delete Snapshots

Automatically Create Linked Server?

Automatically Create Linked Server?

Automatically Create Linked Server?

Sunday, March 11, 2012

Automatic delete from conflict tables

Thursday, February 16, 2012

AUTO UPDATE DATABASE

AUTO UPDATE DATABASE

AUTO UPDATE DATABASE

auto trigger

auto trigger

auto trigger

Sunday, February 12, 2012

Auto increment problem

Auto increment problem

Auto increment problem

auto increment

auto increment

Friday, February 10, 2012

Auto generated CRUD in Sql 2005 issue

Sql Auto grow

Blog Archive

About Me