Subqurey Returns More Than One Value Error HELP....
Jul 23, 2005
My update query updates about 50 columns and they all have the same
format: (Im using ADP to interact with the SQL Server)
UPDATE dbo.GIS_EVENTS_TEMP
SET VEH1TYPE =
(SELECT VEHICLETYPE
FROM VEHICLE AS A
WHERE(GIS_EVENTS_TEMP.CASEID = A.CRASHNUMBER) AND(A.UNITID = 1)),
VEH2TYPE = (....... same as above with UNITID = 2)
VEH1DAM = (Same format different tables...)
..
..
n
The problem is that it's part of an import process that is dependent on
data from an outside source which come once a month.
I found an error in one of the tables (a duplicate ID) which should not
be in the table this is causeing the "Subqurey returns more than one
value" error.
How can I guard against this..? If possible I would like it to only
return the first value..
I tried "COALESCE" but I couldn't get it to work I also tried "SELECT
DISTINCT"....
Is there some example of how i can add a return status into my stored procedure to have an extra field where I can verify if it runs successfully 1 if success -1 if error (server, etc) 0 if empty record return
As part of configuring my SQL Server 2005 box to be able to use the xp_cmdshell function, I ran sp_xp_cmdshell_proxy_account to set up the credentials:
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1 An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.
I am assuming that this is an "Access Denied" error message. Has anyone got any ideas on why this is happening or what I can do to narrow down what the problem is. I can't find any logs with further information.
I have used a similar setup on another SQL Server 2005 box before and have never had a problem running the sp_xp_cmdshell_proxy_account function. I can't see what I have done differently this time.
Further information:
The SQL Server and SQL Server Agent services are running under the DomainDomainUser account.
I have given the DomainDomainUser the following privileges: Act as part of the operating system Log on as a batch job Log on as a service Replace a process level token
Hi All, When I use the following I get an error. I think it is because of duplicate records in my table.
Update person Set username = (Select username From update_person Where person.firsname = update_person.firstname and person.lastname = update_person.lastname)
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
If CurrentProject.IsConnected Then MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly If SQLConexion.State = adStateOpen Then SQLConexion.Close SQLConexion.Close End If SQLConexion.Open SQLSentencia AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
I googled on the latter error number and got nothing back Error: 0xC0048006 at Set Destination File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x016F9EA4.
The former only returns one result which does not explain my problem
I have created a custom CLR user define aggregate function based on the example that i found at http://msdn2.microsoft.com/en-us/library/ms131056(SQL.90).aspx.
It works great until i discovered that it will failed if i try to do either one of the following:
query a large records e.g: more than 4k records has IS NULL in my where clause e.g: WHERE myConcatenatedFld IS NULL
Other than these two, it works perfectly fine.
Here is the error that i got:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have attached the Concatenate class that i used in my UDAGG.
I have a stored procedure that works when executed in query analyzer. (It is also way too long to post here) When called from my application ado.net returns the error:
Invalid object name #idTable
If I run the proc in query analyzer using the same parameters (copied from quickwatch while debugging) there is no error.
While very complicated, this procedure runs quickly so timing out is not an issue.
Does anyone know why a proc would run in query analyzer and not in an asp.net/c# application?
AS DECLARE@UPLIDCount int DECLARE @OldUPLIDCount int SELECT @UPLIDCount = (SELECT Count(UPLID)/1000 AS adjcount FROM tblProvLicSpecloc WHERE DelDate is null OR DelDate > GETDATE())
IF EXISTS(SELECT var FROM tblDMaxVars WHERE var = 'UPLID Count') BEGIN SELECT @OldUPLIDCount = (SELECT var FROM tblDMaxVars WHERE var = 'UPLID Count') IF @UPLIDCount > @OldUPLIDCount BEGIN UPDATE tblDMaxVars SET value = '' + CAST((@UPLIDCount*1000) AS nvarchar(1000)) + '' WHERE var = 'UPLID Count' END END ELSE BEGIN INSERT INTO tblDMaxVars (var, value, description) VALUES ('UPLID Count', '' + CAST((@UPLIDCount*1000) AS nvarchar(1000)) + '', 'counts UPLID records and rounds down to the nearest thousand') END
GO
The table tblDMaxVars only has three columns, none of which are integers, yet I still return this error:
Code:
Syntax error converting the varchar value 'UPLID Count' to a column of data type int.
I have a table that contains a column for a calcuated member (x) of type decimal number. When I tried to display the total of this calculated member in the table footer (=sum(x)), I am getting "#Error" instead of the sum of all displayed calculated values.
Column X
--------------
0
0.67
0.10
0.23
#Error (footer cell, expression -> =Sum(x))
=First(x), =Last(x) and =Max(x) worked fine, not sure why Sum failed. Please help...
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) RETURNS date AS BEGIN; DECLARE @Result date; SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) RETURN @Result; END;
I Installed SQL Reporting services 2012 service with sharepoint 2013 ... and started it with success..I followed the microsoft's procedure.. URL....After , I have not encountered any problems when I created the application <--- 3 databases created in my SQLSERVER instance//The problem occured when I tried to click on my ssrs application_name --> system settings...ERROR 500 Correlation ID: f6c5f49b-6ab0-4009-6f31-fd019ddc3ecc
This what I read in the log of sharepoint -->
01/14/2013 12:18:36.22 w3wp.exe (0x0BEC) 0x05D8 SharePoint Foundation Monitoring nasq Medium Entering monitored scope (Request (GET:http://vwaisql201202:22628/_admin/ReportServer/ReportingServicesSystemSettings.aspx?ID=5abbbf0d-e8dd-4e8e-93d6-eddc77225a9f)). Parent No
I execute an UPDATE statement using EXEC after I build it into a string: @lcSQLString = 'UPDATE ' + @@mytable + 'SET x = y ...' EXEC(@lcSQLString)
When the UPDATE fails, @@ERROR is nevertheless set to 0, because it returns the status of the EXEC command (which succedded) and not the @lcSQLString (which failed). At least, that is the explanation, I think. If I execute the statement directly by substituting the variables with their values, @@ERROR displays the correct value.
I created very simple table with 3 columns and one is varchar(max) datatype
When i insert records thru VC++ ADO code i am getting this error
Exception Description Multiple-step OLE DB operation generated errors. Check e ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned DECLARE @memberList VARCHAR(4000),@hDoc INT SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT) EXEC sp_xml_removedocument @hDoc
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True") Dim cmd As New SqlCommand
Dim returnValue As Object cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)" cmd.CommandType = Data.CommandType.Text cmd.Connection = sqlConnection3 cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar) sqlConnection3.Open() cmd.Parameters("@Names").Value = test If test = "" Then Response.Write("An error occured") Exit Sub Else returnValue = cmd.ExecuteScalar() sqlConnection3.Close() Label6.Text = "Number " & returnValue
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".
To repeat, the sp runs fine in QA, just not in access.
Any thoughts, anybody? Many thanks. David
Alter Procedure spDailyMicroLabel @Date Datetime = null
AS IF @Date IS NULL BEGIN
SET @Date=getdate()
SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled", fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate", fldfermidold AS "FermenterCode", CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay", "Sample Test" = CASE WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA' WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA' ELSE 'No Test Micro' END INTO #tblTempA FROM vwUnfilteredFermenters
SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay, tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results" FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay
could any one tell how to go about keeping carriage returns and line breaks when inserting a chunk of text so that when that text is recieved it will still contain those carriage returns and line breaks?
When I was using a simple query using select statement with where clauses, I can get the results. When I use AND to specify more conditions. It returns no row even though I get the result when query seperately. What should be the possible cause of this. I am using SQL Server 6.5. Thank you
I have a problem where my subquery is returning two values and I need it to return only one. I believe it is doing this because of the group by DocType clause as there are two DocTypes.
Is there anyway I can sum these values in the subquery, to return only one value. This way the query should run ok.
Thanks for any help you might be able to give,
robinsql
SELECT OCRD.Cardcode, OCRD.cardname, @YearStart AS YearStart, @YearEnd AS YearEnd, @PeriodStart AS PeriodStart, @PeriodEnd AS PeriodEnd, OCRD.County, (SELECT CASE WHEN OINV.DocType = 'I' AND SUM(INV1.Quantity) <>0 THEN COALESCE(SUM(INV1.Price * INV1.Quantity), 0) ELSE COALESCE(SUM(INV1.Price), 0) END FROM INV1, OINV WHERE INV1.DocEntry = OINV.DocEntry AND OINV.CardCode = OCRD.CardCode AND OINV.DocDate >= @YearStart AND OINV.DocDate <= @YearEnd GROUP BY OINV.DocType) AS YTDInvoice, FROM OCRD, OINV WHERE CardType='C'
I'm trying to find customers who have spent X (in this case 500 or more) dollars in a month
--photogs spending $500 or more on products in April, 2008 SELECT PhotogCode, SUM(ProductRevenue) FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog WHERE DATEPART(MM, [Date]) = 4 AND DATEPART(YY, [Date]) = 2008 GROUP BY PhotogCode HAVING SUM(ProductRevenue) >= 500
However, I'm finding that if I use a variation of this (see below)
--all photogs spending money on products in April, 2008 ordered by total SELECT PhotogCode, SUM(ProductRevenue) FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog WHERE DATEPART(MM, [Date]) = 4 AND DATEPART(YY, [Date]) = 2008 GROUP BY PhotogCode ORDER BY SUM(ProductRevenue) DESC
and I look at the returned rows, I'm discovering that there are an additional 12 customers who have spent $500 or more...is there something I'm missing with the query using HAVING?
I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.
SELECT db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term,
[code]...
It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS. How to implement to LkupMedical table....
I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..
