How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>

How do I identify a command in a job step?

Hi
I'm having a job that runs a number of steps. One of them runs the CmdExec
"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
how do I find out which one it is? I've looked a bit around to see if I can
find a table where I can find the number and then maybe see the name of the
job, but with no luck.
Can any of you help with this?
Regards
SteenWhen the DTSRun command uses /~Z, it means the command to
run the DTS package is encrypted. Run a trace or Profiler
when the job runs. This will allow you to get the package
name or package guid.
-Sue
On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Hi
>I'm having a job that runs a number of steps. One of them runs the CmdExec
>"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
>how do I find out which one it is? I've looked a bit around to see if I can
>find a table where I can find the number and then maybe see the name of the
>job, but with no luck.
>Can any of you help with this?
>
>Regards
>Steen
>|||Ok...found some more info about it in BOL (...just have to look for the
right thing..:-)..). The /Z argument apparently tells that the command line
is encrypted. That's fine, but...I still need to find out which command it's
running. Any change of doing that?
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:O5Z2ZETVEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm having a job that runs a number of steps. One of them runs the CmdExec
> "DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> how do I find out which one it is? I've looked a bit around to see if I
can
> find a table where I can find the number and then maybe see the name of
the
> job, but with no luck.
> Can any of you help with this?
>
> Regards
> Steen
>|||Thanks Sue
I've just tried to run a trace, but I must admit that my knowledge in
running traces might not be good enough, since I can't find any references
in there that indicates which package is being run.
Are there any easy or "obvious" data to look for to find out which package
is being executed?
Regards
Steen
"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
> When the DTSRun command uses /~Z, it means the command to
> run the DTS package is encrypted. Run a trace or Profiler
> when the job runs. This will allow you to get the package
> name or package guid.
> -Sue
> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
> <SPE@.REMOVEdatea.dk> wrote:
> >Hi
> >
> >I'm having a job that runs a number of steps. One of them runs the
CmdExec
> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> >how do I find out which one it is? I've looked a bit around to see if I
can
> >find a table where I can find the number and then maybe see the name of
the
> >job, but with no luck.
> >
> >Can any of you help with this?
> >
> >
> >Regards
> >Steen
> >
>|||Steen,
Capturing SQL:StmtCompleted or SP:Completed will give you
TextData values along the lines of:
exec msdb..sp_get_dtspackage N'YourPackage', null, null
-Sue
On Fri, 18 Jun 2004 15:26:26 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Thanks Sue
>I've just tried to run a trace, but I must admit that my knowledge in
>running traces might not be good enough, since I can't find any references
>in there that indicates which package is being run.
>Are there any easy or "obvious" data to look for to find out which package
>is being executed?
>Regards
>Steen
>"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
>news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
>> When the DTSRun command uses /~Z, it means the command to
>> run the DTS package is encrypted. Run a trace or Profiler
>> when the job runs. This will allow you to get the package
>> name or package guid.
>> -Sue
>> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
>> <SPE@.REMOVEdatea.dk> wrote:
>> >Hi
>> >
>> >I'm having a job that runs a number of steps. One of them runs the
>CmdExec
>> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
>but
>> >how do I find out which one it is? I've looked a bit around to see if I
>can
>> >find a table where I can find the number and then maybe see the name of
>the
>> >job, but with no luck.
>> >
>> >Can any of you help with this?
>> >
>> >
>> >Regards
>> >Steen
>> >
>