launch an appl from a trigger ?

Is there a way to launch an application(c:\hello.exe)
from a trigger when a new record is inserted into the
table or
a record is updated in the table?
If so, what is the correct syntax ?
THANKS!Understand that launching an application that requires the input from a =user can extremely critical ... This is because this app is invoked in =the same process of SQL Server and the credential of the user to =interact with desktop maynot be there ... Hence such operation can stop =the SQL Server service ... Hence avoid such operations ...
On the contrary you like to send a popup using the network messenger =service then use the xp_cmdshell command ... But it is not recommended =in production code ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"amy" <achen@.comverge.com> wrote in message =news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the > table or > a record is updated in the table?
> > If so, what is the correct syntax ?
> > THANKS!|||Amy,
You can use the xp_cmdshell extended procedure to start an application from
the command prompt. For exact syntax, do please refer to Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"amy" <achen@.comverge.com> wrote in message
news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the
> table or
> a record is updated in the table?
> If so, what is the correct syntax ?
> THANKS!|||I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible. If it
did work, it would get around the issues raised here
about having the .exe hang things up, etc.
>--Original Message--
>Amy,
>What are you trying to do ? Ofcourse you can launch an
application by using
>master..xp_cmdshell, as in
>exec master..xp_cmdshell "dtsrun /?"
>But for every insert and update? well , no..I wont do
that.Also, if this
>hello.exe has any GUI as in exec
master..xp_cmdshell "notepad" ,
>master..xp_cmdshell would hang leading to timeout
problems.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"amy" <achen@.comverge.com> wrote in message
>news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> Is there a way to launch an application(c:\hello.exe)
>> from a trigger when a new record is inserted into the
>> table or
>> a record is updated in the table?
>> If so, what is the correct syntax ?
>> THANKS!
>
>.
>|||Steve,
Since you replied to my post...
It may work or may be we can find a workaround but I still wont do that.In a
heavy OLTP system, triggers are always a overhead for the DML and a trigger
which calls a exe, gui or no gui , would be a recipe for disaster.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> I would investigate having the .exe be part of a "job"
> under DTS Local Packages and having the trigger start
> that job - not really sure if this is possible. If it
> did work, it would get around the issues raised here
> about having the .exe hang things up, etc.
> >--Original Message--
> >Amy,
> >
> >What are you trying to do ? Ofcourse you can launch an
> application by using
> >master..xp_cmdshell, as in
> >
> >exec master..xp_cmdshell "dtsrun /?"
> >
> >But for every insert and update? well , no..I wont do
> that.Also, if this
> >hello.exe has any GUI as in exec
> master..xp_cmdshell "notepad" ,
> >master..xp_cmdshell would hang leading to timeout
> problems.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"amy" <achen@.comverge.com> wrote in message
> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> Is there a way to launch an application(c:\hello.exe)
> >> from a trigger when a new record is inserted into the
> >> table or
> >> a record is updated in the table?
> >>
> >> If so, what is the correct syntax ?
> >>
> >> THANKS!
> >
> >
> >.
> >|||I agree that other paths might serve the same purpose,
but I'm not sure what the need really is. It would make
more sense to me to have a "scheduled" job run regularly
(every x minutes) and sweep up handling all the "rows"
inserted since the last sweep. Or code the .exe in C and
make it an extended stored procedure - run right from
inside the trigger. Or simply code the need in T-Sql and
get rid of the .exe requirement...
>--Original Message--
>Steve,
>Since you replied to my post...
>It may work or may be we can find a workaround but I
still wont do that.In a
>heavy OLTP system, triggers are always a overhead for
the DML and a trigger
>which calls a exe, gui or no gui , would be a recipe for
disaster.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
>> I would investigate having the .exe be part of a "job"
>> under DTS Local Packages and having the trigger start
>> that job - not really sure if this is possible. If it
>> did work, it would get around the issues raised here
>> about having the .exe hang things up, etc.
>> >--Original Message--
>> >Amy,
>> >
>> >What are you trying to do ? Ofcourse you can launch an
>> application by using
>> >master..xp_cmdshell, as in
>> >
>> >exec master..xp_cmdshell "dtsrun /?"
>> >
>> >But for every insert and update? well , no..I wont do
>> that.Also, if this
>> >hello.exe has any GUI as in exec
>> master..xp_cmdshell "notepad" ,
>> >master..xp_cmdshell would hang leading to timeout
>> problems.
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"amy" <achen@.comverge.com> wrote in message
>> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> >> Is there a way to launch an application
(c:\hello.exe)
>> >> from a trigger when a new record is inserted into
the
>> >> table or
>> >> a record is updated in the table?
>> >>
>> >> If so, what is the correct syntax ?
>> >>
>> >> THANKS!
>> >
>> >
>> >.
>> >
>
>.
>|||Steve,
Well if you are asking which of the requirement to take then, my =suggestion would be to use the T-SQL method and a jobs framework ... It =is simply because you are closer to the data and getting out of process =of SQL and then get the job done is something not advisable ...
Now trigger can prove costlier as this will fire with every insert =operation ... But would make it more live data ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Steve Z" <szlamany@.antarescomputing.com> wrote in message =news:0eb201c35779$6045c3b0$a401280a@.phx.gbl...
> I agree that other paths might serve the same purpose, > but I'm not sure what the need really is. It would make > more sense to me to have a "scheduled" job run regularly > (every x minutes) and sweep up handling all the "rows" > inserted since the last sweep. Or code the .exe in C and > make it an extended stored procedure - run right from > inside the trigger. Or simply code the need in T-Sql and > get rid of the .exe requirement...
> >--Original Message--
> >Steve,
> >
> >Since you replied to my post...
> >
> >It may work or may be we can find a workaround but I > still wont do that.In a
> >heavy OLTP system, triggers are always a overhead for > the DML and a trigger
> >which calls a exe, gui or no gui , would be a recipe for > disaster.
> >
> >-- > >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"Steve Z" <szlamany@.antarescomputing.com> wrote in > message
> >news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> >> I would investigate having the .exe be part of a "job"
> >> under DTS Local Packages and having the trigger start
> >> that job - not really sure if this is possible. If it
> >> did work, it would get around the issues raised here
> >> about having the .exe hang things up, etc.
> >>
> >> >--Original Message--
> >> >Amy,
> >> >
> >> >What are you trying to do ? Ofcourse you can launch an
> >> application by using
> >> >master..xp_cmdshell, as in
> >> >
> >> >exec master..xp_cmdshell "dtsrun /?"
> >> >
> >> >But for every insert and update? well , no..I wont do
> >> that.Also, if this
> >> >hello.exe has any GUI as in exec
> >> master..xp_cmdshell "notepad" ,
> >> >master..xp_cmdshell would hang leading to timeout
> >> problems.
> >> >
> >> >-- > >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"amy" <achen@.comverge.com> wrote in message
> >> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> >> Is there a way to launch an application
> (c:\hello.exe)
> >> >> from a trigger when a new record is inserted into > the
> >> >> table or
> >> >> a record is updated in the table?
> >> >>
> >> >> If so, what is the correct syntax ?
> >> >>
> >> >> THANKS!
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Latest value for all members at a given point in time

