Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Change date with trigger

I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Quote:

Originally posted by strellita
I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Needs:
INNER JOIN i.[id column] = preuba.[id column]
after your FROM statement
then it will work correctly.
Full example:
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE p SET datepm = getdate()
FROM inserted i
INNER JOIN preueba p ON i.[ID] = p.[ID]
END
GO
Peace,
tree

Change data type during INSERT INTO ?

I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?

ThanksThe destination table should be compatible with the data type of select
table

Madhivanan|||"rdraider" <rdraider@.sbcglobal.net> wrote in message
news:eRu_d.11608$C47.6368@.newssvr14.news.prodigy.c om...
> I'm doing a data conversion project, moving data from one SQL app to
> another.
> I'm using INSERT INTO with Select and have the syntax correct. But when
> executing the script I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to float.
> Is it possible to change the data type during the INSERT INTO statement?
> Thanks

As the insert into has to guess about the datatypes of the table to create,
the safest way is to specifically create the table prior to the select into.

You can use cast or convert to gain a little more control over the way the
table is created.
but since you didn't post and example I can't help you more than this.|||On Fri, 18 Mar 2005 06:48:42 GMT, rdraider wrote:

>I'm doing a data conversion project, moving data from one SQL app to
>another.
>I'm using INSERT INTO with Select and have the syntax correct. But when
>executing the script I get:
>Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to float.
>Is it possible to change the data type during the INSERT INTO statement?

Hi rdraider,

If you use
INSERT INTO tablename (col1, col2, ..., colN)
SELECT expr1, expr2, ..., exprN
FROM ...
WHERE ...

Then the result of each expression will implicitly be converted to the
datatype of the corresponding column before it's stored in the table.

Your message indicates that one of your expressions is of datetype
varchar, but the corresponding column is of datatype float, and the
implicit conversion failed. E.g. because the varchar value to be
converted happened to be 'xxhasiu'.

If you use INSERT without column list, or SELECT *, then the first thing
should be to add a column list - changes to the table structure might
change the number and order of columns in the INSERT or in the SELECT *,
inducing a mismatch; explicitly naming the columns ensures that this
won't happen.

If that doesn't fix it, then find out which column(s) in the destination
table are of datatype float; run the SELECT (without the INSERT INTO) to
see the results and inspect the output to find the offending value. If
the output has too many rows for visual inspection, you might try adding
AND ISNUMERIC(exprN) = 0
This will return only the rows where exprN can't be converted to int,
float, money or decimal.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Challenging Insert Trigger

First, please reference the following document which shows the layout of the database and tables:

http://www.eastproject.org/cody/table_layout.pdf

Here's the deal--

I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.

Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.

Have I lost you yet? Good.

Please reference the following trigger:

ALTER TRIGGER trig_UpdateForumRoles
ON CommunityServer2.dbo.aspnet_Users
AFTER INSERT
AS
DECLARE @.Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_Users
DECLARE @.UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_Type
DECLARE @.UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_Users
BEGIN

SELECT @.Username=Username, @.UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.

SELECT @.UserType=UserType
FROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIR
WHEREU.Username=@.UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID


-- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles
IF (@.UserType = 'SuperAdmin')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @.UserID
END

IF (@.UserType = 'StaffAdmin' or @.UserType='Partner')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @.UserID
END

IF (@.UserType = 'Facilitator')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @.UserID
END

IF (@.UserType = 'Student')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @.UserID
END


END
GO

However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.

Any ideas?

Thank you very much!
-Cody

First, please reference the following document which shows the layout of the database and tables:

http://www.eastproject.org/cody/table_layout.pdf

Here's the deal--

I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.

Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.)by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.

Have I lost you yet? Good.

Please reference the following trigger:

ALTER TRIGGER trig_UpdateForumRoles
ON CommunityServer2.dbo.aspnet_Users
AFTER INSERT
AS
DECLARE @.Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_Users
DECLARE @.UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_Type
DECLARE @.UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_Users
BEGIN

SELECT @.Username=Username, @.UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.

SELECT @.UserType=UserType
FROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIR
WHEREU.Username=@.UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID


-- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles
IF (@.UserType = 'SuperAdmin')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @.UserID
END

IF (@.UserType = 'StaffAdmin' or @.UserType='Partner')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @.UserID
END

IF (@.UserType = 'Facilitator')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @.UserID
END

IF (@.UserType = 'Student')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @.UserID
END


END
GO

However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.

Any ideas?

Thank you very much!
-Cody

|||

I'm afraid I don't understand your reply.

-Cody

|||You said you have to insert the correct RoleID in the aspnet_UsersInRole table, but all you issue is update statements. Update isn't what you want. Insert is.|||My apologies for the confusion --

The record will already exist in the aspnet_UsersInRoles table; I just need to update the roleID field of the appropriate record.
-Cody|||

Could be anything, try putting print statements in your trigger, then test it using transactions like:

BEGIN TRANSACTION
INSERT ...
ROLLBACK TRANSACTION

then execute that and see what messages you get. You'll need to use sql management studio or query analyzer though to get the print results.

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

CE 3.5, VS 2008, Typed Dataset: Get the updated identity of inserted row

Hello,

Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?

also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."

(Should I post this in Orcas forum?!)

Regards,

Parham.

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

|||

ErikEJ wrote:

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

Tahnks Erick.

This means that if i have inserted some rows into the table and then updated the table to database with the Update command of my Table Adapter, I should REFILL the table to getback the updated identities of the inserted rows?!

Regards,

Parham.

|||

This might help you: http://groups.google.dk/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/3422d5f0774d605f/34a537895803c758?lnk=st&q=dataset+sql+ce+identity+last+inserted&rnum=1&hl=en#

Alternatively you could use uniqueidentifier columns instead, with a new value of Guid.NewGuid() (set in your code, so you will know the value)

I will do some tetsing later today and revert if there are other options.

CDATA and SQL Server 2005

Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?
CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?

CDATA and SQL Server 2005

Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?

Cause INSERT statements without column lists to fail

Hi Hilary, Paul....
I have another real problem I hope you can help with.
Setup replication now with no problems but one!
The database tables fail from the application thats using them because of
the ROWQUID column.
I noticed that JUST before the articles are created it shows this;
SQL Server requires that all merge articles contain a uniqueidentifier
column with a unique index and the ROWGUIDCOL property. SQL Server will add a
uniqueidentifier column to published tables that do not have one when the
first snapshot is generated.
Adding a new column will:
? Cause INSERT statements without column lists to fail
? Increase the size of the table
? Increase the time required to generate the first snapshot
So is there ANY way of getting round this bug because my developer said that
they will not alter their software and the way the tables are accessed.
So, any ideas.
First you have to find out if the code does do something like this
Insert into tablename
select * from anothertablename
This is what will break with merge replication. Ask him specifically about
this. Personally good developers would never do anything like this. They
would also access the database through stored procedures which would allow
you, the dba, to modify something like this.
Another method is to have the application talk to a view which only contains
the non rowguid column. This isn't very scalable.
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
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
> Hi Hilary, Paul....
> I have another real problem I hope you can help with.
> Setup replication now with no problems but one!
> The database tables fail from the application thats using them because of
> the ROWQUID column.
> I noticed that JUST before the articles are created it shows this;
> SQL Server requires that all merge articles contain a uniqueidentifier
> column with a unique index and the ROWGUIDCOL property. SQL Server will
> add a
> uniqueidentifier column to published tables that do not have one when the
> first snapshot is generated.
> Adding a new column will:
> Cause INSERT statements without column lists to fail
> Increase the size of the table
> Increase the time required to generate the first snapshot
> So is there ANY way of getting round this bug because my developer said
> that
> they will not alter their software and the way the tables are accessed.
> So, any ideas.
|||Hi,
No it looks like they have hard coded this one particluar SQL statement.
So what acutally changes in the database that affects the table INSERT
commands?.
Is their a way round this. If there isnt then its back to square one for
me...
Thanks
"Hilary Cotter" wrote:

