Select Most Recently Edited Item AND A Certain Type If Another Doesn't Exist
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int] [Type] [nvarchar](10) [Address] [varchar](50) [City] [varchar](50) [State] [char](2) [Zip] [varchar](5) [AddDate] [datetime] [EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
hi i am running this program and its giving some problem while execution. When the program tries to create the table expt1..its says Msg 2714, Level 16, State 1 There is already an object named 'expt1' in the database.
and if i try to drop the table expt1 it says Msg 3701, Level 11, State 1 Cannot drop the table 'expt1', because it doesn't exist in the system catalogs.
How should I resolve this problem...? ==========================
AND DETAILTRANS.CONTRACT IS NOT NULL AND DETAILTRANS.CONTRACT!='N/A' AND DETAILTRANS.EXPREV='E' AND DETAILTRANS.PRELIMORFINAL='F' AND DETAILTRANS.postedDATE IS NULL ORDER BY DETAILTRANS.GMSKEy
I have a simple question I hope someone can verify for me. At my company we are sending 2 different response messages. One is a commit response and one is a finalize response. Currently the service that we are sending the responses to only cares about the commit but later on in development they will care about the finalize response. The service that we are sending the finalize message to simply does not have a finalize message type and if running the profiler on it an error occurs: "This message could not be delivered because the message type name could not be found." And the message is lost. This doesn't really matter to anyone because at the time no one cares about the message. But I want to make sure that that is expected. After all service broker is supposed to be reliable. So my question is: If a message_type doesn't exist on the target side, will the message disappear and is that normal service broker behavior. I would hate to one day find the transmission queue with thousands of messages in it.
Hi, I not sure where my connection string is incorrect. I cant' access the SQL Server through Query Analyzer but the program can't. here is my connection string."ConnectionString" value="Data Source=xx.xx.xx.xx;Initial Catalog=xxx;UID=xxx;PWD=xxx;MAX POOL SIZE=5"> In the Query analyzer, i choose the SQL Server Authentication and provide login and password. I can access the database. Also, i can access the database at local computer. But, i can't access the database by other pc throught network. do i need to provide any other in the connection string?
I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database? Thanks!
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.
Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.
Product Location Time Sales A X 1/04 200 B Y 1/04 100
A X 2/04 300
In the cube, if you were to look at product by location for just 2/04, you would see:
Product Location Sales All Loc 300 A X 300 Y
All Loc B X Y
How do you get rid of the zero's or combinations that don't exist?
Hi all: for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi"); if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like : if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database"); I can't imagine I got the bool variable via exception... thanks to all..
hello can anybody help me in this issue:-i got two sets of connection string modules, let us say (1) working (2) not working, and here is the code for the two sets:-set (1) which is working fine:- private void OpenData(){System.Data.DataTable dt = new System.Data.DataTable(); // The connection string was not originally hardcoded but for some reason it was staying around when passed // in through the constructor. It was always blank. System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(@"data source=localhost;initial catalog=truckorder;Integrated Security=SSPI;"); //System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(_cnString); cn.Open(); new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM dealers WHERE DealerWeb like '%" + _url + "%'", cn).Fill(dt); if(dt.Rows.Count > 0) {_dealerid = Int32.Parse(dt.Rows[0]["dealerid"].ToString());_dealername = dt.Rows[0]["dealerName"].ToString();_dealerparent = Int32.Parse(dt.Rows[0]["dealerParent"].ToString());_usedMessage = dt.Rows[0]["usedMessage"].ToString();_rentalMessage = dt.Rows[0]["rentalMessage"].ToString();} cn.Close();}----------------------------------------------------------------------------------------------------------------------------------------------------------------------set (2) not working fine:-private void OpenData() { System.Data.DataTable dt = new System.Data.DataTable();System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();conn.ConnectionString = "integrated security=SSPI;data source=FR011PC;persist security info=False;initial catalog=TruckOrder";
try{conn.Open();new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM dealers WHERE DealerWeb like 'yalevictoria.com.au'", conn).Fill(dt); if(dt.Rows.Count > 0){_dealerid = Int32.Parse(dt.Rows[0]["dealerid"].ToString());_dealername = dt.Rows[0]["dealerName"].ToString();_dealerparent = Int32.Parse(dt.Rows[0]["dealerParent"].ToString());_usedMessage = dt.Rows[0]["usedMessage"].ToString();_rentalMessage = dt.Rows[0]["rentalMessage"].ToString(); _dealerWeb = dt.Rows[0]["dealerWeb"].ToString();}} finally {conn.Close();}THE DIFFERENCE IN BOTH THE SETS IS :- The Connection String (if you look at carefully) the one which is working has localhost and the one which is not working is having FR011PC (this code is working fine on FR011PC machine name and SqlServer Name) BUTwhen i transfer FR011PC code to the localhost machine, I mean i changed to datasource from FR011PC to localhost and run its giving me SQL SERVER DOESN'T EXIST OR ACCESS DENIED.Can any body help me on this issue has this is urgent for me please...................
If TABLE2 has a data in resource 01-05 that isn't in resource01-05 of TABLE1 then I want to added it to the next free slot where ref is the unique key.
Hello,few users have problems with our application(ADP/MsAcces2000+SqlServer2000). Whenthey want to open a report which source is a stored procedure, they getmessage that "record source specified..... do not exist" - of course,it's not true.More details:- other users don't have this problem- they have WinXP- all users have the same permissions to SQL objects- I've checked Microsoft KB(http://support.microsoft.com/kb/243532/en-us) and they write about"dbo." before source name - I use that, so it's not a solution. Whatmore, in application there are lists which have SP as row source andwithout "dbo." and it works. (In lists a row source is defined as:"exec <sp_name>", in reports as: "dbo.<sp_name>").- computers have all service packs, new version of MDAC, etc.- I asked another user, who doesn't have this problem on his machine,to login on "bad" machine and he couldn't open reports too.Maybe someone could help? because I don't have more ideas.
Error at Get Fenics data to table [Load into FFFenics table [155]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.FFFenics'.".
Error at Get Fenics data to table [Load into FFFenics table [155]]: Failed to open a fastload rowset for "[dbo].[FFFenics]". Check that the object exists in the database.
Error at Get Fenics data to table [DTS.Pipeline]: "component "Load into FFFenics table" (155)" failed validation and returned validation status "VS_ISBROKEN".
Error at Get Fenics data to table [DTS.Pipeline]: One or more component failed validation.
Error at Get Fenics data to table: There were errors during task validation.
I'm having some very frustrating 'syncing' problems with my report (currently in dev in visual studio) and what actually exists in the database. To start, I had my report definition open, and realized I needed a new sproc. So I went over to the db and created a new sproc, lets call it "Test1". After making a new dataset in ssrs, I can't seem to get SSRS to realize the stored proc actually exists. I continually get the error:
"The stored procedure 'Test1' doesn't exist. (System.Data)
I've shut down visual studio, re-opened, etc. Same thing. It looks like visual studio has some cached version of sys.procedures that only refreshes when the bloody thing feels like it. Any ideas?? I've been doing this for awhile, and I've minded the connection string, correct sproc syntax, etc. It runs fine on SQL Management studio.
Another, similar problem - When adding in a new column to a sproc, the dataset doesn't seem to pick up on it, no matter how many times the "refresh fields" button is hit.
I'd like to get some ideas for the following: I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column) So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it. I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better. Thanks!
I'm trying to create a local copy of a popular CRM database calledSalesforce.com. Many of the tables in the DB have FOREIGN KEYreferences that I want to preserve, but I've run into a chicken and eggproblem. Table "A" has a reference to table "B," and table "B" has areference to table "A." So I can't CREATE one until the other exists.Is there a way to disable these checks until I've created all theschema?Here's what I see (error first, then SQL that caused it):Server: Msg 1767, Level 16, State 1, Line 1Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table'User'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES"UserRole"(Id), "RollupDescription" varchar(80),"OpportunityAccessForAccountOwner" varchar(40),"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime);CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,"Username" varchar(80), "LastName" varchar(80), "FirstName"varchar(40), "CompanyName" varchar(80), "Division" varchar(80),"Department" varchar(80), "Title" varchar(80), "Street" text, "City"varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime, "UserPermissionsMarketingUser" bit,"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/* Script to handle index maintenance Tuning constants are set in-line current values are; SET @MinFragmentation SET @MaxFragmentation SET @TrivialPageCount
I have developed a renderer that renders the xml to paint a rectangle with textboxes in all the right places based on the data retrieved off the database. When i render the a complete report to the reportviewer i have no problem.
I need however to render the rectangle into a custom report item for reusability and different scenarios. This just seem to be impossible since it seems like I cant use anything other than an imagemap to render custom report items. Is this true?
To make sure you understand....in other words....
Is there a way to develop a Microsoft.ReportingServices.ReportRendering.IRenderingExtension that renders the required xml like rectangles/textboxes etc. and then migrate this to a custom toolbox item. It just seems like this should be a very easy thing to do and something regularly needed. For example when you want to use textboxes in certain positions within a rectangle to draw a organisational chart.
I created a simple SSIS package that takes a Flat File Source (CSV file) and Imports it into a OLE DB Destination ([TestCSVImport].dbo.Table1). I have other CSV files I'd like to import, but I don't want to import entries where column "ordereID" (PK) are the equal. Just want to import the new data found in the CSV files. I tried adding a Lookup in-between the Flat File Source and the OLE DB Destination, but I'm not sure how to accomplish only importing new data.
Hiya,I want to populate a list view (in a variety of ways), but I would rathernot execute my function once for every record I want to show in the list.What I want to do is pass an array of "keys" into a stored procedure andhave that procedure return one recordset with all of the records in. Sowhat I want to do is something like:CREATE PROCEDURE MySuperProcedure@MyArrayOfIDs (some way of specifying an array of integers),ASSELECT *FROM MyTableWHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )Obviously this syntax is not available. Is there another way of doing this?
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number?
SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate desc
Hi, I have a stored procedure running on our monitoring server to monitor the backup jobs on other sql servers running on our network. This sp loops thorugh the list of server names and connects remotely to other servers using linked servers. However, if one of the servers is down, the sp stops after raising 42000 error ("SQL Server does not exist") and fails to carry on processing the next server in the list. Is there any way to capture this "SQL Server does not exist" error within the sp so that the sp can carry on with the processing? I am using SQL Server 2000 SP4. Thanks in advance for any replies. Opal
I am developing reports using SQL Reporting Services 2005. These reports have some single valued parameters and these parameters will be provided in report viewer itself. These parameters should have "All" as default. "Select All" comes by default in Multivalue report parameter. But for single value parameter it always shows "<Select a value>" and if the user does not select a value,it prompts for one.I need to remove <Select Value> and show "All" as default thereby user does not have to select a value.
I can include "All" as a part of the select query in the parameter's datasource so that it can be shown in the parameter dropdown . However, I am not sure how I can remove "<Select a value>" from the dropdown.
I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.
When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.
Since we can't attach anything here, below is the link to a screenshot of my issue: http://docs.google.com/View?docid=ddd6j2xn_52c5qd5
If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.
What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.
Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.
Notes: + No special chars are in either dropdown + I am using IE 7 (Also had someone test this on IE 6 - same problem) + Using Visual Studio 2005 to build report - where this works fine
Work around: + If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2
I am using a SQL command in ASP.NET to send a query to a an OLAP cube that returns a dynamic set of data that I load into a datatable and then bind to a GridView. I have made my own ITemplate implementaton for displaying and formatting the data, and the following line is causing me problems: RawValue = DataBinder.Eval(row.DataItem, "[Month].[Month].[MEMBER_CAPTION]") The error returned is: Month is neither a DataColumn nor a DataRelation for table My guess as to what is happening is that it sees the brackets in the field name and stops reading the field name at [Month], when the actual field name is much longer. I know that it knows about the column name because it displays it correctly in the header. Since I am loading this from an OLAP cube the names of my columns vary based on the criteria so I cannot alias the column because I don't know exactly which columns will be displayed. Does anyone know how I might get the DataBinder.Eval function to work with fields that contain square brackets [ and ] ? If I use the GridView's "auto-generate fields" option it will show the data (and this is the column name) but I lose all control over formatting and the other custom code I'm writing in my ITemplate interface. Thanks
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no ABC 1 blah 1 ABC 2 blahh 1 ABC 3 bla3 1 XYZ 1 more n 2 XYZ 2 another 2 EFG 1 blahhh 3
