Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does JOIN order effect efficiency?

Hi all,

Does JOIN order effect efficiency?

If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).

Thanks in advance,

Chiz.it should make no difference. have a look at the query plan to be sure.

Does a synchronous transformation process all rows in a buffer before outputting to next transfo

Hi,

If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?

Thanks in advance,
Lawrie.

Parts:
Component A (CA), Component B (CB), Row 1 (R1), Row 2 (R2), Row 3 (R3)

Example Synchronous:
CA and CB are Synchronous transforms (as defined by something like: output.SynchronousInputID = Input.ID in the ProvideComponentProperties()) (http://msdn2.microsoft.com/en-us/library/ms136027.aspx). The package has the row buffer size set to 1. The data source has 3 rows. The package starts and the following happens.

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA's ProcessInput finishes and R1 is passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB's ProcessInput finishes and R1 is passed downstream

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA's ProcessInput finishes and R2 is passed downstream

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB's ProcessInput finishes and R2 is passed downstream

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA's ProcessInput finishes and R3 is passed downstream

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB's ProcessInput finishes and R3 is passed downstream

Example Asynchronous:
CA and CB are Asynchronous transforms (as defined by something like: output.SynchronousInputID = 0 in the ProvideComponentProperties()) (http://msdn2.microsoft.com/en-us/library/ms135931.aspx). The package has the row buffer size set to 1. The data source has 3 rows. The package starts and the following happens.

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA stores R1
CA's ProcessInput finishes

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA stores R2
CA's ProcessInput finishes

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA stores R3
CA loops through stored rows and calls AddRow() on the output buffer and passes the data from the stored row to the new row
CA's ProcessInput finishes

R1-3 are passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB stores R1
CB's ProcessInput finishes

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB stores R2
CB's ProcessInput finishes

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB stores R3
CB loops through stored rows and calls AddRow() on the output buffer and passes the data from the stored row to the new row
CB's ProcessInput finishes

R1-3 are passed downstream

OR

R1 gets to CA from upstream
CA's ProcessInput is called with R1
CA calls AddRow() on the output buffer and passes the data from R1 to the new row
CA's ProcessInput finishes and R1 is passed downstream

R1 gets to CB from upstream
CB's ProcessInput is called with R1
CB calls AddRow() on the output buffer and passes the data from R1 to the new row
CB's ProcessInput finishes and R1 is passed downstream

R2 gets to CA from upstream
CA's ProcessInput is called with R2
CA calls AddRow() on the output buffer and passes the data from R2 to the new row
CA's ProcessInput finishes and R2 is passed downstream

R2 gets to CB from upstream
CB's ProcessInput is called with R2
CB calls AddRow() on the output buffer and passes the data from R2 to the new row
CB's ProcessInput finishes and R2 is passed downstream

R3 gets to CA from upstream
CA's ProcessInput is called with R3
CA calls AddRow() on the output buffer and passes the data from R3 to the new row
CA's ProcessInput finishes and R3 is passed downstream

R3 gets to CB from upstream
CB's ProcessInput is called with R3
CB calls AddRow() on the output buffer and passes the data from R3 to the new row
CB's ProcessInput finishes and R3 is passed downstream

The point is with Asynchronous transforms is that the component must call AddRow() on the output buffer and passe the data from input buffer row to the new row for it to be passed downstream. As soon as ProcessInput finishes, any rows added to the output buffer are passed downstream. You may need to store all rows or just some and the base classes allow you to pass on records whenever you wish.

|||Hi James,

Many thanks for taking the time to provide such a detailed response. The only problem is that my question was really what happens with synchronous transforms when the package has the row buffer size set greater than 1!

If you could provide an example for this I'd be really grateful...

Thanks,
Lawrie
|||

lawrieg wrote:

Hi,

If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?

Thanks in advance,
Lawrie.

The SSIS pipeline works on buffers at a time, not individual rows (unless buffer size is one).

So, the first component will pass rows to its output when its finished processing that row. But the second compoennt won't start processing until the LAST row in the buffer is passed - because then the buffer will be passed to the next component.

Does that make sense?

-Jamie

|||To expand the explanation for synchronous; change R1, R2, R3 to B1, B2, B3 where B = Buffer.

documentation on SQL OLE methods...Where to find?

I've seen a few examples in BOL and various websites where sp_OAMethod is
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>