> First you have to find out if the code does do something like this
> Insert into tablename
> select * from anothertablename
> This is what will break with merge replication. Ask him specifically about
> this. Personally good developers would never do anything like this. They
> would also access the database through stored procedures which would allow
> you, the dba, to modify something like this.
> Another method is to have the application talk to a view which only contains
> the non rowguid column. This isn't very scalable.
> --
> 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
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
>
>
|||You say one particular SQL Statement - is it in a stored procedure? If so,
just modify the stored procedure so it contains all the column name except
the rowguid column.
ie before
insert into mytable
select * from myothertable
after
insert into mytable
select col1,col2, col3, col4,... from myothertable.
If this can't be done change the table the app writes to so it is now a
view. So if the app wrote to Mytable rename mytable as mytable and create a
view called mytable. This view will look like this create view mytable as
select col1, col2, col3, col4,... from mytable1
This doesn't scale well. How many nodes in your system? You might want to
look at bi-directional transactional replication.
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
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...[vbcol=seagreen]
> Hi,
> No it looks like they have hard coded this one particluar SQL statement.
> So what acutally changes in the database that affects the table INSERT
> commands?.
> Is their a way round this. If there isnt then its back to square one for
> me...
> Thanks
> "Hilary Cotter" wrote:
|||Hi Hilary,
I think I found the problem but I dont know how to get to it.
The dependencies show 3 objects that have a green plus sign next to them.
These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see these
anywhere in the database?.
Any ideas?
TIM
"Hilary Cotter" wrote:

> You say one particular SQL Statement - is it in a stored procedure? If so,
> just modify the stored procedure so it contains all the column name except
> the rowguid column.
> ie before
> insert into mytable
> select * from myothertable
> after
> insert into mytable
> select col1,col2, col3, col4,... from myothertable.
> If this can't be done change the table the app writes to so it is now a
> view. So if the app wrote to Mytable rename mytable as mytable and create a
> view called mytable. This view will look like this create view mytable as
> select col1, col2, col3, col4,... from mytable1
> This doesn't scale well. How many nodes in your system? You might want to
> look at bi-directional transactional replication.
> --
> 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
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...
>
>
|||Where are you seeing these green arrows?
Can you do the following for me?
select name from sysobjects where name in (PHONE_ADD, PHONE_DELETE,
PHONE_AMEND) and type='u'
GO
select object_name(id),name from syscolumns where name in (PHONE_ADD,
PHONE_DELETE, PHONE_AMEND)
GO
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
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:ACD9DC12-A2C2-4E65-83C8-3D4E39EE8109@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think I found the problem but I dont know how to get to it.
> The dependencies show 3 objects that have a green plus sign next to them.
> These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see
> these
> anywhere in the database?.
> Any ideas?
> TIM
> "Hilary Cotter" wrote:

Catching Primary Key Violation on insert error

I've read a few different articticles wrt how the handle this error gracefully.

I am thinking of wrapping my sql insert statement in a try catch and have the catch be something like

IF ( e.ToString() LIKE '%System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %')

{
lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate"

}

Is there a better way?

TIA

Dan

Find out the error number that your Exception is throwing, then trap for that particular error number. Your error might be number 2627 or 2601.

|||

Bummer-

I'm away from my asp.net enviormentt.

Um...

how do I do it?

int errorcode = ex.ToInt

if (errorcode = 123) then...

TIA

Dan

(Can't stop thinknkng about this stuff, I think I need professional help!)

|||

Catch the SqlException first, then display your message based on the returned number.

try{}catch (SqlException ex){if (ex.Number.Equals(2627)){// Display your error here}}

|||

Thanks Ed!

(Cute Kid!)

Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

catch the error on insert or update (was "need help")

hello!

im new to sql... what i'm trying to do is catch the error on insert or update statment of sql.. sound simple but please..

this is the sample table design...

tbl_Customer

CustomerID int(4) Primary AutoIncrement
CustomerCode nvarchar(25)
CustomerName nvarchar(25)
..
..
Deleted bit(1)

what i'm trying to do is when a record is deleted, it's not actually deleted
in the table but only marked 1 (true) the Deleted field.. because i don't want
to lose the relationship...

it's easy to do this on insert statement like this..

Create Procedure InsertCustomer(@.param1 ...) AS
IF NOT EXIST (SELECT * FROM tbl_Customer WHERE DELETED = 0) THEN
// do insert statement here
ELSE
// Do nothing

GO

this is also easy if i create a index constraints on the table.. but this will violate my design idea..

so anybody can help me to create the procedure in update statement and insert statementcatch what error?

can you explain what you want to do here?

Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>

Cat insert textboxes values into a database table

Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)? Or do I miss something else. Please respond as I can't find this.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Manufacturer2.aspx.cs"Inherits="Manufacturer2" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LabelID="Label1"runat="server"Text="Name"></asp:Label>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/>

<asp:LabelID="Label2"runat="server"Text="Password"></asp:Label>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit"OnClick="Button1_Click"/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="TextBox1"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"Name="TextBox2"PropertyName="Text"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

erom:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)">

user insert command not select command

InsertCommandType="Text" InsertCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)"

