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
View 6 Replies
ADVERTISEMENT
Oct 1, 2015
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.
View 3 Replies
View Related
May 20, 2015
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.
Here is the code:
SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.CompanyÂ
[code]....
Due to case by statement and having multiple category I have to use multiple group by.
select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase
[code]....
View 5 Replies
View Related
Oct 8, 2015
I have the following querry:
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.
View 3 Replies
View Related
Apr 10, 2015
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:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
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
How I can setup my report to group properly?
View 6 Replies
View Related
Nov 8, 2006
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
View 3 Replies
View Related
May 1, 2014
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.
View 9 Replies
View Related
Jun 6, 2007
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.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
View 2 Replies
View Related
Dec 28, 2007
HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
View 3 Replies
View Related
Jun 27, 2005
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.
View 1 Replies
View Related
Nov 5, 2007
Hi,I am getting a mysterious error message, and it doesnt say which line it referres to, just gives me a stack trace.Could somone decipher it for me?:System.Data.SqlClient.SqlException: Incorrect syntax near 'items'. [SqlException (0x80131904): Incorrect syntax near 'items'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +190 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +405 System.Web.UI.WebControls.SqlDataSource.Insert() +13 detailproview.Button2_Command(Object sender, CommandEventArgs e) +41 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886 My page code is:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = @qty WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@User)"+ "INSERT INTO Transactions (Itemid)VALUES (@productID)"+ "INSERT INTO Transactions (itemname)VALUES (@Itemsname)"+ "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+ "INSERT INTO Transactions (Qty)VALUES (@qty)"+ command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { SqlDataSource1.Insert(); } }}. Thanks!Jon
View 7 Replies
View Related
Apr 30, 2004
In my enterprise mngr under Microsoft sql Servers
Sql Server Group
I see (No Items)
My network guy doesnt no much about this and i know nothing (all my experience is with db on my local hd.
Please let me know as much as possible.
Thanks
View 1 Replies
View Related
Apr 17, 2008
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.
View 5 Replies
View Related
Mar 26, 2008
Group
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
hassan
View 4 Replies
View Related
Apr 17, 2008
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.
View 6 Replies
View Related
Jan 16, 2014
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?
View 6 Replies
View Related
Jul 23, 2005
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.
View 9 Replies
View Related
Apr 3, 2008
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.
Thank you in advance.
SELECT
convert(nvarchar(20), AlertDateTime,120)
AlertDateTime,
AlertName,
AlertPriority,
AlertCount,
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
SrcAddressInt,
SourcePort,
SourcePortName,
convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) % 256)))
DestAddressInt,
DestPortName,
dbo.SensorData1.ObjectName,
SensorName,
SensorInterfaceName,
AlertTypeID,
convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) % 256)))
SensorAddressInt,
ProtocolID,
Cleared,
VulnStatus,
dbo.SensorDataAVP.SensorDataID,
dbo.SensorDataAVP.AttributeName,
dbo.SensorDataAVP.AttributeDataType,
dbo.SensorDataAVP.AttributeText,
dbo.SensorDataAVP.AttributeValue,
dbo.SensorDataAVP.AttributeBlob,
ResponseTypeName,
ResponseName
from
dbo.SensorData
LEFT JOIN
dbo.SensorDataAVP
ON dbo.SensorDataAVP.SensorDataID =
dbo.SensorData1.SensorDataID
LEFT JOIN
dbo.SensorDataResponse
ON dbo.SensorDataResponse.SensorDataID =
dbo.SensorData1.SensorDataID
LEFT JOIN
dbo.ObjectView
ON dbo.ObjectView.ObjectName=
dbo.SensorData1.ObjectName
WHERE
convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDate
AND
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
between @LowerIP and @UpperIP
AND
AlertName = @EventName
View 2 Replies
View Related
Mar 30, 2015
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 | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
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?
View 6 Replies
View Related
May 18, 2015
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...)
View 5 Replies
View Related
Aug 31, 2007
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??
Thanks in advance!
View 3 Replies
View Related
Feb 21, 2006
'@P0' means what?
thank you!
View 1 Replies
View Related
Apr 9, 2008
SELECT
usr_login "User ALM EMCID",
employeeID "User PV EMPID",
usr_udf_nt_login "ALM USR LOGIN",
usr_first_name "User FirstName",
usr_last_name "User LastName",
usr_email "User Email",
usr_emp_type "Employee Type",
usr_status "ALM Status",
PeopleView.Active_Term "PV Status",
IsActive "AD Status",
benabled "ACE Status",
usr_start_date "User Start Date",
usr_end_date "User End Date",
usr_udf_manager_emcid "Manager EMCID",
manager_first_name "Manager FirstName",
manager_last_name "Manager LastName",
manager_email "Manager Email",
manager_status "Manager Status",
manager_end_date "Manager End Date"
from
(select
ALM.usr_login,
PV.employeeID,
ALM.usr_udf_nt_login,
ALM.usr_first_name,
ALM.usr_last_name,
ALM.usr_email,
ALM.usr_emp_type,
ALM.usr_status,
PV.Active_Term,
AD.IsActive,
ACE.benabled,
ALM.usr_start_date,
ALM.usr_end_date,
ALM.usr_udf_manager_emcid,
ALM.manager_first_name,
ALM.manager_last_name,
ALM.manager_email,
ALM.manager_status,
ALM.manager_end_date
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')
View 8 Replies
View Related
Jun 6, 2006
Hello All,
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
.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 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}
View 1 Replies
View Related
Aug 4, 2005
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
View 3 Replies
View Related
Jun 6, 2006
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}
View 1 Replies
View Related
May 30, 2007
Hi Marc,
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
ExecuteNonQuery method.
can you guide me about solving this issue?
Regards,
Anni
View 2 Replies
View Related
Sep 13, 2006
Hi,
Uses: Sql server 2000, asp.net 1.1;
I've the following Stored Procedure:
<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:
exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@FromDate datetime,@ToDate datetime,@UserID int,@PortalID int', @FromDate = 'Sep 1 2006 12:00:00:000AM', @ToDate = 'Sep 30 2006 12:00:00:000AM', @UserID = 207, @PortalID = 2
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.
Could anyone help me out on this?
Thanks & Regards,
View 6 Replies
View Related
Jul 6, 2015
I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.
Then I have a table usergroups with two columns (groupid, user) with example:
1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott
I want to find groupid 2 and 4 because they exactly match the content of @tbl.
View 8 Replies
View Related
Jul 29, 2006
what am i doing wrong.. :(
CREATE PROCEDURE getlistofspclass(@date datetime,@s varchar(8000) output)
AS
select @s = coalesce(@s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcodeWHERE dbo.calader.caledardatedates = @dateexec( @s)GO
View 6 Replies
View Related
Mar 4, 2004
Hi;
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
My ASP.NET Code:
.......................
SqlParameter SQLParm=new SqlParameter();
SQLStr=new SqlCommand("GetUser",Con.GetConnection);
SQLStr.CommandType=CommandType.StoredProcedure;
SQLParm=SQLStr.Parameters.Add("ReturnValue",SqlDbType.Int);
SQLParm.Direction=ParameterDirection.ReturnValue;
SQLStr.Parameters.Add("@UserName",txtuser.Text);
SQLStr.Parameters.Add("@Pass",txtpass.Text);
Con.GetConnection.Open();
SQLStr.ExecuteNonQuery();
Returned=Convert.ToInt32(SQLStr.Parameters["ReturnValue"].Value);
if(Returned==1)
{
SQLStr=new SqlCommand("GetUserRoles",Con.GetConnection);
SQLStr.Parameters.Add("@UserName",txtuser.Text);
SQLParm=SQLStr.Parameters.Add("@Roles",SqlDbType.Char);
SQLParm.Size=50;
SQLParm.Direction=ParameterDirection.Output;
SQLStr.ExecuteNonQuery();
Roles=Convert.ToString(SQLStr.Parameters["@Roles"].Value);
LblMsg.Text=Roles;
Con.GetConnection.Close();
}
............................
............................
Thank you for your replies from now....
View 6 Replies
View Related
Aug 21, 2004
hi,
i got this error when i run app.
---------------
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()
Source File: c:inetpubwwwrootaspnetsmsassignment_d.aspx.vb Line: 42
--------
what should i do?
anyone have any idea?
plz give solution.
it's urgent.
thanks in advance
View 2 Replies
View Related
Mar 30, 2000
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
StrEmail=request.form("email")
StrName=request.form("name")
StrIssue=request.form("issue")
Set objConn = Server.CreateObject("ADODB.Connection")
Set rs = server.createobject("ADODB.Recordset")
objConn.open "Driver={SQL server};Server=document;DSN=SQL;Database=issues;UI D=steu;PWD;"
sql = "Insert into issue-log2 (name,email,issue) values('" & _
strName & "','" & _
strEmail & "','" & _
StrIssue & "')"
objConn.execute sql
%>
Steu
View 2 Replies
View Related
