Sql Server Ce 3.5 Identity insert

Question

got an issue with identity columns in Sql Server CE

when using Server explorer, in VS2008, executing the following script

SET IDENTITY_INSERT testTable ON; Insert into testTable (id,name) values (1,'Something') SET IDENTITY_INSERT testTable ON;

sends the follow message error 'The Set SQL construct or statement is not supported.' but then inserts the row fine ?!?!?!

anyway, when I try to do the same thing through C#, giving that script as a command text it fails saying the error was in the "Insert key word"

I understand that against SQL SERVER CE the command only accepts one batch command at the time so in that case we have three commands (it would work with the full SQLServer) any idea?

could it be the missing ";" between "INSERT" and second "SET"? — KM., Mar 25 '09 at 18:00

score 9 · Answer 1 · edited Feb 16 '12 at 16:59

If you're using SqlCe 3.5 then this should work. However, you need to send it as two separate commands. You can't separate commands with ";" or "GO" in SqlCe. Rather, you need to do something like this:

            SqlCeConnection connection = new SqlCeConnection(connectionString);
            SqlCeCommand identChangeCommand = connection.CreateCommand();
            identChange.CommandText = "SET IDENTITY_INSERT SomeTable ON";
            SqlCeCommand cmd = connection.CreateCommand();
            cmd.CommandText = "INSERT INTO testTable (Id, column1, column2..) VALUES (10,val1,val2...)";
            try
            {
                connection.Open();
                identChange.ExecuteNonQuery();
                cmd.ExecuteNonQuery();
            }

            catch (SqlCeException ex)
            {
                //log ex
            }
            finally
            {
                connection.Close();
            }

ok, that worked just fine. tks a lot! I was trying to use one command separatelly, setting insert_identity on in my winform and that was fine but i couldn't make the insert after. But like yr example does exactly what i wanted =). cheers — Bee gud, Mar 26 '09 at 09:30

score 2 · Answer 2 · answered Mar 25 '09 at 17:55

2

TRY

SET IDENTITY_INSERT testTable ON; 
Insert into testTable (id,name) values (1,'Something');
SET IDENTITY_INSERT testTable OFF;

OR

SET IDENTITY_INSERT testTable ON
go
Insert into testTable (id,name) values (1,'Something')
go
SET IDENTITY_INSERT testTable OFF
go

answered Mar 25 '09 at 17:55

KM.

101,727
34
178
212

1

GO is a concept that only exists in the tools - the actual parser does not use them to distinguish anything. – Jason Short Apr 28 '09 at 16:58
@Jason Short, you are wrong! do the following: print 'ERROR!!!' create procedure xyz as Print 'this won''t work without a "GO"'. by adding GO after the print 'ERROR!!!', it will work – KM. Apr 28 '09 at 17:18
@KM: SQLCE does not support stored procedures anyways – Nov 23 '09 at 15:53
@Brian Wilkins, then do the same but create a view and not a procedure, you'll get the same error – KM. Nov 23 '09 at 22:07
The second option worked fine for me in SQL Server Management Studio – Ryan Kirkman Aug 02 '12 at 01:41
[`go` is not Transact-SQL](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go) and is only handled by utilities such as SSMS or SMO, as a batch separator. Try sending the second code example directly to SQL-Server through a `SqlCommand`: it fails as being invalid syntax. See [here](/q/40814/1178314) too. – Frédéric Apr 30 '17 at 15:07

Sql Server Ce 3.5 Identity insert

2 Answers2

Linked