Sql Insert, Capture Scope_Identity Output To Session Variable?
May 24, 2007
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it!
A column in my table is being updated and I would like to find out what process, host, user, etc. is performing the update.
I have added a trigger to the table so that when an update occurs I am able capture the session details into a table, however the session details that are being returned are those of the actual trigger rather than those of the original query.
The statement that I am using to capture the session details is...
select * from sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) where session_id = @@spid
There is a parent_session_id returned in the above which I hoped might give me some pointers but this is always NULL.
I have been working my way through the system dynamic views in the hope that one of them will provide the information I am looking for but so far I've had no luck.
Because the system I am troubleshooting is web based the actual server and user details will be those configured as service accounts for the application but if I am able to show the developers the original query that was used for the update I'm sure they will be able to provide valuable information as to which component it is coming from. I imagine SQL will also be able to tell me which host is sending the query. With these two bits of info I'm sure I'll be able to track it down.
Our suspicion is that an old component is running somewhere and I'd like to be able to track it down. Perhaps I've spent too much time looking at this but I'm coming to the conclusion that it may not be possible from the SQL back end, which I am very surprised at.
I have tried capturing every session that was running at the time of the update but there's simply too much info to deal with. I even searched through these results but there's so many different possible combinations that an update statement might take form that it quickly became a worthless exercise.
Perhaps one of you can resolve this for me. Any ideas?
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime) declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
Hi... I'm developing a web app with VS 2005 Beta and I do not know how to include a seasson variable value into my code.
This is my code:
Function GetData() As System.Data.SqlClient.SqlDataReader Dim sqlconn As System.Data.SqlClient.SqlConnection Dim SqlCommand1 As System.Data.SqlClient.SqlCommand Dim sqlRdr As System.Data.SqlClient.SqlDataReader sqlconn = New SqlConnection("SERVER=SRVR;UID=USR;Password=PWD;DATABASE=MyDatabase") sqlconn.Open() SqlCommand1 = New SqlCommand("MyStrProc", sqlconn) SqlCommand1.CommandTimeout = 1800 sqlRdr = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection) Return sqlRdr End Function
MyStrProc is:
CREATE PROCEDURE MyStrProc @MyVar int AS IF @MyVar IS NOT NULL BEGIN ................. ......t12.acct_nbr IN (@MyVar) ..... END GO
The variable @MyVar get its value from user log info in login page, and create a session variable.
My Question:
How and where to call seasson variable @MyVar, because now I'm getting this error: Procedure 'MyStrProc' expects parameter '@MyVar', which was not supplied.
Can we use session variable in reporting services.. say go to report -> properties and create a VB function that get ans set session variable.. If yes, how. If not, any workaround?
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening. SqlConnection conn = new SqlConnection(@"Server=.SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open(); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
// delete all rows cmd.CommandText = "DELETE FROM Person"; cmd.ExecuteNonQuery();
// parameter insert cmd.CommandText = "SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@PersonID, @PersonName, @PersonAge)"; p = new SqlParameter("@PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonName", "Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText = "SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();
Response.Write("<br>end.");
} finally { conn.Close(); } I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing... Thank you.
(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks. Session["snCoDeptRowID"] = SqlDataSource1.Select(); <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT Scope_Identity" </asp:SqlDataSource>
Hi, I try to set a Session variable with the result of a SQL Selec statement. I tried the following, but it is not working. Session("thisone") = SqlDataSource1.SelectCommand = "SELECT myfield FROM [mytable] WHERE ([username] = @username)" @username is a value from a textbox. What I am doing wrong? Thanks for your help, Chris
I'm having a real problem populating my SqlDataSource and was wondering if anyone could help. I'm sure it's something simple First off, I created a session variable on a previous page like so Dim NewSubscribeID As Integer = Convert.ToInt32(retSubcribeID.Value) Session("SubcribeID") = NewSubscribeID I now want to use this session variable on another page to populate a SqlDataSource like so <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionXX %>" SelectCommand="spXX" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="SubscribeID" SessionField="SubscribeID" Type="Int32" DefaultValue="0" /> </SelectParameters> </asp:SqlDataSource> Here is the problem... Only the default value gets used. I've tried putting the session variable into a label to test that it does actually exist lblTest.Text = Session("SubcribeID")
Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s SET Exception_Ind = 1 OUTPUT s.Master_Id, s.TCK_NR INTO #temp2 FROM Master_Summary s INNER JOIN Exception d ON d.Id = LEFT(s.Id, 8) AND d.Barcode_Num = s.TCK_NR WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113 The multi-part identifier "s.Master_Id" could not be bound. Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113 The multi-part identifier "s.TCK_NR" could not be bound.
I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.
I have included the stored procedure. I will appreciate any ideas?
Hi, I am interested to know how I can capture the amount of time bcp takes in a table.... the whole idea is to keep track of all bcp activities and create atable to keep bcp log time to the following table: create table bcp_log(table_name char(20),row_count int,time_in datetime)
what I thought is to create a trigger on each table I want to log its bcp. then declare a variable and assign the duration of bcp .. But I did not know where to pull that value..... Dose anyone knows how to capture the duration of bcp for a table....
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable. code snippet: <UpdateParameters><asp:Parameter Name="hrs_credited" /> <asp:Parameter Name="updater_id" DefaultValue="<%$ Session("User_ID")%>" Type="Int32"/> <asp:Parameter Name="activity_id" /> <asp:Parameter Name="attendee_id" /></UpdateParameters> The error message that I receive is: Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:DevelopmentCMEdataentryattendance.aspx 29 Does anyone have an idea how to assign the session var value to the parameter? Thanks!
Hi Folks, i am trying to add a connection string value to my client side as follows. i am getting error. any ideas?? <asp:SqlDataSource ID="SqldsComments2" runat="server" ConnectionString='<% session("sqlConn") %>' SelectCommand="SELECT * FROM TicketComments Where TicketNo=@TicketNo "> <SelectParameters> <asp:Parameter Name="TicketNo" DefaultValue="0" Type="string" /> </SelectParameters> </asp:SqlDataSource>
Hi everybody, is anyway to capture error description into variable?
Example executing insert into tabMaster(col1) values(1) select @@error
will produce output Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_TabMaster'. Cannot insert duplicate key in object 'TabMaster'. The statement has been terminated.
----------- 2627
(1 row(s) affected)
I want to capture " Violation of PRIMARY KEY constraint 'PK_TabMaster'. Cannot insert duplicate key in object 'TabMaster'." and assign it to variable
BOL state:... All other parts of the error, such as its severity, state, and message text containing replacement strings such as object names, are returned only to the application in which they can be processed using the API error handling mechanisms
I am building a SQL statement that returns a number.when I execute the Built SQL statment EXEC(@Build). What I need to donow is take that number that comes back and store it in anothervariable so I can do some conditional logic. Any ideas? See SQL below.Something like @Count=Exec(@Build) which I know doesnt work.Thanks,PhilDECLARE @PullDate varchar(12)SET @PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''PRINT(@PullDate)DECLARE @COUNTER BIGINTDECLARE @SELECT VARCHAR(500)DECLARE @SELECT2 VARCHAR(1000)DECLARE @BUILD VARCHAR(5000)SET @SELECT='SELECT COUNTER FROMOPENQUERY(PROD,'SET @SELECT2='''SELECTCOUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTERFROMCOLLECTOR_RESULTS,WHEREWMB.COLLECTOR_RESULTS.ACTIVITY_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCOUNT.END_DATE ) =to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy''''))GROUP BYWMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'SET @BUILD=@SELECT+@SELECT2PRINT(@BUILD)EXEC(@BUILD)--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD STOREDINTO @COUNTER so I can do a conditional statement.)if @COUNTER>=1beginprint('yes')end
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID". Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you. Web Page 1: Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
Hi,I am new to SQL. Please bear with me and allow me to ask a dumbquestion.I am debugging a stored procedure (written in Trans-SQL), and I foundthat the SQL analyzer that I use doesn't have a debugger. All I cando it is execute a block of code and see what is going on in aninteraction seesion of the SQL analyzer. I would need to declare somevariable to hold values of the previous query in the interactivesession.I understand that this can be easily done in a stored procedure viathe Declare command (e.g., Delcare @order_no int). Is similarfunctionality exists in an interaction session of the SQL analyzer?If so, what is the command. Please advise.Thank you very much for the help.Alex
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT Quiz.Title, UserQuiz.DateTimeComplete, UserQuiz.Score FROM UserQuiz INNER JOIN Quiz ON UserQuiz.QuizID = Quiz.QuizID WHERE (UserQuiz.UserName = @UserName) ORDER BY UserQuiz.DateTimeComplete"> <SelectParameters> <asp:SessionParameter Name="UserName" SessionField="UserName" /> </SelectParameters> </asp:SqlDataSource>how to modify this code so that it can insert value of the username captured in the session to the database table record?
In my SSIS program I have a main package that calls bunch of packages step by step. I would like to find out how can I capture the status of each task once its done and insert the value whether its "success" or "failure" into a sql server table.
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)... ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be. 1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String) If sIncomingfile <> "" And sOutputDirectory <> "" Then Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None) f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read Dim file As New IO.FileInfo(sIncomingfile) Dim filefs As IO.FileStream = Nothing If file.Exists Then Try filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1 Catch ex As Exception SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1") Application.Exit() End Try End If Dim reader As New IO.StreamReader(filefs) Dim counter As Integer = 0 Dim CurrentFS As IO.FileStream Dim CurrentWriter As IO.StreamWriter Dim extension As String = IO.Path.GetExtension(file.FullName) If extension = ".mnt" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If IsNumeric(Line.Substring(0, 1)) Then Dim Parts() As String = Line.Split(" "c) ' split row into parts If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "mnt") ElseIf extension = ".naf" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "naf") End If Else 'input file not valid SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1") End If End Sub
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
hey folks... i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how.... any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management :-)