We have a fact table with a current balance record. For this balance rows are added to the table only when the balance has changed. This way we can reduce the volume in the fact table by not saving redundant information for every point in time that we want to analyze.

In SQL it is a simple task to get a view of the lastest fact records for a given point in time. SELECT f1.* FROM factTable f1 WHERE f1.timestamp = (SELECT MAX(f2.timestamp) FROM factTable f2 WHERE f2.timestamp <= givenPointInTime AND f2.balanceKey = f1.balanceKey). In other words, the given point in time directly determines which rows should be fetched from our fact table, one row for each balance record (balanceKey). This row will of course include several dimension attributes, which represent the point in time when the balance changed.

Now, we would like to reproduce this behaviour in our Analysis Services 2005 cube. Using a time dimension, we would like to be able to select a given point in time and have the cube return all rows previous to that time, one row for each balanceKey. Not only the rows matching the given point in time. How can this be done? For the other dimension attributes, they should behave in a normal way, such that for the returned rows, you can slice using any combination of them.

Any help or ideas are appreciated,
Lars

Assuming that you're using AS 2005 Enterprise Edition, you could try the "Last Nonempty Child" aggregation:

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

>>

SQL Server 2005 Books Online

Defining Semiadditive Behavior

Semiadditive measures, which do not uniformly aggregate across all dimensions, are very common in many business scenarios. Every cube that is based on snapshots of balances over time exhibits this problem. You can find these snapshots in applications dealing with securities, account balances, budgeting, human resources, insurance policies and claims, and many other business domains.

