@@IDENTITY Vs. SCOPE_IDENTITY() On CLR Context Connection
Aug 1, 2006
Dear all,
I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @@IDENTITY.
The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),
Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection) Try NewTagID = CInt(command2.ExecuteScalar().ToString)
However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @@IDENTITY", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)
Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @@IDENTITY would be the better option.
Thankful in advance for advice.
View 5 Replies
ADVERTISEMENT
Nov 8, 2004
I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.
If I do the INSERT followed by a "SELECT @@IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @@IDENTITY.
I guess what I have is satisfactory but this was frustrating and I want to know why.
This is on SQL Server 2000 Standard edition with version SP3a + hot fixes
View 2 Replies
View Related
Nov 6, 2006
hi to all
who is the best use among IDENT_CURRENT and @@IDENTITY and SCOPE_IDENTITY when i wnat to get last inserted id from a table.
and also give the reason why because i am little bit confuse for useing these..
thanks in advance.
arvind
View 5 Replies
View Related
Jun 11, 2007
Hi,
I need to pass the ID of a newly inserted product in my products db to a wizard step so that I can achieve the following:
Wizard Step 1: Enter product details
Wizard Step 2: Choose product image and upload to file system, then write to DB.
All help is appreciated!
Thanks,
Chris
View 4 Replies
View Related
Feb 24, 2006
Hello Guys,
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
Thank you,
Lubomir
View 1 Replies
View Related
Oct 6, 2006
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
Dim success As Boolean = False
emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf
Dim insertStr As String = "INSERT INTO " & _tblName & "("
Dim values As String = ") Values("
Dim drow As DataRow = Nothing
Dim dCol As DataColumn = Nothing
'add the column names
For Each dCol In _dt.Columns
insertStr = insertStr & dCol.ColumnName.ToString & ", "
values = values & "@" & dCol.ColumnName.ToString & ", "
Next
'remove the last comma & form the final string
insertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values & ")"
Dim connStr As String = "context connection = true"
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Using conn
Try
conn.Open()
For Each drow In _dt.Rows
cmd = New SqlCommand(insertStr, conn)
For Each dCol In _dt.Columns
cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))
Next
SqlContext.Pipe.ExecuteAndSend(cmd)
Next
success = True
Catch ex As Exception
emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf
success = False
Finally
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
success = False
End Try
End Try
End Using
Return success
End Function
View 4 Replies
View Related
Dec 18, 2007
I have a CLR stored procedure written in C#. It use context connection to connect into the SQL Server database. Since the stored procedure is handling a large amount of records, it cost a long time to complete the process. And I will got exception that indicate the connection timeout.
So can I set the value of ConnectionTimeout more large and how to set it?
Thanks!
View 3 Replies
View Related
Jul 15, 2006
Hi,
When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:
System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.
Can we have MARS in the "context connection"?
note: I'm doing this to support multiple open datareaders in a CLR stored procedure.
Thanks!
Andy
View 5 Replies
View Related
Feb 6, 2008
Hello,
I'm using a context connect inside a CLR stored procedure. The CommandTimeout property has no functionality when used with a context connection. See remarks in this url:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
This is a problem for me because I'm at times call a link server that has been known to wait indefinitely. I've tried using a regular conneciton that supports command timeouts within my CLR stored procedure but I get a permissions error (Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0,0, Culture=neutral failed.)
How can I prevent my CLR stored procedure from waiting indefinitely?
Thanks.
View 2 Replies
View Related
Mar 3, 2014
I would like to know if we can have a generic "customer database account" to connect to the database (from a PHP layer) and then pass through the current web logged in user name for auditing purposes.
In the oracle world I would be using the setClientIdentifier function on any connection returned from a pool, but I cannot see anything obvious (to me) on the APIs to support this.
Our backup plan, because we only access table data through stored procedures, is to extend the API to have the username passed through - but this is a little ugly and less than transparent.
View 6 Replies
View Related
Nov 11, 2007
hi,
While trying to connect to SQLSever 2000 or SQLSever 2005 from a vb.net application ,
a user is getting the error "Cannot generate SSPI context" while other users
can still connect to the server.
and moreover, this user were able to connect before.
and if he restarts his computer, then try to connect after a lap of time, it will connect.
but if he waits and opens other applications,and then tries to open the application,it will not work.
so how to resolve this problem?
Regards,
View 3 Replies
View Related
Mar 15, 2008
Hi
I have a DLL deployed in SQL2005 which uses Thread pool. Since DLL exists in DB we can directly access it using context connection = true.
In my case the following works fine, if i execute the same out side Threadpool
SqlCommand cmd = new SqlCommand("select 1...", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);
When i execute inside threadpool, either by passing opened connection object, or tried to open connection to "context connection = true" i am getting following error.
System.InvalidOperationException: The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code. at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at hiqidty4.hiqidty4.Identify_Search(Object identifyin)
Let me know if i am missing any more here
Thanks in advance
View 1 Replies
View Related
Oct 26, 2015
I have 2 servers. On one server i install sql server 2014 and its working fine, i am able to get connected with sql with domain account.
From second machine i want to connect my sql server, for that i create udl file when i give sql server address and click refresh getting following error: Connection failed cannot generate SSPI context
Firewall is off, created fire rule too, i am able to get ping first machine by ip and servername but still getting issue.
View 3 Replies
View Related
Dec 3, 2005
It makes no difference if I'm working with Enterprise Manager, QueryAnalyzer, Access, self written app with OleDb or Visual studio 2003builtin DB manager.Everything is fine but aufter some time - about 30 minutes I getmessage "cannot create SSPI context"After rebooting my machine I can work for another few minutes.Configuration:NB01 is my notebook with Windows XP ProSQL01 is another Windows XP Pro machine with SQL Server 2000 desktopDC01 is the domain controllerall machins within the same domain DOMAIN01The user is a domain admin and has every rightI'v seen some postings for this problem but not one useful idea how toresolve.As it runs perfectly for some time I don't thint it is a DNS problem orsomething like this.As there is no useful error message it must be some kind of bug.Has anybody resolved such a problem?
View 3 Replies
View Related
Feb 10, 2008
We have a nagging issue here in our application side, which I was trying to troubleshoot and reach no where. Can I have your valuable inputs for resolving/understanding the root cause of the issue?
3709-The connection cannot be used to perform this operation. It is either closed or invalid in this context
This error is not coming regularly (twice it happened and after the program is running successfully with out any problem). Problem having exe is running every 2 minutes interval.
Most of the sites saying this is something related to code written in the application but if that is the case why this error is not happening continuously? The problem having exe is running past 4 months with 2 minutes interval and suddenly thrown this error.
I found one MS site describing about this error but not able to find any fixes for this issue (http://support.microsoft.com/kb/839428). We are on the process of upgrading the operating system with SP2; will this help us to resolve this issue? Please advice.
Details
1. Windows 2003 with SP1
2. MDAC 2.8
3. SQL 2005 with SP1
4. VB Application.
View 1 Replies
View Related
Oct 20, 2007
Using scope_identity
I am using SQL2005 and I need to insert a record and return ID. I am using scope_identity() in the stored procedure to return the ID for the record just inserted.
Do you see any problem with this when it comes to multi-user and multi-threaded environment.
View 6 Replies
View Related
Jan 3, 2008
Hi, i need the DiagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement. I was told to use scope_identity(), but i'm not sure how to implement it. Below is my code behind in vb.net. pls help. Dim cmd1 As New SqlCommand("insert into Diagnosis(TypeID, SeverityID, UniBilateral, PatientID, StaffID) values ('" & typevalue & "','" & severityvalue & "','" & unibivalue & "','" & Session("PatientID") & "','" & Session("StaffID") & "')", conn) cmd1.ExecuteNonQuery() Dim i As Integer For i = 0 To hearingarray.Count - 1 Dim li As New ListItem li = hearingarray(i) Dim cmd As New SqlCommand("insert into DiagnosisManagement(ManagementID) values ('" & li.Value & "')", conn) //i need the DIagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement here cmd.ExecuteNonQuery() Next
View 1 Replies
View Related
May 19, 2008
Hi All,
I'm trying to return the last id entered via the following code, and I'm only getting '0' back.
1 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
2 {
3 connection.Open();
4 using (SqlCommand command = new SqlCommand("REG_S_CustomerIDFromPhoneNumber", connection))
5 {
6 command.CommandType = CommandType.StoredProcedure;
7 command.Parameters.AddWithValue("@Mobile_Telephone", MobileNumber);
8
9 int test = Convert.ToInt32(command.ExecuteScalar());
10
11 Response.Write(test.ToString());
12
13
14 }
15 } My SP is as follows (I'm trying to use one that's already been written for me) 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[REG_I_CreateBlankCustomer]
9 @Mobile_Telephone varchar(255),
10 @CustomerID int OUTPUT
11
12 AS
13
14 INSERT INTO Customer (Mobile_Telephone)
15 VALUES (@Mobile_Telephone)
16
17 --SET @CustomerID = @@Identity
18 SELECT SCOPE_IDENTITY();
19
20
21 GO
22 SET QUOTED_IDENTIFIER OFF
23 GO
24 SET ANSI_NULLS ON
25 GO
26
27
28
when I'm running this via Query Analyser, I get the ID returned correctly, however as mentioned when ran via that code above - I get a 0 outputted to me. What am I doing wrong?
Thanks!
View 9 Replies
View Related
Jun 9, 2008
i have such a stored procedure.
but i dont know where to put that scope_identity to retrieve a value.
SELECT SCOPE_IDENTITY() AS [@Car_id]
GO
ALTER procedure [dbo].[insertuser](
@Make nchar(10),
@Model nchar(10),
@SellerID varchar(50),
@MileAge nchar(10),
@Year_Model int,
@Price money,
@Date_added datetime,
@Thumb_ID varchar(50),
@Image_id varchar(50),
@Car_id int
)
AS
INSERT INTO
dbo.tbcar
VALUES(@Make,@Model,@SellerID,@MileAge,@Year_Model,@Price,@Date_added);
INSERT INTO
dbo.tbimages
values
(@Thumb_ID,@Image_id,@Car_id)
View 5 Replies
View Related
Mar 30, 2005
I was wondering if its possible to use this function if your using a
SqlDataReader where I just run a stored procedure that just inserts a
row?
View 2 Replies
View Related
Mar 31, 2006
Hello altogether, my problem ist that I get following error message:
Create Stored ProcedureUnable to chances to the stored procedure.Error Details:'Scope_Identity' is not a recognized function name.
This is my Stored Procedure:
CREATE PROCEDURE sp_HyperSoftCustomer @Name varchar(25), @Adress varchar(250)as insert into HyperSoftCustomer(Name, Adress, Date) values (@Name, @Adress, GetDate()) Select SCOPE_IDENTITY() GO
I am using MSDE - MSSQLServer
I hope there is anybody who can help me?
Thanks, mexx
View 3 Replies
View Related
Jun 3, 2006
I have seen plenty of messages about using scope_index by creating parameters using HTML but I would like to do it from my .aspx.vb page.
Does anybody know if this is possible? I have got as far as the code below and get stuck when trying to add a new parameter with direction of output.
Any help would be much appreciated, cheers,
Doug.
Dim NewProperty As SqlDataSource = New SqlDataSource
NewProperty.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectIt").ToString()
NewProperty.InsertCommand = "INSERT INTO Test (Name) VALUES (@Name); SET @NewID=SCOPE_IDENTITY()"
NewProperty.InsertParameters.Add(NewID, id)
NewProperty.Insert()
View 3 Replies
View Related
Aug 23, 2004
I have four tables:
1- customer details
2- bank details
3- car details
4- contract details
All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly.....
I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager.....
Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
THANKS
View 11 Replies
View Related
Aug 31, 2004
Hi All,
In SQL Server stored procedure
SCOPE_IDENTITY() will return the IDENTITY value inserted in Table, which was the last INSERT that occurred in the same scope.
can any one give me the syntax in Oracle ?
View 4 Replies
View Related
Jan 10, 2008
Here's what I have (OrderID and CustID are PK's and autoincrement):
tblOrders__________tblCustomers
---------__________------------
OrderID____________CustID
CustID_____________Name
Just a simple application I created in ASP.NET and C# with those tables in an SQL database. The user enters their name, clicks Submit, and their information is put into Customers. After that, I want a new Order to be created with the CustID from the Customer just created.
I know I'm supposed to SCOPE_IDENTITY() to create it, but I'm not sure how to use it. I've been told to use a stored procedure, but I'm not sure how to do that either. Here's my code:
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("INSERT INTO tblCustomers(Name)VALUES('"+TextBox1.Text+"');", conn);
//cmd2 with SCOPE_IDENTITY() inserting into tblOrders
conn.Open();
cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
conn.Close();
View 18 Replies
View Related
Jul 23, 2005
I am seeing a problem with an ASP application, where I have 2 tables.In the first table, the ASP inserts just 1 row and retrieves theprimary key of the new row using SCOPE_IDENTITY. It then uses thatprimary key in the column of a second table (foreign key) to insertmany rows.What I'm seeing is an intermittent problem where the foreign key in thesecond table is not what it should be. I think the problem may be dueto the fact that the insert into the first table and the calling ofSCOPE_IDENTITY are done in 2 separate ASP statements with some ASP codein between.Is it possible that 2 users may be calling my ASP page at the same timeand causing a concurrency problem due to the INSERT and theSCOPE_IDENTITY being done in 2 different SQL statements? I read thatSCOPE_IDENTITY always returns the last identity value generated from"the current connection", so I thought that would mean that it wouldn'tget messed up by another ASP request. But now I'm thinking thatperhaps ASP uses connection pooling which could mean that 2 users couldbe sharing the same connection which would cause this concurrencyissue.Does anyone know if my theory of what's wrong is plausible?
View 5 Replies
View Related
Jul 20, 2005
I have an ASP front end on SQL 2000 database. I have a form that submits toan insert query. The entry field is an "identity" and the primary key. Ihave used scope_identity() to display the entry# of the record just enteredon the confirmation page. Now I need to insert the entry into anothertable. This is my query:SET NOCOUNT ONINSERT wo_main(site_id, customer, po_number)VALUES ('::site_id::', '::customer::', '::po_number::')SELECT scope_identity() AS entryINSERT INTO wo_combo_body(entry) VALUES ('::entry::')SET nocount offThis query displays the entry number of the record just entered, but insertsa 0 in to entry field of the 2nd table. Any help would be great.Thanks,Darren
View 4 Replies
View Related
Apr 6, 2006
is there an sql mobile equivalent of SCOPE_IDENTITY()?
View 4 Replies
View Related
Sep 11, 2007
hi
what is difference between thos two's
SCOPE_IDENTITY()
and
@@IDENTITY
thanx
View 5 Replies
View Related
Dec 28, 2006
have a detailsView control with an SqlDataSource whose insert statement looks like this:
InsertCommand="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@NAME, @CITY, @ST, @ZIPCODE, @NAICS, @NAICSDESCRIPTION); SELECT RETURN_VALUE = SCOPE_IDENTITY()"
also played with the same insert but used ...;Select SCOPE_IDENTITY()
my question is how do i get the last record inserted into tblCompaniesNewSetRaw after the insert is run. ie I read that the Select Scope_identity() would return the value but how do i access the return value from within the code behind page, iusing VB.
some things i tried in the detailsView_ItemInserted(...
Dim row As DetailsViewRow
For Each row In DetailsView3.Rows
x = row.Cells.Item(0).Text
Next
in the VS debugger x is just "" and not the last record inserted in that table.
probably way off base on this, clues appreciated, tc
View 3 Replies
View Related
Jun 29, 2007
Hi folks;I'm having trouble retrieving the SCOPE_IDENTITY() with an SqlDataSource, it returns a number that has nothing to do with the real identity.It was always returning 102 or 137 when the real identities were something in between 5 to 10
Here is my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [POD] WHERE [PODID] = @PODID" InsertCommand="INSERT INTO [POD] ([CustomerID], [Airbill], [Shipper], [Consignee], [POD], [Date], [Time], [Pieces], [Weight], [Comments]) VALUES (@CustomerID, @Airbill, @Shipper, @Consignee, @POD, @Date, @Time, @Pieces, @Weight, @Comments); SELECT SCOPE_IDENTITY() AS @newID"
SelectCommand="SELECT * FROM [POD] ORDER BY [POD]" UpdateCommand="UPDATE [POD] SET [CustomerID] = @CustomerID, [Airbill] = @Airbill, [Shipper] = @Shipper, [Consignee] = @Consignee, [POD] = @POD, [Date] = @Date, [Time] = @Time, [Pieces] = @Pieces, [Weight] = @Weight, [Comments] = @Comments WHERE [PODID] = @PODID">
<DeleteParameters>
<asp:Parameter Name="PODID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" />
<asp:Parameter Name="Shipper" Type="String" />
<asp:Parameter Name="Consignee" Type="String" />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" />
<asp:Parameter Name="Weight" Type="Decimal" />
<asp:Parameter Name="Comments" Type="String" />
<asp:Parameter Name="PODID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="newID" Type="Int32" Direction="ReturnValue" />
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" DefaultValue=" " />
<asp:Parameter Name="Shipper" Type="String" DefaultValue=" " />
<asp:Parameter Name="Consignee" Type="String" DefaultValue=" " />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="Weight" Type="Decimal" DefaultValue="0" />
<asp:Parameter Name="Comments" Type="String" DefaultValue=" " />
</InsertParameters>
</asp:SqlDataSource>
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
' ** get the id and redirect to details page
Dim id As Integer = e.Command.Parameters("@newID").Value
Response.Redirect("pod_details.aspx?id=" & ID)
End Sub
Does anybody know what am i doing wrong in here?
View 2 Replies
View Related
Oct 12, 2007
I have a app that is inserting data into a SQL 2005 database and I would like to return the UniqueID of the inserted record. I am using
Dim queryString As String = "INSERT INTO dbo.DATATABLE (FIELD) VALUES (@FIELD);SELECT Scope_Identity()"
Dim sID As String = comSQL.ExecuteScalar()
This isn't working - it says the value returned is DBNull...
Any ideas on how to make this work?
View 4 Replies
View Related
Oct 28, 2007
I have a stored procedure that does three INSERTS each needing to use the primary key from the previous. There are three INSERTS in the procedure.
Is this ok? my reason for asking is that it will get the first @IDPrimary but not the second @IDSecondary
For example;
INSERT (1)
Set @IDPrimary = SCOPE_IDENTITY()
INSERT(2)
Set @IDSecondary = SCOPE_IDENTITY()
INSERT(3)
View 6 Replies
View Related
