I have an interesting query problem. The result needs to be a single recordset as from a select statement, here's the prerequisites:
Tables: tShip tPerson tAddress tCertificate, has a field dtmExp (datetime)
where each Ship has one or more Persons which have one or more Addresses and each ship has one or more certificates.
Now, I need the query to return all ships, all persons and all addresses for those persons BUT ONLY the certificate with the latest expiration date (sort of like the result from a 'select top 1 from tCertificate order by dtmExp desc')
How can this be packed into one query? using inner joins on all tables will return multiple rows for each certficate for a ship, which it must not.
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table. -------Employees table-------- EmployeeID . . . . . . . . . . int Name . . . . . . . . . . . nvarchar(50) ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager. How can that be achieved?
Hi,there are three tables:1) department---------------ID (primary key)name2) reports----------ID (p. key)depIDuserID3) users--------ID (p.key)nameI want to get in one query how many distinct users have made a report for each department.E.g.: table reports may look like this:ID depID userID1 1 12 1 13 1 34 3 65 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
Hi, I have two database servers and in this one is consists of tables and another one is for stored procedures ... Here is my proble... I want to access the stored procedures from my application(.net) ...
Now I want to execute a sql script when ever I will run this script It will not only transfer data between sql server 2000 and dbase file but Also update the corresponding population value in dbf table, which has same building value In sql server table
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_iddt_id project_type project_id time_st owner_rep_id owner_name 103/23/08 AB Corporate 11.203tmw 2 03/23/08 AB Corporate23.252hologa 303/21/08 AB Corporate 12.253tmw 403/23/08 AB Plant14.351henry 503/23/08 AB Plant24.502hologa 603/23/08 Other Project13.242hologa 703/23/08 Other Project24.353tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name From Daily_Time_Entry dl left outer join AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on (ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or (ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output Plz help to solve this query to show output as specified.
hi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now any one have answer of this question then plz plz tell me ........
Daily_Time_Entry Fields are: dl_id,dt_id,project_type,project_id,time_st Data in table Daily_Time_Entry
dl_id dt_id project_type project_id time_st 1 03/23/08 AB Corporate 1 1.20 2 03/23/08 AB Corporate 2 3.25 3 03/21/08 AB Corporate 1 2.25 4 03/23/08 AB Plant 1 4.35 5 03/23/08 AB Plant 2 4.50 6 03/23/08 Other Project 1 3.24 7 03/23/08 Other Project 2 4.35
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name 1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw 2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa 3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw 4 03/23/08 AB Plant 1 4.35 -- 1 -- henry 5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa 6 03/23/08 Other Project 1 3.24 -- -- 2 hologa 7 03/23/08 Other Project 2 4.35 -- -- 3 tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name From Daily_Time_Entry dl left outer join AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on (ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or (ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output Plz help to solve this query to show output as specified.
Daily_Time_Entry Fields are: dl_id,dt_id,project_type,project_id,time_st Data in table Daily_Time_Entry
dl_id dt_id project_type project_id time_st 1 03/23/08 AB Corporate 1 1.20 2 03/23/08 AB Corporate 2 3.25 3 03/21/08 AB Corporate 1 2.25 4 03/23/08 AB Plant 1 4.35 5 03/23/08 AB Plant 2 4.50 6 03/23/08 Other Project 1 3.24 7 03/23/08 Other Project 2 4.35
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name 1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw 2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa 3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw 4 03/23/08 AB Plant 1 4.35 -- 1 -- henry 5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa 6 03/23/08 Other Project 1 3.24 -- -- 2 hologa 7 03/23/08 Other Project 2 4.35 -- -- 3 tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name From Daily_Time_Entry dl left outer join AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on (ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or (ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output Plz help to solve this query to show output as specified.
Daily_Time_Entry Fields are: dl_id,dt_id,project_type,project_id,time_st Data in table Daily_Time_Entry
dl_id dt_id project_type project_id time_st 1 03/23/08 AB Corporate 1 1.20 2 03/23/08 AB Corporate 2 3.25 3 03/21/08 AB Corporate 1 2.25 4 03/23/08 AB Plant 1 4.35 5 03/23/08 AB Plant 2 4.50 6 03/23/08 Other Project 1 3.24 7 03/23/08 Other Project 2 4.35
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_id dt_id project_type project_id time_st owner_rep_id owner_name 1 03/23/08 AB Corporate 1 1.20 3 tmw 2 03/23/08 AB Corporate 2 3.25 2 hologa 3 03/21/08 AB Corporate 1 2.25 3 tmw 4 03/23/08 AB Plant 1 4.35 1 henry 5 03/23/08 AB Plant 2 4.50 2 hologa 6 03/23/08 Other Project 1 3.24 2 hologa 7 03/23/08 Other Project 2 4.35 3 tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name From Daily_Time_Entry dl left outer join AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on (ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or (ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output Plz help to solve this query to show output as specified.
I have create a sales delivery printout with the following example information example below gives a delivery and the two sales orders that delievry is related to.
Two tables are T1 = delivery, T2 = orders
Delivery (T1)
linenum Item delievry Qty Ordernum orderlinenum DeliveryNumber 1 A 10 600 1 5001 2 B 5 600 2 5001 3 c 12 605 1 5001 4 P 1 5001
ORDER (T2) number 600
linemum Item OrderQty orderno 1 A 20 600 2 B 10 600 3 A 5 600 ORDER (T2) number 605 linemum Item OrderQty orderno 1 C 20 605 2 B 20 605 3 F 5 605
I want to print a delivery note with the all items on that delivery (delivered items) plus other items on orders which ware not delivered. so the result of the query should look like below
Result NO Item delievry Qty Ordernum orderlinenum ORDER QTY 1 A 10 600 1 20 2 B 5 600 2 10 3 c 12 605 1 20 4 P 1 5 A 600 3 5 6 B 605 2 20 7 F 605 3 5
You can ignore the first column of results which is just the row number.
other conditions
1. Some times the delivery is not based on any sales order 2. Delivery can base on 0,1,2,3 or any number of sales orders. 3. result should provide all rows from the delivery and all rows from sales orders except the rows in the delivery.
I have 3 tables: Â TABLE [dbo].[Tbl_Products]( [Product_ID] [int] IDENTITY(1,1) NOT NULL, [Product_Name] [nvarchar](50) NOT NULL, [Catagory_ID] [int] NOT NULL, [Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes, A.BuyPrice,A.TotalPrice,A.BuyPrice FROM Tbl_Products B INNER JOIN Tbl_PurchaseHeader C ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A ON A.PurchaseOrder=C.purchaseOrder WHERE A.PurchaseOrder=3
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.
I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.
catalog ----------- id - PK act_name (price and other such info)
Photos ----------- id - PK path_to_photo event_id - FK(catalog.id)
What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.
here is the query that I'm using right now.
Code:
SELECTCOUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name, catalog.location, catalog.price_adult, catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed, catalog.long_description, catalog.online, catalog.act_type, event_types.act_type AS evt_type, event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets, photos.id FROM catalog, event_types, photos WHERE catalog.act_type = event_types.id AND photos.event_id = catalog.id GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name
Which returns the following error. Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.
SELECT [List1].[TITLE], [List1].[FORENAME], [List1].[SURNAME], [List1].[JOB] FROM List1 WHERE EXISTS( SELECT [List2].[forename] FROM [List2] WHERE [List2].[forename] = [List1].[FORENAME] AND [List2].[surname] = [List1].[SURNAME] AND [List2].[email] = [List1].[EMAIL] )
I am on the right track here, aren't I?
That query will.. sort of... loop through List1, get the "current" records firstname, surname and email, and then go and see if there's any single record in List2 that contains exactly that combination of values in those fields. It's a stupid question but I'm not the brightest of sparks and BOL leaves me even more confused. I'm just worried in case it's going "well, record 34 matches the current firstname and record 96 matches the current email, so EXISTS returns true".
Assuming I've got it right, though - many of the records that I know to be duplicates have a NULL email address. And the query above seems to be automatically removing them. If there's a record in both List1 and List2 with identical firstname and surname, and both have NULL email addresses - should the query above pick it up? If not, how can I include those records?
I have the following bit of code that is an onclick event to save information in text boxes and list boxes. I cut out a bunch that was irrelevant to this because ALL items in the text boxes save fine. My biggest question that should lead to the answers for the rest of the code, is for lines 53-57... That stored proceedure does not appear to run. All it is set to do is and the code is DEFINITELY getting passed into it but it almost seems like it isnt and therefoe isnt doing anything. I get no errors, just nothing happens. I would of thought at the very least, it should run this proceedure and delete the information from the table even if it wouldnt save the new information based on the listboxes. I am not sure if I am explaining this correctly, but if anyone has any thoughts, I would greatly appreciate it. The delete proceedure that isnt actually making a changeALTER PROCEDURE [dbo].[Delete_Team_Data] ( @Code as int ) AS
DELETE from tblSectyData2 where Code = @Code DELETE from tblSectyData2 where SectyCode = @Code The actual code 1 2 3 protected void SaveChanges(object sender, EventArgs e) 4 { 5 6 string selectedEmployee = ""; 7 selectedEmployee = EmployeeList.SelectedValue; 8 9 10 11 string fName = ""; 12 //Snipped out a bunch of code relating to the text boxes 13 string secretaryCode = ""; 14 15 fName = txtFName.Text; 16 //Snipped out a bunch of code relating to the text boxes 17 secretaryCode = TeamList2.SelectedValue.ToString(); 18 int selemp = Convert.ToInt32(selectedEmployee); 19 20 21 22 23 String Conn = (string)Application["Facebook"]; 24 SqlConnection IntranetConnection; 25 SqlDataReader IntranetReader; 26 IntranetConnection = new SqlConnection(Conn); 27 //SaveEmpChanges works properly. 28 SqlCommand SaveEmpChanges = new SqlCommand("Exec dbo.Edit_Employee_Data '" + prefix + "','" + lName + "','" + fName + "','" + mName + "','" + pos + "','" + dept + "','" + directdial + "','" + ext + "','" + fax + "','" + hphone + "','" + cphone + "','" + partner + "','" + timekeeper + "','" + notary + "','" + practice + "','" + saddress + "','" + sphone + "','" + lnl + "','" + bar + "','" + oemail + "','" + haddresscom + "','" + haddress + "','" + hcity + "','" + hstate + "','" + hzip + "','" + school + "','" + degree + "','" + status + "','" + floor + "','" + code + "','" + email + "','" + language + "'", IntranetConnection); 29 //Delete Team does NOT work 30 SqlCommand DeleteTeam = new SqlCommand("Exec dbo.Delete_Team_Data '" + selemp + "'", IntranetConnection); 31 //GetEmployeeType does work 32 SqlCommand GetEmployeeType= new SqlCommand("Select EmpType from tblMain2 where Code = '" + selectedEmployee + "'", IntranetConnection); 33 34 IntranetConnection.Open(); 35 IntranetReader = SaveEmpChanges.ExecuteReader(); 36 IntranetReader.Close(); 37 IntranetConnection.Close(); 38 39 40 41 42 int count = 0; 43 string LinkT = ""; 44 string LinkT2 = ""; 45 46 string etype = ""; 47 48 lselemp.Text = selectedEmployee; 49 //Basically, while the page loads fine, it does NOTHING below this line... Ive comments out sections, I have also put in a bunch of labels in to show the variables being passed, it all seems fine. 50 51 52 53 IntranetConnection.Open(); 54 IntranetReader = DeleteTeam.ExecuteReader(); 55 IntranetReader.Close(); 56 IntranetConnection.Close(); 57 58 59 IntranetConnection.Open(); 60 IntranetReader = GetEmployeeType.ExecuteReader(); 61 while (IntranetReader.Read()) 62 { 63 etype = IntranetReader["EmpType"].ToString(); 64 } 65 IntranetReader.Close(); 66 IntranetConnection.Close(); 67 68 int end = Convert.ToInt32(TeamList2.Items.Count); 69 string teamcode = ""; 70 71 while (count < end) 72 { 73 74 teamcode = TeamList2.Items[count].Value.ToString(); 75 76 if (etype == "S") 77 { 78 LinkT = "Secretary"; 79 LinkT2 = "Works with"; 80 } 81 82 else if (etype == "O") 83 { 84 LinkT = "Works with"; 85 LinkT2 = "Secretary"; 86 } 87 88 89 90 //This command does not work 91 SqlCommand saveTeam = new SqlCommand("Exec dbo.Add_Team_Data '" + selemp + "','" + teamcode + "','" + LinkT + "','" + LinkT2 + "'", IntranetConnection); 92 93 94 IntranetConnection.Open(); 95 IntranetReader = saveTeam.ExecuteReader(); 96 IntranetReader.Close(); 97 IntranetConnection.Close(); 98 99 100 count++; 101 } 102 103 104 105 106 Response.Redirect("ManageEmployeeDirectory.aspx"); 107 } 108
I'm trying to create a "Self-Referential, Many-to-Many" relationship under the Database Diagrams section in Sql Server 2005 express and I'm having a heck of a time figuring out where to click and edit to create what I want.
The url below links to the pictorial represenation of what i'm trying to create. Any help is greatly appreciated. Thanks! http://www.communitymx.com/content/source/A1A63/diag.gif
It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: Â Â at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() Â Â at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