...

LastNonEmpty

The member value is evaluated as the value of its last child along the time dimension that contains data.

>>

|||

Thanks Deepak. We are on an Enterprise Edition (pre-SP1) but we do not get the desired behaviour using the "Last Nonempty Child" aggregation. In fact, it doesn't seem to make any difference at all from using SUM. We have created an example containing a relational database with a simple fact table and an analysis services database built upon it with a measure called "Account Balance". If we browse the cube and add the measure and the Account dimension to the results pane, then filter based on the Time dimension and select the date 2005-12-31, Account number 3 should display the balance 400. Currently it does not.

The example (SQL script + XMLA script) can be found at: http://www.intellibis.se/pub/CumulativeExample.zip

Can anyone help us?

Regards,
Lars

sql

Latest record

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display latest order from the customer

You can use ORDER BY.

Select O.OrderId, C.Name, O.Product, O.DateFROM Order OJOIN Customer CON C.CustomerId O.CustomerIdORDER BY O.DateDesc

|||

SELECT orderId, customerId, product,[date], [name]FROM(select a.orderId, a.customerId, a.product,a.[date], b.name, row_number()over(partitionby a.customeridorderby [date]DESC)as RowNum

from [Order] ainnerjoin [Customer] bon a.customerId=b.customerId) t

WHERE RowNum= 1

sql

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

last record in table

can you please help me with sql querry for 'finding last record in a table'Hi,
Inherently, a relational database table is a set. Sets are, by definition, unordered.
So every select statement will show you records in different order , i mean no order, it will be in random order.Getting the last row requires that you have some way of ordering the rows in the database.
If you do not have an identity column, you would need to have some other "identity-like" column, such as a column that holds the last modified date of a row or atleast some serial number as primary key .
Otherwise, SQL Server has no way of identifying the last record.|||Hi,
Answer for your question
-----------
select top 1 * from tablename order by column name desc

Last record in each group

Greetings anyone -

I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.

Would someone please be so kind as to advise how this can be done?

If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.

Help pls?

Tks & B/R

I don't know how to do that in Report Builder, but in Report Designer you can use the Last Function in any expression. In the Edit Expression dialog, click on Common Functions, and then Last to insert fhe function into the expression.

Last record in 10,000,000 rows

Hey gang, I got a table that has about 10,000,000 row and I need a test of the last 10 rows. Isn't the statement: Select bottom 10 from table?Rows in a table don't have any order. You can order the result set any way you want. Take the TOP 10 with the order "upside down" and you are in business!

-PatP|||SELECT TOP 1 * from test order by id desc
OR
select * from TEST a
where 1>(select count(*) from TEST where ID>a.id)
-SS|||Do you have an ADD_ROW_TS or IDENTITY Column?|||Gentlemen, I dont beleive I've been clear on what I'm attempting to do. I have a table with 10,000,000 records. (which happend to reflect 6 months historical data) that I need to confirm it is there. I can do a 'select top 10 from table" to get the first 10 record of this new table. I need to see the last 10 to confirm the month,date,time etc... What I've come up with in the sql anaylser is
select * from dbo.INCOMING_TEMP
where RECORD_NUMBER > 10453700
according to my records the total number of rows at 10453747

It's running but taking all day to get there.|||I'm assuming RECORD_NUMBER is defined like

RECORD_NUMBER int IDENTITY(1,1) NOT NULL

Yes?

SELECT TOP 10 * FROM yourTable99 ORDER BY RECORD_NUMBER DESC

Will get you the official last 10 records...

If that's truly the case...

No?|||Thanks that works fine. I havent been my self since I got the damn cast on my foot. So bear with me. Not I get to compare radomn samples of the data before blowing the original table away.

Last record ?

Hello Team

i want to get a value from a column in the last record

i wrote select ID from (select top 1 * desc from items)

any idea, Thanks lot

If you want to use top you have to use order by clause with desc to fetch the last record..

