I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.
My query now looks like this (i have remove most columns)
SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)
ORDER BY sort
I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !
The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates...
But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render.
In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do...
/*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/
SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection); sda.Fill(dt);
... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned).
So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment?
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
I have the following stored procedure that is returning nothing can anyone please help? SELECT job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date, customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM tblOrderWHERE (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End) When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id
How do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code: CREATE PROCEDURE [dbo].[uspReportData] -- Add the parameters for the stored procedure here @Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL, @Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand(); SqlDataReader drReturn = null; try { // Set the connection for the command object cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString); cmdReader.Connection.Open(); // Set the command type cmdReader.CommandType = Type; // Set the command text cmdReader.CommandText = Sql; // Set the command timeout cmdReader.CommandTimeout = _iTimeout; if (Parameters != null) { // Set the parameters for (int i = 0; i < Parameters.Count; i++) { cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]); } }
// Get the return value drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection); // Dispose of the command object cmdReader.Dispose(); cmdReader = null; } catch (SqlException e) { this.HandleError(e); } catch (Exception e) { this.HandleError(e); } // Return the reader return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Project]( [ProjID] [varchar](300) NOT NULL, [ProjType] [varchar](20) NULL, [ProjectTitle] [varchar](max) NULL, [ProjectDetails] [varchar](max) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](max) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED ( [ProjID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Report]( [ReportName] [varchar](50) NOT NULL, [ProjID] [varchar](300) NULL, [DeptCode] [varchar](50) NULL, [ProjType] [varchar](50) NULL, [ProjectTitle] [varchar](500) NULL, [ProjectDetails] [varchar](3000) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](50) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED ( [ReportName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectStatus]( [ProjectStatusID] [int] IDENTITY(1,1) NOT NULL, [ProjectStatus] [varchar](max) NULL, CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED ( [ProjectStatusID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectManager]( [ProjectManagerID] [int] IDENTITY(1,1) NOT NULL, [FName] [varchar](50) NULL, [LName] [varchar](50) NULL, [Inactive] [int] NULL, CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED ( [ProjectManagerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetReportQuery] ( @ReportName varchar(100) )
AS
BEGIN
SET NOCOUNT ON
IF @ReportName IS NULL BEGIN RETURN -1 END ELSE BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50) SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName)))) AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL) AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL) AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL) AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL) AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL) AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL) ) END
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>' PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5) SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc END ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks
Hello.... I am in the process of building a search function for an application (VS2005, ASP.net, VB code, and AJAX functionality).The user's form has a textbox (for an input string) and two drop-downs. The user would then click on a "Search Now" button, and the data should populatein a Gridview to the left of the search form. I'm trying to search over an SQL database and need to know how to exactly implement what I have so far: <div id="mcb" style="background-color:#2e4b5b; width:195px; height:410px; float:left"> <div id="ht" style="background-color:#1b2c36;padding:10px; border-bottom:solid 1px #fffdff; width:175px;float:left;"> Quick Search </div><div style="clear:both;"></div> <div style="padding:10px; background-color:#2e4b5b"> <div id="tfta" style="padding:5px 0px 0px 0px"> <b>Vendor Name</b> <asp:TextBox ID="txtVendorFilter" runat="server"></asp:TextBox> <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> <b>Contract Type:</b> <asp:dropdownlist ID="ddlContractTypeSearch" runat="server" Width="155px"> <asp:ListItem Value="0">Select...</asp:ListItem> <asp:ListItem Value="1">Vendor Maintenance</asp:ListItem> <asp:ListItem Value="2">Consulting Contract</asp:ListItem> <asp:ListItem Value="3">Hardware License</asp:ListItem> <asp:ListItem Value="4">Software License</asp:ListItem> <asp:ListItem Value="5">Statement of Work</asp:ListItem> <asp:ListItem Value="6">Admin Svcs. Agreement</asp:ListItem> <asp:ListItem Value="7">Master Service Agreement</asp:ListItem> </asp:dropdownlist> <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> <b>Sort By:</b> <asp:DropDownList ID="ddlSortBy" runat="server" Width="155px"> <asp:ListItem Value="0">Select...</asp:ListItem> <asp:ListItem Value="1">Contract Name</asp:ListItem> <asp:ListItem Value="2">Contract Type</asp:ListItem> </asp:DropDownList> <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> </div> <br /> <asp:Button ID="btnUpdateFilter" runat="server" Text="Search" width="120px"/> <asp:Button ID="btnReset" runat="server" Text="Reset" /></div> </div> Here's my select statement used to initially populate the Gridview (set up using the configuration of the Gridview):SELECT [ContractID], [ContractName], [ContractType], [EffectiveDate], [TerminationDate] FROM [Contract]Here's my btnUpdateFilter codebehind (VB): Protected Sub btnUpdateFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateFilter.Click Dim strVendorName As String Dim strContractType As String Dim strSortBy As String If txtVendorFilter IsNot "" Then strVendorName = txtVendorFilter.Text End If ''''''''''''''''''''''''''''If ddlContractTypeSearch.SelectedValue = 1 Then strContractType = "1"ElseIf ddlContractTypeSearch.SelectedValue = 2 Then strContractType = "2"ElseIf ddlContractTypeSearch.SelectedValue = 3 Then strContractType = "3"ElseIf ddlContractTypeSearch.SelectedValue = 4 Then strContractType = "4"ElseIf ddlContractTypeSearch.SelectedValue = 5 Then strContractType = "5"ElseIf ddlContractTypeSearch.SelectedValue = 6 Then strContractType = "6"ElseIf ddlContractTypeSearch.SelectedValue = 7 Then strContractType = "7"Else strContractType = "0"End If ''''''''''''''''''''''''''''''If ddlSortBy.SelectedValue = 1 Then strSortBy = "ContractName"ElseIf ddlSortBy.SelectedValue = 2 Then strSortBy = "ContractType"Else strSortBy = "ContractName"End If End Sub
My question is, "how can I form the proper SQL statement", and then implement it so that my gridview will populate using my generated SQL Statement? Thank you for the help!!! -S
I need some help. I have a function that used to use ADO.NET to return a dataview. Now I'm using linq, and I don't know how to make this work anymore.my original function looked like this: public DataView getIssue() { //do some ADO, return the dataview return ds.Tables["Articles"].DefaultView; } With this I could write Gridview1.dataSource = getIssue();I want to do the same thing with Linq, but I'm running into some trouble:Here's my function now:public DataView getIssue() { var query = from a in db.Articles join i in db.Issues on a.IssueID equals i.IssueID select a; DataView dv = new DataView(); dv = query.asdatatable(); return query; }OK, first off I can't use asdatatable since I am using a join, so I can't make the results of my query a dataview. I can't return the results of my query in this function, something that seemed simple to do in Linq now seems like something that may only be possible in ADO.Thanks for your time
You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.
I have this script that captures metrics and sizes using T-SQL. When running in development I have no issues. I can run under the context of any database and get results back. When deploying to production servers no results are returning. I'm trying to figure out what permissions are needed for this to return results.
No error message is being returned either. My access is limited for triage against Production? I need to be able to have Production DBA set the right permissions so this job can run correctly.
I have a query with a nested query that is used as an in line view. When I run the whole query I keep getting slightly different results each time (10000, 10002, 10001, 9999 etc.), nothing is being changed, no jobs are running on the DB to affect the tables etc.
When I run the inline view query with the nested query, or nested query on its own, it returns same number each time.
What could potentially be the cause of this?
Using SQL Server 2008 R2 Express Edition on local W7 PC
I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.
String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";
This is my SQL string... what it is trying to do is:
find the room where the capacity is the reqcapacity entered by user and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values. The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.
Not sure whether i should be using AND, or OR? or a combination.
I havea 2 part issue with a query I'm trying to run for a report.
I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?
Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?
FROM activities AS activities INNER JOIN incident AS incident ON activities.incid_id = incident.incid_id INNER JOIN activity_result_master AS activity_result_master ON activities.result_id = activity_result_master.result_id INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id
WHERE incident.tagged_delete_flag = 'N' AND activities.result_id = '6' AND (incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate
ORDER BY incident.incid_assigned_to, incident.incid_id
Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San
Hi, I have three tables: 1 Order Details 2 Product Info 3 Received Qty
The query is pretty simple however I'm getting incorrect results.
The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from the product info tbl.
all fairly straightforward and works without a problem..
However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)
I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.
Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl
These are held in a seperate tbl as an order can be deliveryed in several stages.
Here is my statement:
SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived FROM dbo.Suppliers_POs_Details INNER JOIN PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID WHERE (dbo.Suppliers_POs_Details.POID = @POID) GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, dbo.Suppliers_POs_Details_Received.Qty
The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...
It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?
Hello, I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value. I also have a status field, which can be null, but if provided, provides the appropriate status to filter by. Again, the query works in QA, but not in the application. I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null. Results are returned. But, not when the results are done through code. In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string. But, no results are returning. Why isn't that mapping over correctly? In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing. Any ideas? Can't post any code on this one... Thanks.
I have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:
Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:
CASE WHEN EXISTS (Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' ) THEN ind_customer.cst_ind_full_name_dn ELSE '' END AS 'FULL NAME'
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR FOR SELECT * FROM authors OPEN authors_cursor FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0 begin FETCH NEXT FROM authors_cursor end
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where Date1='8/9/2006 11:17:41 AM' 2)select * from X where Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1 FROM employee LEFT OUTER JOIN request AS request ON employee.emp_id = request.emp_id WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) AS dayspending FROM employee AS employee_1 LEFT OUTER JOIN request AS request_1 ON employee_1.emp_id = request_1.emp_id WHERE (employee_1.emp_id = @emp_id) GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname
Hi, Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is: <asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource> This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get) HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query: blogID, title, description, tags, dateAdded, noOfComments 1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0 Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF display_term: END OF FILE column_id: 2 document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed. Can anyone tell me what is causing this?
FROM PSROLEUSER USR JOIN PSOPRDEFN OPR ON USR.ROLEUSER = OPR.OPRID
LEFT OUTER JOIN PS_PERSONAL_DATA PER ON OPR.EMPLID = PER.EMPLID
LEFT OUTER JOIN PS_EMPLOYEES EMP ON OPR.EMPLID = EMP.EMPLID
LEFT OUTER JOIN PS_JOBCODE_TBL JCD ON JCD.SETID = 'RBLTT' AND EMP.JOBCODE = JCD.JOBCODE AND JCD.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE JCD.SETID = SETID AND JCD.JOBCODE = JOBCODE AND EFFDT <= GETDATE())
WHERE OPR.ACCTLOCK = 0 AND USR.ROLENAME = 'RBL MANAGER'
PROBLEM: The SQL insert trigger code below is returning incorrect results. In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement. For instance the value returne for the Price_BeforeAdj field does not = 20000000? It returns a NULL. See code below.
OFFENDING CODE:
ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I] ON [dbo].[budxcWIPAdjustments] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE budxcWIPAdjustments
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.
I'm testing this search, and it doesn't work. The table has the following columns: attachemtId (int) - primary key fileContent (image) - contents of the file fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")
I created a .doc file that simply says "ssn", and then run the following query:
SELECT * FROM TempAttachment WHERE CONTAINS(fileContent,'ssn')
and nothing is returned! I tried the same thing with a .pdf file, and same results.
I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!
