I'm designing a database and was researching common conventions and standards. I found that some people don't use the identity column for a primary key because they prefer to create their own unique id number for the column. Whats your opinion?
Ryan
Hi,
I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.
The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.
The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.
We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .
Any advice would be gratefully recieved
Lee
I was just wondering what if someone can summurize the benefits between using Access and SQL for an ASP.NET 2.0 website? Can you use an Access database that doesn't have SQL installed on it with ASP.NET?
View 3 Replies View RelatedHi, there is an existing application running on a SQL Server database.Now I have to built a different app, which will have two common tableswith the existing database (Customers, sales)Is there any benefit (performance mainly) creating the tables for thenew app in a new database? (On the same machine and the same instance)*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedI find that in Database Exporer in Visual Web Developer, I can specify a DB in two ways, as exemplified here:
Data Connetctions + ke_test_db.mdf + ssftec023-lsqlexpress.master.dbo + ke_test_db2.mdf
Experimentation shows that mdf files that can be put under the "sqlexpress" item (ke_test_db2.mdf in the example) are those that SQL Server Configuration Manager Express knows about, whereas files that I create via adding an item in Solution Exporer appear at the top level (ke_test_db.mdf). My question is, is there any advantage or disadvantage to either architecture? I.e., Is there any advantage to being sure that one's DB files appear under the sqlexpress connection item?
Thanks!
-Kurt Euler
Hi,
I've never really used the SQLDatasource, I usually fill a dataset & use that. When would you use an SQLDatasource? I notice it doesn't return dataviews but I also noticed that you don't need to open the conenction. Any ideas on best practice?
Many thanks
Could someone hit the high points of the benefits of using SQL Server (vs. other databases) with Visual Studio .NET and the .NET Framework.
Thanks
Hey all,
Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Thanks!
Ron Moses
ConEst Software Systems
Hi,
When do we need to have multiple log files for one Database.
What performance counters should I measure before deciding to go for multiple log files.
Any comments / suggestions are welcome.
Thanks,
Loonysan
Hi,
I wonder if there are any drawbacks with NVarChar(max) contra e.g. NVarChar(40) when it comes to performance and amount of storage used?
Are there other arguments to use e.g. NVarChar(40) than that you have more control of how long the strings are when you set the upper limit?
I'm using Sql Server 2005.
Tomsi
Hi
How can I find out which edition of SQL Server is running on my server ?
Also what would be the difference in terms of features between standard edition and enterprise edition ?
Thanks
Vijay
I have read over the feature set and I really don't see a huge reasons to jump on the bus. There are a few niceties like IP enhancements, cluster enhancements and maybe bitlocker for the backup drive. Am I missing something?
View 3 Replies View RelatedWhile 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
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'.
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.
hello,
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;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
rsLastIdent = comm.ExecuteNonQuery()
Catch ex As Exception
Response.Write("Exception:")
Response.Write(ex.ToString)
End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if
The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...
Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @@identity?
Thanks a lot!
tim
hi,
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;}
Hi all,
is there a way in SQL Server for me to get the next available Identity value from an Identity column?
(I don't want to perform an insert, and then get the identity through @@IDENTITY!)
Thanks
Tryst
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();
Is it possible to use the ALTER TABLE statment when modifying an INT field to be an IDENTITY.
thanks in advance.
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.
Hi,
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.
Thanks a lot for your help
Sasha
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.
After a successful insert, I then use:
SELECT @@IDENTITY ClientID
SELECT @ClientID = @@IDENTITY
INSERT INTO table2
(ClientID)
VALUES(@ClientID)
INSERT INTO table3
(ClientID)
VALUES(@ClientID)
INSERT INTO table4
(ClientID)
VALUES(@ClientID)
The insert ClientID into the other tables is being inserted. I added SET @ClientID = @@IDENTITY and it made no difference.
Thanks for the help.
Alan