Code Snippet

Select Top 1 Id from Demo Order By Id Desc

or

Select max(id) from Demo;

Another easy way is,

Code Snippet

Declare @.Id as int;

Select @.ID = ID from Demo;

Select @.ID;

here it always return the last record

|||

Just select from the table ordering by the field that can be used to identify if this is a recent row or not.

Code Snippet

Select Top 1 ID

From Items

Order By ID Desc

Assuming ID is a column that is incremental for each new row. If you don't have an incremental ID, you can use a column that contains the date when the row has been added.

I hope this answers your question. If not, please post more information about the table.

Best regards,

Sami Samir

|||

Thank you very much

and Mr samy too

|||If you want to obtain the ID value of the last record inserted to a table, and then use that ID value in additional INSERTS/UPDATES, you may wish to explore using the system function: SCOPE_IDENTITY. It can return the value of the immediately preceding insert WITHOUT having to execute a query.sql

last n records simpleand easy

how do i display last n record on 6.5
if i have a primary key(number)select pkey
, foo
from yourtable xxx
where n > (
select count(*)
from yourtable
where foo > xxx.foo )
"foo" is the column that determines the sequence

without a sequence, "top" has no meaning|||You mean an identity Column?

SELECT TOP n * FROM yourTable Order by PKID DESC|||Brett, when did sql server add support for TOP

7, wasn't it?

;) ;)|||doooooooh

Details...details...

Yup no TOP|||No i mean a primary key which is a number

select top 300 * from tablename
order by columnname desc (columnname is primary key)

Primary key is smallint

when i do

select clientid, foo
from client x
where 300>( select count(*)
from client
where foo> x.foo)

It gives error foo column not there

here after 6.5 sql became easy|||sjumma, what are the columns in your table?

which one do you want to sort by?|||let say i want 1 column

select clientid
from client

clientid is smallint and contunious number (1,2,3,...)

how will i make just the last 300 records be displayed|||In MS-SQL 6.5, I'd use:SET ROWCOUNT 300

SELECT clientid
FROM dbo.client AS a
ORDER BY clientid DESC

SET ROWCOUNT 0Note that an index on client.clientid will help performance immensely.

-PatP|||<sigh />

oh yeah, rowcount :p

select clientid
from client x
where 300>( select count(*)
from client
where clientid> x.clientid)|||Originally posted by r937
oh yeah, rowcount :p Mondo way ugly, but it gets an answer while I'm still young enough to care! I'd hate to contemplate how long it would take to slog the count query through a half million clients, even though it is esthetically more pleasant than the ROWCOUNT solution!

-PatP

Last GASP on "Insert row in table with Identity field, and get new Identity back " ?

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?

TIA,

Barkingdog

SSIS processes data in a different way than T-SQL. The "pipleine" processes differently than batch oriented SQL and differently than cursor based SQL. This is by design of course and has certain benefits and trade-offs, and requires a different way of implementing a solution.

Whether you want to characterize that as an additional "layer of complexity" or not is up to you. I guess if you now have differing options for processing data, that have different advantages and disadvantages, with different costs and benefits, where previously you had only one option , you might consider that more complexity.

I consider it more options - and glad to have 'em...

|||

This post includes examples of how to create surrogate keys using SSIS, effectively the same problem as you are trying to solve: http://sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

We do not have a packaged solution for highly parallel loads in this version, but we will be releasing a paper on this subject in coming months.

Donald

|||

Thanks,

I will read through the paper.

I am starting to think that, because of parallesim in SSIS, that the sql uniqueidentifier may be a more suitable "primary key" for a table than the traditional Identity value (integer) used in the past.

But I do believe the problem relates to paralellism in SSIS. I don't think this problem would likely occur from a stored proc call that used the "insert a new record, get the identity value, populate the child" approach. (Though I guess it could happen if one had a server farm..... Why is everything so complicated?!)

TIA,

Barkingdog

|||

It's not because of parallelism in SSIS. We have seen this same problem with inserting and immediately retrieving an identity over many years. Unfortunately, everyone thinks they can guarantee the result, and sooner or later they trip up because of some glitch. Unfortunately, because these values are your unique identifiers, such a trip up can be quite serious.

Read:

