.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

Query doesn't complete - pls help

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi, the following query which has been used many times before, just never seems to complete this time. Other than a table name, nothing has changed. The table being updated has 16 million rows and the 2 views used in the select have 216 and 360 million rows. The query has been running for 18hrs now. It has necessary indexes. my search online, found some results where it says that sometimes with SQL 2005, SP2, some people have had this problem, but did not find anything that would help me. I checked for indexes, ran the DBCC free proccache killed and restarted the query with a different session (out of desperation) nothing made difference. When I checked activity monitor, someitmes, it would display as sleep_task. The CPU and Disk I/O numbers keep changing, but the timestamp for the last batch is the same as when I started the query. Also tried the SQL profiler, but did not find anything useful, as my query did not even show up in that trace. There are actually multiple queries like this, and this is only one of them. The server is a pretty robust one and also there were times, when there was nothing else running on the SQL server and still the query won't complete. Query: with cte as (select distinct p.PID id , p.HouseHoldID, Q.[Living Unit ID] , q.[source code] sc, q.[sequence number] sn from vw_All_HH_Persons p join vw_All_Household_Details h on p.hhde

View Complete Post

More Related Resource Links

Workflow doesn't complete when a task is updated directly from an infopath form.

I have an approval workflow that creates a task for a group to approve or reject an InfoPath form. But if a member of the group opens the form and updates the task from there, it doesn't complete the workflow. It only completes the workflow if they go to the actual task and update it from there. Does anyone know how to fix that?

2005sql server doesn't respond to the query

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>FA</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/&g

sp_send_dmail doesn't work in Agent job with query parameter


I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):

 EXEC msdb.dbo.sp_send_dbmail
 @recipients = N'name@email.com'
 ,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
 ,@subject = N'Disabled Jobs on SERVER1'
 ,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
 ,@attach_query_result_as_file = 1

This query works fine when running from a query window.  however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):

Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

If I try other queries in the @query parameter, everything works fine when running from the job.  As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails.  I ran a profiler trace, and I found a more detailed error message that occurs during the execution of the job, unfortunately it involves an undocument SP:

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status

Pulling report, query almost complete......


I have a table structure and query below......

in the activity table there is a field named isCompleted and if this =1 then the activity is completed...

i want to pull this same report but only for the ppl in the XREFEmployeesInvitations that have completed activityId 10

USE [Wellness2010_Current]

/****** Object: Table [dbo].[XREFEmployeesInvitations] Script Date: 09/28/2010 15:03:05 ******/


CREATE TABLE [dbo].[XREFEmployeesInvitations](
	[EmployeeId] [int] NOT NULL,
	[ActivityId] [int] NOT NULL,
	[IsSpouse] [bit] NOT NULL,
	[HasBeenViewed] [bit] NOT NULL,
	[IsCompleted] [bit] NOT NULL,

SQL query gives results, but Visual Studio doesn't



I have problem accessing data from SQL query in ASP.net. I checked the query and it returns two fields, both with data, but when I run the same query in Visual Studio I get the following error: "Invalid attempt to read data when no data is present."

The problem is that data does exist...

This is what SQL query returns when executed on SQL Server Management Studio (I bolded the different header so you can see the difference):

url idClanak
Upload/Default2.png 8
Upload/Default3.png 8
Upload/Catrinel Menghia 7.jpg 8

The following is the ASP.net code (I bolded the piece of the code that apparently has some problems):

    private void Upisivanje(int idClanak, string naslov, string datumString, string kategorija, string autor, string slika, string opisSlike, string sadrzaj)
        string facebook = "<a href='http://www.facebook.com/sharer.php?u=" + Request.RawUrl + "&t=" + naslov + "'><img src='Images/Facebook.png'/></a>";
        string twitter = "<a href='http://twitter.com/home?status=" + naslov + " na: " + Request.RawUrl + "'><img src='Images/Twitter.png'/></a>

MOSS 2007 - 64-bit SP2 Upgrade Breaks Central Administration and doesn't complete


I am setting up a test Intranet Farm for a production Intranet. Everything is being installed on one server. SQL 2005 enterprise 64-bit edition is being used and has been upgraded to SP3. MOSS 2007 with SP1 64-bit edition was used to installed SharePoint and the central administration website installs successfully. I then downloaded WSS 3 SP2 64-bit edition and carried out the installation. As it is a Windows 2008 server with SP2, I use the 'run as administrator', just in-case, even though I am logged on as a local administrator.

The SharePoint Configuration wizard is run post-installation and access to the Central admin website is still available. I am restoring the Farm config for the production Intranet to this test Farm using the in-built backup feature of MOSS 2007 and I am using the GUI. As the production farm has MOSS 2007 SP2, I need to also install that update. I download the MOSS 2007 64-bit update and towards the end of the installation (prior to the configuration wizard appearing) an error occurs stating that the Installation of the Package failed, which appears in a prompt box and a box appears in front with regards to whether a report should be sent.

Central admin then has a 'this page cannot be displayed'.


ObjectDataSource doesn't query on postback


I have two methods for an ObjectDataSource:

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
        SearchWord = txtSearch.Text

that should fire first and then I have this method that I expect to fire after click event above and on every postback:


Protected Sub objWebUploadLog_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles objWebUploadLog.Selecting
        If SearchWord = "" Then
            objWebUploadLog.SelectMethod = "GetWebUploadLogByUserID"
            objWebUploadLog.SelectCountMethod = "GetWebUploadLogCountByUserID"
            Dim param As WebControls.Parameter = New WebControls.Parameter("search", TypeCode.String)
            objWebUploadLog.SelectParameters("search").DefaultValue = SearchWord
            objWebUploadLog.SelectMethod = "GetWebUploadLogByUserIDAndSearch"
            objWebUploadLog.SelectCountMethod = "GetWebUploadLogCountByUserIDAndSearch"
        End If
     End Sub
But selecting event doesn't fire on a postback that is initiated by

Linq to XML query doesn't show data with null values...need it to!


I've got a web service that returns xml from a linq query.

If i have fields 'name' and 'title' my linq query will return results that have data in both 'name and 'title' fields.

How can I get linq to return a query when both fields are populated, and when only the name field contains data?

Here's what I've got:

        public XmlDocument GetUsersByDate()
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);


            var SelectedUsers = (from u in db.Users
                                 select "<UserName>" + u.UserName + "</UserName><x>" + u.Title + "<x>").ToList();


Crawl doesn't work and Query server is not responding; Search works only for old (crawled) contents


We have following three servers with MOSS 2007 STD deployed in SharePoint server farm,


App01- Query Server

App02 - Index Server (We are using this server as dedicated web front end computer for crawling)

SPDBServer - SQL 2005


Search was working fine till 01/05/2008 and it stopped working after this date only for new contents meaning search is still working fine for the old contents which were added before 01/05/2008 but not for new contents added after 01/05/2008.


We have been working to find out reasons why did that happen? We have found following event ids on Query server,


Event ID:10042 - A new query machine, 'App01', has been added to the query rotation based on changes to farm topology.

Complete SQL Solution

SQL Server Solutions: SQL Server 2000/2005 Related Free Snippets.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Parallel port complete: programming, interfacing & using the PC's parallel

Parallel port complete: programming, interfacing & using the PC's parallel


Beginner: this doesn't make sense to me


Do you need and "empty" script tag in the head to run scripts that are in the body?  Also, can you run JQuery scripts if they're in the <head> tag?  The first sample only works if the JQuery script is in the form (and body) tag, not in the head - even if I add $(document).ready(function() { });

This works

<head runat="server">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" />
<script type="text/javascript">
<form id="form1" runat="server">
<a href="">Here is a link</a>
<script type="text/javascript">
$('a').click(function () {
alert("you clicked me!");


This doesn't work.  Notice I removed the second <script> tag from the head

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="serve

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

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