.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

How to avoid "'CREATE VIEW' must be the first statement in a query batch" when I need to use SET Q

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
I have a problem that I have been unable to solve.  I have a stored procedure in a database on server A that needs to create an indexed view in a database on server B.  (The environment is SQL Server 2005.)  The procedure on server A does the following:  (The EXEC statement inside the quotes is really a variable in my procedure, I've just substituted the value to make the example understandable.) EXEC sp_executesql 'EXEC [ServerB].[Database].dbo.sp_executesql @sqlStatement;' The "outer" sp_executesql causes the "inner" sp_executesql to be execute within the database on server B.  (@sqlStatement contains the CREATE VIEW syntax.)  I am able to create the view that I want to index with no problem.  However, when I attempt to create an index on the view in a subsequent step, it fails with the message "Cannot create index. Object '_dta_mv_51' was created with the following SET options off: 'QUOTED_IDENTIFIER'".  Fair enough, but if I attempt to precede my CREATE VIEW statement with the required QUOTED_IDENTIFIER, I receive an error to the effect that "'CREATE VIEW' must be the first statement in a query batch".  I'm not sure how to get around this problem.  Does anyone have the answer? SQL Server 2008 doesn't object to indexing a view that was created with QUOTED_IDENTIFIER

View Complete Post

More Related Resource Links

Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

How to detect and avoid memory and resources leaks in .NET application

Despite what a lot of people believe, it's easy to introduce memory and resources leaks in .NET applications. The Garbage Collector, or GC for close friends, is not a magician who would completely relieve you from taking care of your memory and resources consumption.

I'll explain in this article why memory leaks exist in .NET and how to avoid them. Don't worry, I won't focus here on the inner workings of the garbage collector and other advanced characteristics of memory and resources management in .NET.

Entity Framework: Anti-Patterns To Avoid In N-Tier Applications


Danny Simmons explores some anti-patterns you should look out for when building n-tier applications with the Entity Framework.

Daniel Simmons

MSDN Magazine June 2009

SQL Security: New SQL Truncation Attacks And How To Avoid Them


Exploits using SQL injection have drawn a lot of attention for their ability to get through firewalls and intrusion detection systems to compromise your data layers. Whether it's a first-order or second-order injection, if you look at the basic code pattern, it is similar to any other injection issue where you use untrusted data in the construction of a statement.

Bala Neerumalla

MSDN Magazine November 2006

No More Hangs: Advanced Techniques To Avoid And Detect Deadlocks In .NET Apps


You can combat deadlock using a combination of disciplined locking practices which Joe Duffy aptly explains in this article.

Joe Duffy

MSDN Magazine April 2006

Scale: Real-World Load Testing Tips to Avoid Bottlenecks When Your Web App Goes Live


Load testing should be part and parcel of every Web development effort, and it should be performed early in the process. However, if you think you can load test using your development environment, you're going to have some surprises when you go live. In this article, the authors outline the process of planning your load testing effort, considering which machines to use, how many users to simulate, which tools are right for you, and how to interpret your results.

Jeff Dunmall and Keith Clarke

MSDN Magazine January 2003

How do I avoid chunked encoding either in HttpListener or in SimpleWorkerRequest?



I am writing a simple web server that uses HttpListner (http.sys) as a listener and an output conduit, and a SimpleWorkerRequest to process ASP.NET requests. All works well, but when an application my server hosts outputs chunked response, ASP.NET calls SimpleWorkerRequest's SendResponseFromMemory() method and passes in data that is chunked-encoded. When I write the output to HttpListener's output stream, it gets chunk-encoded again, breaking the whole thing. So I ended up writing chunked decoder in the middle, which makes it inefficient because the response gets massaged three times instead of one:

  1. Chunked-encoded by ASP.NET HttpResponse, then
  2. Decoded by my code in the SimpleWorkerRequest.SendResponseFromMemory(), and
  3. Gets chunked-encoded again by writing to HttpListner's output stream.

My question is how do I tell either SimpleWorkerRequest or HttpListener not to chunk-encode the response so the encoding can be done only once by either of those? (Preferably, it's SimpleWorkerRequest that needs an option to pass the response to the web server without changing it so that HttpListener would encode all responses, whether the come from ASP.NET or not, for example from CGI request processor).

Thank you,

How to avoid network problems while calling a webservice in a SQL CLR trigger?

Hi All, My Goal: DB synchronization between SQL server 2005 and Mysql database via web services. I have created a SQLCLR trigger, in which i'm calling a web service to sync/update remote (MYSQL) DB over a specific constraints. After getting the acknowledgment from web service i'm updating the sync flag to success (In SQL SERVER2005). If any network delay happens i am unable to know whether sync has done successfully or not. How to avoid network dependency here? Is there any reliable queuing mechanism available in sql server 2005 to eliminate dependency over network? I am looking any suitable service/approach in sql server 2005 that can take care of calling web services as asynchronously and update the status of sync flag irrespective of network. Thanks in advance if anyone provide the good approach as step by step in detail.

How to avoid viewstate in page load event??

Hello Everyone, Here Is my scenario,I haveMaster Page -> ASPX page -> User ControlUser control page is contains all dynamic bind controls (e.g Repeater) depended on ASPX Page (Only one parameter say ItemNo) The problem is, if I bound user control on page load event with passing respective parameters then it's create to much view state (I checked source file and it's almost 1.5 MB) In other way if I put one textbox, and a command button and I pass required parameter on button click and load the control then page size is ok (I checked and it's  < 500KB) So is it any way I can reduce my page/state size??

How to avoid warnings in SQL job when Action is taken "on Failure" is "quit the job reporting succes

Hi All, We have set Advanced property of a step On Success and On Failure action as Quit the Job Reporting Success. But it's giving Warning while executing. How can we ignore this warning ? Please suggest???? Thanks in advance.

How to avoid sluggish scrolling in a large DataGrid?

If you have a large DataGrid with many virtualized rows and a screen full of columns, scrolling becomes very choppy - only a few updates per second. Here's a repro: Create a WPF app with this XAML: <Window x:Class="WpfApplication1.MainWindow"         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"         Title="MainWindow" WindowState="Maximized">     <DataGrid Name="_grid" AutoGenerateColumns="False" ColumnWidth="60" /> </Window> and this in the code behind: public partial class MainWindow : Window {     public MainWindow()     {         InitializeComponent();                     for (int i = 0; i < 30; ++i)         {             var c = new DataGridTextColumn();             c.Binding = new Binding();             c.Header

How to avoid Multiple log in for same user

how to avoid multiple log in for one user at a time?

How can I avoid sending UserName/Password for every call?

Hi I am using UserName/Password authentication with X.509 certificate in my WCF application. In this scenario I have to set UserName/Password credentials on every call that means I have to keep UserName and Password somewhere in my client application (ofcourse after user loged in). I am not sure that's a good idea. Does any body know how can fix this problem? I want user to login one time and afte that my client application can communicate securly with my WCF services. Is it possible? Thanks

Writing jquery for tabs to avoid hardcoding

I have created the tab  user control and loading it into webpart i tried to download jquery from the standard site for jquery but i dont want  to change CSS given by designer  so i have applied Class="Active" and display="block" in javascript function as foolows but this is not good approach  if(id=="hrefid1")//             { document.getElementById(id).setAttribute("class", "active"); document.getElementById("dvDtl1").style.display="block"; //tabs document.getElementById("href2").removeAttribute("class"); document.getElementById("href3").removeAttribute("class"); //tabcontents document.getElementById("dvDtl2").style.display="none"; document.getElementById("dvDtl3").style.display="none";   following is the usual html structure for tabs <div > <ul> <li> <href> tab1 <li href>tab 2 <li href> tab 3> </ul> //contents using divs <div1 </div>   how can i write jquery without having the requirement to hardcode the ids in javascript? if anyone has implented tabs . pls go on .   In short , i need to get the selected tab and  change the css  of selected and -non selected tabs dynamicaly

How to avoid StackOverflowException when i use System.Windows.Markup.XamlWriter.Save

do following step, you can reappear the exception. step 1: Create a WpfApplication, named by "WpfApplication2" setp 2: Add two references, Microsoft.ReportViewer.Common and Microsoft.ReportViewer.WinForms step 3: paste the following code to MainWindow.xaml <Window x:Class="WpfApplication2.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:wf="clr-namespace:Microsoft.Reporting.WinForms;assembly=Microsoft.ReportViewer.WinForms" Title="MainWindow" Height="350" Width="525" Loaded="Window_Loaded"> <Grid> <WindowsFormsHost Height="100" HorizontalAlignment="Left" Margin="10,10,0,0" Name="windowsFormsHost1" VerticalAlignment="Top" Width="200" > <wf:ReportViewer Name="rp"></wf:ReportViewer> </WindowsFormsHost> </Grid> </Window> and paste the following code to MainWindow.xaml.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.M

How to avoid app crash if .NET framework uninstalled/disabled

In Windows 7, it is possible to disable the .NET framework via the Control Panel's "Turn Windows features on or off". If this is done, running a .NET application causes an app crash, which is logged to the Windows Event Log, and also generates an application crash report that is sent back to Microsoft (WER). How can we avoid this app crash?  Because the app requires .NET, I don't think it can perform a check for the framework's presence itself.  Providing an application configuration file with "requiredRuntime" specified does not avoid the app crash. The only solution we can come up with is writing an unmanaged wrapper around every managed application. The unmanaged code could check for the framework being installed, and then launch the managed process.  This could be tedious to do for every application. Ideally, this would just fail more gracefully and let the user know that the .NET framework is required but not available.  This solution should probably come from the OS, but we're looking for a way to be able to handle this ourselves for now. Any ideas appreciated. Thanks, David

Login View - How to avoid unnecessary queries to db to GetRolesForUser

Hi,I have built an asp.net web site with .net framework 3.5 and I am trying to avoid sending unncessary queries to my database in order not to have high server load. I have a login view in my master pages and it contains templates for anonymous users and members.To lighten my server load, I am using Sql Data Profiler and Database Tuning Advisor. I noticed that every page load triggers a stored procedure to get user role using dbo.aspnet_UsersInRoles_GetRolesForUser stored procedure.My question is that if it is necessary to check for every page load or I can store it somewhere and check later.is it Login View or Login Control sending queries ?is it about access rules to member's pages ?oris it because I used the login controls in my master page ?any suggestions and comments will help me.Thanks
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