IF NOT EXISTS problem

Hi,

I am migrating a project from SQL to SQL Compact Edition and the following statement keeps failing in the CE project:

Code Snippet

IF NOT EXISTS (SELECT * FROM Court2 WHERE BookingDate = '2007-05-28') INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52) ELSE UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')

It works fine in SQL.

I've done a fair bit of searching for the solution and it appears my syntax is not perfect but I can't see where.

Any suggestions.

Here's the error that CE produces:

Code Snippet

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Thanks,

Glen.

Hi Glen,

IF.. ELSE is not available in SQL CE, as only a subset of the full T-SQL grammar is supported by SQL CE,

so you will have to do this in code, like:

Get a DataReader with

Code Snippet

SELECT * FROM Court2 WHERE BookingDate = '2007-05-28'

and perform your insert or update depending on the result of this.

For documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

|||

Thank you.

I'm now working on the next issue:

Unfortunately I'm only a hacker and learning the hard way .. Is there somewhere that I can go that lists the differences and workarounds for the CE that I can read.

Cheers.

|||See SQL CE Compact Edition BOL, - for documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

IF NOT EXISTS

Hello,

I am trying to create a table if one with the same name does not exists. My code is:

Dim connectionStringAsString ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PensionDistrict4.mdf;Integrated Security=True;User Instance=True"Dim sqlConnectionAs SqlConnection =New SqlConnection(connectionString)Dim newTableAsString ="CREATE TABLE [" + titleString +"Comments" +"] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"

sqlConnection.Open()

Dim sqlExistsAsString ="IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString +"Comments" +"')"Dim sqlCommandAsNew SqlCommand(newTable, sqlConnection)If sqlExists =TrueThen

sqlCommand.Cancel()

Else

sqlCommand.ExecuteNonQuery()

sqlConnection.Close()

EndIf

I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated.

Thanks Matt

your sql Exists is just a string. so your code ofIf sqlExists =TrueThen doesnt make any sense. You need to execute it to find out if a table with the name exists. Alternatively its better to query sysobjects to find out if the table exists.

SELECT * FROM ssyobjects WHERE [Name] = '...' AND xtype = 'u'.

I'd recommend using a stored proc for this, so you can query the sysobjects to see if the table already exists and if it does not then create it else either drop and recreate ot exit appropriately.

IF inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

If Else Statement in a Update Trigger

I am trying to build an update trigger to check the condition if checkbox is true then add 1 year, else add 3 years. The code works before i added the if checkbox = true.

Original Working code:

IF NOT UPDATE (EDITED)

UPDATE drvisit

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Code I am trying to use with an IF Statement:

IF NOT UPDATE (EDITED)

UPDATE drvisit

IF Checkbox = True

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Else

Set nextvisit = dateadd (yy, 3, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Use a case statement instead of "IF"
This example assumes "checkbox" is a bit field...

IF NOT UPDATE (EDITED)

UPDATE drvisit
SET nextvisit = case when checkbox = 1 then dateadd (yy, 1, lastaudio) else dateadd (yy, 3, lastaudio) end
FROM Apt
WHERE rec# IN (SELECT rec# FROM inserted)

|||Sweet thanks.. If i did want to do and if statement how would I? On a few occasions I tried it and could never get it to work.|||

Glad to be of help.

"IF" is really only for controlling the flow of execution of your SQL script. It can't be used within a single SQL statement (like UPDATE) to conditionally apply a value.

If Else problem

I am trying to implement the correction for daylight savings

hence I have the code as

IF DATEPART(yy,GetDATE())>= 2007
{SET @.i=......................

new code

}

[ELSE

{ SET...

old code

}]

This gives me teh error as

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

What is going wrong here

thanks

kiran

Hello Kiran,

I think you need to have something like this:

IF DATEPART(yy,GetDATE())>= 2007
BEGIN
... New Code ...
END
ELSE
BEGIN
... Old Code ...
END

Hope this helps.

Jarret

|||Try running your query in Query Analyzer or Management Studio before using it in Reporting Services. You will get more helpful error messages that will help you debug your query.|||

thank you guys,

I will use query analyzer from now on..

If Else problem

I am trying to implement the correction for daylight savings

hence I have the code as

IF DATEPART(yy,GetDATE())>= 2007
{SET @.i=......................

new code

}

[ELSE

{ SET...

old code

}]

This gives me teh error as

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

What is going wrong here

thanks

kiran

Hello Kiran,

I think you need to have something like this:

IF DATEPART(yy,GetDATE())>= 2007
BEGIN
... New Code ...
END
ELSE
BEGIN
... Old Code ...
END

Hope this helps.

Jarret

|||Try running your query in Query Analyzer or Management Studio before using it in Reporting Services. You will get more helpful error messages that will help you debug your query.|||

thank you guys,

I will use query analyzer from now on..

If Else problem

I am trying to implement the correction for daylight savings

hence I have the code as

IF DATEPART(yy,GetDATE())>= 2007
{SET @.i=......................

new code

}

[ELSE

{ SET...

old code

}]

This gives me teh error as

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

What is going wrong here

thanks

kiran

Hello Kiran,

I think you need to have something like this:

IF DATEPART(yy,GetDATE())>= 2007
BEGIN
... New Code ...
END
ELSE
BEGIN
... Old Code ...
END

Hope this helps.

Jarret

|||Try running your query in Query Analyzer or Management Studio before using it in Reporting Services. You will get more helpful error messages that will help you debug your query.|||

thank you guys,

I will use query analyzer from now on..

If Condition In Select Statement...

Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.

SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROM tblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId

Thanks,
Rahul JhaIt might help if you checked for the correct syntax in Books Online...
Use a CASE statement int he SELECT clause:

SELECT tblCustomer.Customer_LegalName,
case tblCustomer.IsNRACustomer
when TRUE then tblCustomer.Customer_PassportNo
else ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)
end AS TAXID,
...

...but you are still going to have to define what "TRUE" is. What datatype is IsNRACustomer?|||Thanks Blindman :-)

Idle Timeout, Orphan Timeout help

Here's the DB Connection code for a site (Web.config file)
I was wondering how I could specify an idle timeout or orphan timeout.
I tried idle timeout=x and kept getting errors

<add key="DBConn"
value="server=localhost;Trusted_Connection=true;database=SaltwaterFishing;Min Pool
Size=5;Max Pool Size=150;"></add
Thank youHere is the documentation describing allowed options for a SQL Connection.|||Thank you

IDENTITY_INSERT Problems

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8). (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.
[vbcol=seagreen]
[vbcol=seagreen]
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
This works fine. Then, I attempt to allow insertion into the ID_Field.
[vbcol=seagreen]
This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset
[vbcol=seagreen]
[vbcol=seagreen]
Last, I am attempt to add a record to the recordset with an explicit ID,
[vbcol=seagreen]
[vbcol=seagreen]
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/default...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
> >>> r = win32com.client.Dispatch('ADODB.Recordset')[vbcol=seagreen]
Ugh, have you considered using an INSERT statement, or calling a stored
procedure that uses an INSERT statement?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi drs,
I didn't check all your code, but I noticed that the Field_2 column you use
is NOT NULL, and in the code you post you don't insert a value in this
column. I.e. the code as you have posted it will fail because of this.
Jacco Schalkwijk
SQL Server MVP
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (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.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> 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/default...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
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e8ssHj1cEHA.2616@.TK2MSFTNGP11.phx.gbl...
> Ugh, have you considered using an INSERT statement, or calling a stored
> procedure that uses an INSERT statement?
Yeah, using an INSERT statement failed in the same way.
-d
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi drs,
> I didn't check all your code, but I noticed that the Field_2 column you
use
> is NOT NULL, and in the code you post you don't insert a value in this
> column. I.e. the code as you have posted it will fail because of this.
Just the example code I posted, not the real code that wouldn't run.
-d
|||Why would you post dummy code and not "the real code that wouldn't run"?
http://www.aspfaq.com/
(Reverse address to reply.)
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gb6057novl208@.corp.supernews.com...
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote
> in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> use
> Just the example code I posted, not the real code that wouldn't run.
> -d
>
|||> Yeah, using an INSERT statement failed in the same way.
Can you show your new code that fails? The REAL code, not stuff you make up
on the fly?
http://www.aspfaq.com/
(Reverse address to reply.)
|||IDENTITY_INSERT is only active for particular transaction. Even if you turn
IDENTITY_INSERT ON it doesn't mean that you can then insert duplicate values
into the a IDENTITY column. If you have a constant need to insert values
into an IDENTITY column then I suggest that you re-think your database
design.
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (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.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> 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/default...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
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNrAMv3cEHA.3128@.TK2MSFTNGP11.phx.gbl...
> Can you show your new code that fails? The REAL code, not stuff you make
up
> on the fly?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
Sure, after the line[vbcol=seagreen]
I tried
[vbcol=seagreen]
-d
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNJuBv3cEHA.1888@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Why would you post dummy code and not "the real code that wouldn't run"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
> message news:10gb6057novl208@.corp.supernews.com...
By "dummy code" I mean that I left out the line where I added a value for
the NOT NULL field -- a perfectly reasonable thing to do as actually leaving
out this line would not cause an error until calling the Update() function.
Further, I changed the dsn to take out my real name, etc. Oh, and the
CREATE statement was shortened so you did not need to read about all of the
fields I was creating which had no relevance to this question, and there
were times when I tried a create statement which did not contain a field
which was NOT NULL. Last, since Python is interactive (the >>> prompts
indicate that I was doing this from the command line) I actually tried over
a hundred different things. What I posted was reasonable example code which
did indeed not work, and which was the closest I could come in a short
amount of space to demonstraiting what I though might work, but which
didn't. It really does not work. It does not fail, however, due to some
problem other than my lack of understanding how the IDENTITY_INSERT command
works. That is to say, the NOT NULLness of a field, or the altered dsn does
not make my question more difficult to understand. I am sorry that my short
response did not elucidate this point more clearly.
Really, what I am looking for is an example of something that does work. I
have been unable to find one online.
So far, no one seems to think my code should work, but why is still a
mystery to me.
-d

Identity Specification limit

what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?

the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers

information will be appreciated

David Wilson.

Hi,

When the maximum has been reached, an error will be generated. Here is an example:

--CREATE TABLE IDENTITYTEST
--(
-- ID TINYINT IDENTITY(1, 1),
-- TEXTVALUE VARCHAR(50)
--)

DECLARE @.COUNTER INT
SET @.COUNTER = 0

WHILE @.COUNTER < 260
BEGIN
INSERT INTO IDENTITYTEST(TEXTVALUE) VALUES ('VALUE ' + CAST(@.COUNTER AS VARCHAR(3)))
SET @.COUNTER = @.COUNTER + 1
END

/*
RESULT:
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

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

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

right, I did about the same thing (not quite as elegant :-)) - with the same result

the question is - How do you fix it? and how do you code something into a daily checkup routine or the like to find it and fix it before it happens?

IDENTITY Problems Updating ZipCode Table

I am having problems updating my zip code table that contains zip, city, state, long, lat, ect..

I have the latest CSV file, I tried to use the import wizard in SQL Server 2000 Enterprise Manager.
I set the ID field as <ignore> and specified the appropriate columns for the rest of the data matching from CSV to already designed and working zip code table. Also I checked the box that said "Delete Rows in Destination Table" as well as "Enable Identity Insert" was checked

I ran the wizard, and now I have empty table and it will not insert any records because the error said that the identity column can not accept NULL.

What do I do? I am not updating the identify column so Is it telling me it can't insert NULL into ID?

Any suggestions...

Thanks,
LitoI set the ID field as <ignore>
...
as well as "Enable Identity Insert" was checked
...
error said that the identity column can not accept NULL.

You are inserting NULL into the ID field, because you have "<ignore>" selected for the ID column, and "Enable Identity Insert" checked. You need to uncheck "Enable Identity Insert" and this should work.|||For the 5th Time I repeated the process and this time i did not check "Enable Identity Insert" and it worked.

Sory to bother you, I was just getting frustrated with this stupid problem|||That's Ok... I think the reason that most of us hang out here is to give folks a hand (and occaisionally make the others say "Doh, why didn't I think of that!"). As long as life is good now, that is all that counts!

-PatP

IDENTITY ON CRETE TABLE HELP PLEASE!

Below is the snippet of code that matters. Currently Figure 1 works great. I need to make Figure 2 work. Notice the Identity Seed vaue I need changed. Any ideas?

FIGURE 1:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY (1, 1),

Figure 2:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY ((SELECT NBRCOLUMN FROM TABLE WHERE BLAH = 'BLAH'), 1),

I need this desperately...
Thanks in advance.

JoeWhat exactly is the business requirement here?

If you needed to know, at run time, what the seed was, you could always create a dynamic sql string and exectute it.

But I have no idea why you would need to do this...|||I have to perform a query on a table which will retrieve a 4 digit number. This 4 digit number will become the identity seed for the purpose of this script. Once the data has been imported and the script completes, we will reverse the process and remove the Identity value on the column. Does that help?

Joe

identity inserts

Hey All,

I was trying to use a typed dataset to create a very simple DAL. I found that the code generated for the INSERT statement includes an identity field the table has. That can obviously never work (unless identity_insert is set, which it is not). My question is whether it is possible to control this insert statement generation? Is there a property I am missing somewhere? My solution was to change the INSERT statement on the DataTableAdapter, but that seems awkward for me to have to do that..

Thanks,

Yuval

From SQL side you can turn on an option to enable identity inserts to a table:

SET IDENTITY_INSERT ONmyTable

Then you can insert values specifying columns and identity value:

INSERT INTOmyTable (id,name) VALUES(100, 'Iori')

Note: this is a session option, which means you have to turn on this option for every connection you want to insert identity values. For more information about this option, please refers to

http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_7zas.asp?frame=true

|||In my post, I actually said that this is not what I am trying to achieve. The problem is that the dataset generates an insert statement that includes the identity column and I have to edit it to not do that.|||Sorry for misunderstood you:)I've no idea of your issue, waiting for right answer...

Identity column in temp table

Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...

SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table'#User', which already has column 'UserId' that inherits the identityproperty.
Is there any way to work around this?
Thanks for your help.
You'll have to explictly create the #User table with your 2 columns and then INSERT INTO it.

Identity and SqlDataSource Question

I'm trying to update my e-commerce approach from ADO-heavy code to a more modern approach based on the SqlData Source. I also want to move away from Stored Procedures for the moment, if I can, though I may return to them later (mainly for educational purposes at the moment).

In the past I used @.@.Identity in a stored procedure to return an ID# which I passed on to the end user as their "order number". Unfortunately this approach seems to only apply to SPs.

In short, what's the best way to handle this using SqlDataSource and minimal ADO code?

Just to add a bit more detail, I've got basically three tables, in a fairly obvious relationship. Orders stores the main order info (Customer's name and address, total, etc), OrderDetails lists the line items, and Products contains detail on the items.

(Put another way, the auto-generated tags in the SqlDataSource object either don't include the OrderID param because it's toggled for Identity in the database, or if I toggle Identity off then I don't know how to trigger it to toggle the next number in sequence. And either way I don't know how to feed that information back to the program.)

Thanks!

Well no sooner than I posted the above than I found one way to do it, which is using the SQL function Ident_Current, which sends back the last used Identity number in the table, like this:

Select Ident_Current('Orders')

But it requires writing several lines of ADO code, e.g.:

Dim conGetInvNum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyDB").ConnectionString)
Dim cmdInvoiceNumber As New System.Data.SqlClient.SqlCommand("Select Ident_Current('Orders')", conGetInvNum)
conGetInvNum.Open()
OrderNumber = cmdInvoiceNumber.ExecuteScalar
conGetInvNum.Close()

Which is not exactly laborious, but it does mean writing ADO code. Is there an easier, more 2.0-oriented/SqlDataSource-oriented way to do this?

|||

I think that is dangerous to use in a multi-user application.

From the SQL Documentation:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
|||

I appreciate the reply; that greatly clarified things.

Unfortunately that would remove the Insert handling from the SqlDataSource. I couldn't see a way to do that within the purvue of the DS. I tried this (note bolded text:

<asp:SqlDataSource ID="DSOrders" runat="server" ConnectionString="<%$ ConnectionStrings:SimpsonsDB %>" DeleteCommand="DELETE FROM [Orders] WHERE [OrderID] = @.OrderID"InsertCommand="INSERT INTO [Orders] ([UserID], [Total], [OrderDate]) VALUES (@.UserID, @.Total, {fn NOW()}); SELECT SCOPE_IDENTITY();" SelectCommand="SELECT [OrderID], [UserID], [Total], [OrderDate] FROM [Orders]" UpdateCommand="UPDATE [Orders] SET [UserID] = @.UserID, [Total] = @.Total, [OrderDate] = @.OrderDate WHERE [OrderID] = @.OrderID">
<DeleteParameters>
<asp:Parameter Name="OrderID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="Total" Type="Decimal" />
<asp:Parameter Name="OrderDate" Type="DateTime" />
<asp:Parameter Name="OrderID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="Total" Type="Decimal" />
<asp:Parameter Name="OrderDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>

And then launched it with this:

OrderNumber = DSOrders.Insert()

I realize that's not an "ExecuteScalar" but that Method isn't available for the DS, and unsurprisingly I got an error that suggested that it wasn't running the command. This suggests to me that this approach just isn't accomodated. (In other words, there's no way to return a value from an Insert command using SqlDataSource.) But that still seems unlikely to me -- it's a fairly obvious thing for them to have included. So I must be missing something.

Any further thoughts would be appreciated.