How do I know which record is being updated in Update trigger?

Hi All,
Thanks in advance!
I want to generate an XML file in a Update trigger script.
Only the updated record needs to be exported to the XML file.
How do I know which record is being updated in the Update trigger?
Thank you so much!
Regards,
SeanSean,
Could be more than one record. Use the DELETED and INSERTED pseudo/logical
tables available within the firing of the trigger.
HTH
Jerry
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Define being updated. If you mean the rows that were touched, even if all
values remain the same:
select *
from inserted
If you mean those that have actually changed:
select *
from inserted
join deleted
on <some unchangable value, like a surrogate (identity)
key>
where inserted.col1 <> deleted.col2 --not nullable
or (inserted.col2 <> deleted.col2
or (inserted.col2 is null and deleted.col2 is not null)
or (inserted.col2 is not null and deleted.col2 is null) )
If you mean something else, then please provide more info.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Dear Louis,
I need all the records that have changed and inserted.
That means a modified record and new record.
For example:
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
I have a problem detecting the @.@.identity. I could not get the information
of which records have been modified and inserted.
Thank you very much!
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Define being updated. If you mean the rows that were touched, even if all
> values remain the same:
> select *
> from inserted
> If you mean those that have actually changed:
> select *
> from inserted
> join deleted
> on <some unchangable value, like a surrogate (identity)
> key>
> where inserted.col1 <> deleted.col2 --not nullable
> or (inserted.col2 <> deleted.col2
> or (inserted.col2 is null and deleted.col2 is not null)
> or (inserted.col2 is not null and deleted.col2 is null) )
> If you mean something else, then please provide more info.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
>|||Yeah, you cannot rely on identities to identify a row like this. You really
need to have another key that you can expect not to change. Since you
insert the values, you know them and can trust them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
> Dear Louis,
> I need all the records that have changed and inserted.
> That means a modified record and new record.
> For example:
> CREATE TRIGGER trgUpdate
> ON User_Master
> FOR UPDATE
> AS
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
> REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
> SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
> CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
> EXEC master..xp_cmdshell @.bcpCommand
> I have a problem detecting the @.@.identity. I could not get the information
> of which records have been modified and inserted.
> Thank you very much!
>
> Regards,
> Sean
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Dear Louis,
Thanks for you information.
It helps a lot!
I am trying to copy the modified or new data from User_Master into a
User_MasterTemp table using an Update trigger.
CREATE TRIGGER trgUpdate ON User_Master
FOR UPDATE
AS
Delete From User_MasterTemp
Insert into User_MasterTemp Select * From Inserted
GO
Then do a exporting to XML file from the temp table using an Insert trigger.
CREATE TRIGGER User_MasterInsert ON [dbo].[User_MasterTemp]
FOR INSERT
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_MasterTemp for xml raw"
queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
Hopefully, the code can work. I am doing the troubleshooting cause the
script in the Insert trigger hangs the SQL Server.
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O04yriG0FHA.916@.TK2MSFTNGP10.phx.gbl...
> Yeah, you cannot rely on identities to identify a row like this. You
> really need to have another key that you can expect not to change. Since
> you insert the values, you know them and can trust them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
>

How do I kill a restore?