http://www.aspfaq.com/show.asp?id=2499

http://www.databasejournal.com/features/mssql/article.php/3307541

Donald

|||

>> We have seen this same problem with inserting and immediately retrieving an identity over many years"

The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm. I can hear the developers saying that the "problem" mentioned can not happen in that of environemnt. Are they correct or can someone come up with a simple t-sql script that forces the problem to happen in any sql environment?

TIA,

Barkingdog

|||

>The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm.

The problem I face is that I have heard this many times from many users - and yet somewhere, somehow, somewhen, the world turns out to be not quite this tidy.

If you think this will work for you, I guess I can only say good luck. But I have an uneasy feeling that in a few months time we'll be reading the "how do I rekey all my child records?" post.

Donald

|||

Thanks Donald. I will follow your sage advice!

I'm just preparing for the bruises\abuse I'm going to get from developers who will think I'm wrong and crazy on this point. I'm sure the URL's you listed will dull their bite.

Barkingdog

|||

Here's what I have learned about this issue so far:

1. This issue has always existed. It is now aggravated by multi-processor systems, server farms, and the parellelism introduced in SSIS.

2. The best thought seems to be to generate one's own counter and use it instead of relying upon an Identity field.

3. While better to use SCOPE_IDENTITY than @.@.Identity or IDENT_CURRENT, even that will not guarantee a correct parent-child linkage. (The problem is if one t-sql operaiton starts before another their is no guarantee it will complete and commit before the other. Forget sequential processing.)

