Inserting Multiple Rows In Loop With A Sql Stored Procedures
Jun 4, 2008
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String) 'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName) Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable objConn.Open() command.CommandType = CommandType.Text command.Connection = objConn command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS") adapter.Fill(DataTable) 'Sql DB vars 'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn) sqlServerCommand.CommandType = CommandType.StoredProcedure sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.
For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...
declare @card_start dec(16) set @card_start = '1234000012340000' declare @card_count int set @card_count = 100
i want to grant exec permissions on all of my user stored procedures. but this process of writing 'grant exec sp_tblAction on public' for each procedure would take years. how can i write a query that loops through all stored procedures, checks if it is a user created procedured, and then grant exec permission for that procedure? can it be done?
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID Primary Key TeamCode a code value representing different teams Week will equal to 2Points nullable valuee.g.Table 1======UID TeamCode Week Points1 A1 1 1002 A2 1 99trying to insert into table 1Table 1========UID TeamCode Week Points1 A1 1 1002 A2 1 993 A1 2 null4 A2 2 nulletc...As you can see, UID is primary key, TeamCode may repeat according to week value Week is a constant Points will be nullHow can I do that with a single Insert Command? Thank you for your help. :)
I am trying to insert 9 rows into an table at the same time. My situation is this...
I have a survey page. There are 9 parts with each part ment for an individual person. Each part has 8 questions, and each part has the same 8 questions
The questions are answered using one of the answers in a drop down box.
So when the surveyer clicks submit, all the 9 parts should be entered into the table.
If this is confusing, I have the form up on the Internet at...
http://www.lavenderlane.ie/wage_survey_test.asp
I can insert one part no problem, (when I reduce the form to only 1 part) but i need to insert all of the 9 parts simultaneously. I reckon its some sort of for loop but if u could help me out i would appreciate it!
Is it possible to insert multiple rows in a table using one INSERT statment. If yes, how can I do that ? I tried doing this using the substitution method.
Using Substitution Method - This is how,I proceeded.
I apologize since this seems to be a fundamental question, but I did try to search and there seems to be something wrong - after clicking on the "search" button, the page just will not update... I even tried "advanced search" but no luck there, either.
Anyway, my question is about writing multiple data rows to tables in a SQL Server database. Currently I'm using embedded SQL from a VC++ 2005 .Net program to write data to a SQL 2005 Express server (either on the same PC or on another, via TCP). As data becomes available, I issue INSERT statements, one by one. One row is about a dozen columns, typically 8 bytes each, for each of the few tables in the database. In most cases, I have several rows of data available at the same time. Those rows come at the rate of around 200-1000 per second for the different tables. With that setup, my SQL server is not able to keep up - the data ends up getting buffered in the program, waiting for the server, the server process uses just about all the CPU cycles it can get, etc.
I'm reading the "SQL in a nutshell" book from O'Reilly but it's mostly language oriented, it doesn't say much about performance improvements. It would seem natural that one could improve the performance. For example, when I do a SELECT query, the data comes very quickly, which makes me think that it is the overhead of making those individual calls with small amount of data. I would think that I should be able to request that multiple rows are written at the same time with a single SQL statement, and that this would improve the performance. However, I have no idea how to do that.
I did try one thing - enlisting the sequence of INSERTs into a single transaction, thinking it will all get buffered and only get written to the server after the Commit command is issued. I did that but it doesn't seem to help. What should I try to alleviate this problem? One thing to consider: although I use SQL Server for testing, I am trying to keep compatibility with other databases, e.g., ODBC to any available data source, including MS Access over Jet. I would love it if the solution to this were compatible (i.e., not involve any Transact SQL or other vendor-specific tricks). I thank you in advance for your assistance!
Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)
Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece. thanks.
Hello there! I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with. Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50) I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail"). I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using: Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End Sub When I try to run my application I'm getting an error which reads: Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated. The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong! The code I'm using for my stored procedure is as follows: ALTER PROCEDURE AddCustomer ( @CustomerID int, @Email nvarchar(50), @Password nvarchar(MAX), @Name nvarchar(50), @Address1 nvarchar(50), @Address2 nvarchar(50), @Address3 nvarchar(50), @City nvarchar(50), @County nvarchar(50), @PostCode nvarchar(50) ) AS INSERT INTO Customers (Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) VALUES (@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode) I'd be really grateful if anyone could help me out with this. Thanks in advance, Luke p.s. just incase it helps, here's my createaccount.aspx page: <%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %> <%-- Add content controls here --%> <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1"> <span style="text-decoration: underline"><strong>Create Account<br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)"> <SelectParameters> <asp:Parameter Name="CustomerID" Type="Int32" /> <asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Password" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address1" Type="String" /> <asp:Parameter Name="Address2" Type="String" /> <asp:Parameter Name="Address3" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="County" Type="String" /> <asp:Parameter Name="PostCode" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /> </strong></span> <table style="font-weight: bold; width: 394px; text-decoration: underline"> <tr> <td style="width: 111px; height: 21px; text-align: left"> Email:</td> <td style="height: 21px"> <asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Password:</td> <td style="height: 21px"> <asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Name:</td> <td style="height: 21px"> <asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 1:</td> <td> <asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Address 2:</td> <td style="height: 21px"> <asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 3:</td> <td> <asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> City:</td> <td> <asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> County:</td> <td style="height: 21px"> <asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Post Code:</td> <td> <asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td> </tr> </table> <asp:Button ID="btnSubmit" runat="server" Text="Submit" /> </asp:Content> Thanks again
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
I am trying to execute a simple SELECT statement in a stored procedure from within Visual Studio 2005. The procedure executes but returns no rows. (There are rows in the table and a VIEW using the same SELECT statement does return the rows.)
I added a PRINT statement to the stored procedure to see if it executed and it did print my text in the output window as expected.
The SQL server is running on Windows Server 2003 with latest service packs and patches. I had detached the database while doing maintenance on our system and re-attached it later.
I can not find any reason why it refuses to return rows.
I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error? In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL. To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures. To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed. I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that. I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work. Thank
My stored procedure looks like this: ALTER PROCEDURE dbo.Calculate AS BEGIN SET NOCOUNT ON declare @lastUpdate varchar(15) declare @sql nvarchar(4000) declare LastUpdate cursor for select updateID from currentUpdate OPEN lastUpdate FETCH NEXT FROM LastUpdate INTO @lastUpdate SELECT @sql = 'INSERT INTO A ...WHERE ..' + @lastUpdate EXEC(@sql) SELECT @sql = 'INSERT INTO B ...WHERE...' + @lastUpdate EXEC(@sql) SELECT @sql = 'UPDATE ...WHERE...' + @lastUpdate EXEC(@sql) CLOSE LastUpdate DEALLOCATE LastUpdate END
When I run this stored procedure from my web page, only the first sql is executed and then I get a timeout message. When I execute the stored procedure manually on the server, all the sqls are executed correctly. How can I solve this problem? Thank You.
Am i the only one who is forced to open multiple instances of SQL Server Enterprise Manager in order to edit more than one stored procedure at a time? A lot of times i'll be comparing two sprocs or writing one based off of a "template" i wrote earlier. But i have to open two instances and alt-tab between them. Is there an easier way? (aside from pasting it into a notepad and using that)
Hi, I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table. i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates. As I know nothing about stored procedures I'm not even sure how to go about this?? Regards. Peter.
I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyzer I get multiple errors but the one that is coursing me the most bother is
‘Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134 Incorrect syntax near the keyword 'procedure'.’
What does this mean and why can’t I run more than once Stored Procedure at once?
I am using multiple stored procedures which are using same set of tables at a time . As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing. My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me the most bother is
€˜Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134 Incorrect syntax near the keyword 'procedure'.€™
What does this mean and why can€™t I run more than once Stored Procedure at once?
Hello All,I can not figure out how to accomplish the task that I want. So, please help.I have two tables, Parent and Child tables. For both Tables I have stored procedures for inserting records. I simply pass parameters for field values to stored proecdures and Stored procedures simply insert record with those values. I am first inserting record in Parent table and then in child table via calling their stored procedures one by one.Now, what I want to do is, after inserting record in parent table successfully, If record can not be inserted in child table successfully then I want to rollback, I also want to undo the insertion operation done in previous parent table's stored procedure call. The problem is, I can not span a Transaction across multiple stored procedure. If I begin a Transaction in a stored procedure for a Parent table, then I have to either roll back or commit that transaction in the same stored procedure. How can I span a transaction across multiple stored procedures, so that I can rollback in the Child Table's stored procedure in such a way that the Record Insertion of the Parent table can be cancelled. What to do...? Please help, it's urgent. Thanks to all.
I have my database and i generated the script that creates the entire database. My database has some stored procedures.
When i create the script without the stored procedures, everything works fine when i try to reinstall the database. But if i create the script with the stored procedures, i get the following error message when i try to reinstall the database:
Code:
System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near the keyword 'PROCEDURE'. Must declare the scalar variable "@time1".
as i understand, i must have 1 storedprocedure per script. I tried separating one stored procedure per script and everything works fine, the problem here is that i have many many stored procedures. Now, is there a way to have multiple stored procedures in one single sqlscript?
Do i really need to separate my stored procedures in one script per stored procedure or is there any way to put them all in one single script?