I have a form in ASP.NET and I want to write the values to the SQL
Server tables.
The tables are Customer and Address tables.
There will be an insert into the Customer table, and I need to use the
Identity of this inserted record for the Foreign Key in the Address
table insert.
For example
INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)
Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)
My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?
TIA
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
What I need is to make sure that in case if my Windows CE based device lost power supply my SQL Compact 3.5 database will not be corrupted and I can read data from it. Has anybody any experience in this area?
This is critical requirement for our project and I want to know it is feasible or not.
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve #1 detach the db(s) #2 copy the mdf(s) and ldf(s) to L:xxx #3 attach the db(s) again #4 zip up the db(s) #5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer. It seems we want to handle disaster recovery BEFORE we even have our first system crash.
Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:
Statistic Value -------------------------------- ------------------------ Cache Hit Ratio 100.00898 Cache Flushes 0.0 Free Page Scan (Avg) 0.0 Free Page Scan (Max) 0.0 Min Free Buffers 331.0 Cache Size 4362.0 Free Buffers 9434.0
I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.
I have a process that restores a backup from a primary server to a backup server daily. When doing the restore, sometime it fails (for various reasons).
I have coded a job to Set offline, set online, an then do the restore:
RESTORE DATABASE [xxx] FROM DISK = N'D:Backup Stagingxxx.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Sometimes it fails to bring back online, other errors as well. Is there a reliable method of doing this?
For this scenario, what is the best method of exporting data to sql 2005.
I want to export data from desktop app across internet to sql which can do on a row by row basis, but this is very slow and if the connection goes down halfway then pretty much buggered.
What is the best, reliable and fastest way to copy data across internet (several thousand rows), I have read about Bulk Insert etc... but also how would get around an upload and crashes half way, is there a way of uploading and until the whole upload goes through then the data is inserted into the database.
I have been tring to get this one line figured out for a few days now. 'Job2 Info Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM '" & CompanyKey & "'" '<<-------HERE Dim cmd2 As New SqlCommand(selectSQL2, con) 'Job2 Select Try con.Open() reader = cmd2.ExecuteReader()
(I have the full code below.) So here is the problem, this code is not populating the datagrid. There is data in the table I am selecting from. When I log in, my CompanyKey value displays in the label as "21". When I take out the "CompanyKey" variable, and just type in 21, the grid is populated. It is confusing the heck out of me. I have tried it this way: selectSQL2 = "SELECT * FROM [" & CompanyKey & "]" -and this way: selectSQL2 = "SELECT * FROM & CompanyKey and all the other ways I could think of. I researched it and can just not get it to work any way I do it. Any suggestions? Full code below: ____________________________________
Partial Class _Default Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'Job1(Info)Dim currentUserID currentUserID = Context.User.Identity.Name.ToString() Label1.Text = currentUserID Dim selectSQL1 As StringselectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')" Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReader Dim CompanyKey 'Job1 Select Try con.Open() reader = cmd1.ExecuteReader()Do While reader.Read() CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally con.Close() End Try 'Job2 Info Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM [" & CompanyKey & "]" Dim cmd2 As New SqlCommand(selectSQL2, con) 'Job2 Select Try con.Open() reader = cmd2.ExecuteReader() GridView1.DataSource = reader GridView1.DataBind() reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally ReaderResults.Text = CompanyKey con.Close()End TryEnd Sub
I posted a more detailed technical question a couple of weeks ago with Subject = "Compact Edition 2005 Subscribers - Merge Agent failed after detecting that retention-based cleanup has deleted metadata" But I got no replies.
Now my question is simpler: Is SQL Server 2005 Merge Replication reliable with Compact Edition subscribers on WindowsXP tablets?
We deleted all users' local databases a couple of weeks ago, and republished two publications with retention = 60 days. Now we have at least one user who can't sync with the server, and replication monitor shows the error below. But if she e-mails her sdf file to a support person, they can successfully sync her database. We're sure that it's not a permissions issue, which is a normal suspicion when one user can perform a task that another can not. It doesn't seem like a retention problem, because she's well inside the retention period. She can successfully sync with the publication with upload/download tablers, but not with the publication with download only tables. Both publications were created on the same day, by the same DBA, with nearly identical properties.
This problem user has operated in the past with no problem, and she's configured identically to all of the others. It just seems like random flakyness with merge replication for Compact Edition subscribers.
I feel stupid saying that, because it sounds like I believe in a magical ghost in the machine. So my question is - IS MERGE REPLICATION A LITTLE BIT FLAKY FOR COMPACT EDITION SUBSCRIBERS ON XP MACHINES?
We've applied SP2 to the server, but no subsequent HotFixes.
We're trying to sell users and management on developing a larger occasionally connected system with the same replication scheme, so we need to know if it deserves our confidence.
Error messages:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402) Get help: http://help/MSSQL_REPL-2147199402
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?
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
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 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
when 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'.
i 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?
I 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#.
I 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?
I've got a problem reading the @@identity in vb.net I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found. (--> means with rsLastIdent)
comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @@IDENTITY AS Ident;"
I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'. This where i encounter this error:
SELECT @OrderID = @@Identity /* Copy items from given shopping cart to OrdersDetail table for given OrderID*/ INSERT INTO OrderDetails ( OrderID, ProductID, Quantity, UnitCost ) SELECT @OrderID, ShoppingCart.ProductID, ShoppingCart.Quantity, Prices.UnitCost FROM ShoppingCart INNER JOIN Prices ON ShoppingCart.ProductID = Prices.ProductID WHERE CartID = @CartID
is there any way to rewrite this statement so that I can put it in the form insert()values(). ?
HiTrying to get a return value from this code, but only gets a 0. Am using SQLExpress.SqlParameter[] p = new SqlParameter[4];p[0] = new SqlParameter("@a", "aaa");p[1] = new SqlParameter("@b", "bbb");p[2] = new SqlParameter("@c", "ccc");p[3] = new SqlParameter("@d", SqlDbType.Int, 40);p[3].Direction = ParameterDirection.ReturnValue; string s = @"set nocount on INSERT INTO ABC(A, B, C) VALUES(@a,@b,@c) SELECT scope_identity()"; using(SqlConnection conn = new SqlConnection(this._connection)){ conn.Open(); SqlHelper.ExecuteNonQuery(conn, CommandType.Text, s, p); int foo = p[3].Value;}
I am trying to follow other examples I have seen on the site, and am still getting the Must declare the scalar variable "@@INDENTITY". string sqlAdd = string.Format("INSERT INTO " + siteCode + "_campaign_table (campaign_name, prod_id, type) " + "VALUES('{0}', '{1}', '{2}'); SELECT @@INDENTITY", campaignName, prodID, type); SqlCommand comAdd = new SqlCommand(sqlAdd, con); comAdd.CommandType = CommandType.Text; con.Open(); //comAdd.ExecuteNonQuery(); int identity; identity = Decimal.ToInt32((decimal)comAdd.ExecuteScalar()); lblErrorMessageAdd.Text = identity.ToString(); con.Close();
I would like to know the best way to select/maintain a sequence number in SQL Server. I've seen locking problems with using the @@identity and was wondering if there is a better way.
Several of our applications have the need to generate a sequence number that is inserted into one or several tables. In one application they have done the following ... - Created a table with a column defined with identity attribute, for example TableA ColA defined as Int with Identity checked ColB define as char(20)
- In the application, code looks like to get the sequence number: insert into tableA (ColB, 'anything'); select (@@identity) as sequence from TableA
Then the last 5 positions of sequence are used to insert into another table. Problem with this is that several rows are being created in tableA when only a sequence number is needed. Also, we need to make sure no one else does an insert before the select @@identity.
Another approach I'm thinking about would be to create a one row table that contains an integer field initialized with a value of 1. To select/update the sequence number the code would need to: set transaction serializable select number from tableA UPDLOCK update tableA set number = number + 1
How are most people generating a sequence number in SQL Server? In Oracle this would be done by selecting sequence.nextval. For example: Select sequenceA.nextval from dual;
Is there an equivalent way in SQL Server 7.0? Thanks.
Is there a way, I can give access(not dbo or sa) to a person so they can bcp into tables that have identity columns? I want to be able to give permissions ahead so I do not have to bother setting the identity insert on every time he wants to bcp.
I'm successfully inserting into a db using a stored proc, but I need to replicate the ClientID to 10 other tables. For some reason, this one is escaping me.
Does anyone know if there is bug/problem with the @@identity global variable in SQL Server 7.0? I have a stored procedure that inserts a row into a table with an identity column and returns (outputs) the value of the identity column just generated. The SP is called by a Java program. The SP works fine most of the time, however from time to time it retuns a NULL value! Your comments/suggestions are much appreciated.
I have a table (tblMoney). When I insert a new row into the table, via a standard insert stored procedure, the new row, has the identity column numbered correctly (say 34 for example). At the end of the stored procedure, we return @@IDENTITY for the developers. This will return a much bigger number (1198).
Any idea how these got out of sync, or how they can be fixed??
