hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'
If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work
do I need to change something in the sql statement?
Thanks in advance
SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
Hi, I m Trying TO use A sql insert Query but it showing an error i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field my code for insert query is
Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)
and Error is Shown by browser is as follows
Server Error in '/WebApplication1' Application.
Missing semicolon (;) at end of SQL statement. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.Source Error:
Line 466: con.Open() Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con) Line 468: q1.ExecuteNonQuery() Line 469: con.Close() Line 470: End SubSource File: C:InetpubwwwrootWebApplication12.aspx.vb Line: 468 Stack Trace:
SELECT QueryOne.StudentID, Max(QueryOne.Year) AS MaxOfYear, Count(QueryOne.StudentID) AS CountYear, IIf([CountYear]<2,"1","0") AS NewStudent, IIf([CountYear]>1,"1","0") AS ContStudent, IIf(Max([Order]-1)<5,"K-4",Max([Order])-1) AS [Grade Level], Max(Lookupgradelevel.APR_Order) AS Sorted_Grade_LevelFROM Lookupgradelevel RIGHT JOIN QueryOne ON Lookupgradelevel.gradelevel = QueryOne.GradelevelGROUP BY QueryOne.StudentIDHAVING (((Max(QueryOne.Year))=[Forms]![APR]![Year]))ORDER BY Max(Lookupgradelevel.APR_Order);---------------------------------------------------------------------------------------------------I run the above statements in sql server 2005 ,it says that there are errors near "<", and near "Max"Could you help me, thanks.
Anyone see any thing wrong with this syntax? Getting an exeption error on the update command: Incorrect syntax near ','. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
UPDATE tblRMADetail SET ToVendorShipDate =, ToVendorShipTime =, ToVendorShipMethod =, ToVendorTrackingNumber =, Status = WHERE (RMADetailKeyID = @original_RMADetailKeyID) AND (FKRMA = @original_FKRMA) AND (RMANumber = @original_RMANumber) AND (ItemID = @original_ItemID) AND (ItemDescription = @original_ItemDescription) AND (ItemCost = @original_ItemCost) AND (DatePlacedOnRMA = @original_DatePlacedOnRMA) AND (Quantity = @original_Quantity) AND (QuantityFilled = @original_QuantityFilled) AND (QuantityReceived = @original_QuantityReceived) AND (QuantityCancelled = @original_QuantityCancelled) AND (SONumber = @original_SONumber) AND (FKExchange = @original_FKExchange) AND (ToVendorShipDate = @original_ToVendorShipDate) AND (ToVendorShipTime = @original_ToVendorShipTime) AND (ToVendorShipMethod = @original_ToVendorShipMethod) AND (ToVendorTrackingNumber = @original_ToVendorTrackingNumber) AND (ExpectedDate = @original_ExpectedDate) AND (ExpectedTime = @original_ExpectedTime) AND (ExpectedShipMethod = @original_ExpectedShipMethod) AND (VendorReimbursementAmount = @original_VendorReimbursementAmount) AND (ReimbursementDate = @original_ReimbursementDate) AND (Status = @original_Status) AND (ItemSource = @original_ItemSource) AND (BinLocation = @original_BinLocation) AND (AccountNumber = @original_AccountNumber) AND (CustomerRMANumber = @original_CustomerRMANumber) AND (ReceivedFromCustomerDate = @original_ReceivedFromCustomerDate) AND (ShipMethodFromCustomer = @original_ShipMethodFromCustomer) AND (ContractNumber = @original_ContractNumber) AND (FKCustomerInventory = @original_FKCustomerInventory) AND (FKCustomerInventoryAssemblyDetail = @original_FKCustomerInventoryAssemblyDetail) AND (Comments = @original_Comments) AND (GLCode = @original_GLCode) AND (Serialized = @original_Serialized) AND (Type = @original_Type) AND (QuantityReturnedToStockOrCustomer = @original_QuantityReturnedToStockOrCustomer) AND (CancelDate = @original_CancelDate) AND (CancelReason = @original_CancelReason) AND (CancelledBy = @original_CancelledBy) AND (ReplacementCostDifferential = @original_ReplacementCostDifferential) AND (ReplacementGLCode = @original_ReplacementGLCode) AND (VendorInvoiceNumber = @original_VendorInvoiceNumber) AND (DropShipFlag = @original_DropShipFlag) AND (ShipToName = @original_ShipToName) AND (ShipToAddress1 = @original_ShipToAddress1) AND (ShipToAddress2 = @original_ShipToAddress2) AND (ShipToCity = @original_ShipToCity) AND (ShipToState = @original_ShipToState) AND (ShipToPostalCode = @original_ShipToPostalCode) AND (ShipToCountry = @original_ShipToCountry) AND (PONumber = @original_PONumber)
dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...
I have a table named spt_datatype_info that table has a column called TYPE_NAME I have given a WHERE statement in a query:
SELECT * FROM spt_datatype_info WHERE (TYPE_NAME = smallint)
i know actually the value smallint has to be given in quotes... Now my question is: When i give the Verify SQL syntax then it does not return any error, but when i run it...then it given the following error...
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.
what does this mean...?? If the SQL statement is wrong then it should return an error when verifying the statement...
Insert Into Weekly(TesterID) Select TesterID FROM ALD where TesterID = 'CMT32' , 'CMT31' , 'CMT02','CMT33','CMT04','CMT36','CMT37','CMT38','CMT03','CMT05','CMT42','CMT34','CMT41','CMT40','CMT29','CMT30','CMT01','CMT39','CMT35'
Im receiving the following error while trying to execute the given query. Advice please.
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','.
I have a case statement that is reading a value from a csv column, and based on where that value falls, calculates a product's a numeric value. It works on one computer, and when copied/pasted to mine, it throws errors.
This is the error: Error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '121.67END' at line 1"
Here is the case statement:
CASE WHEN [CSV_COL(8)] BETWEEN 0 AND .01 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 4.36 WHEN [CSV_COL(8)] BETWEEN .011 AND .05 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 5.44 WHEN [CSV_COL(8)] BETWEEN .051 AND 1 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 11
DECLARE CareModel_cursor cursor FOR SELECT DISTINCT ObAnteEvent.UrNo FROM ObBabyEpisode INNER JOIN ObAnteEvent ON ObAnteEvent.UrNo = ObBabyEpisode.UrNo AND ObAnteEvent.EpisodeID = ObBabyEpisode.EpisodeID WHERE ObAnteEvent.EventType='CareModel' ORDER BY EventDate,EventTime AND CONVERT(DateTime,ObBabyEpisode.BirthDate,103)>= Convert(DateTime,'1/1/2000',103) AND CONVERT(DateTime, ObBabyEpisode.BirthDate,103)<= Convert(DateTime,'1/1/2007',103)
Error is at 'AND ObAnteEvent.EpisodeID = ObBabyEpisode.EpisodeID' Why is this a problem and how do I solve this?
hi i have copied this from my other page where it works fine and i cant understand what is going wrong! maybe one of your guys can point out what i cant see! herei s my code string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID); int intCustID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"].ToString());lblCustomerID.Text = Request.QueryString["qsnOrderCustID"].ToString();lblOrderID.Text = Request.QueryString["orderID"].ToString();
SqlConnection myConn = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx"); //This is the sql statement.string sql = "SELECT [del_address], [del_post_code], [del_time] From tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); try { lblDelTime.Text = Convert.ToString(dr["del_time"].ToString); lblDelAddy.Text = dr["del_address"].ToString();lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text != "") { lblDelDate.Visible = true;lblDelTime.Visible = true; Label1.Visible = true;Label2.Visible = true; } }catch (Exception except) {lblerror.Text = Convert.ToString(except); }
Receive Error: when I Run Select Statement Asking to Declare @Country Not sure how to Declare the variable '@Country'. Any help would be appreciated... Thank You. Here is the Code: ____________________________________________________________________________________________________________ Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) If Not IsPostBack Then Dim conCommerce As SqlConnection Dim cmdSelect As SqlCommand Dim CategoriesCounter As SqlDataReader Dim StylesSource As SqlDataReader conCommerce = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) conCommerce.Open() cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce) StylesSource = cmdSelect.ExecuteReader() txtCounter1.DataSource = StylesSource txtCounter1.DataTextField = "Country" txtCounter1.DataBind() StylesSource.Close() conCommerce.Close() End If ______________________________________________________________________________ Error Message: Must declare the variable '@Country'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@Country'. Source Error: Line 220: cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)Line 221:Line 222: StylesSource = cmdSelect.ExecuteReader()Line 223:Line 224:
SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblLooker,tblSubject,tblStop WHERE NOT (SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblSubject, tblLooker, tblStop WHERE tblSubject.Subject > tblLooker.Looker AND tblSubject.Subject < tblStop.Stop AND tblLooker.id = tblStop.id) Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near ')'. Can anyone tell me why this is not working? Thanks
Im getting this error when i try to change my value of my int RoleID: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
Source Error:
Line 373: tryLine 374: {Line 375: rowsAffected = dbCommand.ExecuteNonQuery();Line 376: }Line 377: finallyThe code that courses this error should be this code from my DatabaseHandler class:... queryString = "SELECT unitID FROM Units WHERE containerID = @containerID AND logDateTime = @logDateTime"; dbCommand = new SqlCommand(queryString, dbConnection); //ContainerID Param IDataParameter dbParam_containerID = new SqlParameter(); dbParam_containerID.ParameterName = "@containerID"; dbParam_containerID.Value = containerID; dbParam_containerID.DbType = System.Data.DbType.String; dbCommand.Parameters.Add(dbParam_containerID); //LogDateTime Param IDataParameter dbParam_logDateTime = new SqlParameter(); dbParam_logDateTime.ParameterName = "@logDateTime"; dbParam_logDateTime.Value = logDateTime; dbParam_logDateTime.DbType = System.Data.DbType.DateTime; dbCommand.Parameters.Add(dbParam_logDateTime);...The logDateTime param comes as a param to the method this code i located in. In my database the two tables are created as the following:CREATE TABLE [dbo].[Roles] ( [RoleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [RoleLevel] [int] NOT NULL ) CREATE TABLE [dbo].[Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MobilPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IsActive] [bit] NOT NULL , [RoleID] [int] NOT NULL ) To me it all looks fine, but my application doesn't agree with me....can anybody help here?
I am getting an error message when trying to run this update statement:
update table1 set col1 = (select substring(col2,1,2) + '-' + substring(col2,3,7) from table1)
Error message:
Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
I have a following update statement that returns an error:
update job_schedule_rep set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuf f(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3 ,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup' from (select name, job_id, active_start_time from sysjobschedules where name like 'TLBkup%') as tl join (select name, job_id from sysjobs where name like 'TL Backup%') as sjt on sjt.job_id = tl.job_id where sjt.name not like '%Maintenance%' order by sjt.name)
Here is an error:
Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
I have tried replacing '=' with in and exists but it didn't make any differe nce.
DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '''' END FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot DROP TABLE ##pivot
Hi,I am relatively new to SQL. I am using SQL 2000. I am trying toUpdate a field base in a criteria in a scond table.UPDATE Tbl1SET Tbl1.Row2 = '1'WHERE Tbl1.Row1 =(SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3))Row 1 is the key between the two table. If I am doing only the selectbelow, I am getting the right value.SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3)When I am running the entire querry, I am getting this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.What I am trying to do is to update a field in Tbl1 base on a date inTbl2. If the date is expire, I want to raise a flag, in Tbl1.Thank youPhilippe
Please help me debug this statement:Select vVehRegNAddr.RegContChar1 From (((vVeh LEFT OUTER JOINvVehBrand ON vVeh.Plate = vVehBrand.Plate) LEFT OUTER JOINvVehRegNAddr ON vVeh.Plate = vVehRegNAddr.Plate) LEFT OUTER JOINvVehLegal ON vVeh.Plate = vVehLegal.Plate) Where ((vVeh.Active = 1)AND (vVeh.VUse NOT IN ('C/G','H/D','SNO','SNX')) AND (vVeh.LExpDt ='20050715'))ERROR MESSAGE:"Error in selection of Aggregates:If ANY aggregate functions(Count, Sum,...) are chosen,All fields mustbe aggregate that are not in Grouping"
Hi All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error
" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
And code is as follows
Code Snippet
Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)
MsgBox("Data saved successfully") myConnection.Close() and table structure is as follows and for date types i am passing them through date variables from textbox
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#. the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]); VragenTableAdapter VraagAdapter = new VragenTableAdapter(); lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1) { pnlVraag1.Visible = true; }
if (Vraag_ID == 2) { pnlVraag2.Visible = true; }
} protected void btnVraag1_Click(object sender, EventArgs e) { //make a new user GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter(); DateTime Moment = System.DateTime.Now; GebruikerAdapter.GebruikerToevoegen(Moment); int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment)); this.ViewState.Add("gebruiker_id", GebruikerID);
//go to 2nd question Response.Redirect("Default.aspx?vraagid=2", true); }
protected void btnVraag2_Click(object sender, EventArgs e) { //get back the user from question 1 int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message : SQL Server message 512, state 1, severity 16: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL Server message 3621, state 0, severity 0: The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microsoft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO.
I have the following error during setting partner on mirror server Msg 1431, Level 16, State 4, Line 1 Neither the partner nor the witness server instance for database "masterserver" is available. Reissue the command when at least one of the instances becomes available.
The partner is available through telnet. I've also checked ports vai netstat and have no found errors.
There are two noteworthy erros in the error log at mirror server Error: 9642, Severity: 16, State: 3. and An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
I am trying to take all the trips that equal a one and divide them by the total number of service calls and then multiple them by 100 to get the percentage.
I am receiving the following errror: "The value expression refers to the field 'SvcCallNumber'. Report item expressions can only refer to fields within the current dta set scope or, if inside an aggregate, the specified data set scope...
Hi,I try to insert record using select statement. But, I am having a problem with this code:Insert into PaymentVoucherTable (ID, Duration1, min1, Total1, Duration2, min2, Total2)select (ID, Duration1, min1, (Duration1 * min1), (select Duration2,min2,(Duration2 * min2) from Table2)from Table1The MSSQL keep displaying error message : "the number of column in INSERT statement is not the same as the select statement...".Is my subselect statement structure correct ???Thanks...
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue: sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error: Incorrect syntax near the keyword 'Bulk'. Anyone have any ideas, thanks
