Transact SQL :: Syntax To Group Items In One Line?
Nov 20, 2015
I have records that I get in this format:
ID                      Customer              Type                 TypeNUm
100                     Tiger                    Item                   T100 Â
100                      Tiger                    Item                   T200
100                     Tiger                    Item                   T300
100                     Tiger                    Shiper                  SAAAÂ
100                      Tiger                   PO                      POAAA
200                      Panera                 GL                  WE
200                      Panera                 PO                  POBBB
The reftypes are not always the same, what I need is to get it in this form
ID                      Customer              Type             TypeNUm
100                     Tiger                    Item                   T100,T200, T300Â
100                     Tiger                    Shiper                  SAAAÂ
100                      Tiger                   PO                      POAAA
200                      Panera                 GL                     WE
200                      Panera                 PO                    POBBB
I have a query that returns the data about test cases. Â Each test case can have multiple bugs associated to it. Â I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription  BugID BugStatus 1         TestCase1            1    Closed 2         TestCase1            2    Open 3         TestCase2            11   Closed 4         TestCase2            12   Closed 5         TestCase2            13   Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
SELECT APHIST.ReturnDate AS ATDATE ,API_HIST.[ActionPlanItemID] ,API_HIST.[ActionPlanID] ,PIT.[ProductItemID] ,PIT.ProductItemCode ,PIT.Name,
[Code] ....
That query is suppose to add to calculation field OutStock and InStock based on the value of n
When executing this  query I get the following message :
Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
I have table1 with orderID and demographic info. Table2 with orderID and items. I would like to have a results display like this: OrderIDDemographicInfo Item1Item2Item3....ect One line per order. When I do a join I displaying all items in different rows.
I have sql server 2000 that was working fine until today,today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) .no items under SQL SERVER GROUP. But my application connected with sql server is running without any problem insert record and show all data. ERROR MESSAGE: A connection could not be established to sql server-2000- Reason: Cannot open user default database. Login failed
Please vrify SQL Server is running and check your SQL server registration properties(by right clicking on the Win-2000-Server node and try again)
PLEASE what that happens. please iam afraid help me now iam waitting now Thanks
I am using SQL Server 2000. I am logging on local machine. When I open "SQL Server Enterprise Manager," I only see following: Console Root --> Microsoft SQL Servers --> SQL Server Group --> (No items)
The server is running properly and applications are able to access the database. Also, I can run "Query Analyzer" and view all databases.
Please help to resolve the issue to view items in SQL Server Group. Thanks, Suraj.
Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName, o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount from orders o, customers c, Employees e, Shippers s, [Order Details] od where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.
I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.
Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.
With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!
My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.
Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)
I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.
My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.
One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.
Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)
How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
from dbo.ALM_USERS ALM, dbo.PeopleViewPV, dbo.corp_emp_ids AD, dbo.ALM_USERS ACE
where cast(ALM.USR_login as int)= PV.EmployeeID -- PV and ALM.USR_login = CAST(AD.employeeid AS varchar)---AD and ALM.USR_login = ACE.chdata ----ACE AND ALM.USR_login NOT IN ('BTGWC1', 'DMADMIN', 'IFILL', 'TESTUSER', 'WTDEV06', 'XELOPERATOR', 'XELSELFREG', 'XELSYSADM') AND ALM.USR_STATUS = 'Disabled')
Me saying " has any body come across such error would be underestimating".
Well I am getting a very peculiar and unique error "Line 1: Incorrect syntax near 'Actions'."
Explaining you the scene is the following Stored Proc.
This stored proc is execute from a VB code in the .net application as like: - {Try Connection.Init_Variables() cn.ConnectionString = Connection.gstrConnection ResDb.ConnectionString = Connection.gresConnection cn.Open() With sqlCmd .Connection = cn .CommandText = "DSP_Get_Required" .CommandType = CommandType.StoredProcedure
On Execution I get the subjected Error "Line 1: Incorrect syntax near 'Actions'." Any Ideas from your all experience to get away from this error will be helpful. Look forward to read somebody soon.
Stored Proc:- {SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id, N'ISPROCEDURE') = 1) DROP PROCEDURE dbo.DSP_Get_Required_ActionS GO
CREATE PROCEDURE DSP_Get_Required_ActionS @ActionID INT OUTPUT, @PersonID INT OUTPUT, @ReturnMessage Varchar(1000) OUTPUT, @Exists BIT OUTPUT, @Days INT OUTPUT, @StartDate DATETIME OUTPUT, @EndDate DATETIME OUTPUT, @OutCome VARCHAR(20) OUTPUT, @Evaluate INT OUTPUT, @DbName VARCHAR(100) OUTPUT AS
SET NOCOUNT ON
--DECLARE @PopulateSQL AS NVarchar(4000)
DECLARE @Rule_ID AS NUMERIC(9) DECLARE @Curr_ActionSubType AS VARCHAR(20) DECLARE @Eval_SubType AS VARCHAR(20) -- DECLARE @OutCome AS VARCHAR(20) -- DECLARE @Evaluate AS INT -- DECLARE @Days AS INT DECLARE @Message AS VARCHAR(1000) DECLARE @Mandatory AS BIT
-- This is the variable used to interpret the Precedant subtype DECLARE @Prec_Subtype AS VARCHAR(20)
-- DECLARE @Exists AS BIT --this is supposed to be the deceision maker variable to be used within the precedant check.
DECLARE @Precedant_SubTypes_Cnt AS INT --This is the variable used to recordcount the Precedant Subtypes to be checked DECLARE @Counter AS INT -- Counter used to loop through the Table of precedant Subtypes. DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNT
SET @Counter = 1
--Process to retrive @Curr_ActionSubType Variable CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100)) EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName +'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID+' AND ' +@DbName+'.resadm.action.status =''A''')
SET @Curr_ActionSubType = (Select ActionSubType from #Curr_ActionSubType) DROP TABLE #Curr_ActionSubType
--Process to retrive @StartDate Variable CREATE TABLE #StartDate(StartDate DATETIME) EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction) FROM '+@DbName+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND '+@DbName+'.resadm.action.status =''A''' )
SET @StartDate = (Select StartDate from #StartDate) DROP TABLE #StartDate
SET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY RULE_ID) SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Evaluate_Subtype) SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY OutCome) SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Evaluate) SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Days) SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Message) SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Optional_Mandatory_Precedant)
-- create the temporary table for the Subtypes to be evaluated CREATE TABLE #Preceding_SubTypes_Details ( SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL, RULE_ID NUMERIC(9), SubType VARCHAR(20), )
-- insert the current subtype that needs to be evaluated. INSERT INTO #Preceding_SubTypes_Details SELECT Rule_ID, Prec_Subtype FROM Rules_Details WHERE Rule_ID = @Rule_ID
-- create the History table for Reference --sk/* Modified to accomodatethe need ot dynamic database name to retrive from the different Resman databases CREATE TABLE #dsHistory ( ActionID INT, PersonID INT, ActionTypeID VARCHAR(1), DateofAction DATETIME, Status VARCHAR(1), Subtype VARCHAR(6), ActionTypeName VARCHAR(30), ActionSubtypeID VARCHAR(6), EffectCandidateCurrentState VARCHAR(10), TaxCode VARCHAR(6) ) EXEC ('INSERT INTO #dsHistory SELECT '+@DbName+'.Resadm.Action.ActionID, ' + @DbName+'.Resadm.Action.PersonID, '+@DbName+'.Resadm.Action.ActionTypeID, ' + 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction)DateofAction, ' + @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, ' + @DbName+'.ResAdm.Action_Types.ActionTypeName, '+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID, ' + @DbName+'.Resadm.Action_subtypes.EffectCandidateCurrentState, '+@DbName+'.Resadm.Person.TaxCode ' + ' FROM '+@DbName+'.Resadm.Action ' + ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) ' + ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID = '+@DbName+'.Resadm.Action.ActionTypeID ' + ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) ' + ' ON '+@DbName+'.Resadm.Action.subtype = '+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid ' + ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) ' + ' ON '+@DbName+'.Resadm.Person.PersonID = '+@DbName+'.Resadm.Action.PersonID ' + ' WHERE '+@DbName+'.Resadm.Action.actionID <> CONVERT(VARCHAR,'+@ActionID+')' + ' AND '+@DbName+'.Resadm.Action.PersonID = CONVERT(VARCHAR,'+@PersonID+')' + ' AND '+@DbName+'.Resadm.Action.Status =''A'' ' + 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction) > ' + ' ISNULL(( SELECT MAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ ' + @DbName+'.Resadm.Action.TimeOfAction)) ' + ' FROM '+@DbName+'.Resadm.Action ' + ' WHERE ('+@DbName+'.Resadm.Action.PersonID = CONVERT(VARCHAR,'+@PersonID+')) AND ' + ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') ' + ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) ' + ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')
--sk*/ SET @EndDate = (SELECT ISNULL((SELECT DateOfAction FROM #dsHistory WHERE SubType = @Eval_SubType), getdate()))
-- set the rowcount to retrieve the number of check to be carried out
SET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM #Preceding_SubTypes_Details)
WHILE @Counter <= @Precedant_SubTypes_Cnt BEGIN SET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details WHERE SubTypes_LIST_ID = @Counter)
SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype = @Prec_Subtype)
IF @ROWCOUNT > 0 BEGIN SET @Exists = 1 END
IF @ROWCOUNT = 0 BEGIN IF @Mandatory = 1 BEGIN SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; ' SET @Counter = @Precedant_SubTypes_Cnt SET @Exists = 0 END ELSE IF @Mandatory = 0 BEGIN SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; ' SET @Exists = @Exists END END
SET @Counter = @Counter+1 END
IF @Exists = 0 BEGIN EXEC( ' UPDATE '+@DbName+'.Resadm.Action ' + ' SET '+@DbName+'.Resadm.Action.Status = ''I'' ' + ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET @ReturnMessage = '+@Message ) END ELSE IF @Exists = 1 BEGIN SET @ReturnMessage = @Message END
IF @Rule_ID = Null BEGIN SET @ReturnMessage = 'Validation Rule Not Present' END -- Select 'Exist value : ', @Exists, 'Return message is : ', @ReturnMessage DROP TABLE #Preceding_SubTypes_Details DROP TABLE #dshistory
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO}
Hi,I'm getting the above error when i try to fill a Dataset through adataAdapter.I presume it is to do with the sql statement. Below is the relevantcode:string strPntUnitID = patientCodeLbl.Text;string strPntFName = fNameLbl.Text;string strPntLName = lNameLbl.Text;// Create DataAdapter & DatasetSqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,doctorNo FROM tblPatient" +"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+strPntFName +"')"+"AND (pntLName = '"+ strPntLName +"')", conn);DataSet dsDocNoToPnt = new DataSet();// Create command builder, automatically generates the update commandsSqlCommandBuilder pntCmd = new SqlCommandBuilder(daRelateDocToPnt);// Set the MissingSchemaAction property to AddWithKey because Fill willnot cause primary// key & unique key information to be retrieved unless AddWithKey isspecified.daRelateDocToPnt.MissingSchemaAction = MissingSchemaAction.AddWithKey;// Use dataAdapter to fill DataSetdaRelateDocToPnt.Fill(dsDocNoToPnt, "Patient");// place intDocNo into the dataset schemadsDocNoToPnt.Tables["Patient"].Rows[0]["doctorNo"] = "intDocNo";// Update The DatabasedaRelateDocToPnt.Update(dsDocNoToPnt, "Patient");Can anyone spot the problem?Thanks
Hello All,Me saying " has any body come across such error would beunderestimating".Well I am getting a very peculiar and unique error "Line 1: Incorrectsyntax near 'Actions'."Explaining you the scene is the following Stored Proc.This stored proc is execute from a VB code in the .net application aslike: -{TryConnection.Init_Variables()cn.ConnectionString = Connection.gstrConnectionResDb.ConnectionString = Connection.gresConnectioncn.Open()With sqlCmd..Connection = cn..CommandText = "DSP_Get_Required".CommandType = CommandType.StoredProcedure..Parameters.Add("@ActionId", SqlDbType.Int, 9).Value = ActionID..Parameters("@ActionId").Direction = ParameterDirection.InputOutput..Parameters.Add("@PersonID", SqlDbType.Int, 9).Value = PersonID..Parameters("@PersonID").Direction = ParameterDirection.InputOutput..Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 1000).Value =ReturnMessage.ToString..Parameters("@ReturnMessage").Direction =ParameterDirection.InputOutput..Parameters.Add("@Exists", SqlDbType.Bit, 1).Value = Exists..Parameters("@Exists").Direction = ParameterDirection.InputOutput..Parameters.Add("@Days", SqlDbType.Int, 9).Value = 0..Parameters("@Days").Direction = ParameterDirection.InputOutput..Parameters.Add("@StartDate", SqlDbType.DateTime).Value = Now()..Parameters("@StartDate").Direction = ParameterDirection.InputOutput..Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Now()..Parameters("@EndDate").Direction = ParameterDirection.InputOutput..Parameters.Add("@OutCome", SqlDbType.VarChar, 20).Value = "Stop"..Parameters("@OutCome").Direction = ParameterDirection.InputOutput..Parameters.Add("@Evaluate", SqlDbType.Int, 9).Value = 0..Parameters("@Evaluate").Direction = ParameterDirection.InputOutput..Parameters.Add("@DbName", SqlDbType.VarChar, 100).Value =ResDb.Database.ToString..Parameters("@DbName").Direction = ParameterDirection.InputOutput..ExecuteReader(CommandBehavior.Default)}On Execution I get the subjected Error "Line 1: Incorrect syntax near'Actions'."Any Ideas from your all experience to get away from this error will behelpful. Look forward to read somebody soon.Stored Proc:-{SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,N'ISPROCEDURE') = 1)DROP PROCEDURE dbo.DSP_Get_Required_ActionSGOCREATE PROCEDURE DSP_Get_Required_ActionS@ActionID INT OUTPUT,@PersonID INT OUTPUT,@ReturnMessage Varchar(1000) OUTPUT,@Exists BIT OUTPUT,@Days INT OUTPUT,@StartDate DATETIME OUTPUT,@EndDate DATETIME OUTPUT,@OutCome VARCHAR(20) OUTPUT,@Evaluate INT OUTPUT,@DbName VARCHAR(100) OUTPUTASSET NOCOUNT ON--DECLARE @PopulateSQL AS NVarchar(4000)DECLARE @Rule_ID AS NUMERIC(9)DECLARE @Curr_ActionSubType AS VARCHAR(20)DECLARE @Eval_SubType AS VARCHAR(20)-- DECLARE @OutCome AS VARCHAR(20)-- DECLARE @Evaluate AS INT-- DECLARE @Days AS INTDECLARE @Message AS VARCHAR(1000)DECLARE @Mandatory AS BIT-- This is the variable used to interpret the Precedant subtypeDECLARE @Prec_Subtype AS VARCHAR(20)-- DECLARE @Exists AS BIT --this is supposed to be the deceision makervariable to be used within the precedant check.DECLARE @Precedant_SubTypes_Cnt AS INT--This is the variable used torecordcount the Precedant Subtypes to be checkedDECLARE @Counter AS INT -- Counter used to loop through the Table ofprecedant Subtypes.DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNTSET @Counter = 1--Process to retrive @Curr_ActionSubType VariableCREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+@ActionID+' AND ' +@DbName+'.resadm.action.status =''A''')SET @Curr_ActionSubType = (Select ActionSubType from#Curr_ActionSubType)DROP TABLE #Curr_ActionSubType--Process to retrive @StartDate VariableCREATE TABLE #StartDate(StartDate DATETIME)EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+'+@DbName+'.Resadm.Action.TimeOfAction)FROM '+@DbName+'.resadm.actionWHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND'+@DbName+'.resadm.action.status =''A''')SET @StartDate = (Select StartDate from #StartDate)DROP TABLE #StartDateSET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERECurr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BYEvaluate_Subtype)SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY OutCome)SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Evaluate)SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Days)SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Message)SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERECurr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BYOptional_Mandatory_Precedant)-- create the temporary table for the Subtypes to be evaluatedCREATE TABLE #Preceding_SubTypes_Details(SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,RULE_ID NUMERIC(9),SubType VARCHAR(20),)-- insert the current subtype that needs to be evaluated.INSERT INTO #Preceding_SubTypes_DetailsSELECT Rule_ID, Prec_SubtypeFROM Rules_DetailsWHERE Rule_ID = @Rule_ID-- create the History table for Reference--sk/*Modified to accomodatethe need ot dynamic database name toretrive from the different Resman databasesCREATE TABLE #dsHistory ( ActionID INT,PersonID INT,ActionTypeID VARCHAR(1),DateofAction DATETIME,Status VARCHAR(1),Subtype VARCHAR(6),ActionTypeName VARCHAR(30),ActionSubtypeID VARCHAR(6),EffectCandidateCurrentState VARCHAR(10),TaxCode VARCHAR(6))EXEC ('INSERT INTO #dsHistory SELECT'+@DbName+'.Resadm.Action.ActionID, '+ @DbName+'.Resadm.Action.PersonID,'+@DbName+'.Resadm.Action.ActionTypeID, '+ 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+'+@DbName+'.Resadm.Action.TimeOfAction)DateofActio n, '+ @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, '+ @DbName+'.ResAdm.Action_Types.ActionTypeName,'+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID , '+ @DbName+'.Resadm.Action_subtypes.EffectCandidateCu rrentState,'+@DbName+'.Resadm.Person.TaxCode '+ ' FROM '+@DbName+'.Resadm.Action '+ ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) '+ ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID ='+@DbName+'.Resadm.Action.ActionTypeID '+ ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '+ ' ON '+@DbName+'.Resadm.Action.subtype ='+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '+ ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) '+ ' ON '+@DbName+'.Resadm.Person.PersonID ='+@DbName+'.Resadm.Action.PersonID '+ ' WHERE '+@DbName+'.Resadm.Action.actionID <>CONVERT(VARCHAR,'+@ActionID+')'+ ' AND '+@DbName+'.Resadm.Action.PersonID =CONVERT(VARCHAR,'+@PersonID+')'+ ' AND '+@DbName+'.Resadm.Action.Status =''A'' '+ 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.Dateof Action +''''+ '+@DbName+'.Resadm.Action.TimeOfAction) > '+ ' ISNULL(( SELECTMAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.Dat eofAction + '' ''+ '+ @DbName+'.Resadm.Action.TimeOfAction)) '+ ' FROM '+@DbName+'.Resadm.Action '+ ' WHERE ('+@DbName+'.Resadm.Action.PersonID =CONVERT(VARCHAR,'+@PersonID+')) AND '+ ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') '+ ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) '+ ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofA ction +'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')--sk*/SET @EndDate = (SELECT ISNULL((SELECT DateOfActionFROM #dsHistoryWHERE SubType = @Eval_SubType), getdate()))-- set the rowcount to retrieve the number of check to be carried outSET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM#Preceding_SubTypes_Details)WHILE @Counter <= @Precedant_SubTypes_CntBEGINSET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_DetailsWHERE SubTypes_LIST_ID = @Counter)SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =@Prec_Subtype)IF @ROWCOUNT > 0BEGINSET @Exists = 1ENDIF @ROWCOUNT = 0BEGINIF @Mandatory = 1BEGINSET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; 'SET @Counter = @Precedant_SubTypes_CntSET @Exists = 0ENDELSE IF @Mandatory = 0BEGINSET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; 'SET @Exists = @ExistsENDENDSET @Counter = @Counter+1ENDIF @Exists = 0BEGINEXEC(' UPDATE '+@DbName+'.Resadm.Action '+ ' SET '+@DbName+'.Resadm.Action.Status = ''I'' '+ ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET@ReturnMessage = '+@Message)ENDELSEIF @Exists = 1BEGINSET @ReturnMessage = @MessageENDIF @Rule_ID = NullBEGINSET @ReturnMessage = 'Validation Rule Not Present'END-- Select 'Exist value : ', @Exists, 'Return message is : ',@ReturnMessageDROP TABLE #Preceding_SubTypes_DetailsDROP TABLE #dshistoryGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO}
I have a similar problem as mentioned in the first mail of this chain. I am getting Line 202:Incorrect syntax near 'end'" while I try to execute the procedure from my VC++ code using
<SQL Code> CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch @FromDate DATETIME, @ToDate DATETIME, @PortalID INT = 1, @UserID INT AS SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt FROM PABX_Ext_Watch INNER JOIN PABX_Calls ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo INNER JOIN PABX_PortalUsers ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @FromDate, 102) AND PABX_Calls.eTime <= CONVERT(DATETIME, @ToDate, 102) AND PABX_PortalUsers.uID = @UserID AND PABX_PortalUsers.PortalID = @PortalID GROUP BY PABX_Ext_Watch.ExtNo GO </SQL Code>
However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:
Line 1: Incorrect Syntax Near ......
When I check the sql profiler the stored procedure is been executed in this way:
I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.
I am trying to prepare a login page.My code first controls if user exists in database with GetUser stored procedure then if he/she exists getting the roles assigned to that user with GetUserRoles procedure but I am taking this error Line 1: Incorrect syntax near 'GetUserRoles'.
GetUserRoles Procedure:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER Procedure GetUserRoles ( @UserName char(10), @Roles char(10) Output ) As Select @Roles=RoleID From UserRoles Where UserID=@UserName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
--------------- Line 1: 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: Line 1: Incorrect syntax near '='.
Source Error:
Line 40: Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE" & _ Line 41: "stud_id=" & sid, con) Line 42: adpt.Fill(ds, "SMS_student_class_master") Line 43: txt.Text = ds.Tables.Item("roll_no").ToString Line 44: con.Close()
I am confused. Any help you can provide, to resolve this error, would be very appreciated...... Thanks in advance!!
I am getting this error message......
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'. /formstoday/issue_log-preview.asp, line 76
The code for line 76 is thus...... objConn.execute sql
Here is the code gor the asp page........ <% Dim StrEmail Dim StrName Dim StrIssue
