If Nothing Selected Into Variable What Is The Value Of Variable?

DECLARE @.PayTypeValue numeric (18, 5)
SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)
IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?
NULL OR 0DECLARE @.PayTypeValue numeric (18, 5)

SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?

NULL OR 0

why don't you try it and see for yourself:

SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue|||Also, try
SET @.PayTypeValue = 42
SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue

and see what happens.|||i thought it would return NULL.....|||But it returns...

The answer would be useful for people with similar questions ;)|||The answer is 42.
If the query returns no rows, the variable keeps its old value.|||Ahh that's clever - kinda like having an "else" value.
Cheers ivon|||hmmm...

That could be dangerous if you were using that variable in a loop.
I would wrap the SQL in ISNULL()

set @.somevariable=ISNULL((select somevalue from sometable),-9999999)
If @.somevariable=-9999999
'no rows were returned
Else|||That could be dangerous if you were using that variable in a loop.

How so? It retained it's initially declared value...
If you looped round, @.PayTypeValue would equal 42 each time..?|||Yes, but if you were expecting the value to change with each iteration...
What is returned from the select statement is also dependant on
@.compdiv and @.loadpay, so if those values change, it's quite possilbe that @.paytypevalue would change, too.|||Yes, but if you were expecting the value to change with each iteration...
That's how I found out; I got some very peculiar results.

BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

Solutions I'm using are
- Setting the variable to a default value before filling it with the query,
- Checking @.@.ROWCOUNT to see if any rows were returned from the query.|||I think it all depends on how you put it together.

declare @.tmp integer
set @.tmp=42
set @.tmp=isnull((select 18 where 1=0),0)
select @.tmp

Returns 0, which is what I'd expect, since
SELECT 18 WHERE 1=0
returns no rows|||How do I use @.@.ROWCOUNT to do this?

I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.|||RedNeckGeek: I now see that you put the isnull around the entire query.

tdecker81:
Something like
SELECT @.l_var = value
FROM table
WHERE <conditions>

SELECT @.l_records = @.@.ROWCOUNT

IF @.l_records > 0
BEGIN
' Do stuff with @.l_var
END|||Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
set nocount on

declare @.MyValue int
set @.MyValue = 42

--This method retains @.MyValue
select @.MyValue = id from sysobjects where 1 = 0
select @.MyValue as 'Unchanged!'

--This method sets @.MyValue to NULL
set @.MyValue = (select id from sysobjects where 1 = 0)
select @.MyValue as 'Set to NULL!'

Learn something new every day...|||Learn something new every day...

my problem is I forget 2 somethings every day, so I think I'm losing the battle...

(most of it is stuff my wife tells me so I guess it's ok)

:)|||I'm sure you're wife will be there to constantly remind you too.sql

If not CURSORS ?

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@.here.now) with the next sequential value (i.e. smith.j02@.here.now)

Any takers?

Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.Concatenate the datetime(getdate()) down to 1/1000 second. I am sure it will be unique. That's most of the spam mailers do when they create a fake ID to get around your blocking.|||Better yet, concatenate newid(). That will guarantee you a unique value all the time.|||Originally posted by joejcheng
Better yet, concatenate newid(). That will guarantee you a unique value all the time.

It also has to be sequential, not just unique|||You can use substring and max functions to achieve the same sequentially.|||You can't do this with sequential values if you insist on the stipulation that a record can be removed from the database and readded at another time. Even using a cursor to find out that smith.j02@.here.now, smith.j03@.here.now, and smith.j04@.here.now already exist, there is simply no way to know whether smith.j05@.here.now was not previously created and deleted. You have to store the used values permanently somewhere.|||...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...|||Unfortunately it is possible that more than one new entry can be made in the same import. (i.e. smith.j exists and two more smith.j are imported).

The list of historic values are being saved in a seperate table as described without the "EMailIncrement int" field. I had considered your exact solution but did not know how to increment 2 newly added values with different increments.

Originally posted by blindman
...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...

Unfortunate|||Use a cursor in combination with the table of historical values.

If Isnull Question

I have a SP I am trying to set a value for a variable in. The default value
for the variable is Null.
If there is not parameter passed I want to do an If Isnull statement to set
the variable in the SP, except I keep recveiving an incorrect Syntax error.
If any one can suggest a fix I would greatly appreciate it.
Here is my statement
If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
'Citi Special Servicing', 'HUD601SalomonDeal3'))
Thanks
-ChrisYou should look up the IF statement in Books Online, also look at how
variables should be checked for null values.

> If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
> 'Citi Special Servicing', 'HUD601SalomonDeal3'))
Is this a part of a query? Then you should also look up the CASE expression
in Books Online.
After you're done with BOL, check out this recent thread:
http://msdn.microsoft.com/newsgroup...5f-b8940be08178
I'm guessing you want something like that.
ML
http://milambda.blogspot.com/|||you want "if @.Client is null. "
isnull is a function which will replace a null occurence with a value
"@.Client is null" is using the is keyword, which will do an equality
check(ish - null != null).
beware not to use if @.Client = null as this will not work.sql

If in current month

I need to do this but can't find the syntax:

IIf(Fields!EnteredDate.Value is in current month , Cint(Fields!PC.Value), CInt(0))

I think theres a month, day and date function that you can use for this.|||

Failing that something like:

IIF(DateAdd("m",0,Fields!EnteredDate.Value) = DateAdd("m",0,now()), Cint(Fields!PC.Value), CInt(0))

DateAdd(<level to extract>, <Point in time>, <set>)

I'm not sure about the current date function. Isn't there a Global parameter which gets the system current date!?

|||

=iif(Month(Fields!EnteredDate.Value) = Month(Today), CInt(Fields!PC.Value), CInt(0))

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp

Similar to the Month() function, there are also other functions available in the VB Runtime Library - such as Year(), Day(), etc.

-- Robert

|||How can I restrict dates that are in this month and year. For example if today is 2/20/2006, I do not want that EnteredDate included. 2/20/2005 is acceptable though...|||

solved my second question:

=IIF(Month(Fields!EnteredDate.Value) & Year(Fields!EnteredDate.Value) <> Month(Today) & Year(Today),Cint(Fields!PDC.Value),0)

If Exists capture value returned from stored proc

I have a stored proc with a query which checks whether an identicalvalue is already in the database table. If so, it returns a value of 1.How do I caputure this value in an asp.net page in order to display amessage accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE...
)
Return 1
Else
...INSERT INTO... code here.Did this exact thing for someone already in the past 2 weeks, search the forums.|||

Motley wrote:

Did this exact thing for someone already in the past 2 weeks, search the forums.

Thanks, will do so.sql

If Else

All,

looking for help with outputting different text value into a cell.

e.g. a column of a table will only ever have two values, Y & N.

I am looking to populate a CELL within SSRS with Yes or No using a seperate Dataset.

SELECT 'Yes' AS [LookupValue], 'Y' AS LookupCode UNION
SELECT 'No', 'N'

This gives me my two column to lookup which is what I want. However, i need this to populate a cell with Yes instead of Y.

I have tried using it as a parameter but to no avail.

Anyone got any ideas?

Duncan,

Have you tried using the Switch statement instead?

=Switch(Fields!LookUpCode.Value = "Y", "Yes", Fields!LookUpCode.Value = "N", "No")

Hope this helps.

Jarret

IF construct in DTS

Hi All,

I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
Main = DTSTransformStat_OK

Thanks in advance.

Regards,
Sathishtry the code below, use the line breaks I have...

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC_transport") = NULL
Else
DTSDestination("ABC_transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK|||Hi,

Thanks for the reply.

I tried it, still it loads only the NULL and not the values.

Regards,
Sathish|||Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Originally posted by rokslide
Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Okie,... well there are 1000 non N/A records, 5159 N/A records and 1 null,... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?|||Hi,

That was pretty fast. I am getting loaded with 6159 NULLs.

Regards,
Sathish|||using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code...

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("Renewal Month") = DTSSource("Renewal Month")
If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC transport") = NULL
Else
DTSDestination("ABC transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK
End Function

I get it with values where they are meant to be...

Wanna try this as a test to try and locate your problem?|||Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

Thank you very much for the help.

Regards,
Sathish|||No worries. Let me know if you need more help, sorry I couldn't solve your problem.|||The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.|||By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison...

Also, wouldn't that also mean that my test transformation wouldn't work where it did work?

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

IDENTITY_INSERT is set to OFF

I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.

I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.

Any help would be most welcome. Many thanks in advance

Looks like you are trying to insert a value into a column that has been defined as IDENTITY column? Is that right?|||You need to change the Identity Insert mode. Have a read ofthis article it should explain what is going on.|||

Yes the column is set as IDENTITY. After a bit more reading I think that the issue is with the explicit naming of the identity column. I don't believe that I am explicity defining the field just as @.Trader_ID.

|||Thanks for this, I would like to insert the field without speciying it so that it will increment automatically. The solution in the doc seems to specify the record to be added to the identity field.

identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".
> Thanks,
> Subbu.
>

identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".

> Thanks,
> Subbu.
>

IDENTITY value copy in INSERT statement

Hello,
I would like to insert the value of an identity column into an other field
during the same insert statement and not by using a trigger.
Sample Table
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
Sample statements NOT working but to indicate what I would like to do
INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
Is this possible and if yes, could you please inform me how?
Thanks in advance,
RemcoWithout using a trigger (error handling omitted):
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
DECLARE @.ID int
BEGIN TRAN -- these next two data operations should be atomic
INSERT INTO Test(DocumentParentID) VALUES (NULL)
SET @.ID = @.@.IDENTITY
UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
COMMIT
INSERT INTO Test(DocumentParentID) VALUES (@.ID)
I dont know of a way to acheive this inline using identity.
Mr Tea
"Remco" <rembo_r@.hotmail.com> wrote in message
news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>|||I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then use
@.@.identity
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> Without using a trigger (error handling omitted):
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
> DECLARE @.ID int
> BEGIN TRAN -- these next two data operations should be atomic
> INSERT INTO Test(DocumentParentID) VALUES (NULL)
> SET @.ID = @.@.IDENTITY
> UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
> COMMIT
> INSERT INTO Test(DocumentParentID) VALUES (@.ID)
> I dont know of a way to acheive this inline using identity.
> Mr Tea
> "Remco" <rembo_r@.hotmail.com> wrote in message
> news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
field
>|||thanks for the tip :)
Mr Tea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6mVLN1FFHA.3972@.TK2MSFTNGP15.phx.gbl...
>I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then
>use
> @.@.identity
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> field
>|||Why does the document reference itself as its own parent? Typically an
adjacency list hierarchy in a table looks like this:
CREATE TABLE Documents (documentid INTEGER NOT NULL PRIMARY KEY,
parent_documentid INTEGER NULL REFERENCES Documents (documentid))
The root nodes of the tree then have NULL as the parent_documentid. If
you use IDENTITY as the key then will need either a trigger or an
INSERT followed by an UPDATE to populate a self-referencing parent id.
David Portas
SQL Server MVP
--|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||Try using Ident)Seeed()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
"Remco" wrote:

> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>
>|||IDENT_SEED returns seed value, not the identity column value.
If you have a column defined as IDENTITY(1,1), The IDENT_SEED
Function will return 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
> Try using Ident)Seeed()
> as in
>
> INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
>
> "Remco" wrote:
>|||I tested it with a newly created table, and it returned a '1', which was
both the seed and the value to be inserted... <gr>. It's Ident_Current()
That is needed here.
Try using Ident_Current()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Current('Test') + 1)
"Roji. P. Thomas" wrote:

> IDENT_SEED returns seed value, not the identity column value.
> If you have a column defined as IDENTITY(1,1), The IDENT_SEED
> Function will return 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
>
>sql

Identity seed/increment of MS SQLSERVER 2000 and MSDE

How can I change the increment value of an identity column?
I absolutely need to set a new value for the "increment"
DBCC CHECKIDENT only allow me to change the increment.
ALTER COLUMN does not allow altering identity
Is there a system column i can update or ?
I read few post suggesting using the enterprise manager to do so... but
we got lots of tables with many levels and fk. it's almost impossible in
our situation.
In fact we got many offline databases that where supposed to be
identity(-1,-1) but I just get a surprise!
Tons of data are alrealy inserted (positively) by a "kind of"
replication. but locally created records should be negative.
Thanks a lot
*** Sent via Developersdex http://www.developersdex.com ***Hi
You cannot update an IDENTITY property. Create another non_identity column
and move all data (of identity values) to the column.
You will be able to update a new created column and later on delete an
IDENTITY column
"Vincent" <anonymous@.devdex.com> wrote in message
news:Oq%23ev9A9HHA.600@.TK2MSFTNGP05.phx.gbl...
> How can I change the increment value of an identity column?
> I absolutely need to set a new value for the "increment"
> DBCC CHECKIDENT only allow me to change the increment.
> ALTER COLUMN does not allow altering identity
> Is there a system column i can update or ?
> I read few post suggesting using the enterprise manager to do so... but
> we got lots of tables with many levels and fk. it's almost impossible in
> our situation.
> In fact we got many offline databases that where supposed to be
> identity(-1,-1) but I just get a surprise!
> Tons of data are alrealy inserted (positively) by a "kind of"
> replication. but locally created records should be negative.
> Thanks a lot
>
> *** Sent via Developersdex http://www.developersdex.com ***

Identity seed lost...?

Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:

> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--

Identity Seed

Can I change the value of a column's identity seed programmatically? If
so, how?

Thanks in advance.William Ortenberg <billort@.pacbell.net> wrote in message news:<v%FYa.2724$yF6.589@.newssvr22.news.prodigy.com>...
> Can I change the value of a column's identity seed programmatically? If
> so, how?
> Thanks in advance.

You can use DBCC CHECKIDENT with RESEED, but the new value is not
permanent. If you truncate the table, the seed will return to the
original value specified when you created the table.

If you want to change the seed value permanently, then you'll have to
recreate the table with the new seed specified in the column
definition.

Simon

create table dbo.t (
col1 int identity(1,1)
)

insert into t default values
select scope_identity() -- 1

dbcc checkident(t, RESEED, 5)

insert into t default values
select scope_identity() -- 6

truncate table dbo.t

insert into t default values
select scope_identity() -- 1

drop table dbo.t|||Hi William, The SET IDENTITY_INSERT command allows explicit values to
be inserted into the identity column of a table.

There are examples in the online help.

I have used this many times for migration of legacy data into SQL

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> > what happens if IDENTITY value reaches the max value?
> You will get an overflow error.
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> > Dear All,
> >
> > what happens if IDENTITY value reaches the max value?
> >
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> >
> > Rami,
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,
> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,
|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>

IDENTITY reaches the max value

Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.

> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:

> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSE
RT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>

Identity Property

I am setting up a field with the Identity property. I need the Seed value to
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb
>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
...
compcol AS 'R' + CAST( idcol AS VARCHAR )
... );
Anith

Identity Property

I am setting up a field with the Identity property. I need the Seed value t
o
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
..
compcol AS 'R' + CAST( idcol AS VARCHAR )
.. );
Anith