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 ranges

Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:

> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>

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

Identity insert on

After setting identity_insert on for a table is there any way by which I can insert multiple or a range of records at a time?From where?

INSERT INTO myTable SELECT * FROM myOtherTable?

Or do you mean From a file?

BULK INSERT INTO myTable FROM 'C:\TEMP\newdata.dat'|||I mean from any table between a range of data.|||I mean from any table between a range of data.|||Well the INSERT INTO should do it with a predicate (WHERE clause)

Your next statement will be...

How do I get the middle 500 rows...

Yes?