.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

(INSERT EXEC statement cannot be nested.) and (Cannot use the ROLLBACK statement within an INSERT-EX

Posted By:      Posted Date: September 25, 2010    Points: 0   Category :ASP.Net

hi all
i have a very important issue,

read this scenario please

i have three Stored Procedures Sp1,Sp2 and Sp3 .

the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.

if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

View Complete Post

More Related Resource Links

INSERT Statement Inside a UDF?

I am using SQL Server 2005.  I know you can not put INSERT or UPDATE statements in a UDF.  I am looking for an alternative to getting the end result I need since this call has to go at a specific point in the code or it will never work.  I have a UDF which packs smaller boxes into a bigger box efficiently.  The UDF typically runs and creates as many bigger boxes as possible until all smaller boxes are used and then it returns a TABLE variable back to a stored procedure.  I need to insert a record into a new table at the point where each bigger box is full before it moves on to the next bigger box.  There is only a couple alternatives I can see but don't really know if each is possible.  I am open to an ideas: 1. Call a stored procedure from the UDF which executes the insert statement 2. Using EXECUTE to perform the insert statement 3. Re-write the UDF as a stored procedure.  It must be able to table two inputs and return a table which can be unioned with two other views. 4. Anything Else UPDATE: Scratch idea #1 since it says I can only call other functions or extended stored procedures UPDATE: Scratch idea #2 as it did not work

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Insert Into SQL Server inside a For Each - Next Statement

I have the following code to For each Item in the file uploader, Insert Into database.... but It doesnt work in the For Each Statement but if i take it out of the For Each statement it works.   For Each item As AttachmentItem In Attachments1.Items            Dim objConn2 As New SqlConnection("Data Source=XXX")            objConn2.Open()            Dim objCmd2 As New SqlCommand("INSERT INTO clientport (name, event, date1, username, password, path)" & "VALUES (@name, @event, @date1, @username, @password, @path)", objConn2)                                 objCmd2.Parameters.AddWithValue("@name", clientname.Text)            objCmd2.Parameters.AddWithValue("@event", [event].Text)            objCmd2.Parameters.AddWithValue("@date1", [date].Text)            objCmd2.Parameters.AddWithValue("@username", username.Text)  &nbs

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

Insert statement giving me syntax error


Good evening,


I have a MS Access table with the "ModelID" column set as AutoNumber and primary key. The ModelID is set as Field Size --> Long Integer, New Values --> Increment, Format --> General Number, Indexed --> Yes (No Duplicates).

Here is the code i am trying to execute, and is giving me an exception -> insert into syntax error:

string strInsert = "INSERT INTO tbl_Models (fName, lName, Image, Answer1, Answer2, Answer3, Answer4, Answer5, Votes, ImageHead, Age, Program, StudentID, PhoneNumber, EMail) VALUES (@fName, @lName, @Image, @Answer1, @Answer2, @Answer3, @Answer4, @Answer5, @Votes, @ImageHead, @Age, @Program, @StudentID, @PhoneNumber, @EMail)";
        OleDbCommand cmdInsert = new OleDbCommand(strInsert, myCon);

        cmdInsert.Parameters.AddWithValue("@fName", txtFirstName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@lName", txtLastName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Image", FileUploadBody.FileName.ToString() + sRandomBody);
        cmdInsert.Parameters.AddWithValue("@Answer1", txtWhyBeAModel.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Answer2", "");
        cmdInsert.Parameters.AddWithValue("@Answer3", "

INSERT statement and Getting Data From User Into My Database



I have spent 2 days reading and trying to figure this out and everything i have read and every book I have brought only tells how to read info from a database and how to edit info that is in a database and they all display the data in the database!

I have a page created (getinfo.aspx)

On that page I have a correctly configured SQLDatasource (SQLSource1) (it is set to allow insert, edit and delete).

I have 1 fileupload control

I have 2 text boxes

I have 2 drop down list boxes

The 2 drop down list boxes are "binded" to to seperate tables (via 2 other correctly configured SQLDatasource controls 0 but with out permission to insert, edit or delete) and they correctly provide a drop down list for the user to pick from the 51 states and the 200+ Countries. Those work wonderfully).

I also have a Button Control

The table (myFriends) that SQLSource1 is "connected to" has the following columns:
Column Name  Type   Length Nullable
   userID  uniqueidentifier 16 false
   userName  sql_variant  80

insert statement does not reflect in database


I am trying to insert data into my table whose primary key is auto increment. The problem i encounter is that anytime i perform the insert, it goes through and i can view it when my application is running but as soon as i stop debugging and i run again i dont see the data again. I also notice that the insertion i did does not show up in the database. Below is the code i am using:


String name, type;
name = txtname.Text;
type = txttype.Text;

int qtyToInt = Int32.Parse(txtqty.Text);
decimal priceToInt = decimal.Parse(txtprice.Text);

shopDataSet.stockRow newStockRow = shopDataSet1.stock.NewstockRow();

newStockRow.name = name;
newStockRow.type = type;
newStockRow.quantity = qtyToInt;
newStockRow.price = priceToInt;


Insert multiple rows with a single INSERT statement


With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite".


How does this happen? the tblLinks doesn't even have a column called websiteID.  websiteID is the primary key of the table tblwebsite. LinkID is the primary key of tblLinks. There is not LinkID column in tblwebsite.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite". The conflict occurred in database "Linkexchanger", table "dbo.tblWebSite", column 'WebSiteID'. The statement has been terminated.


So how does this happen? How do I terminate the foreign key relationship? I understand I can add this to my Insert stored procedure:

DROP CONSTRAINT FK_tblLinks_tblWebSite

but that stored procedure is used for other functions in my website, and I don't know if doing that is a good idea. Is there a way to relax the constraints for the purpose of this function here?  I'm taking data from a backup links table and trying to write a new row in the links table.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...


Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "


Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you


Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

insert statement violated because Foreign key constraint


I  create an SSIS package, i use data flow to import rows from different server to local server. Unfortunately, there always be error message something about insert statement violated because of foreign key "The INSERT statement conflicted with the FOREIGN KEY constraint" since the table is transactional table that has some foreign key. I want to minimize using temporary table and trying to use toolbox item in ssis.I would appreciate any since i am ssis newbie.


[VB 2010 + sql server compact 3.5] SQL Insert Statement dosen't work (without any errors).


Hi guys!

I'm trying to insert some records in a sql server compact 3.5 database, but with no success.

The code:

Dim conn As New SqlCeConnection
Dim cmd As New SqlCeCommand

conn.ConnectionString = "datasource = .\Database1.sdf&quo

Nested listview fk-problem with insert



My problem is this, i have a nested listview that works fine but if i need to do an insert and the nested listview dont have retrived any values (that is no posts created) i cant get hold of the fk that i need. If i put a hiddenfield in the parents listview with the fk how can i find it in codebehind when i do an insert? 





How can I insert values from a query directly to a table in my database?
the query gets values from a linked server and i want to directly insert the results in my local database. This is how i thought would be done:

    INSERT INTO dbo.mytable
    EXEC dbo.QuerytoLinkedServer

When i execute dboSP_INSERTfromEXEC procedure, then i get this error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "blahblah" was unable to begin a distributed transaction.

Now, when I execute dbo.QuerytoLinkedServer procedure, i do get the values, there is no error. It just doesn't want to insert it I suppose.

Any help would be greatly appreciated!

Nested CAML Querie results in exception " Some part of your SQL statement is nested too deeply. Rewr



I am trying to mimic SQL"IN" operation using CAML. Lets take a scenario where we need fetch all the ListItems with ID within a list of IDs. The target list is realy large ( 50k records)

Possible options are from the Working with large list Whitepaper

-> CAML (Object model) -> CAML (using Webservices) -> Portal Site Map Provider -> Search API

The Custom webpart that we are developing is on live data ( NO search API) and query is user dependent (No Portal Site provider)

As our code is deployed on to the server, we can use SPobject model SPQuery, but as CAML deosn't support "IN" Operator and not more than two logical operators in we have to nested query.

When the nested query hits the 478 nested ORs then the exception is raised

"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries "

Firstly the Row lmit 2000 is resulting in multiple queries and on top of it, spliting it further results in performance impact.

Is there any other better solution???

Server Error Page: Some part of your SQL statement is nested too deeply.


Hello.  I've come across this error page with a Sharepoint Server 2007.  I've seen a few forums that say that Microsoft was going to fix this issue in its next major version, but I couldn't find out what version that was.  Is the 2007 version without a fix for this?  Is there a patch I could get, or is this part of a bigger issue?

I should mention, I've received this error page after simply editing html code in a web part.  There was no editing of sql statements.  Thanks in advance.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend