I need a script that returns the last identity value generated for each table in a database that has an identity column.
I am using SQL Server 2005.
Thanks.
Hi,
How can I retrieve generated identity value after insert a row into table in case I can identify inserted row?
(the INSERT statement is dynamic constructed and queried from data access layer, not in stored procedure)
Thanks,
Hi,
How can I retrieve generated identity value after insert a row into table in case I can identify inserted row?
(the INSERT statement is dynamic constructed and queried from data access layer, not in stored procedure)
Thanks,
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedSLQ Server 2005
Hi Guys,
Well, the big re-write of our app in Russia is an official failure and it has come back to us (after 2+ years at $20K per month). I suggested we get the data model straightened out before we do anything else.
Here's a notion that I'm expecting to have to stomp, but I thought I'd run it by you smart guys first.
Basic structure will be Admin DB as a hub and each customer (because they all request this anyway for 'security') will have a separate DB for their Disability/Leave data.
As an additional layer of 'security' it was requested that, say, the unique id of a person would be unique across all client databases. Hence if the interface queries medical info on Person 123 for Customer B there is no possiblity of accidentally returning Person 123 from Customer H, because 123 only exists as a primary key in the Person table in Customer B's database. Each available key number is used uniquely across the admin & customer databases. (As you may guess, we've had several instances of company a's data going to company b.)
We're new to the idea of splitting out customers into separate DB's but our plan is to keep the customer DB's identical in every aspect except data.
Is there a functional way to ensure a key's uniqueness across our whole structure (Admin DB as Hub and multiple client db's)? Are we nuts to want this?
Note I did look at some info on 'creating your own identity' key thinking that maybe we could keep an 'identity' in a table in the Admin DB for each client table that needs this feature. But it sounds like I'll end up with issues for sure when bulk inserts are run unless the insert is run on the table in the Admin DB.
I can't imagine anyone is going to try to answer this, but it made me feel better to type it all out here!
Dear all,
I would like to use SSIS to populate product information from a remote database to a local one.
There are Product table (which contains the basic information) and ProductImage table(which contains the product images in binary).
I would like to use a join query
SELECT A.name, A.price, B.image from Product A INNER JOIN ProductImage B WHERE A.ProductImageID = B.ProductImageID
to return all the product information from the remote database.
When populating the local database, the local ProductImage table will be populated first and then the local Product table will be populated with the ProductName, ProductPrice return from the query together with the newly genetated ProductImageID afer insert a record into the local productimage table.
The ProductImageID column is set as the identity column in the ProductImage table.
I am very new to SSIS. Is there a way to handle this scenario? What components do I need to use?
Thanks
Ok, let's say that I do a following inline INSERT statement.....
INSERT INTO Car ( carMake, carModel ) VALUES ( 'Honda', 'S2000' )
When I do this, a carId int value will automatically be generated because it is the Car table's PK and is an int identity. How do I obtain this value so I can immediately use it in let's say, the following example.
INSERT INTO Person ( carId ) VALUES ( @carId ) ..... where @carId is the value I need
Maybe you would say I should do something like.....
SELECT carId FROM Car WHERE carMake = 'Honda' AND carModel = 'S2000'
However, let's assume that the carMake and the carModel fields are NOT unique. I'm sure there's an easy way to extract the new identity value (maybe even in the same query) as the INSERT, but I just don't know how.
Thanks.
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
How do I return a list of all user tables in a database where table name starts with EmpID_
for example the table names are:
EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........
I am using SQL Server 2005. Thanks.
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
Suppose I want to generate a table as below
No. Col1 Col2
1 100 Alan
2 110 Alan
3 120 Alan
In Column I there is a sequence of numbers from 1 to 3 with a step of 1
In Column 2 there is a sequence of numbers from 100 to 120 with a step of 2
In Column 3 there is a name that is repeated accross the whole column
Can I generate such a table with just a single SQL Statement . I don't see any reason why it should not be possible. We know the logic to generate all the numbers of every row. It would be possible using a while loop , but that is be tedious and I am looking for a better way.
One application I can think of is, Suppose I want to find out all deleted primary key ids from a table or say if I have a set
(1,2,3, 7,8,9) , and wish to know the missing numbers in the range 1 to 9. If an sql like above is possible I could use it in a left join to get me the desired output.
Could anyone tell me if it is possible and if not , why not ?
Thanks,
Alec
I have three databases and 40 tables within each database on my server, for each of the tables I want to know which SSIS package generates that table. Is there a script that can do this?
If the SSIS package does not create or populate a table on the server I then want to check if there is a stored procedure that populates this.
How do i return a Identity through a stored procedure?
View 18 Replies View RelatedSorry I'm new in this,Add a record using T-SQL and Connection.ExecuteHow can i insert the identity in a VB-variableDim objConn As ADODB.ConnectionSet objConn = New ADODB.ConnectionobjConn = CurrentProject.ConnectionobjConn.OpenobjConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')SELECT @@IDENTITY as 'NewID'"Msgbox NewID triggers an error !Filip
View 5 Replies View RelatedWhat I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script.
This is my SP
CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)
AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity
return
GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
SQLCommander.Commandtype = Commandtype.StoredProcedure
SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)
'// this section not working right, it wont write return id
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue
response.write(SQLCommander.Parameters("ReturnValue").Value)
SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()
End sub
Can anybody see anything I missing? I appreciate any imput
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.
Thanks
Hello,
I'm using C# to access sql server.
When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use?
Thanks.
I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.
Here is the sp. Any ideas?
CREATE PROCEDURE sp_ExecuteInsert
@pIdentity int OUTPUT,
@pSQL1 varchar(255),
@pSQL2 varchar(255) = NULL,
@pSQL3 varchar(255) = NULL,
@pSQL4 varchar(255) = NULL,
@pSQL5 varchar(255) = NULL,
@pSQL6 varchar(255) = NULL,
@pSQL7 varchar(255) = NULL,
@pSQL8 varchar(255) = NULL
AS
EXECUTE (@pSQL1 + @pSQL2 + @pSQL3 + @pSQL4 + @pSQL5 + @pSQL6 + @pSQL7 + @pSQL8)
SELECT @pIdentity = @@IDENTITY
GO
FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.
Thanks...
Hi!
I have a question that might sound obvious to some of you but (obviously) not to me.
I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).
I want to return the value of the TableID of the newly created record. Sound simple ?
Thanks.
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View Relatedhello,
I'm new to store procedure in slq 2000.
I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier",
now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful.
So how to create this store procedure ?
much thanks
I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)
AS
INSERT INTO tbLabel
(LabelType
, Logo
, Field01)
VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)
How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...
Zack
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
CREATE PROCEDURE addMagicRecTest
(
@theSequence int,
@theSubject int,
@theFirstName nvarchar(50)=null
@theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@theSequence,@theSubject,@theFirstName,@theLastName)
SELECT @@identity AS finalNum
magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure
' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"
' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"
' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName
' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName
DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If
I need finalMagicNum2
how do i return the identity from stored procedure to asp.net code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
{
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies View RelatedHi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
I downloaded the AdventureWorks OLTP Schema and am trying to use some of the design concepts that are used in this example but I don€™t quite understand some things.
First there is a column named rowguid, at the moment I assume that this is a GUID. If this is wrong most likely my next question will make no sense.
Second if that is indeed a guid all the tables have another id; example would be Employee table would have employeeid and rowguid, why do this? My best guess is that you would use the guid to easily insert (linked/sync) data into multiple tables without needing to retrieve the id from the table that was first input into. My scenario would be inserting an employee; to insert an employee you need to create a row in the employee, contact and address tables and for ease you would use the guid to link these rows. But if this is the case wouldn€™t there be sync issues that would arise?
If my guess is totally off please correct me also if anyone can direct me to some good resources that cover database design.
The schema can be downloaded at http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en#filelist
Thanks for any help.
Hello
I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".
How can I add entities to my Sqlce- database?
I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.
What do I have to change to make successfully a SaveChanges()?
Thanks for your help,
Gerald
View 1 Replies View Related
does anyone know how sql 2k stores whether a column is an identity column? is it in any system tables or information_schema?
i've read that i can't find the next value of the identity, which i find weird. I just want to know if the column is an identity or not.
Thanks in advance.
Dear experts,
I've just got the assignment to write a stored procedure or function. It was requested to return 3 tables from that sp or function.
eg.
table1
fd1
fd2
table2
fd2_1
fd2_1
table3
fd3_1
fd3_2
It is posible for sp or function to return many tables at once? if so, which of them is better (sp and function).
Thanks in advance for your help!
Martin
Hi,
Please let me know if it is possible to replicate a table with identity property defined in it. Both the publisher and subscriber tables have identity property defined. Which option should be used while setting up transactional replication to allow the identity values at the publisher pushed to the subscriber, which also has identity property defined? Not for replication option with the identity property also fails. Whichever option I choose I get the error, 'An explicit value for the identity column in table 'jobs_id_nfr' can only be specified when a column list is used and IDENTITY_INSERT is ON.' This works only if the identity property is not defined at the subscriber. But, I need to have the identity property defined at the subscriber also because the subscriber should be an exact copy of production.
Thanks in advance,
Praveena