Hope this will help.|||

Hi u can go with the first reply which is secured one

or u can write like this

st="insert into x values(" & trim(t1.value & "," & t2.value & ")"

cmd.executenonquery

cmd is the command object

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I tried this one but it didn't work. I don't know what is happening. Again no stored data in my database. Do I also have to write something in .cs file??

Thank you for your reply

|||

erom:

o I also have to write something in .cs file??

You need to call the Insert method of the DataSource in the button click event.

SqlDataSource1.Insert()

below is the .aspx page code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" InsertCommand="INSERT INTO Manufacturer(Name, Password) VALUES ( @.T1 , @.T2 )" ConnectionString="<%$ ConnectionStrings:xxxx%>"> <InsertParameters> <asp:ControlParameter ControlID="TextBox1" DefaultValue="" PropertyName="Text" Name="T1" /> <asp:ControlParameter ControlID="TextBox2" DefaultValue="" PropertyName="Text" Name="T2" /> </InsertParameters> </asp:SqlDataSource>

|||

Unfortunately, I still can't store the values inthe table. My code is the above (for the .aspx file) - Tell me if what am I missing:

<asp:LabelID="Label1"runat="server"Text="Name"></asp:Label>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/>

<asp:LabelID="Label2"runat="server"Text="Password"></asp:Label>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit"/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"InsertCommand='INSERT INTO Manufacturer(Name, Password) VALUES (@.TextBox1, @.TextBox2)'

ConnectionString="<%$ ConnectionStrings:ConnectionString %>">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue=""Name="TextBox1"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue=""Name="TextBox2"PropertyName="Text"/>

</InsertParameters>

</asp:SqlDataSource>

And for the .cs file:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclassManufacturer2 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

}

}

|||

The page and the code looks fair enough. the only thing I suppose you are missing is the InsertCommandType=Text. This is just a small piece of try out. I'm interested in what response do you get when you press the button ? Has any error occurred or something like that?

Cast COM object error on OleDb Destination (Access 2003)

Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.

In the Script Transformation I get this error.

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.

Private sqlConn As OleDb.OleDbConnection

Private sqlCmd As OleDb.OleDbCommand

Private sqlParam As OleDb.OleDbParameter

Private connstring As String

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.ConnectionOLE

'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

connstring = connMgr.ConnectionString

sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)

End Sub

Any help would be appreciated.

The OLEDB connection manager is not using managed OleDb classes but the native OleBD interfaces. AcquireConnection returns a COM object that is created from IDBCreateSession::CreateSession call.

HTH,
Ovidiu Burlacu

|||

You are correct. The destination connection was using the Native OLE DB for Jet 4.

I created a Data Source using .NET OLE DB provider for Jet 4 and I got connected in the Script Component OK.

New Problem.

My insert OLE DB command worked fine which uses the original destination connection (Native), But my update OLE DB command using same Native connection is erroring out on each input row of the OLE DB Command component.

[UpdateRow [2727]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Numeric field overflow.".

Connection string on Destination OLE DB connection is:

Data Source=C:\Projects\Data\TSLists.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;OLE DB Services=-1;

UPDATE: The table in the Access database I am trying to update is a linked table representing a SharePoint 2003 list. What is interesting is you can push (insert) data into this table, but you can not update data in the same linked table from the same OLE DB connection in SSIS.