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


Post New Web Links

Query does not work in the master DB

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server
 

I have this script to find open transactions, wanted to create it as a SP sp_GetOpenTransactions in the master db, but I get a syntax error. Works just fine in the other DB. Server is sql 2008. I noticed that the master db compatibility level is 80, the userdbs are in level 100. Wondering if that is the problem here ? Why not the system dbs get their compatibility level changed after an upgrade ?

  • SELECT L.request_session_id AS SPID,
  • DB_NAME(

View Complete Post


More Related Resource Links

In BCP what is work of 'query out'

  
In BCP what is work of 'query out' is there any example"SQLSERVER DBA" "INDIA"

Can't make parameterized query work in data flow task

  

I have an OLE DB source in a data flow task.  The connection is pointing to a SQL Server 2008 instance.  My SQL works fine when I click the preview button.  Now I need to make it parameterized by adding a question mark in my where clause.  After doing so & clicking the Parameters button, I get the error described in this post .  When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement is not supported."

I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate as is doesn't work - can't evaluate).

So the question is, how do I parameterize a query?  And why does SQL here work so radically differently from SQL in an execute SQL task?


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

UpdatePanel Trigger dont work with Master Page

  

Hi

I got a aspx page using a master page. In the aspx page, i got multiple Content section:

<asp:Content ID="Content1" ContentPlaceHolderID="CPHSubHeader" runat="server">

<asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
    <ContentTemplate>

<asp:Panel ID="Panel1" runat="server">
            <asp:table id="tblSubHeader" runat="server" width="100%" bgcolor="white">
              <asp:TableRow runat="server">
                <asp:TableCell ID="TableCell1" runat="server" Width="50">
                    <asp:Label runat="server" Text="Area" Font-Names="Arial" Font-Size="14px"></asp:Label>
                </asp:TableCell>
                <asp:TableCell ID="TableCell2" runat="server" Width="100">
                    <asp:DropDownList runat="server" ID="ddlArea" AutoPostBack="true" OnSelectedIndexChanged="DDLArea_SelectedIndexChanged"></asp:DropDownList>
                </asp:TableCell>
                
                <asp:TableCell ID="TableCell3&q

Master Data Services, SSRS, and the Query Designer.

  

Hey everyone,

 

For the most part, I have this thing figured out; however, I now need to assign to a property of a method's parameter a collection of member types.

 

Here is my query (I have emboldened the segment of the query which my question is directed towards):

<Query>

          <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="MetadataGetRequest">

          <Parameters>

          <Parameter Name="ResultOptions" Type="XML">

<DefaultValue>

<Attributes>Identifiers</Attributes>

</DefaultValue>

          </Parameter>

 <Parameter Name="SearchCriteria" Type="XML">

<DefaultValue>

<MemberTypes attr0="MemberTypeArra

Cant get a multi join query to work! Going mad!

  

Hi, I am having trouble with a view I am writing which provides the available status of a new or existing hardware installed at an organisations site for a web application.

The view runs a number of queries which are unioned to provide the correct available statuses.  The logic is as follows:

T_Site - lists all sites at an organisation which have none or more gateways, or bureau gateways.  A site also has site configuration which says how many active gateways (bureau and gateway) a site can have.  So a site with a particular site config should only be allowed to have the maximum number of ACTIVE gateways (gateway + bureau) gateways which the site config allows.

The other parts of the query involve:
T_gateway - table holding gateway details
T_Bureau_Gateway - table holding bureau gateway details
T_Site_config - table holding site configuration details.

The view originally worked fine since it just took into account the number of gateways and their statuses and returned what gateway sites and gateways that could have an active status assigned to them.

But now I need to add in the bureau gateway table, but this is causing problems.  The joins aren't working and bringing back lots of rows which aren't correct.

Logic should be:

Get site_id, gw_id, where the total number of active gw's is l

This query does not work, it returns more than 5 Categories

  
The following query, is supposed to return only the 'top 5' categories of sales, based on sales amt. However, it is returning much more than 5. Perhaps someone can see what the problem is?

USE [PBS]
GO
/****** Object: StoredProcedure [dbo].[_USP_QuoteMonth_Top5_only5] Script Date: 11/03/2010 17:12:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* --------------------------------------------------------------------------------------------------------*/
ALTER PROCEDURE [dbo].[_USP_QuoteMonth_Top5]
(
@FromDate VARCHAR(25),
@ToDate VARCHAR(25),
@CUSTNAME varchar(250),
@Department varchar(4000),
@Category varchar(4000),
@SalesTypes Varchar(50),
@Debug bit = 0
)
AS
/*
UNIT TESTING
============
EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '01/31/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1'
EXEC [_USP_QuoteMonth_Top5_only5] '01/04/2009', '01/08/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1', @Debug = 1

EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '12/31/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1'
EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '12/31/2009', 'Northern Ma

Query does not work on sharepoint 2007. Gives error

  

Hello,

I am facing problems retrieving correct number of records when I am performing a people search on SP 2007.

Following is the query that I had originally written:

Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') AND ( FirstName LIKE '%Keyword%' OR LastName LIKE '%Keyword%' OR OfficeNumber LIKE '%Keyword%' ) ORDER BY Title ASC

But, there is a problem here. Those records where the condition "FirstName LIKE '%Keyword%'" is satisfied, does not display if there is no value in OfficeNumber. This is giving incorrect number of records.

To correct this, I have changed the query and am using UNION in my query. See the following query:

(Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (FirstName LIKE '%Keyword%'))
UNION
(Select Title,Path,PreferredNam

Query does not work on sharepoint 2007.

  

Hello,

I am facing problems retrieving correct number of records when I am performing a people search on SP 2007.

Following is the query that I had originally written:

Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') AND ( FirstName LIKE '%Keyword%' OR LastName LIKE '%Keyword%' OR OfficeNumber LIKE '%Keyword%' ) ORDER BY Title ASC

But, there is a problem here. Those records where the condition "FirstName LIKE '%Keyword%'" is satisfied, does not display if there is no value in OfficeNumber. This is giving incorrect number of records.

To correct this, I have changed the query and am using UNION in my query. See the following query:

(Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (FirstName LIKE '%Keyword%'))
UNION
(Select Title,Path,PreferredNam

Is there a work-around to create indexes for a view defined by a sub-query, derived table, or CTE?

  

I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID, ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will not allow.

Has anyone figured a workaround for this? Any suggestions much appreciated.

CREATE

 

VIEW [dbo].[aonCompositeFactView] WITH

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.

Why does my SQL query not work?

  

Hi,

I have probably a pretty simple problem that i do not understand why i does not work.

My database questions is:

SELECT        COMPAN, SUM(AMOUNT) AS SALES
FROM            MAIN
WHERE        (ACCOUNT = 3010)
ORDER BY COMPAN

If I remove the WHERE then everything works but I dont get in that case what I want. Why cant I do like this? Secondly in that case how shall I do it then?

Thanx in advance


Leif

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.

Master-Detail with the GridView, DetailsView and jQuery's ThickBox

  
One more article about grids from Matt Berseth. This time he shows how to create master-detail with the gridview, detailsview and jQuery's ThickBox. He writes:
So I thought I would check some of this out and along the way rebuild my Master-Detail with the GridView, DetailView and ModalPopup Controls and replace the ModalPopup with jQuery's ThickBox. I found it pretty interesting - read on to see how it went

GridView inline Master/Detail record display

  
This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.

Master-Detail with the GridView, DetailView and jQuery's ThickBox

  
An example of using the jQuery ThickBox with ASP.NET's GridView and DetailView controls

RadioButton inside GridView, How to get it work as normal

  
Did you tried before to drag a RadioButton control inside a Gridivew templatefield, and then you attempt to select these RadioButtons , you will notice that the behavior of RadioButton control will be changed and it will work just like the behavior of checkbox control! the user will be able to select more than one radiobutton in the grid!
Categories: 
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