Sql Query Error
Sep 13, 2007
Hi all, i wrote a sql statement by using a subquery, and on the subquery "(SELECT IssueID FROM UserIssue WHERE (UserName = @UserName)", it returns more than one IssueID, so I got the error likes "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. " How can I solve this problem? Thanks a lot.
<asp:SqlDataSource ID="IssueSQLDB" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT IssueID, DateOfIssue, DateOfRelease, DateOfUpload, CostOfCurrentIssue, IssueDesc FROM Issue WHERE (PublicationName = @PublicationName) AND (IssueID <> (SELECT IssueID FROM UserIssue WHERE (UserName = @UserName)))">
<SelectParameters>
<asp:ControlParameter ControlID="ddlPublication" Name="PublicationName" PropertyName="SelectedValue"
Type="String" />
<asp:SessionParameter Name="UserName" SessionField="loginName" />
</SelectParameters>
</asp:SqlDataSource>
View 2 Replies
ADVERTISEMENT
Jan 22, 2001
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
View 1 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Jun 30, 2000
When I am trying to read rows from any table from any database in the offsite production
server with Pc Anywhere ( all other servers can be acess fine with pc anywhere
I get this error
An unexpected error happned during this operation
{query} query designer error encountered a query error
unexpected error
I checked the version of Enterprise manager
Help
About
microsoft managment console 1.1version 4.0 bulid 1381
service pack 4
View 1 Replies
View Related
Nov 26, 2007
I am running a query on a SQL Server 2005 database and encounter the following error message
"Internal Query Processor Error: The query processor encountered an unexpected error during execution."
There is a join between a table on the 2005 database and another on a 2000 database. I have run DBCC CHECKTABLE and found no errors on the two tables.
Anybody with ideas?
Thanks
View 3 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Jan 7, 2002
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error.
We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database
maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go
into production in a few days.
View 2 Replies
View Related
Nov 23, 2004
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
View 8 Replies
View Related
Jan 22, 2007
I have a SProc that runs across many clients without any problems. Every now and then, though, I get the following error:
Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630).
All I am doing is populating Temp tables with some data and then joining them together to create a Global Temp table that is being BCP'd to a network share.
Has anyone come across this error in SQL Server 2005? I cannot find anything on Google or Microcsoft.
Thanks,
Robert
View 1 Replies
View Related
Jan 23, 2008
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
View 12 Replies
View Related
Mar 15, 2004
Has anyone come across error 8630 "Internal Query Processor Error: The query processor encountered an unexpected error during execution." with SQL Server 7 with SP4 installed?
There are microsoft articles on the error, but the errors are suposidly fixed in SQL Server 7 SP2 and SP3.
We are selecting from one view left outer joined with another, but no unions are involved.
This is not a problem in SQL Server 2000, but unfortunatlely Microsoft are supporting SQL Server 7 until 2005 and so we have to do the same...
View 4 Replies
View Related
Apr 3, 2008
Hi all
my problem is as follows : when i try to configure an ODBC DataAdapter using the configuration wizard
i use the query builder to select some fields from multiple tables , the displayed info depends in a user input , when i define the input parameter by writing(= ?) in the criteria column of the input field and continues , it displays an error
Error H4000 Error while executing the query , Error near ')' near character (214 for example).
so please if any one knows the solution to this error reply soon
Thanks a lot
Mina Samy
View 3 Replies
View Related
Sep 17, 2007
Hi all, I wrote a sql query in order to count the keyword searched, my query is "SELECT Count(Distinct [Keyword]) As 'SearchTimes',[UserName], [LoginDateTime], [SearchType], [Keyword] From [KeywordStatistics] ORDER BY [UserName], [LoginDateTime]" and then I got the following errors.
System.Data.SqlClient.SqlException was unhandled by user codeMessage="Operand data type text is invalid for count operator."
Any idea to solve this problem? Thanks.
View 5 Replies
View Related
Oct 12, 1999
I having problem when I try to return rows on a table. This is the message Im getting: (Query) - Query Designer encounted a Query Error. Unspesified error. Anyone els whos getting this erreor.
View 1 Replies
View Related
Apr 5, 2005
Code:
SELECT [ESDSystems Catalog_CatalogHierarchy].Child_OID, [ESDSystems Catalog_CatalogProducts].CategoryName, [ESDSystems Catalog_CatalogProducts].[#CategoryDisplayName], [ESDSystems Catalog_CatalogProducts].Description
FROM [ESDSystems Catalog_CatalogHierarchy] LEFT OUTER JOIN [ESDSystems Catalog_CatalogProducts] ON [ESDSystems Catalog_CatalogHierarchy].Child_OID = [ESDSystems Catalog_CatalogProducts].OID
WHERE (([ESDSystems Catalog_CatalogHierarchy].OID = 78) AND ([ESDSystems Catalog_CatalogProducts].[i_ClassType] = 1)) ORDER BY [ESDSystems Catalog_CatalogProducts].OrderIndex
I cannot find a single thing wrong with this query, and it runs fine in SQL server, but when actually using in ASP here is the error I get:
Code:
CategoryMicrosoft OLE DB Provider for SQL Server
DescriptionLine 1: Incorrect syntax near ')'.
Big thanks to whomever figures it out!
View 6 Replies
View Related
Nov 27, 2007
I am reading O'Reilly Learning SQL on SQL Server 2005.I get an error right at the first query that you create reading this book.
The query is this one :
USE Student
SELECT *
FROM Student
And the error I get is this one :
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'Student'. No entry found with that name. Make sure that the name is entered correctly.
Am I doing something wrong?
View 2 Replies
View Related
Jul 21, 2004
Hi,
When I compiled the below I get the following error :
The column prefix 'cat' does not match with a table name or alias name used in the query"
What might be the error cause ?
Please adivce.
Thanks,
Sam
CREATE VIEW colview
AS
SELECT
wi.wiciu,
wi.ttldfg,
wi.lngcdedfg,
lng.desccdedfgd AS LNGCDETXTSDGF,
wi.durhhdfg,
wi.durmmdfg,
wi.durssdfg,
CAST(DATEPART(YYYY, cprdtsdf) AS VARCHAR) + CAST(DATEPART(MM, cprdtsdf) AS VARCHAR) + CAST(DATEPART(DD, cprdtsdf) AS VARCHAR) AS CPRDTFXGV,
wi.cprnrsdf,
wi.recdindsdf,
wi.postdtsdf,
CAST(DATEPART(YYYY, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(MM, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(DD, wi.cretssdf) AS VARCHAR) AS CRETSFG,
wi.creuidg,
wi.lstupddtfdg,
wi.upddfguid,
'DOMDG' AS CAT,
cat.dessdfccde AS CATTXTSDF,
cat.lngsdf,
wi.discatdsf,
mdc.desccde AS DISCATTXTSDF,
wi.txtmusrelsdf,
tmr.desccdesdf AS TXTMUSRELTXT,
wi.musarrdsf,
mac.dessdfccde AS MUSARRTXT,
wi.lyrsdfcarr,
lac.dedsfsccde AS LYRCARRTXT,
wi.vrsdftyp,
vrt.dessdfccde AS VRTYPTXT,
wi.exrsdfptyp,
exrp.desdfsccde AS EXRPTYPTXT,
wi.cpssdfttyp,
cps.desdfsccde AS CPSTTYPTXT
FROM cds_lkup cat,
cds_lkup mdc RIGHT OUTER JOIN tbltitle wi ON mdc.txtcde = LTRIM(RTRIM(wi.discat))
ANDmdc.lkupid = 'MUSDISCAT'
ANDmdc.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup tmr ON tmr.txtcde = LTRIM(RTRIM(wi.txtmusrel))
ANDtmr.lkupid = 'TXTMUSREL'
ANDtmr.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup mac ON mac.txtcde = LTRIM(RTRIM(wi.musarr))
ANDmac.lkupid = 'MUSARRCDE'
ANDmac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lac ON lac.txtcde = LTRIM(RTRIM(wi.lyrcarr))
ANDlac.lkupid = 'LYRADPCDE'
ANDlac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup vrt ON vrt.txtcde = LTRIM(RTRIM(wi.vrtyp))
ANDvrt.lkupid = 'VRTYP'
ANDvrt.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup exrp ON exrp.txtcde = LTRIM(RTRIM(wi.exrptyp))
ANDexrp.lkupid = 'EXCPTYP'
ANDexrp.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup cps ON cps.txtcde = LTRIM(RTRIM(wi.cpsttyp))
ANDcps.lkupid = 'CPSTTYP'
ANDcps.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lng ON lng.txtcde = wi.lngcde
ANDlng.lkupid = 'LNG'
ANDlng.dmncde = 'WRK'
ANDcat.txtcde = 'DOM'
ANDcat.lkupid = 'CAT'
ANDcat.dmncde = 'WRK'
WHERE (cat.lng = mdc.lng
ORmdc.lng IS NULL)
AND(cat.lng = tmr.lng
ORtmr.lng IS NULL)
AND(cat.lng = mac.lng
ORmac.lng IS NULL)
AND(cat.lng = lac.lng
ORlac.lng IS NULL)
AND(cat.lng = vrt.lng
ORvrt.lng IS NULL)
AND(cat.lng = exrp.lng
ORexrp.lng IS NULL)
AND(cat.lng = cps.lng
ORcps.lng IS NULL)
AND(cat.lng = lng.lng
ORlng.lng IS NULL)
View 5 Replies
View Related
Apr 7, 2004
Hi,
I am trying this query
rsOFF.Open "SELECT SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0)) AS tot FROM dbo_OFF_ITEM WHERE DB_CONTRACT=" & Form_dbo_AE_CONTRACT1.DB_CONTRACT, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
but is is giving me this error:
Wrong number of argument used with function in query expression SUM(ISNULL(OFF_QUANT,0)*ISNULL(OFF_PRICE,0))
I could not find error.
can anyone help me.
View 1 Replies
View Related
May 26, 2004
ODBC error 4604 (42000) There is no such user or group 'sa8'
this is the error message I get when I run my stored procedure that has the @query parameter and I have been told this is a bug within sql that microsoft doesn't seem to be aware off.
Are you coming across this and what do you do to go around it???
Regards
View 2 Replies
View Related
May 17, 2008
Help me
Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
)
When I run this query I get an error
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ;);AS
Message: An unexpected token "" was found following ")". Expected tokens may include: "AS".
Can anybody help
Appreciate you reply
View 8 Replies
View Related
Jan 21, 2006
I am trying to execute the following query
SELECT count(ARTICLE_RATING.rating) as numberoftimes, ARTICLE_RATING.rating, sum(rating/numberoftimes) from ARTICLE_RATING
group BY ARTICLE_RATING.rating
and i receive the following error.
Invalid column name 'numberoftimes'...Could someone help me with this??
View 6 Replies
View Related
Feb 6, 2008
Dear All,
i'm getting error while running this query
help me regarding this
SELECT DISTINCT(table019.col1) DOC_TEMPLATE_ID, VORG.Name SUB_ORGCODE, table019.col2 BRANCH_ID,
TABLE013.col4 REPOSITORY_CODE, TABLE013.col5 REPOSITORY_NAME, table019.col3 REPOSITORY_ID,
table019.col4 CODE, table019.col5 NAME, table019.col4 DOCTYPENAME, table019.col6 DOC_TYPE_ID,
(SELECT col4 FROM table019 WHERE col1 = table019.col7) DOCUMENTCATEGORY,
table019.col7 DOC_CATEGORY_ID, table019.col4 doccon,
table019.col8 doc_id, table019.col9 ECR_REQUIRED,
table019.col10 ATTACH_TEMPLATE, table019.col11 LOCATION, table019.col12 THUMBNAIL_IMAGE,
CASE table019.col13 WHEN '3' THEN 'Closed' WHEN '4' THEN 'In Progress' WHEN '5' THEN 'Pending' WHEN '6'
THEN 'Open' WHEN '9' THEN 'Approved' WHEN '10' THEN 'Rejected' END STATUS, table019.col13 DOCSTATUS,
table019.cold1 BRANCHID, table019.cold2 COMPANYID, table019.cold3 CREATEDBY,
table019.cold4 CREATEDDATE, table019.cold5 LASTUPDATEDBY, table019.cold6 LASTUPDATEDDATE,
table019.cold7 ROWGUID, table019.cold8 UPDATEDSITE, table019.cold9 LANGID FROM table019
Inner Join VORGAN VORG On table019.col2 = VORG.Org_Id Inner Join TABLE013
On table019.col3 = table013.col1 Inner Join table019 On table019.col6 = table019.col1
Inner Join table019 On table019.col8 = table019.col1 where table019.col4 LIKE 'dc%' AND
table019.col13 LIKE '6' OR table019.col13 LIKE '9' and table019.col17 like '%'
View 4 Replies
View Related
Mar 16, 2008
I am trying to run a query to create some stored procedures but am getting the following error:
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)
Does anyone know what this Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__08EA5793'. Cannot insert duplicate key in object 'aspnet_SchemaVersions'. means?
Thanks, Mark
View 3 Replies
View Related
Mar 15, 2006
The database scheme consists of four relations:Product(maker, model, type)PC(code, model, speed, ram, hd, cd, price)Laptop(code, model, speed, ram, hd, screen, price)Printer(code, model, color, type, price)The relation "Product" shows the maker, model number, and type (pc,laptop, or printer). It is assumed that model numbers are unique forall the makers and product types. For each model number specifying pcin the relation "PC", its listed speed (of the processor in MGz), totalRAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),and the price. The relation "Laptop" is similar to that one of PCsexcept for the CD ROM speed which is replaced by screen size (ininches). For each printer model in the relation "Printer" it is pointedwhether the printer is color or not (color attribute is 'y' for colorprinters; otherwise it is 'n'), printer type (laser, jet, or matrix),and the price.I need to write a query for Find printer makers.Result set: maker.My Query isselect distinct product.maker from product inner join printer onproduct.model = printer.modelI get the messageYour query produced correct result set on main database, but it failedtest on second, checking database.* Wrong number of records (less by 1)This question is an SQL exercise in URL http://www.sql-ex.ru/Can anyobe explain me "correct result set on main database," why "butit failed test on second, checking database."* Wrong number of records (less by 1)Thank you very much,Miks
View 4 Replies
View Related
Jul 20, 2005
Hi There,I can't seem to see what's wrong with the query belowDELETEFROM Users_Details UD1WHERE UD1.UserID = (SELECT TOP 1 UD2.UserIDFROM Users_Details UD2WHERE UD1.useremail = UD2.useremail)Keeps giving me incorrect syntax on Line 1 near UD1.Can you not alias tables when using a Delete?What I'm trying to do is cleanse the table of duplicate e-mailaddresses but I always want to leave one copy of the e-mail addressi.e. if there are 3 of the same e-mail address then I want to remove 2of them.Anyone got a better way of doing it that doesn't use cursors then Iwould appreciate it.Cheers
View 4 Replies
View Related
Nov 7, 2006
I am not able to execute the following sql statements as a stored procedure as able to execute as sql statements.
getting error
"
Msg 156, Level 15, State 1, Procedure sp_Getalldomaintextbox, Line 16
Incorrect syntax near the keyword 'order'.
"
CREATE Proc [dbo].[sp_Getalldomaintextbox]
as
(
SELECT d.domainname as domainname,convert(nvarchar,d.registrationdate)as registrationdate
,da.activation_status,da.domainid as domainid, convert(nvarchar,d.registrationdate,101)as registrationdate1
from
domain d, domain_admin da
where
d.domainname like '%test%'and
d.domainid = da.domainid
order by registrationdate1 desc
)
View 1 Replies
View Related
Jan 24, 2008
I get error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS". from the following query:
SELECT servicedate
,emplenbr
,sum(drivetime + adjdrivetime + mileage + adjmile +
meals + adjmeals + airline + adjairline + hotel + adjhotel +
trans + adjtrans + tools + adjtools) As 'Expenses'
FROM dbo.mshq_ivr1
WHERE plannbr = '83779'
AND q2 = '26643'
AND storenbr = 69529
AND emplenbr NOT IN (SELECT i2.servicedate, i2.emplenbr FROM dbo.mshq_ivr1 i2
WHERE i2.plannbr = 26643 and i2.storenbr = 69529 and i2.servicedate = servicedate)
GROUP BY servicedate, emplenbr
ORDER BY servicedate desc, emplenbr
Using NOT EXISTS produces other problems due to the GROUP BY. I need to know how to properly include the NOT IN portion in the WHERE to show employ expenses entered on dates (servicedate) not actually worked.
View 4 Replies
View Related
Nov 20, 2006
Hi
I have the following query used to predict the values in 2006 based on cluster model built using 2004 and 2005 data. I'm using a prediction join on a cube containing the data. I can join a single dimension from the cube to a column in a the data mining model. However when I try to join to a column in a nested table I get this error:
Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Internal error: An unexpected exception occured.
Execution complete
The query I'm running is this
select t.*
, Predict([Time].[Deal Count])as predicted_deal_count
, Predict([Time].[Deal Revenue])as predicted_deal_revenue
, Cluster()
From
[Time]
prediction join
(
select {[Measures].[Deal Count], [Measures].[Deal Revenue]} on 0,
non empty {{[Time].[Month Number Of Year].[Month Number Of Year].members} *
{[Country].[KeyCountry].[KeyCountry].members} * {[Country].[Region].[Region].members}} on 1
from [DM]
where ([Time].[Year].&[2006-01-01T00:00:00])) as t
on
[Time].[Month Number Of Year] = t.[[Time]].[Month Number Of Year]].[Month Number Of Year]].[MEMBER_CAPTION]]]
and [Time].[Country].[KeyCountry] = t.[[Country]].[KeyCountry]].[KeyCountry]].[MEMBER_CAPTION]]]
and [Time].[Country].[Region] = t.[[Country]].[Region]].[Region]].[MEMBER_CAPTION]]]
I'm running SQL Server 2005 with SP2 CTP2.
Thanks
Sanjay
View 4 Replies
View Related
Nov 7, 2006
I am not able to execute the following sql statements as a stored procedure as able to execute as sql statements.
getting error
"
Msg 156, Level 15, State 1, Procedure sp_Getalldomaintextbox, Line 16
Incorrect syntax near the keyword 'order'.
"
CREATE Proc [dbo].[sp_Getalldomaintextbox]
as
(
SELECT d.domainname as domainname,convert(nvarchar,d.registrationdate)as registrationdate
,da.activation_status,da.domainid as domainid, convert(nvarchar,d.registrationdate,101)as registrationdate1
from
domain d, domain_admin da
where
d.domainname like '%test%'and
d.domainid = da.domainid
order by registrationdate1 desc
)
View 1 Replies
View Related
Jul 6, 2007
Dear All,
I am using a query as it is in SQL Server 2000 in my C# windows application.
The query is having name with "dbo" in all database object. If i remove that dbo from the name it is working fine. Why it is not working .net application ? What is the theory behind it. Please explain me..Thanks in advance.My query is given below.
strsql.Append("SELECT dbo.[Order].OrderID, dbo.Clients.CompanyName AS Customer,");
strsql.Append("dbo.Employee.LastName AS Employee, dbo.[Order].OrderDate, ");
strsql.Append("dbo.[Order].ShipmentDate, dbo.Clients_1.CompanyName AS Shipper, ");
strsql.Append("dbo.[Order].Freight,dbo.Clients.ClientID as CustomerID, ");
strsql.Append("dbo.Clients_1.ClientID as ShipperID FROM dbo.[Order] INNER JOIN ");
strsql.Append("dbo.Employee ON dbo.[Order].EmployeeID = dbo.Employee.EmployeeID INNER JOIN ");
strsql.Append("dbo.Clients ON dbo.[Order].CustomerID = dbo.Clients.ClientID INNER JOIN ");
strsql.Append("dbo.Clients Clients_1 ON dbo.[Order].ShipperID = Clients_1.ClientID ");
string sqlOrder = strsql.ToString();
Thanks,
Saji
View 4 Replies
View Related
Jan 30, 2007
Hi all,
I'm a new guy trying to tutor myself on MRS and I'm using
SQL05DEV on my desktop. I managed to finally get connected to
AdventureWorks db, but when I run this query:
SELECT S.OrderDate, S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName
FROM HumanResources.Employee E INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
=====================================================
I get this error:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Invalid object name 'HumanResources.Employee'.
------------------------------
ADDITIONAL INFORMATION:
Invalid object name 'HumanResources.Employee'. (Microsoft SQL Server, Error: 208)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
-----------------------------------------------------------
Any tips for troubleshooting?
How would Icheck to see if the human resources table even exists?
Thanks,
>Scott
View 4 Replies
View Related
Dec 6, 2007
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'pk_pc'. Cannot insert duplicate key in object 'dbo.pc'.
The statement has been terminated.
I'm trying to insert data from one table to another using:
select * from pc
insert into pc (pcstamp,conta,descricao,ano)
select pcstamp,rtrim(ltrim(conta)),substring(rtrim(ltrim(descricao)),0,55),ano
from work_pc
When i do it it gives the upper message...
View 1 Replies
View Related
Nov 28, 2007
select ZT_AuthDetail
Where MasterKey in (select * from ZT_AuthDetail d, ZT_AuthMaster m where (ReplyTime Between '2004/11/22' and '2004/12/31') And (m.CustId = '33196700') And (m.Prikey = '1840'))
when I run this query , the error message shows somethins wrong with "Sub Query from Exists....."
if I run (select * from ZT_AuthDetail d, ZT_AuthMaster m where (ReplyTime Between '2004/11/22' and '2004/12/31') And (m.CustId = '33196700') And (m.Prikey = '1840')) alone
the result is fine and Ok.. but with select ZT_AuthDetail where MasteKey in... I got error message
can you please tell me why and how to fix it?
** I use SQL2000
thank you very much
View 2 Replies
View Related