Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >

Does scope_identity or @@identity always return NULL on Subscriber

I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does NEWID() affect my insert performance?

I have a SQL script, which is run during installation, as I want a
particular table to be populated initially. For this particular table I
have around 4000 recs that need to be populated. The script first
deletes all the rows in the table by the following statement:
Delete from [dbo].[Table1]
GO
Then there are 4000 following insert statement is as follows:
INSERT INTO [dbo].[Table1]
(Field1, Field2,Field3,Field4,Field5,Field6,Field7)
VALUES
(NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
GO
Since I have started using this script, my installer takes considerably
more time ....I cannot do a bulk insert using CSV because I am using
NEWID().
Is there a workaround?
Thanks.
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegrou ps.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
>
The problem with using NEWID() like this is that the values are very
unordered, and so the index inserts get scattered all over. SQL Server 2005
introduces NEWSEQUENTIALID() to address this problem.
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
But 4000 isn't a ton of rows in any case, and should take less than 10
seconds either way.
David
|||Thanks David, But I am using SQL 2000. Does anyone know, a way to do
this without changing the schema of the table?
Thanks
|||I agree with David that there should be no noticeable difference using
NEWID() with only 4000 rows. You mentioned that your installer takes
considerably longer but are you certain that it is this script that is the
cause?
Hope this helps.
Dan Guzman
SQL Server MVP
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegrou ps.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>
|||You're change is probably because you are doing single inserts instead of
bulk insert. You might see if doing a truncate instead of delete buys you
enough to get your performance back.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegrou ps.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>

Does NEWID() affect my insert performance?

I have a SQL script, which is run during installation, as I want a
particular table to be populated initially. For this particular table I
have around 4000 recs that need to be populated. The script first
deletes all the rows in the table by the following statement:
Delete from [dbo].[Table1]
GO
Then there are 4000 following insert statement is as follows:
INSERT INTO [dbo].[Table1]
(Field1, Field2,Field3,Field4,Field5,Field6,Field
7)
VALUES
(NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
GO
Since I have started using this script, my installer takes considerably
more time ....I cannot do a bulk insert using CSV because I am using
NEWID().
Is there a workaround?
Thanks."Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field
7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
>
The problem with using NEWID() like this is that the values are very
unordered, and so the index inserts get scattered all over. SQL Server 2005
introduces NEWSEQUENTIALID() to address this problem.
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
But 4000 isn't a ton of rows in any case, and should take less than 10
seconds either way.
David|||Thanks David, But I am using SQL 2000. Does anyone know, a way to do
this without changing the schema of the table?
Thanks|||I agree with David that there should be no noticeable difference using
NEWID() with only 4000 rows. You mentioned that your installer takes
considerably longer but are you certain that it is this script that is the
cause?
Hope this helps.
Dan Guzman
SQL Server MVP
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field
7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>|||You're change is probably because you are doing single inserts instead of
bulk insert. You might see if doing a truncate instead of delete buys you
enough to get your performance back.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field
7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>

Does NEWID() affect my insert performance?

I have a SQL script, which is run during installation, as I want a
particular table to be populated initially. For this particular table I
have around 4000 recs that need to be populated. The script first
deletes all the rows in the table by the following statement:
Delete from [dbo].[Table1]
GO
Then there are 4000 following insert statement is as follows:
INSERT INTO [dbo].[Table1]
(Field1, Field2,Field3,Field4,Field5,Field6,Field7)
VALUES
(NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
GO
Since I have started using this script, my installer takes considerably
more time ....I cannot do a bulk insert using CSV because I am using
NEWID().
Is there a workaround?
Thanks."Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
>
The problem with using NEWID() like this is that the values are very
unordered, and so the index inserts get scattered all over. SQL Server 2005
introduces NEWSEQUENTIALID() to address this problem.
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
But 4000 isn't a ton of rows in any case, and should take less than 10
seconds either way.
David|||Thanks David, But I am using SQL 2000. Does anyone know, a way to do
this without changing the schema of the table?
Thanks|||I agree with David that there should be no noticeable difference using
NEWID() with only 4000 rows. You mentioned that your installer takes
considerably longer but are you certain that it is this script that is the
cause?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>|||You're change is probably because you are doing single inserts instead of
bulk insert. You might see if doing a truncate instead of delete buys you
enough to get your performance back.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pratham" <pratham17@.gmail.com> wrote in message
news:1144101429.666024.8280@.z34g2000cwc.googlegroups.com...
>I have a SQL script, which is run during installation, as I want a
> particular table to be populated initially. For this particular table I
> have around 4000 recs that need to be populated. The script first
> deletes all the rows in the table by the following statement:
> Delete from [dbo].[Table1]
> GO
> Then there are 4000 following insert statement is as follows:
> INSERT INTO [dbo].[Table1]
> (Field1, Field2,Field3,Field4,Field5,Field6,Field7)
> VALUES
> (NEWID(),'0', '0', 'Test text','User1',GETDATE(),'Test Text')
> GO
> Since I have started using this script, my installer takes considerably
> more time ....I cannot do a bulk insert using CSV because I am using
> NEWID().
> Is there a workaround?
> Thanks.
>

Does format file for bulk insert allow mix of native and character format?

I tried to place this question to the .Net framework Data Access and Storage forum and got no answer, so I am trying to move it on this forum.

So I have a module which require me to import big amount of data. I believe that the native format data files with format files will be the most efficient way of implementation. I am trying to programmatically produce a BCP like exported files of native format(it means without type conversion) from tables with nullable and nonnullable values.I prefere to be able to not produce computed or identity or rowguid fields,so I need format files.

I don't have problems producing different kinds of int or float( which are the majority of fields) or char or nchar fields.

Problems are emerging with the datetime or smalldatetime or decimal fields because I don't know how to convert to them from the strings or from the CLR types.

So I trying to find a way to find a native format of those fields or to find if a bulk insert will accept mixed format files with some of the fields in the native format without field terminators and some with field terminators or to use char format with field terminators only plus maybe format files.

So if the answer to above question is positive I can partially resolve the problem, if negative I will have to use the character format.

Unless you can educate me on the convertion to the SQL server internal formats of datetimes and decimals from the CLR types.

See SQL Server 2005 Books Online topics:

SQL Server Data Types and Their .NET Framework Equivalents
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
Specifying File Storage Type by Using bcp
http://msdn2.microsoft.com/en-US/library/ms189110.aspx

Data Type Conversion (Database Engine)
http://msdn2.microsoft.com/en-us/library/ms191530.aspx

Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...

Does any one have similar problem with nvarchar column storing chinese characters?

I am running into some wired problem with nvarchar(4000). When I insert the
chinese characters, the text got cut off. The following two sections shows
what happen, the first chinese section is what is ended up in the database,
the second section (separate by "--") show the complete
parameter value before IDbCommand.ExecuateQuery() is called. It is less
than 4000 bytes. It seems a chunk of text got loss somewhere.
No sure what happen here. But it seems to be a serious problem. My full
text search certainlly wouldn't work if I can't get the data into the
column. Is this a bug in 2005 CTP or something I did wrong ? Thanks
--Xin Chen
?...?? - ? - ? - ?... ? - ?Q - ?... ?
- ? - ?... ? - ? ? > ? > ? ??
?:2005?03?23?03:56?:????
?
????
???
??1937?12?13?27???,??
??,?,??
"? ?...?? - ? - ? - ?... ? - ?Q - ?... ?
? - ? - ?... ? - ? ? > ? > ? ??\n
?:2005?03?23?03:56?:????
?\n???? \n \n??
? \n ??1937?12?13?27???,?
???,?,???,??
????,??30??
?,???,??,??,??,?
???1938?3?24?,????,?
??2?31? ??,?27?,?
??,?31??,??28?,?
?31?,??29?,?2??,?
??,?2???31?,???
?4?3?,????,?
???,??,???
6?,???7?,???
?,???????
?,???,?4.6?,?7500?
??,??1????
??,??? ?\n ??
??\n ?? ????
???\n??? ??
\n?? ??\n??3-26
? ??\n?? ??\n?
?? ??\n?:? ?<?
?> \n ? ??"?"??:? ?
? ? \n -- ?? ChinaRen - ? - ? - ?
? - ? - ? - ? - ? - ?? - About SOHU -
?\nCopyright 2005 Sohu.com Inc.All rights reserved."
how are you inserting these characters? OpenRowset?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:ulgv5WANFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I am running into some wired problem with nvarchar(4000). When I insert
the
> chinese characters, the text got cut off. The following two sections
shows
> what happen, the first chinese section is what is ended up in the
database,
> the second section (separate by "--") show the complete
> parameter value before IDbCommand.ExecuateQuery() is called. It is less
> than 4000 bytes. It seems a chunk of text got loss somewhere.
> No sure what happen here. But it seems to be a serious problem. My full
> text search certainlly wouldn't work if I can't get the data into the
> column. Is this a bug in 2005 CTP or something I did wrong ? Thanks
> --Xin Chen
> ?...?? - ? - ? - ?... ? - ?Q - ?... ?
> - ? - ?... ? - ? ? > ? > ? ??
> ?:2005?03?23?03:56?:????
> ?
> ????
> ???
> ??1937?12?13?27???,??
> ??,?,??
> ----
--
> --
> "? ?...?? - ? - ? - ?... ? - ?Q - ?... ?
> ? - ? - ?... ? - ? ? > ? > ? ??\n
> ?:2005?03?23?03:56?:????
> ?\n???? \n \n??
> ? \n ??1937?12?13?27???,?
> ???,?,???,??
> ????,??30??
> ?,???,??,??,??,?
> ???1938?3?24?,????,?
> ??2?31? ??,?27?,?
> ??,?31??,??28?,?
> ?31?,??29?,?2??,?
> ??,?2???31?,???
> ?4?3?,????,?
> ???,??,???
> 6?,???7?,???
> ?,???????
> ?,???,?4.6?,?7500?
> ??,??1????
> ??,??? ?\n ??
> ??\n ?? ????
> ???\n??? ??
> \n?? ??\n??3-26
> ? ??\n?? ??\n?
> ?? ??\n?:? ?<?
> ?> \n ? ??"?"??:? ?
> ? ? \n -- ?? ChinaRen - ? - ? - ?
> ? - ? - ? - ? - ? - ?? - About SOHU -
> ?\nCopyright 2005 Sohu.com Inc.All rights reserved."
>
|||no, I am using a store proc and call it via ADO.NET in my app
I store my text in the nvarchar(4000) as string, I am not using openrowset.
the stored proc use regular insert into (...) values (...)
--Xin Chen
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:exEotkANFHA.2468@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> how are you inserting these characters? OpenRowset?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Xin Chen" <xchen@.xtremework.com> wrote in message
> news:ulgv5WANFHA.1176@.TK2MSFTNGP15.phx.gbl...
> the
> shows
> database,
complete
> ----
> --
>

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Documented Bug in SQL Server 2005?

I set up a WMI alert in SQL2K5, which invokes a response launching a job.
The alert is AUDIT_LOGIN_FAILED. The response job is to insert the contents
of the token $(WMI(TextData)) into a table.
The job step text looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('$(WMI(TextData))', '''', ''''''))
The error looks like:
Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near 'bubba'.
[SQLSTATE 42000] (Error 102).
The profile trace looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('Login failed for user 'bubba'.
[CLIENT: <local machine>]', '''', ''''''))
I have tried this with and without a REPLACE. The result is the same. Is
this documented? Is there a workaround?
Thanks for all input.
John T (JohnT@.discussions.microsoft.com) writes:
> I set up a WMI alert in SQL2K5, which invokes a response launching a job.
> The alert is AUDIT_LOGIN_FAILED. The response job is to insert the
> contents of the token $(WMI(TextData)) into a table.
> The job step text looks like:
> INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
> VALUES('AUDIT_LOGIN_FAILED', REPLACE('$(WMI(TextData))', '''', ''''''))
> The error looks like:
> Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near
> 'bubba'. [SQLSTATE 42000] (Error 102).
> The profile trace looks like:
> INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
> VALUES('AUDIT_LOGIN_FAILED', REPLACE('Login failed for user 'bubba'.
> [CLIENT: <local machine>]', '''', ''''''))
> I have tried this with and without a REPLACE. The result is the same. Is
> this documented? Is there a workaround?
Documented? Sort of. On
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-
f849e5fc4d43.htm
it says:
SQL Server Agent replaces an exact value for the token. Your job steps
must take this into account and correctly quote the tokens you use.
Bug? Certainly quite a serious design flaw in my eyes. There is no way you
can save this situation with replace(). As I understand it, SQL Server
Agent pastes in the value of the token, and it's your job to put that
value in a syntactic correct context. Which is quite an uphill battle in
this case. SQL Server Agent would need to provide to means to expand a
token: as-is and as-string-literal. Say that the syntax for the latter
would be $$(WMI(TextData)). That would expand the string to
N'Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near
''bubba''. [SQLSTATE 42000] (Error 102).'
An alternative would be that ' were not expanded as such but as some
innocent character, for instance `.
The workaround you can employ is to say SET QUOTED_IDENTIFIER OFF in
the script. Then you can say:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', "$(WMI(TextData))")
But that would not be waterproof, since you would get the same syntax
error for a message that includs a ".
I would suggest that you submit a bug/suggestion for this on
http://lab.msdn.microsoft.com/productfeedback/. If you submit it as
a bug, it may be closed as "By Design". I would not expect this to
be fixed in SQL 2005, as it would be a feature change. Then again,
since there is a potential source for SQL injection here, maybe there
is cause for alarm. Feel free to include to suggestions I've given here
in your submission on the Feedback Centre.
Disclaimer: I have very little experience of working with tokens in
SQL Server Agent, so I may have missed something obvious. (Which is
why I don't want to submit any bug/suggestion myself.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx