Help!
I have said yes to identity for my id in some tables. I left the values of identity seed and increment as 1, so it should really start at 1 and keep increasing by 1. When I input and then decide delete a row it affects the increment seed!!!e.g. if i delete row 2, the next row should be 2 but it becomes 3!! how do i stop this???
For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739
Any ideas why this would happen?
thanks
hi,
As i have created a table with a identity column and now i am using a procedure to insert the values into that table....but when i execute it it gives an err saying err converting varchar data to int and also on the asp.net ......plz help me out for the procedure and the using it with sqlcommand in .net.
create table demo2(id int identity(1,1),name varchar(20))
create procedure sp_demo2
(@id int,@name varchar(20))as begin
insert into demo2(name) values(@name)
set @id=@@identity
end
exec sp_demo2 'j'
thanks,
rajiv
Hey all, I have been working with Northwind on SQL Server Express
Trying to learn Transact-SQL. I am stuck at this point from code in the instruction manual. I don't know if I have a permission set to something that I need or what but I get the following error that is outlined at the bottom of the code. Any help would be appreciated.
USE Northwind
GO
ALTER PROC spInsertDateValidatedOrder
@CustomerIDnvarchar(5),
@EmployeeIDint,
@OrderDatedatetime= NULL,
@RequiredDatedatetime= NULL,
@ShippedDatedatetime= NULL,
@ShipViaint,
@Freightmoney,
@ShipNamenvarchar(60)= NULL,
@ShipAddressnvarchar(40)= NULL,
@ShipCitynvarchar(15)= NULL,
@ShipRegionnvarchar(15)= NULL,
@ShipPostalCodenvarchar(10)= NULL,
@ShipCountrynvarchar(15)= NULL,
@OrderIDintOUTPUT
AS
DECLARE @InsertedOrderDatesmalldatetime
--Test to see if supplied date is over seven days old. If so
--replace with NULL value otherwise, truncate the time to be midnight.
IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
BEGIN
SELECT @InsertedOrderDate = NULL
PRINT 'Invalid Order Date'
PRINT 'Supplied Order Date was greater than 7 days old'
PRINT 'The value has been reset to NULL'
END
ELSE
BEGIN
SELECT @InsertedOrderDate = CONVERT(datetime, (CONVERT(varchar,@OrderDate, 112)))
PRINT 'The Time of Day in Order Date was truncated'
END
--create the new record
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@OrderDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
--Move the identity value from the newly inserted record into output variable.
SELECT @OrderID = @@IDENTITY
Errors received,
Msg 8101, Level 16, State 1, Procedure spInsertDateValidatedOrder, Line 46
An explicit value for the identity column in table 'Orders' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Thanks
I have a few problems, all showing up in the Reporting Services Configuration Manager, which I will try to summarize.
SERVER SOFTWARE
Windows 2003 Standard 64bit SP1
SQL 2005 Enterprise 64bit SP2
PROBLEMS
1) I have one Red "X" in Reporting Services Configuration Manager, "Web Service Identity". The ASP .NET Service Account field is empty, but I can open the rsreportserver.config file and see "<WebServiceAccount>NT AuthorityNetworkService</WebServiceAccount>". In addition, when I pick "ReportServer" from the drop-down list for both Report Server and Report Manager fields and click Apply, I get the following error:
ReportServicesConfigUI.WMIProvider.WMIProviderException: A virtual directory must first be created before performing this operation.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)
2) In the "Report Server Virtual Directory" section, the Website field is not populated but the setup worked and virtual directories were created. Why isn't it showing "Default Web Site" in this blank?
3) In the "Report Manager Virtual Directory" section, same problem as in item number 2 above.
Thanks for any help you can offer.
I'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?
Hi
I'm just wondering why the IDENTITY value is still used when you attempt
an INSERT that fails?
I placed a Foreign Key Constraint on my table. When I put bogus data in
the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used!
For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!!
why does the Identity get incremented when the Insert fails?
Angel
I'm atempting to use an Instead of Insert Trigger on a view to insert a record into a base table. The base table boundaryline has an identity primary key defined. The following is the error I'm receiving:
The column 'id1' in table 'dbo.ParcelBoundaries1' cannot be null.
Could not insert a record in the database.
The view is as follows:
SELECT IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION,
IS_CONFLICT, TYPE, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI, id1
FROM dbo.BoundaryLine
The trigger is as follows:
CREATE TRIGGER TR_ParcelBoundaries1 on ParcelBoundaries1
INSTEAD OF INSERT
as
BEGIN
INSERT INTO BoundaryLine
(IS_TAXLOT,
IS_OTHER,
IS_CARTOGRAPHIC,
IS_RAILROAD_ROW,
IS_IRRIGATION_ROW,
IS_STREET_ROW,
IS_CLOSING_ROW,
IS_CONSTRUCTION,
IS_CONFLICT,
GMPID,
BEARING,
DISTANCE_LEGAL,
DISTANCE_CALCULATED,
GEOMETRY,
GEOMETRY_XLO,
GEOMETRY_XHI,
GEOMETRY_YLO,
GEOMETRY_YHI)
Select 'YES', 'NO','NO','NO','NO','NO','NO','NO','NO', GMPID, BEARING, DISTANCE_LEGAL,
DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI
from
inserted
END
Any thoughts would be greatly appreciated?
I'm installing TFS on W2k3 R2 with MSQL2005
Finishing the TFS setup, I got this message:
"Error 29112. Team Foundation Report Server Configuration: Report Server is not properly configured or the Reporting Services Web site could not be reached. Use the Report Server Configuration tool to confirm that Report Server is configured properly and that Reporting Service Web site can be founda before running the installation again. For more information see the Team Foundation Installation Guide."
Then, I go to the SQL Report Configuration page and I see the error in Web Reports Site configuration.
But trying to configuring I got this message:
"ReportServicesConfigUI.WMIProvider.WMIProviderException: A virtual directory must first be created before performing this operation.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)"
Any idea? I created the Virtual Directorys and application pools but something I'm missing cause I still getting this same error trying to configure the Web Report Service.
Thanks!
I set up replication on my DB between two server. The Publisher will not update tables on the Subscriber that use an identity column and I get an error "8102:cannot update identity column". I have looked around and can't seem to locate any solid fixes. If I remove the identity column from the subscribers tables the package works, but I am not able to use the DB on the subscriber because I need the Identity columns for my app to function properly. My Publisher and Subscriber are both running SQL Server 2000 Standard Edition SP3.
Thanks.
Cannot insert explicit value for identity column in table 'tblUsed' when IDENTITY_INSERT is set to OFF.
:confused:
Hi,
I'd set up Transactional Replication btw 2 servers running on MSSQL 2000.
However, from time to time, I encounter Error 8102. I try to reinitialise the subscription but failed. After I delete the current subscription and PUSH a new one, it will works fine again.
I'm curious why this is happening as I'd already set all the tables (articles) in the subscriber to "YES, not_for_replication".
Any idea?
Thanks.
Hi all i have setup merge replication with one publisher and 3 subscribers
The replication worked fine for about 2 months then i recieved the following error on all my subscriptions
The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity
ranges to allocate to its own Subscribers or when an identity column data type does
not support an additional identity range allocation. If a republishing Subscriber has
run out of identity ranges, synchronize the republishing Subscriber to obtain more
identity ranges before restarting the synchronization. If a Publisher runs out of identit
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
I have updated my sql server to service pack 2
The publisher is sql Standard edition and the subscribers are Express edition
Any help would be great
Thanx
I guess there is first for everything...I had never seen error like this before
Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated.
In this case Primary Key is Identity column. I do not include Idenity column in Insert statement.
Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it.
I see this error randomly.
How should I debug it
I am trying to capture the rows that fail during an insert.
The insert is an OLE DB command component.
I have config'd errors to redirect to a flat file.
My problem is this
I need to override the identity, fine if you are using 'fast load' but then you can't capture the redirected rows.
So I am using the regular 'table or view' data access mode, but then I lose the 'keep identity' checkbox.
At this point I add in another OLE DB command "SET IDENTITY_INSERT table OFF" before the actual insert ODB component.
But now it won't validate. I changed the task 'delay validation' property to true, but still it is failing.
It seemingly cannot detect this statement.
So how then can I capture error rows from an insert where I need to switch the identity off?
[This is a one off historic load I am working on]
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
Hi,
I have a table t1 and t2 with following structure.
t1(
ID1 int IDENTITY PRIMARY KEY,
name1 varchar(20),
addr1 varchar(20)
)
t2(
ID2 int IDENTITY PRIMARY KEY,
name2 varchar(20),
addr2 varchar(20)
)
Objective here is to match name1 and name2 column using fuzzy look up. So, I used t1 as source table and t2 as reference/lookup table and mapped name1 and name2 column in Fuzzy look up editor. As output column I selected "ID2" column from t2.
Now when i run the package, it throws error
"Multiple identity columns specified for table '##FLRef_070522_14:16:39_5064_c1c6cbbd-5a54-4e36-9154-1371118f0931'. Only one identity column per table is allowed."
I suppose that during Fuzzy lookup, SSIS internally created temporary table and thats where this error occurs when adding two columns as identity. Can someone help me in resolving this issue.
Thanks
Sid
PS: I need ID2 column as output for further calculation.
Hello!
I´m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity
ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070003
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)
The default website contains an office sharepoint 2007 beta solution if that could cause the error..
Would be real grateful for some hints...
Regards
Erik B
I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.
Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.
It's like it's trying to add it's own Id to the temp table created for the tokens
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".
Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.
I am trying to move data from Access to SQL Server 2000 using DTS.
I have an Access Source and SQL Server Desitination, My destination table has a field called tableID that is not in the source. TableID is a Primary Key and an Identity column.
I have Enable Identity Insert checked in the options of the Transform Data Task.
When I execute ythe task, I get the error
"Cannot insert the value NULL into column 'TableID'. Does not allow nulls. Insert Fails.
Does anyone know why this simple task would fail?
Mike
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?
TIA,
Barkingdog
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?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedHi,
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 working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View Relatedwhen i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View Relatedi have found loads of topics on this but have yet to find one that gives the answer i need.I want to display the last insert id into a label/textbox after the INSERT functionhow do i do this?
View 18 Replies View RelatedI got two tables
one table has the fields ie table1
orderid ofd orderdate customername
where order id is autonumber
the other table2
orderid ofd product id productname
the problem here is thatif customer purchases 3 product at a time all the 3 products get the same ofd number ........and any 2 customers can have the same ofd number................ now i have to pull the order ID value from table 1 to table 2............ can somebody help with this i am the front end is asp.net amd the database is done on SQL server management studio
Hi, I was looking through this thread about @@Identity: http://forums.asp.net/p/1039145/1443971.aspx#1443971 I'm still unsure how to ue it. I have an Orders table, a Products table and a Products_Orders table. When I add an order to the Orders table I want the PK OrderID in this table to also update the FK OrderID in my Products_Orders table. I'm using SQL EXP05 and I'm in C#.
View 1 Replies View RelatedHi i want to get the naxt identity number how can i get it pls help me very urgent
View 3 Replies View RelatedI know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?
Thanks for any advice.