.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

Help Identifying What Wrong W/ Query Syntax - sp_send_dbmail

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


I know that I have probably done more than a few incorrect things in this statement but it works when I run it in the studio so I am not sure why it will not work when trying to use it from within sp_send_dbmail. I am pretty much new and learning as I go but this one has me lost.

My Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is what I am trying to run from Studio to validate that the syntax is correct before attempting to set it as a scheduled task.

DECLARE @like1 varchar(100);
SET @like1 = '%-LOST-%'; 
DECLARE @like2 varchar(100);
SET @like2 = 'LOST%-'; 
SET @sql = (SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10e.ocx' 
	  WHERE ProfileDate > GetDate()-1 
	  and (ProfileName not like @like1 and ProfileName not like @like2)
	  and version <> '10,1,82,76'
	  SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10i.ocx' 
	  WHERE ProfileDate > GetDate()-1

View Complete Post

More Related Resource Links

Syntax error (missing operator) in query expression

Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

Sub Query Syntax

Hi All, I know this is a repost but I have struggled though it and managed to nut most of the issues from this query. An issue I am still getting are multiple values returned by a sub query... Which one SQL doesn't like to clue me into. Below is the stored procedure and I know it is mess, this is reverse engineering a terrible application.   USE [CMI] GO /****** Object: StoredProcedure [dbo].[CMIReport] Script Date: 09/02/2010 09:49:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[CMIReport] @startdate datetime, @enddate datetime as BEGIN ---- ~This drops the tables created by the report before recreating it~ ---- DROP TABLE CMI_Report DROP TABLE CMI_Report_Final_Data ---- ~This Query defines the dates early on in the procedure~ ---- SET NOCOUNT ON select * into CMI_Report from Case_Query_View where end_dt Is Null Or end_dt > @startdate and end_dt < @enddate --- ~This Query Replaces Name Veiw in the Report~ ---- select * into CMI_Report_Final_Data from Name_View ---- ~This is the defining query, This controls YTD and such! ---- SELECT nq.surname, nq.sw_ur, nq.MaxOfnext_rev_dt, cq.CTenddt, aq.LastOfadm_dt, aq.LastOfsep_dt, nq.end_dt, nq.clnt_id, nq.case_id, nq.HCPName, nq.hcp_id, cq.LastOfstart_dt, nq.MaxOfwellb_dt, (select MAX(dq.icd_desc) from Diagnosis_View dq where nq.clnt_id = dq.clnt_id) AS Diag

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

Generic Query Designer displaying non standard syntax

I've just discovered a problem with the Generic Query Designer on my machine. When I do a simple join of two tabels it does not display in the SQL pane as standard syntax, so instead of SELECT a.1 from ainner join b on a.1=b.1It does SELECT a.1from a, bwhere a.1 = b.1I am doing this against an oracle 10g database, it will run the query, but I want the query designer to out put at standard SQLI am presuming that it is a setting in SSBIDS, but I don't know why it is doing this. Any suggestions?

What is wrong with my query here?


Hi I am trying to search all the contacts from Contact lists from MOSS 2007 farm, and I used the following query.

SELECT Title, FirstName, WorkPhone, Email FROM scope() WHERE contentclass = 'STS_ListItem_Contacts'

I have created a few contacts in Contact lists but the result set returns empty set.

Is my query wrong here?

Thanks for your help

Nested Full Text Query and SQL syntax



Quick question, is it possible to use the SQL TOP syntax in a Full Search Query something like

Select Top 1 title from SCOPE() where = 'All Sites' AND Productid= '1234556'


Can you use nesteed select statments ?

Select title from SCOPE() where = 'All Sites' AND Productid=  (select productid from scope() where scope = 'all sites' AND product = 'example')


Dave Roberts


Can anybody tell me whats wrong in this query?


Hi, this is the query for SsisConfiguration. i have done few changes in the main package so i have to change the data files path. Can anybody tell me if this is correct? i have 5 child packages so do i have to do the same thing for all packages?

USE SsisLogs



IF  EXISTS (SELECT * FROM [SsisLogs].[audit].[SSISConfig]
UPDATE SsisLogs.Audit.SsisConfig
SET ConfiguredValue = '\\uafc.com\ua\MH\DIRFILEDEV\CHG67570\Data'
WHERE ConfigurationFilter = 'FileLocation_RxCuiToNdc_Load'
IF NOT EXISTS (SELECT * FROM [SsisLogs].[audit].[SSISConfig]

Not identifying Sql Quote when executing query


Im using SQL Server 2008 R2, Server version 9.0 SP3

Im trying to do a replace on a comma seperated values before submitting to the "IN" condition.

Declare @inputrecords nvarchar(max)
Declare @inputrecordscsv nvarchar(max)
set @inputrecords = N'a,b,c'
set @inputrecordscsv = ''''+ replace(@inputrecords, ',', ''',''') + ''''

select * from MyTable
where Mytable.Record IN (@inputrecordscsv)
My intension is to get query work like this:
select * from MyTable
where Mytable.Record IN ('a','b','c')
But its not working, and its considering the @inputrecordscsv as a single string.
Any suggestions how to make it work?



What am I doing wrong in this LINQ query


I am retrieving an INT value from the one column in my "Counters" table.  I want to cast it to an int then increment it.  The error thrown states that sHitCounter (note that this is not names as it appears but rather the 's' is for "string) is uninitialized at the "parse..." line.

#region Counters
    public class Counters
        public int hCounter { get; set; }
        public static int HitCounter()
            string sHitcounter;
            int iHitcounter;
            IVA.Classes.DataClasses1DataContext hc = new IVA.Classes.DataClasses1DataContext();
                var hitC = 
                    from Counter in hc.GetTable<IVA.Classes.HitCounter>()
                    select Counter;

                foreach (var Counter in hitC)
                    sHitcounter = Counter.ToString();

                iHitcounter = Int32.Parse(sHitcounter);
                    return (iHitcounter);

    }//Class Counters


SQL Query within a form fails with "parser error" - syntax not correct?


Hello, I've created an Expression Web form with a SQL datasource which is doing a query/select statement.

Somehow it appears that I'm making a syntax error. When I divide the query in two and execute them separate, it works fine.

I need to filter for an EventType, Region and Today's date + a custom interval (like 7 day's for a week)

The parameters come from a search form where the user can select these values via a dropdown list.

The two separate (and working) queries within my form are:


Query nr.1 :

"Select * from Events WHERE  (([EventType] LIKE '%' + @EventType + '%') AND ([Region] LIKE '%' + @Region + '%'))">


<asp:formparameter DefaultValue="%" FormField="EventType" />

<asp:formparameter DefaultValue="%" FormField="Region" />


Query nr.2 :






Whats wrong with this syntax


I had issues with updating database with .xls  and .csv files, but got it working.. I need more control over the csv upload, so i was trying a few things i found online for connecting to the file similar to the excel file that works great.


I have the follow connections to an xls and csv and the csv logic throws an exception that it cant find the file, even though it was uploaded successfully prior to the reader.

Here is the logic for an excel file and then for the csv, both are identical except for the Extended Properties on the connectionstring, so not sure why its giving that error, the file is uploaded to the folder fine and exists prior to the reader.

{"'C:\\\\inetpub\\wwwroot\\SyNet\\TestSecurity\\Sion\\Sion\\Data\\testAPconfig.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."}


Connection String for .CSV

                            OleDbConnection csvConnection = new OleDbConnection();
                            csvConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=" + fullName + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited;\"";


SQL Query Syntax in Enterprise Search


Here is the sql query for FullTextSqlQuery:

SELECT url,DatePublished FROM portal..scope() WHERE ( ("SCOPE" ='All Sites')) AND url like '%url.com%' and artefacttype='aaa'


 the portal..scope() means? is there any other scope?

what is the url and artefacttype means, are those a customer column or something?


Also I want a tutorial document for MOSS 2007 search.

Including KeywordQuery, Query in a List View(SPQuery), CAML.


Thanks a lot!

error with sql query syntax


I run following query:

Grant view database state to domain\username

and get error message

Msg 102, level 15, state 1,Line 1

Incorrect syntax near '\'


How to fix it?

sp_send_dbmail fails when @query parameter and OLE Automation



I'm using OLE Automation to build disk drive information into a table, and then using sp_send_dbmail with the @query to do a select from the table and include the information in an email. 

Both parts run fine on their own, but when I run them together, without a GO between them, then I get the following failure:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106


Previous forum discussion has suggested doing the 2 parts in different threads as a workaround, so I have created seperate stored procedures for each part.  But still no joy.  When I run without a GO it still gives the error.

For example,

exec [dbo].[sp_get_diskspace]

exec [dbo].[sp_dbmail_send_dm]

fails.... but:

exec [dbo].[sp_get_diskspace]


exec [dbo].[sp_dbmail_send_dm]


Since I want to run the above from a stored procedure I can't have the GO in that position.

Any workaround suggestions?

Here's an extract from the procs (procs were taken from other forum appends):

proc 1:


whats wrong with this query


please help..what's error here...I'm trying to do dynamic Pivot here, please help


SELECT 'XYZ', 'DEC', 10, 100 UNION ALL SELECT 'XYZ', 'JAN', 13, 300 UNION ALL SELECT 'XYZ', 'FEB', 14, 400

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
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