database problem: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

restrict update trigger to updated rows

How do I restrict an update trigger to just the rows updated?
Would like to have two audit fields with user and time of modification for
updates.
The following trigger would attempt to update all rows in the table:
ALTER trigger tm_stmp_admit_proc
on dbo.tblAdmissions_procedures
for update
as
begin
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
end
Couldn't find any description in BOL analogous to the FOR EACH ROW parameter
in
PostgreSQL:
CREATE OR REPLACE FUNCTION public.timestamper()
RETURNS trigger AS
'
BEGIN
NEW.time_stamp := \'now\';
NEW.user_modify := current_user;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER timestamper
BEFORE INSERT OR UPDATE
ON public.admit_procedures
FOR EACH ROW
EXECUTE PROCEDURE public.timestamper();
Thanks,
David P. Lurie
If you have a primary key column in the table
use INSERTED table in the trigger.
like
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
where exists ( select keycolumn from inserted
where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
but, then u may also have to set this parameter
SET RECURSIVE_TRIGGERS OFF
HTH
Faris
"David P. Lurie" <abc@.def.net> schrieb im Newsbeitrag
news:%23Di0ArVbEHA.2944@.TK2MSFTNGP11.phx.gbl...
> How do I restrict an update trigger to just the rows updated?
> Would like to have two audit fields with user and time of modification for
> updates.
> The following trigger would attempt to update all rows in the table:
> ALTER trigger tm_stmp_admit_proc
> on dbo.tblAdmissions_procedures
> for update
> as
> begin
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> end
> Couldn't find any description in BOL analogous to the FOR EACH ROW
parameter
> in
> PostgreSQL:
> CREATE OR REPLACE FUNCTION public.timestamper()
> RETURNS trigger AS
> '
> BEGIN
> NEW.time_stamp := \'now\';
> NEW.user_modify := current_user;
> RETURN NEW;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER timestamper
> BEFORE INSERT OR UPDATE
> ON public.admit_procedures
> FOR EACH ROW
> EXECUTE PROCEDURE public.timestamper();
> Thanks,
> David P. Lurie
>
|||Thanks a lot -
I tested this on two of the tables, and seems to work. Didn't turn off
recursive triggers to see whether it was necessary, and no problems thus far
with a few updates on each table. Assume that infinite loop would have
occurred if that setting was needed.
David P. Lurie
"Faris" <faris@.bss-india.com> wrote in message
news:eTBHKhWbEHA.3944@.tk2msftngp13.phx.gbl...
> If you have a primary key column in the table
> use INSERTED table in the trigger.
> like
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> where exists ( select keycolumn from inserted
> where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
> but, then u may also have to set this parameter
> SET RECURSIVE_TRIGGERS OFF
> HTH
> Faris

Monday, March 26, 2012

Restoring the backedup database

Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)

Originally posted by pardhu
Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,|||Hai dbadelphes,

I tried even by checking that option.

Originally posted by dbadelphes
Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)|||Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||Hai,

I am trying thru Enterprise Manager Wizard. I made the database names identical even.

Regards,

Originally posted by Satya
Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||From QA have you tried using WITH MOVE (no existing db1), or REPLACE (when you have a new db1)?

Try making sure that no db1 currently exists, then something like:
RESTORE DATABASE db1 FROM DISK='c:\db.doc'
WITH MOVE 'db1_Data' TO 'R:\Microsoft SQL Server\MSSQL\data\db1.mdf',
MOVE 'db1_Log' TO 'q:\Microsoft SQL Server\MSSQL\data\db1.ldf'

Refer to BOL for more information on BACKUP statement.sql

Wednesday, March 21, 2012

Restoring of database using SQL-DMO part 2

Hi its me again, I posted a message some time ago concerning restoring of database.

http://www.dbforums.com/showthread.php?postid=3582736#post3582736

My restore module basically restores a backup of a database file.

I found that when I integrate my restore module with a login module, I am getting the same exclusive access error again. I also found out that the connection created for authenticating the user, remains sleeping even after it is closed, which in effect prevents my restore module to get exclusive access.

What am I doing wrong? Any ideas appreciated, thanks for reading!Run This before you restore database statement:
declare @.l_spid varchar(4)
,@.l_hostname varchar(20)

