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!
SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName);
SELECT @CoDeptRowID = SCOPE_IDENTITY()"
<insertparameters>
<asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/>
<asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/>
<asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/>
<asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" />
</insertparameters>
</asp:SqlDataSource>
View 5 Replies
ADVERTISEMENT
Nov 4, 2007
Hi guys,
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?
Regards,
Joe
View 7 Replies
View Related
Aug 28, 2007
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'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
Jun 27, 2003
I want to capture the output of the RESTORE HEADERONLY command into a table but so far I've not been smart enough to do it.
I know that I could run it manually then save the output to a file but that's not going to work for me in this case.
I tried using the command along with Insert (just they way I would a select stmt) but SQL didn't appreciate it.
Has anyone done this before? Got any ideas?
Sidney Ives
Database Administrator
Sentara Healthcare
View 2 Replies
View Related
Jun 25, 2014
How can i get the output (like 1 rows(s) inserted)in a file from an input file through sql command prompt.
View 1 Replies
View Related
Oct 29, 2004
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.
Any help will be great.
Tks
View 4 Replies
View Related
Jun 22, 2007
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?
View 2 Replies
View Related
Jan 8, 2001
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.
Thanks.
Tim
View 3 Replies
View Related
Mar 14, 2007
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();
Response.Write("start... <br><br>");
// ad-hoc insert
cmd.CommandText = "SET IDENTITY_INSERT Person ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Response.Write("ID = ");
Response.Write(cmd.ExecuteScalar());
Response.Write("<br>");
cmd.CommandText = "SET IDENTITY_INSERT Person OFF";
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.
View 4 Replies
View Related
May 23, 2007
(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>
View 4 Replies
View Related
May 13, 2007
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
View 2 Replies
View Related
May 29, 2008
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")
and it does...What am I doing wrong?
View 2 Replies
View Related
Jan 8, 2006
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.
View 5 Replies
View Related
May 28, 2015
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.
View 5 Replies
View Related
Jan 21, 2008
Hello,
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?
ALTER PROCEDURE [dbo].[thhill_InsertReport]
(
@RPT_ID int,
@REPORT_DATE datetime,
@COMMIT_DATE datetime,
@JOB_NUMBER nvarchar(50),
@TECH_NAME nvarchar(75),
@CLIENT_NAME nvarchar(255),
@CLIENT_CONTACT nvarchar(75),
@CITY nvarchar(75),
@STATE nvarchar(75),
@COUNTRY nvarchar(75),
@SUPPLIER nvarchar(75),
@WORK_ORDER nvarchar(75),
@RIG_NAME nvarchar(75),
@WELL_NAME nvarchar(75),
@OCSG_NUMBER nvarchar(75),
@AFE_NUMBER nvarchar(75),
@MGMT varchar(6000),
@Complete varchar(3),
@newReportId int output,
@changeCommitDate bit = 1
)
AS
SET NOCOUNT ON;
if @changeCommitDate = 1
set @COMMIT_DATE = getdate()
INSERT INTO [reports] ([RPT_ID], [REPORT_DATE], [COMMIT_DATE], [JOB_NUMBER], [TECH_NAME], [CLIENT_NAME], [CLIENT_CONTACT], [CITY], [STATE], [COUNTRY], [SUPPLIER], [WORK_ORDER], [RIG_NAME], [WELL_NAME], [OCSG_NUMBER], [AFE_NUMBER], [MGMT], [isReportComplete])
VALUES (@RPT_ID, @REPORT_DATE, @COMMIT_DATE, @JOB_NUMBER, @TECH_NAME, @CLIENT_NAME, @CLIENT_CONTACT, @CITY, @STATE, @COUNTRY, @SUPPLIER, @WORK_ORDER, @RIG_NAME, @WELL_NAME, @OCSG_NUMBER, @AFE_NUMBER, @MGMT, @Complete );
declare @reference int, @tmpReportId int
set @tmpReportId = SCOPE_IDENTITY()
set @reference = @tmpReportId * -1
SET NOCOUNT Off;
Update [reports] set [RPT_ID] = @reference where id = @tmpReportId
select @newReportId = RPT_ID
FROM REPORTS
WHERE (ID = @tmpReportId)
View 3 Replies
View Related
Nov 19, 1998
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....
View 2 Replies
View Related
Apr 29, 2007
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>
View 1 Replies
View Related
Jun 12, 2007
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!
View 1 Replies
View Related
Apr 17, 2008
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>
View 6 Replies
View Related
Aug 23, 2002
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
thank you
View 2 Replies
View Related
Jul 23, 2005
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
View 6 Replies
View Related
Nov 16, 2006
Hi
We have set up an SSIS package which goes to an FTP site and downloads files.
Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.
However, I want the package to continue to run.
Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?
thanking you in advance
David
View 1 Replies
View Related
May 25, 2007
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);
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Jun 12, 2006
<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?
View 4 Replies
View Related
Feb 5, 2008
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.
any ideas how should I go about doing this?
View 1 Replies
View Related
Sep 28, 2007
Hi all!!
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)".
View 3 Replies
View Related
Mar 24, 2006
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
View 2 Replies
View Related
Jan 14, 2008
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
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
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
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
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
View 5 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
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.
View 6 Replies
View Related
Jan 18, 2006
Is there any benefit to using Output parameters:eg.[query set-up and execution goes here]
View 2 Replies
View Related
May 27, 1999
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
:-)
take care.....kim
View 2 Replies
View Related
