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

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

How to find the statement which causes a 'FailedOperationException' when multiple commands are exe

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

When executing a large .sql file contents, which creates a lot of objects on a database, running the ExecuteNonQuery over a Database object, we're getting a FailedOperationException. Is there a way to find which of the SQL statements passed in the string is causing the exception, and have some more 'familiar' error message?

Are there other options to run the multiple T-SQL statements created with the SQL Server Management Studio database script generation feature?

We are using the SMO on a .NET Fx 3.5 app running against a SQL Server 2008 instance on a 32 bits computer.

Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt

View Complete Post

More Related Resource Links

how to multiple condition in if statement in asp.net C#



i am using if condition to find out a time

if(time=="08 AM)


response.write"shift A";


but problem is how can i put multiple condition in if like

if(time=="06 AM" and "08 AM" and "07 AM")

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Update Statement help from Multiple database

Hi I am having two separate datbase on same sever one datasbase name private from where i am executing my below query and other name is ODS Table name in Private=[A].[DimCapabilitySpecialty] Table name in ODS = [A].[CapabilitySpecialty]   both of them having column SpecialtyCd   i want to update the flag value of ODS table where there is particular specialitycd missing in source means table in private database   so for that i wrote below query   update A.DimCapabilitySpecialty set ActiveInd ='I' where SpecialtyCd !=[ODS].[A].[CapabilitySpecialty].[SpecialtyCd]   But it is giving me this error   Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ODS.CapabilitySpecialty.SpecialtyCd" could not be bound.

Cancel multiple WFs at once & global list find & replace

We have a number of products in our company, and a need for an email alert for various reasons for each product. Thus, I have a SP list where each list item has a drop-dead date, plus the manager's name and manager's email address as well as a note as to what needs to be done. When a new item is created, or a list item is changed, a workflow is initiated. Remember, each single product associated with a manager can have dozens of these email alert list items with differing drop-dead dates. The workflow is designed to send alerts out at stated intervals before the drop-dead date. The problem is that the manager in charge of a given product can and does change. What I have to do now is to open each of the items that pertain to that manager, stop the existing workflow, change the manager's name and email address in the item, then save it (thus starting a new workflow). This can get tedious when a single product can have several dozen list items corresponding to several dozen drop-dead dates. I would like to be able to filter the list, kill all the workflows that are connected to each item in the filtered list, change the manager name and email using the Datasheet facility, then save. At that point, X new workflows would start where X is the number of list items in the filtered list. 1. Is that possible (I doubt it) in our native SP 2007? 2. Does anyone

It is possible to alter multiple columns within a single alter table statement?


It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.


Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Need help with SELECT statement for multiple tables


Here's my current SELECT statement:

SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId

This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script.  There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table).  I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site.

So how can I adjust that SELECT statement so this one is combined with it somehow?:

SELECT Email FROM ExtraEmails WHERE Rules = 'true'

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?

Commands - multiple KeyBindings


I have a command that I want to use with several different menu items, but in each case the command parameter would be different. For example, suppose my command is call SelectCommand and I have three menu items called Select 1, Select 2 and Select 3:

<MenuItem Header="Select 1" InputGestureText="Ctrl+Shift+F1"
 Command="{x:Static local:MyClass.SelectCommand}"
<MenuItem Header

select statement to find a column which contains the word



I am trying to find out how to select a row of data from a table, where a column contains a keyword (from a querystring)

How would i write this select statement? I only want the column to contain a keyword, along with other words, rather than the columns only data is the keyword.

eg: the row below would be returned because column2 contains 'hello'




multiple join in one statement


1. select -----

from table1 t1 join table2 t2 on t1.a=t2.a

left join table3 t3 on t1.a=t3.b

2. select -----

from table1 t1 join table2 t2 on t1.a=t2.a

left join table3 t3 on t2.a=t3.b


I'm not sure about the behavior of above 2 statements. What kind of result do i expect?

When should i use both kind of joins?


Multiple commands on the codebehind after button_click event to insert and read inserted.


I want to be able to show the record that was just inserted after add_button_click event. I run the insert from mycommand and then set mycommand2 to run the read to populate a details view.

I have a feeling I am going about this the wrong way.

The insert fires correctly but the details view does not populate.

 Dim connectionString As String = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString
        Dim insertSql As String = "INSERT INTO Customers(CompanyName, CompanyAddress1, CompanyAddress2, CompanyCity, CompanyCounty, CompanyZip, CompanyState, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanyContactFirst, CompanyContactLast, CompanyActive, CompanyID, CreatorID, CreatorCompanyID, PrivateCustomer)VALUES(@CompanyName, @CompanyAddress1, @CompanyAddress2, @CompanyCity, @CompanyCounty, @CompanyZip, @CompanyState, @CompanyPhone, @CompanyFax, @CompanyWebsite, @CompanyEmail, @CompanyContactFirst, @CompanyContactLast, @CompanyActive, @CompanyID, @CreatorID, @CreatorCompanyID, @PrivateCustomer)"
        Dim insertNewSql As String = "IF NOT EXISTS(SELECT * FROM Customers WHERE CompanyName=@CompanyName) INSERT INTO Customers(CompanyName, CompanyAddress1, CompanyAddress2, CompanyCity, CompanyZip, CompanyState, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, 

Writing multiple if statement in where clause in Stored Procedure



I have one Stored Procedure which returns some value. now i have some different conditions in my select query so can i write them in my where clause instead of writing the whole select query again for different conditions? 


My Current Select Statement in SP looks like :








Now i have two more conditions in my Select Statement that are

@ProductList varchar(max),

@StatusList Varchar(max)

and they gets the list of id of their name from another function that is made for them respectively fun_GetProductId and fun_GetStatusId

so with this my Select Statement in SP will look like 






     (UniqueRef='@UniqueRef' or @UniqueRef='') and 

    (Productid in (Select * From fun_GetProductId(@ProductList))) and

   (StatusId in (Select * From fun_GetStatusId(@StatusList))) 

Selecting data from multiple tables whilst using a union statement.



any help on this would be great.

I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different  database that contains information regarding the group of the data.

I so far have the following:

SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx

I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.

Thanks again,


Multiple calculations in a single Statement in SQL Ce


Hi guys

I have a table that has a date, amount of litres, price per litre. The table is updated every month, what I have been trying to do is display the total revenue for each year ( amount of litres * price per litre this will give me the revenue for each month and then add each month together to return that years revenue) I can't seem to get this statement right, any tips?



Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Binding the multiple drop down boxes in GridView using JQuery in ASP.NET

Here is the code for binding the multiple drop down boxes in GridView using JQuery in ASP.NET

ASP.Net Gridview Edit Update Cancel Commands

In ASP.Net 2.0, GridView Control also provides the functionality to edit and update the data retrieved from the database using CommandField template. You can cancel the action using Cancel Command of the CommandField. GridView consists of events that can be used to perform the actions like edit, update and cancel upon the Data items displayed in the ASP.Net GridView Data Control.
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