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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

In BCP what is work of 'query out'

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
In BCP what is work of 'query out' is there any example"SQLSERVER DBA" "INDIA"


View Complete Post


More Related Resource Links

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

Query does not work in the master DB

  

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(

    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.

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!

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.

Looking for guidance: wanting to work with AJAX architecture

  

Hi,

I'm starting a small website that will be backed by a database, and will allow a small number of users to authenticate and select an entry in the database, and then view (and optionally update) a corresponding entry (think master-detail).

I have some flexibility in how I'll do this, but I'd really like to incorperate some newer AJAX style coding, eliminating some page postbacks. I think this is a great opportunity to learn the concepts well.

I'll probably go with an ASP.NET 4.0 site. From what I've seen there are MANY ways to go about this:

-WCF Data Services to expose the data, and consume in ASP.NET

-Page methods to expose the data, separate method to update the data

-I'd rather avoid the UpdatePanel because I'm really looking for a full hands-on approach, with learning client side scripting being an objective as well.

Beyond that, it seems that one can use jQuery to build clientside templates, but also one can use the ASP.NET AJAX template engine (in ASP.NET 4.0 I believe).

A concern I have: users should only be able to work with the data exposed via these endpoints if they are logged into the website. Hopefully I can somehow reuse the ASP.NET forms authentication cookie for this purpose. 

Well, I hope I've posted enough to a

animate effect work but the render items are messed up.

  

Hi all,

     I have a very unique problem, humm i think. I am using a jquery effect that animate bounce effect, i have a line in my javascript pageLoad function $("#UserBrowserInfoDIV").show('bounce'); - the effects run smoothly but what it does is mess up the bolded text in the div. I have try the items inside the div without bolding and it works fine and no render problem but when bolded the text that are bolded is quite messed up, and barely readable.

Please let me know is there anything else i can do i really would like use this effect.

P.S all the effect have the same render problem, and i am using ie8 under compatibility mode.

Thanks

Kabir

 

 


Why getTreeNode does not work?

  

Hi,

I try to run a  treeview example from msdn:

http://msdn.microsoft.com/en-us/library/ms529261%28v=VS.85%29.aspx#TreeView_References

I also want to use code from

http://msdn.microsoft.com/en-us/library/ms529288%28v=VS.85%29.aspx

Here is my code:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register TagPrefix="mytree"
Namespace="Microsoft.Web.UI.WebControls"
Assembly="Microsoft.Web.UI.WebControls" %>
 
<html>
<head>
</head>
<body>
 <script type="text/javascript" language="javascript">


     function xxx() {


         var myNode = treeview_one.getTreeNode(treeview_one.selectedNodeIndex);
         alert(myNode.getAttribute("Text"));

         //alert("ok");
     }


</script>
<form id="myform" runat="server">
<mytree:treeview id="treeview_one"  runat="server" Child

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