Retrieving Hierarchical Data From A Single Table
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.
Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:
ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
------------------------------------------------------------------------------------------------------------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1
Desired output:
Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains
View Complete Forum Thread with Replies
Related Forum Messages:
Hierarchical Table Functions Vs Hierarchical CTE
Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go... Query Cost (relative to batch) : 0.49% Query Text : Select * From fn_GetTree(8); Query Cost (relative to batch) : 99.51% Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...) What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode. Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?
View Replies !
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
Retrieving Data From SQL Server Table To Display On Button On Datagrid Table.
I have nine type of buttons, EnrollAmtBTM PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation() { WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass(); string SQLCONDITION = "";string RUN_SQLCONDITION = ""; SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null) {ValueSelected = new SessionValues(); Session.Add(Session_UserSPersonalData, ValueSelected); } else { ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]); }ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false; Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode(); SelectedNode = AgencyTree.SelectedNode; SelectedAgency = SelectedNode.Value.ToString(); Agencytxt.Text = SelectedAgency; Agencytxt2.Text = SelectedAgency; Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null; try {CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'"); } catch { }DataTable TableSet = new DataTable(); TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows) {if (IndexingValues == 0) {SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } else {SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } IndexingValues += 1; }SQLCONDITION = SQLCONDITION + ")"; ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0) { Dataviewlisting.ActiveViewIndex += 1; } else { Dataviewlisting.ActiveViewIndex = 0; } SelectedAgency = SQLCONDITION; ValueSelected.CONDITION = SelectedAgency; ???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION; try { CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION); EnrollAmtBTM.Text = CollectionCount.ToString(); } catch { }////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg. "SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List" here is the functionpublic int CollectedFinaceDataCount(String SQLStatement) {int DataCollection; DataCollection = 0; try { SQLCommandExe = FinanceConnection.CreateCommand(); SQLCommandExe.CommandType = CommandType.Text; SQLCommandExe.CommandText = SQLStatement; ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar(); DisconnectToDatabase(); }catch (Exception ex) {Console.WriteLine("Exception Occurred :{0},{1}", ex.Message, ex.StackTrace.ToString()); } return DataCollection; } So here mu requirement request is to display only the value fronm the view i have against the agency selected Please help ASAP Thanks Santosh
View Replies !
Help With Data Binding (passing Parameters To SQL Query And Retrieving Data To Populate Table)
I am working on building a data access website. The database has 12 tables (2001exp, 2001imp, 2002exp, 2002imp, 2003exp, 2003imp, 2004exp, 2004imp, 2005exp, 2005imp, 2006exp, 2006imp). All the tables have same no. of columns and names. (Commodity, Country, Month, Quantity, Value)The webpage has 4 dropdown menus (as shown in the image)Type [Imp, Exp]Year [2001, 2002, 2003, 2004, 2005, 2006]Month [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]Country [ list of 228]The inputs Type and Year combined tell me which table the data will be retrevied from i.e., if I select imp and 2003, the data will be retrevied from 2003imp. And the inputs month and year will be parameters for Where condition in the Query.I don't know how to combine (concatinate) the first 2 inputs and pass on to the SQL query.The output of the table should be just a table with top 10 values.I am rookie to programming and I would appreciate if anyone can help me. (I am using Visual Studio 2005 and SQL Server 2005) The SQL query is (I am passing 3 parameters: tablename, country, month)Declare @SQuery nvarchar(4000)DECLARE @TblName varchar(100)Declare @Country Int, @Month IntSet @Country=5310Set @Month=12SET @TblName = '[2005exp]'SELECT @SQuery = 'select top 10 a.commodity as Commodity, c.descrip_1 as Description,c.quantity_1 as Unit, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],CASE WHEN (select sum(b.all_val_mo) from ' + @TblName + ' b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from ' + @TblName + ' b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S.Share(Value) %],CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price]from ' + @TblName + ' a inner join concord c on a.commodity=c.commodityWHERE a.cty_code= ' + convert(varchar(10), @Country) +' and a.stat_month= ' + convert(varchar(10), @Month) +'GROUP BY a.commodity, c.descrip_1, c.quantity_1order by [Value] desc'EXEC sp_executesql @SQuery
View Replies !
Retrieving Data From More Than One Table
Hi I ve a datagrid . And Two Database table in sqlServer2005. The name of the tables are 'Property' and 'userid'. My datagrid wants to retrive all records from Property table and one record from userid table. The Property table contains Propertycode, lastdate , departmentname. The userid table contains so many record along with 'id' record which my datagrid wants to retrieve. pl tel me how 2 write code for that?
View Replies !
Retrieving Data From SQL Table
this is a simple problem but it's just driving me mad as it's not reading from my dB. basically, I've have reviews stored in my dB and want to display them in a textbox by clicking on a button called btnReviews. I think the problem might be that there is too much text stored per row of the table (as it is a review), but I have the datatype set as text in sql. here's the simple un-errorred code I have behind the button. any ideas where I went wrong. i've a feeling it's something small but it's just taken too long to figure out.protected void btnReviews_Click(object sender, EventArgs e) { String strConn = ConfigurationManager.ConnectionStrings["conLocalDatabase"].ConnectionString; SqlConnection dbConnection = new SqlConnection(strConn); SqlCommand dbCommand = new SqlCommand("Select [ReviewC] From [Review])", dbConnection); dbCommand.Parameters.AddWithValue("Review", txtReviewView.Text); try { dbConnection.Open(); dbCommand.ExecuteNonQuery(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { if (dbConnection != null) { dbConnection.Close(); } } }
View Replies !
Retrieving Data From A Junction Table
Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC Below is my SQL statement; SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
View Replies !
Retrieving Table Structure And Data
Here`s my problem. I deleted 4 very important tables from my DB. I need to get the table structure and data that was contained inside the tables back. is there any way to do this?? Your help will be greatly appreciated!!!!!!!!!!!!!!! Teddy
View Replies !
Retrieving Data From Table With 7 Million Entries Takes Time
Can anyone help me on this... when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all.. Will it take this much time to retrieve data.?. The table does include an indexed field, So can anyone help me on the different things i can do to make the retrieval faster? Andy
View Replies !
Hierarchical Table (count)
Hellofor MS SQL 2000 i am having :CREATE TABLE [dbo].[Items]([id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[id_ItemsSup] [int] NULL,[Name] [nvarchar] (100) NOT NULL,[SubItems][int] DEFAULT (0)) ON [PRIMARY]with : UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1I get how many subItems has Item = 1how can I update the Column SubItems (for each row) ?to get the total of subItems for each Item ?thank you
View Replies !
On Delete Cascade && Hierarchical Table
for MS SQL 2000 I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE CREATE TABLE [dbo].[Users]( [id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, [id_UsersSup] [int] NULL, [Users] [nvarchar] (100) NOT NULL ) ON [PRIMARY] ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Sup] FOREIGN KEY ( [id_UsersSup] ) REFERENCES [Users] ( [id_Users] ) ON DELETE CASCADE but MS SQL refuse to create the foreign key even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under thank you for helping
View Replies !
Flatten Hierarchical Data
Hello I have a problem that I am hoping somebody can help me with! I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:- Newspapers National Newspapers Daily Express Express Publications Express Supplements Daily Mail Mail Publications Mail Supplements Mirror So my table would look like below:- 1 Newspapers Null 2 National Newspapers 1 3 Daily Express 2 4 Express Publications 3 5 Express Supplements 3 and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column. NewsPapers National Newspapers Daily Express Express Publications NewsPapers National Newspapers Daily Express Express Supplements Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy. Any help would be greatly received! Thanks Rich
View Replies !
Import Hierarchical Data
hi folks, I have to import hierarchical text files like: 32;country;city;postalcode;street 21;name;firstname;salutation;title;age;nickname 21;name;firstname;salutation;title;age;nickname ... additionally I have to eleminate doubles. what is the best way for this problem ? I have set up a flatfilesource with two columns and a conditional split on the first column so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ? Iam looking forward for any helping idea. rgrds, matze
View Replies !
Filtering Hierarchical Data
Hi, Can any body tell me how can i filter hierarichal data in a table while doing Merge replication? I have a table having a column self referencing to the primary key of one of the table record. This way a parent-child-grandchikd relation is maintained between entities. I want to filter this relationship on the basis of SUSER_Sname(). I have to parttion the data for the current user so that all of its children and grand children can also be replicated. Did anyone have had similar problem in past. I would really appreciate the help. Thanks
View Replies !
Hierarchical Data In Result Set
How can I create a function that returns hierarchical data from a table with this structure: - CategoryID - CategoryName - CategoryFather I want to bring the result set like this... CategoryID | CategoryName | CategoryFather | HierarchicalLevel 1 | Video | 0 | 0 2 | DivX | 1 | 1 3 | WMV | 1 | 1 4 | Programming | 0 | 0 5 | Web | 4 | 1 6 | ASP.Net | 5 | 2 7 | ColdFusion | 5 | 2 How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005. Thanks.
View Replies !
Hierarchical Data Model
Hi, I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node. what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels. thanks, RK
View Replies !
Help With Bottom To Top Query Into Hierarchical Data
Hello to all:First let me apologize for the length of this question, I've made an attemptto include as much information as is needed to help with the question.I am having problems putting together a query to pull out an alternativehierarchical view of my data. The database is implemented under SQL Sever2000 and I am writing the front end using VB.Net and ADO.net. The followingis the portion of my database structure that I am having problems with. Inthis diagrameach table has a one to many relationship with the table beneath it (Theprimary key for each table is the table name with ID appended, and theforeign keys for each child table refernce the primary key in the tableabove).Deals|Contracts___|Offers || | || |__OfferDetails| |Acceptances || ||__AcceptanceDetailsNotice that an Offer and it's associated Acceptances can be in more than oneContract, the OfferDetails describe how the Offer is broken up into eachContract.While there is a one to many relationship between Contracts andOfferDetails, there would only ever be a single OfferDetail per Contract perOffer (i.e. splitting up the Offer means assigning one and only oneOfferDetail to each Contract it is assigned to). This info may not berelavant, but it can be counted on as true. This doesn't hold true with theAcceptanceDetails however, there can be multiple AcceptanceDetails perAcceptance associated with OfferDetails.I wish to establish a couple of different heierachical grids into this dataas part of my user interface:View by Deals (In this view we see only the part of Each Offer associatedwith the Deal at the top of thehierarchy):Deal1Contract1Offer1 + OfferDetails associated with Contract1Acceptance1 + AcceptanceDetails assoctaited with OfferDetails above.Deal2Contract2Offer1 + OfferDeatils assoctaited with Contract2Acceptance1 + AcceptanceDetails associated with OfferDetails above.This grid has been relativly straitforward to figure out, as the hierrchicalinformation is alreadystructured to plug it into a hierrchical grid control easily (aftercombining the Offers and OfferDetailsinto a new table).View by Offer/Acceptance (In this view we see the entire Offer+Acceptance asthe top of the hierarchy,and each Deal (and associated Contract) underneath the Offer+AcceptanceOffer1+Acceptance1 (with all the details rolled up into a summuray includedin this row).Deal1Contract1Deal2Contract2This is the grid which is causing me problems, the problem area beingputting together a query which retrieves each DealID assoctiated with theOfferID at the top of the hierarchy. I need a query which essentiallyStarts with the OfferID, pulls each OfferDetailID associated with it, thenmoves upstream tofind each ContractID assoctaed with the OfferDetailsID (saving those ID'sfor use in third level of the grid, since there can be more Contracts underthe Deal which are not associated with the Offer we're looking at), and thenmoves upstream again to finally get the DealID which forms the second levelof the hierarchy.Can somebody help me with what such a query (or queries, as I assume I'llneed one to get the DealID's and another to get the ContractID's, althoughthe former might use the latter as a sub-query) might look like? I'm not atall good with T-SQL, but I'm learning fast! I haven't been able to find anyexamples onhow to go backwards up a tree to get to a record. I'll most likely beimplementing these as stored procedures, as these grid views are integral tomy userinterface.Thanks in advance to any who take the time to reply!
View Replies !
Implementing Inheritance For Hierarchical Data
Hi, in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data and auditing and reporting on individual nodes. what is the best way to store this type of data? my current approach is to have an attribute table having nodeid as a foreign key and only store the direct and NOT the inherited attributes of the node in the table, but this means to find all the attributes for the node, I need to gather the attributes of all the parents until the root node. I can't see any easy way out for this.
View Replies !
Help With Creating SQL Statement To Get Data From Single Table...
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table: I have the following table of data __User___Votes___Month __A_______14______2__A_______12______3__A_______17______4__A_______11______5 __B_______19______2__B_______12______3__B_______15______4 I want to beable to pull out the total number of votes a user has had over a period of months. eg Total up each users users votes for months 4 and 5 that would give: __User____TotalVotes ___A________28___B________15 An added complecation is that user B does not have any data for month 5 Any help or pointers would be fanstatic Many thanks
View Replies !
Displaying Data In Hierarchy From Single Table..
Hi, I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing.. Table Structure and Sample Data Id ParentId Name Code DisplayOrder 1 null Group 1 G00001 1 2 null Group 2 G00002 2 3 1 Sub-Group 1 SG0001 1 4 2 Sub-Group 2 SG0002 1 5 3 Sub-Sub-Group 1 SSG001 1 6 null Group 3 G00003 3 7 3 Sub-Sub-Group 2 SSG002 2 Desired Result Id ParentId Level Name ExtendedName DisplayOrder 1 null 1 Group 1 Group 1 1 3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1 5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1 7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2 4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1 2 null 1 Group 2 Group 2 2 6 null 1 Group 3 Group 3 3
View Replies !
Designing (or Gathering Data From): A Hierarchical Database Using SQL
Hi All, I am attempting to create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such as the Exchanges the stocks are traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams. I have "designed" a database with the following tables: tblDate_ 1 column clmDate (Primary Key, smalldatetime, NOT NULL) tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL) clmParentID (int, null) clmStockExchange (nvarchar(50), NOT NULL) clmMarkets_ (FK, nchar(20), NOT NULL) tblMarkets_ 1 column clmMarkets (PK, nchar(20), NOT NULL) tblIndices_ 1 column clmIndices (PK, nchar(50), NOT NULL) tblSectors_ 1 column clmSectors (PK, nchar(50), NOT NULL) tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL) clmMarkets_ (FK, nchar(20), NOT NULL) clmSectors_ (FK, nchar(50), NOT NULL) tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL) clmSecurity_Type (nchar(5), NOT NULL) clmSecurty_Name (nchar(50), NOT NULL) clmSectors_ (FK, nchar(50), NOT NULL) tblSecurities_Indices 3 columns clmSecurities_IndicesID (PK, int, NOT NULL) clmEPIC_ (FK, nchar(10), NOT NULL) clmIndices_ (FK, nchar(50), NOT NULL) tblSecurities_Date_OHLCV 8 columns clmOHLCVID (PK, int, NOT NULL) clmEPIC_ (FK, nchar(10), NOT NULL) clmDate_ (FK, smalldatetime, NOT NULL) clmOpen (float, NOT NULL) clmHigh (float, NOT NULL) clmLow (float, NOT NULL) clmClose (float, NOT NULL) clmVolume (float, NOT NULL) Why so many tables? perhaps you should put some more in... This was the only way I could work out how to store one-to-one and one-to-many relationships required for: - Many closing prices for many stocks - Stocks belonging to many indices - Stocks belonging to only one sector - Stocks belonging to only one market (MainMarket or AIM for LSE) - Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time) Why nchar's and not nvarchar's? Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells. Why do some tables have IDs and others don't? I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful. To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application. It would seem that pulling hierarchal data from a relational database, to pass to the TreeView control, is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Model or Nested Set Model) but they define a fairly simple example at the beginning (based on fruit or electrical goods) but don't appear to talk about gathering data from an existing relational database or changing an existing relational database so that it is more suited to storing hierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me! I would like the hierarchy to look like this: StockExchange Market Sector Stock Indices Sector Stock I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design? Any help would be greatly appreciated.
View Replies !
Comparing Data In Two Consecutive Rows From A Single Table
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View Replies !
A New Approach To Storing Ordered Hierarchical Data In RDBs.
Hello!I've discovered a new approach to storing ordered hierarchical data inRDBs. As far as I know there is no similar model out there and itdiffers significantly from traditional models (such as adjacency list,materialized path or the nested sets approach). A brief articledescribing the model (with accompanying sql code) can be found athttp://orangebeta.blogspot.com/2006...ies-in-sql.htmlGoran
View Replies !
Loading The Different Language Data From Excel File To The Single Table
can anyone help me to solve this problem i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english. so please tell me how to solve that reply to my mail id(sandeep_shetty@mindtree.com)
View Replies !
Hierarchical (tree) Data Structure Where A Node Can Have Multiple Parents
Hi all! I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options: Example 1 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child Example 2 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table? Thanks in advance,
View Replies !
Data Flow Task - Multiple Columns From Different Sources To A Single Table
Hi: I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks MA2005
View Replies !
Better Practices Wanted For Cascading Inserts Of Hierarchical Data From Staging Tables
I apologize if this has been asked, but I can't find a complete answer. We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key. I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least. I'll give an example that mirrors our actual solution, and ask if anyone has a better solution? ---------- Code Snippet CREATE TABLE [dbo].[TABLE_A]( [ID] [int] IDENTITY(1,1) NOT NULL, [DATA] [nchar](10) NOT NULL, [STAGING_COLUMN] [bigint] NULL, CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC) ) GO CREATE TABLE [dbo].[TABLE_B]( [ID] [int] IDENTITY(1,1) NOT NULL, [A_ID] [int] NOT NULL, [DATA] [nchar](10) NOT NULL, [STAGING_COLUMN] [bigint] NULL, CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC) ) GO ALTER TABLE [dbo].[TABLE_B] ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID]) GO CREATE TABLE [dbo].[STAGE_TABLE_A]( [A_Key] [bigint] NOT NULL, [DATA] [nchar](10) NOT NULL ) GO CREATE TABLE [dbo].[STAGE_TABLE_B]( [B_Key] [bigint] NOT NULL, [DATA] [nchar](10) NOT NULL, [A_Key] [bigint] NOT NULL ) GO The STAGING_COLUMN columns are the ones that will be added before, and dropped after. Code Snippet DECLARE @TABLE_A_MAP TABLE ( A_ID INT, A_Key BIGINT ) INSERT INTO TABLE_A (DATA, STAGING_COLUMN) OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP SELECT DATA, A_Key FROM STAGE_TABLE_A INSERT INTO TABLE_B (A_ID, DATA) SELECT TAM.A_ID, STB.DATA FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work. Here are a few of my constraints: The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here. This has to finish over the course of a weekend. This has to be delivered to QA this Friday Thanks for any help or insight.
View Replies !
Retrieving A Given Id From More Than One Table ???
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
View Replies !
SQL - Retrieving Same Table Twice
Hi, I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information. For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name: ----- SQL CODE ----- SELECT DISTINCT scContacts.ID, scContacts.inheritCoAddr, scContacts.fName, scContacts.lName, scContacts.contact, scContacts.billToAddress1, scContacts.billToAddress2, scContacts.billToState, scContacts.billToZip, scContacts.billToCity, scContacts.billToCounty, scContacts.billToCountry, scCompany.ID AS companyID, scCompany.companyName, scCompany.companyURL, scCompany.billToAddress1 AS companyAddress1, scCompany.billToAddress2 AS companyAddress2, scCompany.billToCity AS companyCity, scCompany.billToState AS companyState, scCompany.billToZip AS companyZip, scCompany.billToCounty AS companyCounty, scCompany.billToCountry AS companyCountry, scCompany.businessType, scCompany.phoneExt, scCompany.phoneNum, scCompany.faxNum, scCompany.minEmployees, scCompany.maxEmployees, scCompany.actionTypeMAX, scCompany.actionDateMAX, scCompany.actionTimeMAX, scCompany.statusID, scCompany.userID, scCompany.repID FROM scCompany, scContacts INNER JOIN scStatus ON scStatus.ID = scCompany.statusID WHERE scgroupContacts.contactID = [insert cookie value] AND scgroups.ID = [insert cookie value] AND scCompany.ID = scContacts.companyID AND scCompany.groupID = scgroups.ID ----- END SQL CODE ----- As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table? thanks, mellamokb
View Replies !
Retrieving PK To Insert Into Another Table
The query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade? --PROPERTYITEM INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId]) SELECT Property.propertyId, ITEM.ItemID FROM ITEM RIGHT OUTER JOIN miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN Property ON property.adprintid = miamiherald.adprintid WHERE validAD=1
View Replies !
Retrieving Data
Hi I've got a module that contains the following function. Imports System.Data.SqlClient Module SQL_Sprocs Function ListUsers() Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn")) Dim cmd As SqlCommand = New SqlCommand("list_users", conn) cmd.CommandType = CommandType.StoredProcedure conn.Open() Dim dr As SqlDataReader dr = cmd.ExecuteReader ListUsers = dr dr.Close() conn.Close() End Function End Module I then want to call this function from my webform. So I'm using the following Dim dr As SqlClient.SqlDataReader dr = Timetracker.SQLProcs.ListUsers() Do While dr.Read Label1.Text &= dr("first_name") & " " & dr("last_name") & ", " Loop dr.Close() But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages. What am I doing wrong? Lbob
View Replies !
Retrieving Data
Faculty member writes: "I meant to delete just one assignment, doing which was giving me difficulty. I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that. Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!" My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs. Then the server is backed up to tape shortly after the 5 pm full backup. Obviously, I can't restore from the backup for this situation because it would hurt other data. But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?) I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this. I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched. That's exactly what I need to do now with MS SQL 2000. As the faculty dude said, "HELP!" And Thanks.
View Replies !
Retrieving SQL Data Via ASP
I'm trying to display four SQL records and their associated links based on an ID. I'm getting really close now. Now it's returning each of the four names 5 times making me scroll down the result page to see them all. The names are: Mark Crispin Miller Andrew Gumbel Walter Mebane Bryan Jones These are the associated links: 'Democrats Say 2004 election failed in Ohio' 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections' 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones Mark Crispin Miller's blog This is how I see them when I test in my browser: Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them) Related Links: 'Democrats Say 2004 election failed in Ohio,' The Washington Post Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them) Related Links: 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections,' The Boston Globe Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them) Related Links: 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them) Related Links: This is a test link for the John Doe name Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them) Related Links: This is another test linked created Tuesday May 15 2007at 12 noon by Eric Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: 'Democrats Say 2004 election failed in Ohio,' The Washington Post Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections,' The Boston Globe Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: This is a test link for the John Doe name Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: This is another test linked created Tuesday May 15 2007at 12 noon by Eric Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy at the University of Washington Related Links: 'Democrats Say 2004 election failed in Ohio,' The Washington Post Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy at the University of Washington Related Links: 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections,' The Boston Globe Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy at the University of Washington Related Links: 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy at the University of Washington Related Links: This is a test link for the John Doe name Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy at the University of Washington Here is the SQL code and the html/asp display code: Code Snippet strSQL = "SELECT T_ProgramGuests.ProgramID, T_ProgramGuests.GuestName, T_ProgramGuests.GuestDescription, T_ProgramLinks.URL, T_ProgramLinks.Description FROM T_ProgramGuests LEFT OUTER JOIN T_ProgramLinks ON T_ProgramGuests.ProgramID = T_ProgramLinks.ProgramID WHERE (T_ProgramGuests.ProgramID = 9734)" Code Snippet <% Do while Not RS.Eof %> <br /> <strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br /> Related Links:<br /> <li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li> <% RS.MoveNext Loop %> How can I ensure that I see "One" name and it's associated link displayed only "Once" each?
View Replies !
Retrieving Data From .MDF???
Dear fellows, Up to the moment I've got enough knowledge for read data stored into .LDF files by dbcc log and so on. It's very useful and interesting. Now, I wonder how to retrieve the same information but on MDF files. At first, I want to make sure that is not possible by mean of traditional methods (dbcc or something like that) I suppose so but I'd like to hear opinions regarding that. Thanks in advance for any idea, though or further information.
View Replies !
Retrieving Data From SQL
Hi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone. Birju
View Replies !
Retrieving Data
Hi, I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server. I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
View Replies !
Retrieving Autonum / IDENTIFIER Value From SQL Table Using DAO.
Hello,I am in the midst of converting an Access back end to SQL Server Express.The front end program (converted to Access 2003) uses DAO throughout. InAccess, when I use recordset.AddNew I can retrieve the autonum value for thenew record. This doesn't occur with SQL Server, which of course causes anerror (or at least in this code it does since there's an unhandled NULLvalue). Is there any way to retrieve this value when I add a new recordfrom SQL server or will I have to do it programmatically in VB?Any direction would be great.Thanks!
View Replies !
SQL Help - Retrieving Rows Not In A Joining Table
I wonder if you can help... I have a simple setup: 2 tables and a joining table, and want to retrieve a data set showing every possible combination of table A and table B together with whether that combination actually exists in the joining table or not. My tables: channels ====== channel_id channel_name items ==== item_id item_name channels_items (joining table) =========== channel_id item_id created An example of the dataset I want (assuming 2 items and 2 channels, with itemA not being in channelB): item_id item_name channel_id channel_name exists ======= ========= ========== ============ ====== 1 ItemA 1 ChannelA True 2 ItemB 1 ChannelA True 1 ItemA 2 ChannelB False 2 ItemB 2 ChannelB True I'm completely stuck on how to achieve this. Any guidance would be very much appreciated.
View Replies !
Retrieving All Column Names From A Table
Is there a stored proc or systable that holds the actual names of the columns of a specific table? I know there are for foreign keys but I need the varchar names of every column of a table. Thanks in advance. Regards, Matthew Nye
View Replies !
Retrieving Image Field From A Table
i have a database (SQL Server) with a table that I retrieve records . One field is an image datatype and my problem is retrieving the image... my query is working in datatab (but it takes too much of time to retreive)... when i select preview.. it just hanged
View Replies !
Retrieving Records And Inserting Into Table
Folks: I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why? SET NOCOUNT ON DECLARE @ImportId INT SET @ImportId = 5151 DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode) =================================================================================== Same Query when trying to insert the records in a temp table it takes hours. =================================================================================== SET NOCOUNT ON DECLARE @ImportId INT SET @ImportId = 5151 DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId) create table #TResults ([ID] [INT] IDENTITY(1,1) NOT NULL, DealName VARCHAR(200), CUSIP VARCHAR(100), Vintage INT, PoolType VARCHAR(100), PaidOff BIT) INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode) SELECT * FROM #TResults ============================================ Thanks !
View Replies !
HELP In Retrieving Query Data
Folks, after retrieving the results ( SQL 2000 ) within Enterprise Manager, there were several rows who's text ( from a message field ) simply read " < Long Text > " adjacent to the author's name... ( I can't expand this column but I KNOW there's data ). All other message rows returned the text as expected.... Can I expand this retrieved data to present the text I'm expecting? Thanks in advance!!!! Vic
View Replies !
Retrieving XML Data From A Directory
Can anyone tell me how to read all the XML files in a directory(using a stroed procedure in SQL Server 2000) and insert the values of tags found in every file into a table For example suppose I have the tag <customer> in each XML file existing in a particular directory. I have to read each XML file and retrieve the information from <customer> tag and put it into a table. (using a stored procedure). Thanks
View Replies !
Retrieving Sorted Data
I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement? To clarify, say I have the following data: Table1: Anglesey, Cardiff, Ceredigion Table2: London, Dorset, Lancashire Table3: Antrim, Armagh Table4: Glasgow, Berwick, Edinburgh I'd want the data retrieved from all four tables and sorted so that the data retrieved would be: Anglesey, Antrim, Armagh, Berwick, Cardiff, Ceredigion, Dorset, Edinburgh, Glasgow, Lancashire, London I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.
View Replies !
Retrieving UDT Data In Non .NET Languages. ?
I have been thinking of using SQL server 2005 as i would like the flexibility i get through UDT. Retrieving the UDT data in managed could is ok but i would like to retrieve it in non .NET languages too. For example lets say i create a UDT "Point". I insert data in a table that has some columns of type point. Now is there a way i can get the data of the type point in a point object in non .NET languages like perl, python...
View Replies !
Retrieving Data From SQL2000
I am trying to retrieve data that has been inserted into the database but only half the text is being displayed. The field is varchar(8000) In the Enterprise Manager I can view the data, then I copy and paste it into MS Word and everything is there. 851 Characters with spaces. However, when I enter the SQL code into SQL Query Analyzer only half the text shows, about 257 characters are displayed with spaces. When I display the information on the webpage only half the text is displayed (running IIS/PHP 5) I don't know why this is doing this and not sure what I can do to display the full text. Please help.
View Replies !
Help With Retrieving String Data
Hello, I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters. autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'"; autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'"; autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'"; Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it. Thanks for your help in advance.
View Replies !
Problem Retrieving Data From SQL CE
Hi, I am using Visual Studio 2008 with .NET 3.5 Visual Basic. I'm having a problem retrieving data from my SQL CE database. I have a function that imports a file, parses it and writes the contents to a database. When I then query the database from my code, the data I have just written doesnt seem to be there. Even when i close and re-run the program. Only when I first view the table contents through Visual Studio then re-run the program does the data show up. The things I know are. When I insert data there is no problem. I can view the data through visual studio server explorer. only when I view data through the server explorer can my code retrieve it. Could this be something to do with caching? Any help would be appreciated. The (stripped down) code I use to insert data is below kevin Dim rowsAffected = 0 Dim connection As New SqlServerCe.SqlCeConnection(connectionstring) Dim transaction As SqlCeTransaction ' command objects for inserting a ContactList and retrieving its last identity Dim cmdInsertContactList As New SqlServerCe.SqlCeCommand("INSERT INTO ContactLists (Name, FolderID) VALUES (@Name,@FolderID)") cmdInsertContactList.Parameters.Add("@Name", SqlDbType.NVarChar) cmdInsertContactList.Parameters.Add("@FolderID", SqlDbType.NVarChar) Try connection.Open() transaction = connection.BeginTransaction() cmdInsertContactList.Connection = connection cmdInsertContactList.Transaction = transaction ' insert a new list cmdInsertContactList.Parameters("@Name").Value = list.Name cmdInsertContactList.Parameters("@FolderID").Value = folder.ID rowsAffected += CInt(cmdInsertContactList.ExecuteScalar()) transaction.Commit() Finally connection.Close() End Try
View Replies !
Retrieving Data From Database...
Hi all, I am working on a project on PocketPC in which it is required to reteive data from database. I have created database on simulator as .sdf file. I want to retreive data from eVC++ code. How i can do so? thanx
View Replies !
Retrieving A Portion Of PK Data
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View Replies !
|
TRACKER