Question: I have a few databases that were told to restore via a
script. They've been stuck in a 'Restoring...' state for two days now,
and I need to kill it.
What can I do from administration standpoint to stop this?
Thanks,
MichaelThe restore process is probably done by now, and the databases are left in the restoring state
(either because the restore process was terminated, or because you restore using the NORECOVERY
option). If it is the later, try:
RESTORE DATABASE dbname WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> Question: I have a few databases that were told to restore via a
> script. They've been stuck in a 'Restoring...' state for two days now,
> and I need to kill it.
> What can I do from administration standpoint to stop this?
> Thanks,
> Michael
>|||I believe it is the former - when I try and run "RESTORE DATABASE
trial75441 WITH RECOVERY", I receive the following:
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete
processing on file 'trial75441_log'. Either restore the backup set that
was interrupted or restart the restore sequence.
Is there a way to force this restore to terminate via a command? If
not, what command allows me to restart?
Again, I apologize for my ignorance. I've away from the Windows
sysadmin side for a couple of years, and am now getting back into it
heavily. By the way, is there a good book that you can recommend for
running SQL Server 2005?
On Nov 3, 9:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> The restore process is probably done by now, and the databases are left in the restoring state
> (either because the restore process was terminated, or because you restore using the NORECOVERY
> option). If it is the later, try:
> RESTORE DATABASE dbname WITH RECOVERY
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> > Question: I have a few databases that were told to restore via a
> > script. They've been stuck in a 'Restoring...' state for two days now,
> > and I need to kill it.
> > What can I do from administration standpoint to stop this?
> > Thanks,
> > Michael|||Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
DATABASE and then start the restore operation from scratch.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
>I believe it is the former - when I try and run "RESTORE DATABASE
> trial75441 WITH RECOVERY", I receive the following:
> Msg 4319, Level 16, State 3, Line 1
> A previous restore operation was interrupted and did not complete
> processing on file 'trial75441_log'. Either restore the backup set that
> was interrupted or restart the restore sequence.
> Is there a way to force this restore to terminate via a command? If
> not, what command allows me to restart?
> Again, I apologize for my ignorance. I've away from the Windows
> sysadmin side for a couple of years, and am now getting back into it
> heavily. By the way, is there a good book that you can recommend for
> running SQL Server 2005?
> On Nov 3, 9:58 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> The restore process is probably done by now, and the databases are left in the restoring state
>> (either because the restore process was terminated, or because you restore using the NORECOVERY
>> option). If it is the later, try:
>> RESTORE DATABASE dbname WITH RECOVERY
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
>> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
>> > Question: I have a few databases that were told to restore via a
>> > script. They've been stuck in a 'Restoring...' state for two days now,
>> > and I need to kill it.
>> > What can I do from administration standpoint to stop this?
>> > Thanks,
>> > Michael
>|||I just had the same problem and doing what Tibor Karaszi mentioned
worked.
I dropped the databases that were created by the failed restore and
re-ran the restore script and it worked the 2nd time around.
Tibor Karaszi wrote:
> Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
> DATABASE and then start the restore operation from scratch.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
> news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
> >I believe it is the former - when I try and run "RESTORE DATABASE
> > trial75441 WITH RECOVERY", I receive the following:
> >
> > Msg 4319, Level 16, State 3, Line 1
> > A previous restore operation was interrupted and did not complete
> > processing on file 'trial75441_log'. Either restore the backup set that
> > was interrupted or restart the restore sequence.
> >
> > Is there a way to force this restore to terminate via a command? If
> > not, what command allows me to restart?
> >
> > Again, I apologize for my ignorance. I've away from the Windows
> > sysadmin side for a couple of years, and am now getting back into it
> > heavily. By the way, is there a good book that you can recommend for
> > running SQL Server 2005?
> >
> > On Nov 3, 9:58 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> The restore process is probably done by now, and the databases are left in the restoring state
> >> (either because the restore process was terminated, or because you restore using the NORECOVERY
> >> option). If it is the later, try:
> >>
> >> RESTORE DATABASE dbname WITH RECOVERY
> >>
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> >>
> >> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
> >> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> >>
> >> > Question: I have a few databases that were told to restore via a
> >> > script. They've been stuck in a 'Restoring...' state for two days now,
> >> > and I need to kill it.
> >>
> >> > What can I do from administration standpoint to stop this?
> >>
> >> > Thanks,
> >>
> >> > Michael
> >|||Perfect guys. I did that, and I'm in good shape.
Thank you all for your help.
On Nov 3, 11:38 am, "Hulicat" <dennis_A_wh...@.yahoo.com> wrote:
> I just had the same problem and doing what Tibor Karaszi mentioned
> worked.
> I dropped the databases that were created by the failed restore and
> re-ran the restore script and it worked the 2nd time around.
> Tibor Karaszi wrote:
> > Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
> > DATABASE and then start the restore operation from scratch.
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> > "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in message
> >news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
> > >I believe it is the former - when I try and run "RESTORE DATABASE
> > > trial75441 WITH RECOVERY", I receive the following:
> > > Msg 4319, Level 16, State 3, Line 1
> > > A previous restore operation was interrupted and did not complete
> > > processing on file 'trial75441_log'. Either restore the backup set that
> > > was interrupted or restart the restore sequence.
> > > Is there a way to force this restore to terminate via a command? If
> > > not, what command allows me to restart?
> > > Again, I apologize for my ignorance. I've away from the Windows
> > > sysadmin side for a couple of years, and am now getting back into it
> > > heavily. By the way, is there a good book that you can recommend for
> > > running SQL Server 2005?
> > > On Nov 3, 9:58 am, "Tibor Karaszi"
> > > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > >> The restore process is probably done by now, and the databases are left in the restoring state
> > >> (either because the restore process was terminated, or because you restore using the NORECOVERY
> > >> option). If it is the later, try:
> > >> RESTORE DATABASE dbname WITH RECOVERY
> > >> --
> > >> Tibor Karaszi, SQL Server
> > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> > >> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
> > >> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> > >> > Question: I have a few databases that were told to restore via a
> > >> > script. They've been stuck in a 'Restoring...' state for two days now,
> > >> > and I need to kill it.
> > >> > What can I do from administration standpoint to stop this?
> > >> > Thanks,
> > >> > Michaelsql

How do I get the script to create a table & all the Indexes

In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005?

Thanks

Have you looked at the Generate Script Wizard?|||

No. How do you use it?

Thanks

|||Right click on a database, select all tasks, generate scripts and then just follow the steps. If you are using SSMS Express it is not included.|||

That works for me.

Thanks

|||? You might want to check out this tool: http://www.sqlteam.com/publish/scriptio/ -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <VonSch@.discussions.microsoft.com> wrote in message news:102a22da-2ee1-4208-a1b1-79dfe66a70a6@.discussions.microsoft.com... In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005? Thanks|||

I tried it and it works.

Thanks.

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens