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 Is Null in Select Statement

Greetings,
I am getting the following error
"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'."
My SQL statement is:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
dbo.qry1.col5 END
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
Note: qry1 and qry2 are the same query but am join different columns to the
same column in the table
Thanks for the help
KeithSELECT ..., COALESCE(qry1.col5, qry2.col3)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||IF IS NULL doesnt exist in SQL Server. Use ISNULL(Columtocheck, ElseValue),
OR COALESCE(Columntocheck[,ColumnTocheck], ElseValue). If you wanna put an
IF / CAse Expression in your query refer to the BOL and to the syntax of
CASE, example:
CASE Somecolumn
WHEN NULL THEN 'SomeValue'[Or a cloumn]
WHEN ...
...
ELSE 'Something'
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith" <Keith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||If is a Transact SQL Control flow statement, and cannot be used inside of a
SQL Statement. What you want is the SQL Case Expression. (look it up in
Books OnLIne)
as Folows:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
Case When dbo.qry1.col5 Is Null
Then dbo.qry2.col3
Else dbo.qry1.col5 End
FROM dbo.tbl1
LEFT OUTER JOIN dbo.qry2
ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN dbo.qry1
ON dbo.tbl1.col4 = dbo.qry1.col5
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith|||Hi Keith,
The query can be re-written as
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
ISNULL(dbo.qry1.col5, dbo.qry2.col3)
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
best Regards,
Chandra
---
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith

If I could explain the problem..........

In SQL database we need to concatenate 2 fields to display in one and turn them into an email address in the following format

joe.bloggs@.company.co.uk

They are the following: forename & surname

The expression will require a . to be added between the forename & surname and @.company.co.uk at the end.

Anyone help...... ?This should work:

SELECT forename || '.' || surname || '@.company.co.uk' AS [name]
FROM [table]
[WHERE ...];

If file exist, FTP file

Within a SQL Server Job, I am using the following vbscript to connect to a
FTP server and FTP a file:
strLocalFolderName = "My Folder Name where we put the file to be FTPed"
strFTPServerName = "FTP Server Name"
strLoginID = "FTP Server Login ID"
strPassword = "FTP Login ID Password"
strFTPServerFolder = "Folder Name on FTP server where the file resides"
strFile2Get = "This file"
strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile (strFTPScriptFileName)
End If
Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine ("open " & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine ("cd " & strFTPServerFolder)
objMyFile.WriteLine ("ascii")
objMyFile.WriteLine ("lcd " & strLocalFolderName)
objMyFile.WriteLine ("get " & strFile2Get)
objMyFile.WriteLine ("bye")
objMyFile.Close
Set objFSO = Nothing
Set objMyFile = Nothing
Before I FTP the file, I need to check to see if that file exist. What is
the best way for me to check to see if that file exist? Or how can I
capture the code from the FTP command if it can't find the file?Oops, I left this out of my code example. Add this to the end of the code:
Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run ("ftp -s:" & chr(34) & strFTPScriptFileName & chr(34))
Set objShell = Nothing
"David" wrote:

> Within a SQL Server Job, I am using the following vbscript to connect to a
> FTP server and FTP a file:
> strLocalFolderName = "My Folder Name where we put the file to be FTPed"
> strFTPServerName = "FTP Server Name"
> strLoginID = "FTP Server Login ID"
> strPassword = "FTP Login ID Password"
> strFTPServerFolder = "Folder Name on FTP server where the file resides"
> strFile2Get = "This file"
> strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt"
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> If (objFSO.FileExists(strFTPScriptFileName)) Then
> objFSO.DeleteFile (strFTPScriptFileName)
> End If
> Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
> objMyFile.WriteLine ("open " & strFTPServerName)
> objMyFile.WriteLine (strLoginID)
> objMyFile.WriteLine (strPassword)
> objMyFile.WriteLine ("cd " & strFTPServerFolder)
> objMyFile.WriteLine ("ascii")
> objMyFile.WriteLine ("lcd " & strLocalFolderName)
> objMyFile.WriteLine ("get " & strFile2Get)
> objMyFile.WriteLine ("bye")
> objMyFile.Close
> Set objFSO = Nothing
> Set objMyFile = Nothing
> Before I FTP the file, I need to check to see if that file exist. What
is
> the best way for me to check to see if that file exist? Or how can I
> capture the code from the FTP command if it can't find the file?

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql

IF {ELSE IF} Construct

Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

JamesPlease comment on any deviation from standard programming that this IF ELSE IF construct may introduce. I am too novice to see it.

Cheers

James|||This should work, but your code will be more readable if you can use a CASE statement instead.

blindman|||true
but isn't IF and ELSE more efficient than CASE
I assume that because what i said is true in general programming

cheers
james|||For a single criteria IF ELSE is probably more efficient, but when you start nesting IF statements I doubt there is any difference. The db engine has to make the same logical comparisons in either case.

Evaluation of a CASE statement completes as soon as a match is found, and further criteria are not considered. I'm not sure if this is true of nested IF/ELSE statements; ie, the optimizer may evaluate the entire statement. Perhaps someone else on the forum knows how the optimizer handles this scenario.

Truth is, neither of these is a very fast operation when performed against large tables. You gotta do what you gotta do.

blindman|||Originally posted by nano_electronix
Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

James

I think this is the correct solution. Lets say "condition" refers to weekend day, a holiday or a week day flag and "TSQL" refers to three totaly diffrent queries. Your code would be resonable.

Now lets say "TSQL" is identical except for the treatment of a date column and all you want is the words "Holiday", "Weekend" or "Weekday" returned in your query, a CASE statment might be the better choice.

Clear as mud?|||agree :)

Identity_insert not happening

I am setting insert_identity to on for a table in t-sql.
The table name is passed as a parameter in the tsql procedure.

When i write the following code.
set @.setStr = 'set IDENTITY_INSERT ' + @.toTableName +' ON'
execute (@.setStr)

and then set the insert query and execute it as follows:

set @.insQuery = 'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName
execute(@.insQuery)

when i execute the procedure it doesnt insert values into the table and gives the following error though i am setting the identity to on.

Error: cannot insert explicit value for identity column in table 'emp' when IDENTITY_INSERT is set to OFF.

i cant make out why it is not applying identity_insert to the table.
Can anybody help me out.

Thank YouDon't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||it doesnt work... :(
beacuse i've to use exec to execute the sp_executesql proc.
so it gives the same result..

so i can try to make one string by putting a newline character between the following 2 strings... and then just run one string... i guess it might be possible..

'set IDENTITY_INSERT ' + @.toTableName +' ON'

and

'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName

but the problem is that i dont know how to append a newline character in the string \r \n \\r \\n dont work... can somebody suggest something on this...

Originally posted by macka
Don't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.|||Thanks for this.. i really appriciate ur help...
space works and actually newline character is char(10).. it works with this too... :)

Originally posted by macka
Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
--
Message posted via http://www.sqlmonster.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via SQLMonster.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com
>

Identity Values

Hello,
I have the Following Problem:
I have some tables on a SQL Server database that have primary keys
without the identity property.
This was necessary for importing data from old databases...
Now I want to change some primary key columns to be an Identity.
Of crourse the identity seed should be set to a value higher than the
highest existing value in the column.
I can do that easily with the Enterprise Manager, but how can I do that
with transact sql statements?
Regards FerdinandMake the changes in the table designer of EM but don't save them yet. Then
look on the toolbar for the button that is 3rd from the left. It will show
you how EM makes the changes.
Andrew J. Kelly SQL MVP
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys without
> the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand|||Why would you destroy a perfectly good primary key by replacing it with an
identity column?
I know there are different schools of thought on this one, but I have always
found a logical primary key based on the actual values in the table to be
far more intuitive than an identity field, which is little more than an
artificial row number in my book. Having keys based on real values makes
joining to other tables far easier, even if slightly more typing and storage
is used in the process.
From a programming and maintainability perspective, I would stick with the
original primary keys.
Of course, this is just my opinion. Some folks consider an identity field
to be a requirement on every table. I avoid using them as a general rule.
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys
> without the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand

Identity range managed by replication is full and must be updated by a replication agent.

Hello,

I'm getting the following error message when I try add a row using a
Stored Procedure.

"The identity range managed by replication is full and must be updated
by a replication agent".

I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )

sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

For Testing:

I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.

I've checked the current Identity values in the Agent Table:

DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.

I check the Table to make sure there will be no conflicts with the
primary key:

SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.

Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:

Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80

In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?

The Identity range for this table cannot be exhausted! I'm not sure
what to try next.

Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:

Quote:

Originally Posted by

I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

IDENTITY on a BitMap column?

Consider the following table
CREATE TABLE Attributes( id int identity(1,1),
name varchar(100),
mask bigint)
Now, attribute mask will be a bit mask of the attribute name. Consider the
data:
id name mask
-- -- --
1 foo 1
2 bar 2
3 mann 4
4 frau 8
5 kein 16
6 alles 32
How do I put a trigger on this table, or something so that I do not have to
worry about the mask when I insert data? Inserting 1 record at a time would
be no problem, just get the MAX of mask and double it. But how to handle
two or more inserts at a time? Should I use an instead-of-trigger?
Sorry about the poor english.
Dieter"Dieter Katzenland" <deiter@.rrtc.com> wrote in
news:#e8DYffjDHA.2964@.tk2msftngp13.phx.gbl:
> How do I put a trigger on this table, or something so that I do not
> have to worry about the mask when I insert data? Inserting 1 record
> at a time would be no problem, just get the MAX of mask and double it.
> But how to handle two or more inserts at a time? Should I use an
> instead-of-trigger?
hi,
in this case it would be enough to let the mask empty on inserting and then
use an AFTER INSERT Trigger for calculating the mask.
--
best regards
Peter Koen
--
MCAD, CAI/R, CAI/S, CASE/RS, CAT/RS
http://www.kema.at|||Assuming that your multi-row INSERT originates from a table or query:
CREATE TABLE foo (name VARCHAR(10) PRIMARY KEY)
INSERT INTO foo VALUES ('Alpha')
INSERT INTO foo VALUES ('Beta')
INSERT INTO Attributes (id, name, mask)
SELECT COUNT(*)+
(SELECT MAX(id) FROM attributes),
A.name,
POWER(2,COUNT(*))*
(SELECT MAX(mask) FROM attributes)
FROM foo AS A
JOIN foo AS B
ON A.name >= B.name
GROUP BY A.name
--
David Portas
--
Please reply only to the newsgroup
--

identity insert issue

I am trying to update an identity column with a new value. I am doing the
following:
set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.
What am I doing wrong? Thanks in advance."sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@.microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.
You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.
--
David Portas|||On Nov 27, 3:37 am, sharman <shar...@.discussions.microsoft.com> wrote:
> I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?

Identity Field

Folks

I am inserting some values into a table with the following stmt

Insert into table(number,name) values ('12','name')

In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.

ie Select @.@.identity from table (any condition??)

How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.

Thanks for the help,There is only one @.@.identity tracked for any given connection to MS-SQL (each spid). To retrieve its value, you just select it (no table needed). Something like:DECLARE @.id INT
INSERT INTO HHGtable (theAnswer) VALUES (43) // whatever
SELECT @.id = @.@.identity-PatP|||Pat

Thanks for the idea. BTW I have a question can I use

select Max(ID) from table

So that It gives only the maximum value and it would be same value
when the record is inserted? I am just asking your suggestion. Is that logically correct??

Thanks for the help,|||I would think so, but only as long as the tables next identity never is reset to a lower value (dbcc checkident is able to do so).

Identity columns

I have been using the following query to identify the IDENTITY columns
in a given table. (The query is inside an application.)

select column_name
from information_schema.columns
where table_schema = 'user_a' and
table_name = 'tab_a' and
columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1

This works. When "user_a" performs the query, everything is OK.

Now, another user wanted to use the same application. So, "user_b"
clicks on a button, and the exact same query as above is run. (No
substitutions are made; user_b is trying to see the identity column in
[user_a].[tab_a]). However, the query returns null, instead of the
identity column name. User_b can read the table and select from it
just fine.

Why am I getting two different results against the same query? Do I
need to rewrite the query to go against different information schema
views?You might try specifying the table schema in the OBJECT_ID function to avoid
ambiguity. Also, consider quoting the identifiers:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'user_a' AND
TABLE_NAME = 'tab_a' AND
COLUMNPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
COLUMN_NAME, 'IsIdentity') = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

<newtophp2000@.yahoo.com> wrote in message
news:1103334367.788877.229370@.f14g2000cwb.googlegr oups.com...
>I have been using the following query to identify the IDENTITY columns
> in a given table. (The query is inside an application.)
> select column_name
> from information_schema.columns
> where table_schema = 'user_a' and
> table_name = 'tab_a' and
> columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1
> This works. When "user_a" performs the query, everything is OK.
> Now, another user wanted to use the same application. So, "user_b"
> clicks on a button, and the exact same query as above is run. (No
> substitutions are made; user_b is trying to see the identity column in
> [user_a].[tab_a]). However, the query returns null, instead of the
> identity column name. User_b can read the table and select from it
> just fine.
> Why am I getting two different results against the same query? Do I
> need to rewrite the query to go against different information schema
> views?|||Thanks, Dan! This works great.

Dan Guzman wrote:
> You might try specifying the table schema in the OBJECT_ID function
to avoid
> ambiguity. Also, consider quoting the identifiers:
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_SCHEMA = 'user_a' AND
> TABLE_NAME = 'tab_a' AND
> COLUMNPROPERTY(
> OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
> COLUMN_NAME, 'IsIdentity') = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||Dan Guzman wrote:
> You might try specifying the table schema in the OBJECT_ID function
to avoid
> ambiguity. Also, consider quoting the identifiers:
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_SCHEMA = 'user_a' AND
> TABLE_NAME = 'tab_a' AND
> COLUMNPROPERTY(
> OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
> COLUMN_NAME, 'IsIdentity') = 1

Hi Dan,

As I noted before, this works; however, it seems that it doesn't do the
right thing if the databases are different.

So, my question is, given a database, a table, and a column (along with
dbo/table owner), is there a way to check whether or not that column is
the identity for that table? Is it possible to generalize the above
query to work across databases/users/etc.?

Thanks!

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||(newtophp2000@.yahoo.com) writes:
> As I noted before, this works; however, it seems that it doesn't do the
> right thing if the databases are different.
> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?

SELECT *
FROM db..sysobjects o
JOIN db..syscolumns c ON o.id = c.id
JOIN db..sysusers u ON o.uid = u.uid
WHERE o.name = @.tbl
AND c.name = @.col
AND u.name = @.user
AND c.status & 0x80 <> 0

will return a row if the column is an identity column.

When I wrote this query, I assumed that I was on undocumented ground,
but this value is actually documented for syscolumns.status, and thus
permissible to use. The code should work in SQL 2005 as well. (Although
SQL 2005 also offer new catalog views which are better for the task.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?

You can specify the desired database context with a USE statement
immediately before the SELECT to set the database context.

To return data from different databases in the same query, you'll need to
use the technique Erland suggested and use a UNION ALL to concatenate
results from different databases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<newtophp2000@.yahoo.com> wrote in message
news:1108137110.964647.268740@.o13g2000cwo.googlegr oups.com...
> Dan Guzman wrote:
>> You might try specifying the table schema in the OBJECT_ID function
> to avoid
>> ambiguity. Also, consider quoting the identifiers:
>>
>> SELECT COLUMN_NAME
>> FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_SCHEMA = 'user_a' AND
>> TABLE_NAME = 'tab_a' AND
>> COLUMNPROPERTY(
>> OBJECT_ID(
>> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
>> COLUMN_NAME, 'IsIdentity') = 1
> Hi Dan,
> As I noted before, this works; however, it seems that it doesn't do the
> right thing if the databases are different.
> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?
> Thanks!
>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>