4. I think the only available way to get the proper linkage is to use GUID (from NEWID()) to generate the "identity" key for tables. (But that approach won't be quick for use in JOINS unless I index the GUIDs!)

In summary, I don't think there is a GOOD solution to thie fundamental issue yet. It was mentioned that MicroSoft is working on a best-practices document regarding surrogate keys generation . I can hardly wait for it to be released!

Barkingdog

|||

Actually, I can tell you the technique I have used, and don't recollect ever having a problem with it:

1: ALL rtables must have a _natural_ key defined, and enforced as a unique index.

2: Now after inserting a row into an rtable that uses a system generated surrogate key, you can use the _natural key_ to find the row, therefore it is guaranteed to be the correct row, and retrieve whatever surrogate system generated key was inserted in that row. Obviously there is a performance price to pay to getting the right answer, but typically that's better than a fast wrong answer...

3:now you can use that _guaranteed correct_ surrogate key in further processing as needed.

It has been many years since I have built an rtable that did not enforce uiqueness based on a _natural_ key. It is the only way to go...

Last GASP on "Insert row in table with Identity field, and get new Identity back "

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?

TIA,

Barkingdog

SSIS processes data in a different way than T-SQL. The "pipleine" processes differently than batch oriented SQL and differently than cursor based SQL. This is by design of course and has certain benefits and trade-offs, and requires a different way of implementing a solution.

Whether you want to characterize that as an additional "layer of complexity" or not is up to you. I guess if you now have differing options for processing data, that have different advantages and disadvantages, with different costs and benefits, where previously you had only one option , you might consider that more complexity.

I consider it more options - and glad to have 'em...

|||

This post includes examples of how to create surrogate keys using SSIS, effectively the same problem as you are trying to solve: http://sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

We do not have a packaged solution for highly parallel loads in this version, but we will be releasing a paper on this subject in coming months.

Donald

|||

Thanks,

I will read through the paper.

I am starting to think that, because of parallesim in SSIS, that the sql uniqueidentifier may be a more suitable "primary key" for a table than the traditional Identity value (integer) used in the past.

But I do believe the problem relates to paralellism in SSIS. I don't think this problem would likely occur from a stored proc call that used the "insert a new record, get the identity value, populate the child" approach. (Though I guess it could happen if one had a server farm..... Why is everything so complicated?!)

TIA,

Barkingdog

|||

It's not because of parallelism in SSIS. We have seen this same problem with inserting and immediately retrieving an identity over many years. Unfortunately, everyone thinks they can guarantee the result, and sooner or later they trip up because of some glitch. Unfortunately, because these values are your unique identifiers, such a trip up can be quite serious.

Read:

http://www.aspfaq.com/show.asp?id=2499

http://www.databasejournal.com/features/mssql/article.php/3307541

Donald

|||

>> We have seen this same problem with inserting and immediately retrieving an identity over many years"

The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm. I can hear the developers saying that the "problem" mentioned can not happen in that of environemnt. Are they correct or can someone come up with a simple t-sql script that forces the problem to happen in any sql environment?

TIA,

Barkingdog

|||

>The problem I face is that we will be running a single program or SSIS package that is the only program which updates these tables. Nothing else will update them. There are no multiple users, no server farm.

The problem I face is that I have heard this many times from many users - and yet somewhere, somehow, somewhen, the world turns out to be not quite this tidy.

If you think this will work for you, I guess I can only say good luck. But I have an uneasy feeling that in a few months time we'll be reading the "how do I rekey all my child records?" post.

Donald

|||

Thanks Donald. I will follow your sage advice!

I'm just preparing for the bruises\abuse I'm going to get from developers who will think I'm wrong and crazy on this point. I'm sure the URL's you listed will dull their bite.

Barkingdog

|||

Here's what I have learned about this issue so far:

1. This issue has always existed. It is now aggravated by multi-processor systems, server farms, and the parellelism introduced in SSIS.

2. The best thought seems to be to generate one's own counter and use it instead of relying upon an Identity field.

3. While better to use SCOPE_IDENTITY than @.@.Identity or IDENT_CURRENT, even that will not guarantee a correct parent-child linkage. (The problem is if one t-sql operaiton starts before another their is no guarantee it will complete and commit before the other. Forget sequential processing.)

4. I think the only available way to get the proper linkage is to use GUID (from NEWID()) to generate the "identity" key for tables. (But that approach won't be quick for use in JOINS unless I index the GUIDs!)

In summary, I don't think there is a GOOD solution to thie fundamental issue yet. It was mentioned that MicroSoft is working on a best-practices document regarding surrogate keys generation . I can hardly wait for it to be released!

Barkingdog

|||

Actually, I can tell you the technique I have used, and don't recollect ever having a problem with it:

1: ALL rtables must have a _natural_ key defined, and enforced as a unique index.

2: Now after inserting a row into an rtable that uses a system generated surrogate key, you can use the _natural key_ to find the row, therefore it is guaranteed to be the correct row, and retrieve whatever surrogate system generated key was inserted in that row. Obviously there is a performance price to pay to getting the right answer, but typically that's better than a fast wrong answer...

3:now you can use that _guaranteed correct_ surrogate key in further processing as needed.

It has been many years since I have built an rtable that did not enforce uiqueness based on a _natural_ key. It is the only way to go...

Last Function

Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature?

Example:

Item_Code Time
123456 11:40
123456 11:41
123456 11:42

By grouping on item code and using the last function on the Time column, the third record would be returned.

Item_Code Time
123456 11:42

All, help is appreciated.Last is not as reliable as you may think, and SQL Server doesn't have it. Try Max instead.|||well, i really cannot use max because it will give me a max each cloumn. For example:

Item Date Time
12345 7/10/06 11:21
12345 7/10/06 11:45
12345 7/12/06 09:45

If i group on item and use max for date and time i would get

Item Date Time
12345 7/12/06 11:45

I basically need to find a way o get the last record of a set.

Maybe there is a better way to approach this.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.

I think that would probbaly be my best bet. Thanks for all your help.|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us

Sweet, I really appreciate all the help and notes.

LAST / TOP 1 / ...?

Hi,
I always thought there was a way to have the Last record of the group of
records that accorded to your query. But when I tried "LAST" this didn't
work really (althoug the Query analyzer recognized it as a valid
statement?).
I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
really be nice if there was something like a LAST and FIRST-function.
I have now:
SELECT TOP 1 c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
ORDER BY c21.ID DESC
But it would be nicer if this could be without the ORDER BY and some
LAST-function:
LAST(c21.ID), c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
Anybody knows anything about this subject? hehe :-)
Thanks a lot in advance,
PieterThat would imply that a table is ordered, which it isn't.
(Think about it, for each way to represent information, you need 4 set of
operators, read, add, remove and change. In SQL, we already have SELECT,
INSERT, DELETE and UPDATE. I don't think we want 4 more operators just to
add the concept of ordered tables.)
However, Yukon will probably have some language constructs which make paging
and stuff a bit easier to manage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eNryScgAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I always thought there was a way to have the Last record of the group of
> records that accorded to your query. But when I tried "LAST" this didn't
> work really (althoug the Query analyzer recognized it as a valid
> statement?).
> I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
> really be nice if there was something like a LAST and FIRST-function.
> I have now:
> SELECT TOP 1 c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
> ORDER BY c21.ID DESC
> But it would be nicer if this could be without the ORDER BY and some
> LAST-function:
> LAST(c21.ID), c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
>
> Anybody knows anything about this subject? hehe :-)
> Thanks a lot in advance,
> Pieter
>

