I am developing an integration process between two databases. One of
them is a SQL Server 2000 and the other is using MSDE 2000. The
integration process is done in C# (VS2003).
The main database is the SQL Server, the MSDE will contain a really
small subset of the data found on the main. To help diminish the amount
of time taken to develop an integration process between those
databases, the same structure are found on both side. The only
difference, when I insert data in the MSDE from the SQL Server, I set
the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.
I can insert one set of data without problem, but from there, if I try
again, I will always receive the "Cannot insert explicit value for
identity column in table ... when IDENTITY_INSERT is set to OFF." I
saw on Microsoft website the article ID 878501; I noticed I was using
MSDE sp3, I upgraded to SP4... and I still have the problem.
I know, when I call the update function on the sqldataadapter, the
adapters contain the IDENTITY_INSERT ON and it's set to OFF after the
insert. The "Cannot insert..." error is the only one I received.
Can anyone help me on that issue? Take note that this approach was
used because of customer requirements; the size of the database also
causes some problem (over 200 tables) and we decided to use the same
structure on both side to minimize the support time.
I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.although in my table I set IDENTITY_INSERT on (master Key)Please help me
Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click Dim addclient As New newclient() newclient.ClientID = 0 Dim result As DialogResult result = addclient.ShowDialog If result = DialogResult.OK Then
Dim Client As clients_and_sitesDataSet1.ClientsRow Client = Clients_and_sitesDataSet1.Clients.NewClientsRow Client.ClientID = addclient.ClientID Client.Clientname = addclient.Clientname Client.Street_and_number = addclient.Street_and_number Client.Zipcode = addclient.Zipcode Client.Place = addclient.Place Client.Phone = addclient.Phone Client.Email = addclient.Email Client.Contact_person = addclient.Contact_person Clients_and_sitesDataSet1.Clients.AddClientsRow(Client) ClientsTableAdapter.Update(Client)------------------------------problem MessageBox.Show("New Client is saved") Else MessageBox.Show("User cancelled operation") End If addclient = Nothing End Sub
Code in VB (2005 express edition)
Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line. when i try to run the program, i get an exeption
Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF
how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out.
And please give me specific guide lines on how to put in the code and code lines. I tried using SET IDENTITY_INSERT Clients ON But than I get all errors bout things not being declared, set not being supported anymore, things like that.
Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add @PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256) as insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
I am trying to insert a new record into a sql table and I get the following error message:
Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'tbl_MLS_ReportCriteria' when IDENTITY_INSERT is set to OFF.
If a column is set to allow nulls I know that a constraint can be used to supply a default (i.e. GetDate() ) when no value is provided but what about when an explicit NULL is provided in an INSERT or UPDATE statement?Is there any way other then an AFTER trigger to substitute a value for an explicitly provided NULL? In other words, assuming that dtAsof is a NULL enabled column, is there any way to over ride what the following will do to MYTABLE:
If there's no way to do this in SQL Server 2008R2 then what about later versions of SQL Server? Do any more recent versions have a way to deal with this? We have a third party app that uses a SQL Server back end and many of the tables have columns for storing audit like data such as date/time but many are left to NULL values and I'd really like to fix that in as passive a way as possible so as to not break the app that uses the database. I know a constraint with a default can be sued to over ride a null but not when a null is explicitly provided.
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
after i have inserted a row into the DB i am trying to get the last identity like this : Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", ) Dim myReader2 As SqlDataReader myReader2 = myCommand2.ExecuteReader() where myConnection2 is the connection objectafter i did this i dont underdsand how do i get the 'Identity' value?i tried myReader2 ('Identity') but no luckany 1 can helpthnaks in advancepeleg
Let me start off by saying I have posted this on: comp.databases.ms-sqlserver
My apologies, try not to do that again.
I have a table that I am trying to insert into with data from another table. Here is that Query:
INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice, customnumber3, customnumber4) Select partno, name, description, weight,price, qtyprice, qty From Import2
This seems to work fine.
My dilemma has to do with an 'id' column in that item table. This is incrementally updated by one..ie Natural Key. I have set it to Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But for reasons that have to do with a front end admin tool used by the home office I can't set this column to have an Indentity property. Screws up the insert done with the admin tool.
This is a item/price database by the way. Name of item, price, description, qty price..etc.
So I've tried to put this trigger on the item table CREATE TRIGGER auto_fill ON [dbo].[item] FOR INSERT, UPDATE AS BEGIN declare @maxc int set @maxc = (select Max(id) from item) set @maxc = @maxc +1 Select 'id' AS '@maxc', notorderable = '0', createdon = getdate(), createdat = getdate(), createdby = 'Steve', modifiedon = getdate(), modifiedat= getdate(), modifiedby = 'Steve'
END
But the Insert Into statement (see above) will not work...giving the error "Cannot insert the value NULL into column 'id', table 'new_Catalog1v1.dbo.item" Which I know...that's why I set it to Identity..but that cannot be.
So the question is how to set an autonumber (or natural key or I'm not sure of the name) that updated from the max(id)table when inserting from another table.
And then...one more.
I have to update a table named UNIQUEIDS with the lastest value of the id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted into the id column for a number of tables. Here is another trigger I put on the item table to update the UNIQUEIDS table.
create trigger upUniqueIds_item on [dbo].[item] for update,Insert, delete as begin Select @@Identity as 'id' from inserted Update uniqueids set id = @@identity Where tablename = 'item' End
But of course this doesn't work if I can't set the columns to IDENTITY.
I hope someone can help and I hope my explanation had made sense. Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?
i have a question: i have a table that has an identity id column called pId and also a column called ParentPid. now my question is that i want to insert the value of pId into the ParentPid when i'm adding a new Property to a table. any idea? thanks
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost)How do i obtain this value and how would I supply it to the second INSERT statement?
Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it. The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false". When the page is viewed, I insert a record into the dB: Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;") If Not IsPostBack Then AddTrack() End If End Sub Sub AddTrack()
Dim myCommand As SqlCommand Dim insertTrack As String insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn) myCommand.Connection.Open() Try myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack Catch ex As SqlException tempTxt.Text = ex.Number.ToString()
End Try myCommand.Connection.Close() End Sub And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1". The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance! <script runat="server"> Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting e.Command.Parameters("@house").Size = 5 End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Dim house = e.Command.Parameters("@house").Value Response.Write(house) End Sub Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) End Sub Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub </script> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>" oninserted="SqlDataSource1_Inserted" oninserting="SqlDataSource1_Inserting" DeleteCommand="DELETE FROM [household] WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@housenum, @streeraddr, @aptnum, @city, @state, @zipcode, @HHPhone, { fn NOW() }); SELECT @house = SCOPE_IDENTITY()" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [household]" UpdateCommand="UPDATE [household] SET [housenum] = @housenum, [streeraddr] = @streeraddr, [aptnum] = @aptnum, [city] = @city, [state] = @state, [zipcode] = @zipcode, [HHPhone] = @HHPhone, [timedate] = @timedate WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" OnSelecting="SqlDataSource1_Selecting"> <InsertParameters> <asp:Parameter Name="housenum" Type="String" /> <asp:Parameter Name="streeraddr" Type="String" /> <asp:Parameter Name="aptnum" Type="String" /> <asp:Parameter Name="city" Type="String" /> <asp:Parameter Name="state" Type="String" /> <asp:Parameter Name="zipcode" Type="String" /> <asp:Parameter Name="HHPhone" Type="String" /> <asp:Parameter Type = "String" Name="house" Direction= "Output"/> </InsertParameters> Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?
I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.
I'm nesting a bunch queries, the parent being a select, and the children being inserts. I'd like to retain the auto generated IDs from the original table, and insert them into the new tables (into identity fields). I believe that there is a command that I can use to temporarily turn identity auto numbering off for the current query - can anyone help me with this?
hi to the group, i am small problem, i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc) with out using identity(sql server)/generated always(db2) can any one knows please explain it
hi to the group, i am small problem, i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc) with out using identity(sql server)/generated always(db2) can any one knows please explain it bye
I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.
I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001
when i try to do that iam getting an error "An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON. " even when I turned IDENTITY_INSERT ON for TableA