declare kill_cursor scroll cursor
for
select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
where db_name(dbid) = 'Your Database Name'

open kill_cursor
select @.@.cursor_rows

fetch next from kill_cursor into
@.l_spid
,@.l_hostname
while (@.@.fetch_status = 0 )
begin
select @.l_hostname Killed
exec ( 'kill ' + @.l_spid)
fetch next from kill_cursor into
@.l_spid
,@.l_hostname
end
close kill_cursor
deallocate kill_cursor

YOUR RESTORE DATABASE STATEMENT|||Thanks suresh. I will try that.sql

Tuesday, March 20, 2012

Restoring multiple transaction logs

Can anyone tell me if there is an easy way to restore
multiple transaction logs at the same time as a group from
within the Enterprise console? When I select multiple
(consecutive) transaction logs to restore I receive a
message about the transaction log not being part of "a
multifamily(RAID) media set". There has to be an easier
way.The error message say that you striped the backup, i.e., doesn't have anything to do with applying
multiple logs in sequence.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jim" <brutjimmy@.yahoo.com> wrote in message news:041201c38391$a1798c60$a401280a@.phx.gbl...
> Can anyone tell me if there is an easy way to restore
> multiple transaction logs at the same time as a group from
> within the Enterprise console? When I select multiple
> (consecutive) transaction logs to restore I receive a
> message about the transaction log not being part of "a
> multifamily(RAID) media set". There has to be an easier
> way.|||It sounds like you are trying to restore multiple disk backup files together
as if it was a single "backup set".
In EM, under "Restore Database", you should be able to find a dialog that
lets you choose a "recovery plan" of multiple steps. There will be a "check
box" to click for each backup set to apply (starting with a full database
backup, etc).
"Jim" <brutjimmy@.yahoo.com> wrote in message
news:041201c38391$a1798c60$a401280a@.phx.gbl...
> Can anyone tell me if there is an easy way to restore
> multiple transaction logs at the same time as a group from
> within the Enterprise console? When I select multiple
> (consecutive) transaction logs to restore I receive a
> message about the transaction log not being part of "a
> multifamily(RAID) media set". There has to be an easier
> way.

Saturday, February 25, 2012

Restoring databases

Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please helpWhat errors are you getting? I haven't used backupexec before, but have you
tried doing a restore using SQL Server's native RESTORE command? You can
find more information and examples on RESTORE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:071c01c3da4c$f2428040$a301280a@.phx.gbl...
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please help

Tuesday, February 21, 2012

restoring database onto a different machine

Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Every time I have to copy a database from one machine to another I get
> > stuck in a problem with login IDs. Here's what happens:
> >
> > On machine A I have a database called D. there is a user called U that
> > maps to a login called U (no NTLM). U owns all the tables in database D,
> > and D is the default database for U. On machine B there is also a login
> > called U.
> >
> > The machines are not on the same network so I backup the database to a
> > flat file and restore it onto machine B
> >
> > Now if I log onto machine B as user U, I do not have permission to
change
> > into the newly restored database D. I presume this is because user U on
> > machine B is different to user U on machine A, and the database is owned
> > by user U on machine A.
> >
> > So I log into machine B as 'sa' and I try to add user U to the database
D.
> > This fails because there is already a user called U on the database. I
> > cannot remove the existing user called U because it owns all the tables.
> > So it seems I am in a catch 22 situation.
> >
> > I have tried sp_fix_users_login which looks like it should do the right
> > thing but to no avail.
> >
> > The only way I can get working is create a new user called something
other
> > than U, add this to the database, and then use sp_changeobjectowner to
> > change ownership of all the tables to the new user, which is a right
PITA.
> >
> > I have tried to keep it as simple as possible (only 1 user per database,
> > only 1 database per user, login name same as user name, etc etc) but I
am
> > still stuck. Any ideas
> >
> > TIA
> >
> > Andy
> >
> >
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
>> There is a way to create a Login on one server with the GUID of another
>> login on another server.
>> This should get rid of the need to recreate the login everytime a restore
> is
>> done.
>> I cannot recollect how I used to do it as it is a year since I last
>> worked
>> on DB administration.
>> You might want to check with BOL.
>> Gopi
>>
>> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
>> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > Every time I have to copy a database from one machine to another I get
>> > stuck in a problem with login IDs. Here's what happens:
>> >
>> > On machine A I have a database called D. there is a user called U that
>> > maps to a login called U (no NTLM). U owns all the tables in database
>> > D,
>> > and D is the default database for U. On machine B there is also a login
>> > called U.
>> >
>> > The machines are not on the same network so I backup the database to a
>> > flat file and restore it onto machine B
>> >
>> > Now if I log onto machine B as user U, I do not have permission to
> change
>> > into the newly restored database D. I presume this is because user U on
>> > machine B is different to user U on machine A, and the database is
>> > owned
>> > by user U on machine A.
>> >
>> > So I log into machine B as 'sa' and I try to add user U to the database
> D.
>> > This fails because there is already a user called U on the database. I
>> > cannot remove the existing user called U because it owns all the
>> > tables.
>> > So it seems I am in a catch 22 situation.
>> >
>> > I have tried sp_fix_users_login which looks like it should do the right
>> > thing but to no avail.
>> >
>> > The only way I can get working is create a new user called something
> other
>> > than U, add this to the database, and then use sp_changeobjectowner to
>> > change ownership of all the tables to the new user, which is a right
> PITA.
>> >
>> > I have tried to keep it as simple as possible (only 1 user per
>> > database,
>> > only 1 database per user, login name same as user name, etc etc) but I
> am
>> > still stuck. Any ideas
>> >
>> > TIA
>> >
>> > Andy
>> >
>> >
>>
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "rgn" <gopinathr@.healthasyst.com> wrote in message
> > news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> >> There is a way to create a Login on one server with the GUID of another
> >> login on another server.
> >> This should get rid of the need to recreate the login everytime a
restore
> > is
> >> done.
> >>
> >> I cannot recollect how I used to do it as it is a year since I last
> >> worked
> >> on DB administration.
> >>
> >> You might want to check with BOL.
> >>
> >> Gopi
> >>
> >>
> >> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> >> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > Every time I have to copy a database from one machine to another I
get
> >> > stuck in a problem with login IDs. Here's what happens:
> >> >
> >> > On machine A I have a database called D. there is a user called U
that
> >> > maps to a login called U (no NTLM). U owns all the tables in database
> >> > D,
> >> > and D is the default database for U. On machine B there is also a
login
> >> > called U.
> >> >
> >> > The machines are not on the same network so I backup the database to
a
> >> > flat file and restore it onto machine B
> >> >
> >> > Now if I log onto machine B as user U, I do not have permission to
> > change
> >> > into the newly restored database D. I presume this is because user U
on
> >> > machine B is different to user U on machine A, and the database is
> >> > owned
> >> > by user U on machine A.
> >> >
> >> > So I log into machine B as 'sa' and I try to add user U to the
database
> > D.
> >> > This fails because there is already a user called U on the database.
I
> >> > cannot remove the existing user called U because it owns all the
> >> > tables.
> >> > So it seems I am in a catch 22 situation.
> >> >
> >> > I have tried sp_fix_users_login which looks like it should do the
right
> >> > thing but to no avail.
> >> >
> >> > The only way I can get working is create a new user called something
> > other
> >> > than U, add this to the database, and then use sp_changeobjectowner
to
> >> > change ownership of all the tables to the new user, which is a right
> > PITA.
> >> >
> >> > I have tried to keep it as simple as possible (only 1 user per
> >> > database,
> >> > only 1 database per user, login name same as user name, etc etc) but
I
> > am
> >> > still stuck. Any ideas
> >> >
> >> > TIA
> >> >
> >> > Andy
> >> >
> >> >
> >>
> >>
> >
>

Friday, March 30, 2012

restrict update trigger to updated rows

Monday, March 26, 2012

Restoring the backedup database

Wednesday, March 21, 2012

Restoring of database using SQL-DMO part 2

Tuesday, March 20, 2012

Restoring multiple transaction logs

Saturday, February 25, 2012

Restoring databases

Tuesday, February 21, 2012

restoring database onto a different machine

restoring database onto a different machine

restoring database onto a different machine

database problem

Blog Archive

About Me