LAST / TOP 1 / ...?

Hi,
I always thought there was a way to have the Last record of the group of
records that accorded to your query. But when I tried "LAST" this didn't
work really (althoug the Query analyzer recognized it as a valid
statement?).
I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
really be nice if there was something like a LAST and FIRST-function.
I have now:
SELECT TOP 1 c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
ORDER BY c21.ID DESC
But it would be nicer if this could be without the ORDER BY and some
LAST-function:
LAST(c21.ID), c21.*
FROM c21
WHERE (c21.Text LIKE '21%')
Anybody knows anything about this subject? hehe :-)
Thanks a lot in advance,
PieterThat would imply that a table is ordered, which it isn't.
(Think about it, for each way to represent information, you need 4 set of
operators, read, add, remove and change. In SQL, we already have SELECT,
INSERT, DELETE and UPDATE. I don't think we want 4 more operators just to
add the concept of ordered tables.)
However, Yukon will probably have some language constructs which make paging
and stuff a bit easier to manage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eNryScgAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I always thought there was a way to have the Last record of the group of
> records that accorded to your query. But when I tried "LAST" this didn't
> work really (althoug the Query analyzer recognized it as a valid
> statement?).
> I'm able to replace it with a "TOP 1" and an "ORDER BY DESC", but I would
> really be nice if there was something like a LAST and FIRST-function.
> I have now:
> SELECT TOP 1 c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
> ORDER BY c21.ID DESC
> But it would be nicer if this could be without the ORDER BY and some
> LAST-function:
> LAST(c21.ID), c21.*
> FROM c21
> WHERE (c21.Text LIKE '21%')
>
> Anybody knows anything about this subject? hehe :-)
> Thanks a lot in advance,
> Pieter
>

Large Truncate Performance

I have a 27 million record table that I need to truncate to free up
storage. Will this have any significat impact on what is a pretty busy 40
+GB database?Hard to tell but truncate is minimally logged and is typically extremely
fast.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?|||Truncate is a fast, nonlogged operation. It should have minimal impact =on performance. With that said, I have tried truncating a table that =large. Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all =rows in a table. It is almost always faster than a DELETE statement with =no conditions because DELETE logs each row deletion, and TRUNCATE TABLE =logs only the deallocation of whole data pages. TRUNCATE TABLE =immediately frees all the space occupied by that table's data and =indexes. The distribution pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE =remains in the database, along with its indexes and other associated =objects. The DROP TABLE statement must be used to drop the definition of =the table.
-- Keith
"Brad" <me@.privacy.net> wrote in message =news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up > storage. Will this have any significat impact on what is a pretty =busy 40
> +GB database?|||Just a minor correction. Truncate is minimal logged. This is to ensure that
we would be able to rollback a transaction. Here is a quick demo.
select *
into tmp
from Northwind..Orders
go
begin tran
truncate table tmp
rollback tran
go
select count(*) as 'tmp_cnt_after_truncate' from tmp
go
drop table tmp
go
--
-oj
http://www.rac4sql.net
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OVRBue80DHA.2460@.TK2MSFTNGP10.phx.gbl...
Truncate is a fast, nonlogged operation. It should have minimal impact on
performance. With that said, I have tried truncating a table that large.
Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all
rows in a table. It is almost always faster than a DELETE statement with no
conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs
only the deallocation of whole data pages. TRUNCATE TABLE immediately frees
all the space occupied by that table's data and indexes. The distribution
pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE
remains in the database, along with its indexes and other associated
objects. The DROP TABLE statement must be used to drop the definition of the
table.
Keith
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?