Search Results

Search found 29121 results on 1165 pages for 'sql 2000'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 634/1165 | < Previous Page | 630 631 632 633 634 635 636 637 638 639 640 641  | Next Page >

  • connecting to secure database from website host

    - by jim
    Hello all, I've got a requirement to both read and write data via a .net webservice to a sqlserver database that's on a private network. this database is currently accessed via a vpn connection by remote client software (on standard desktop machines) to get latest product prices and to upload product stock sales. I've been tasked with finding a way to centralise this access from a webservice that the clients then access, rather than them using the vpn route to connect directly to the database. My question is related to my .net service's relationship to the sqlserver database. What are the options for connecting to a private network vpn from a domain host in order to achive the functionality of allowing the webservice to both read and write data to the database. For now, I'm not too concerned about the client connectivity and security (tho i appreciate that this will have to be worked out too), I'm really just interested in discovering the options available in order to allow my .net webservice to connect to the private network in as painless and transparent a way as posible. The option of switching the database onto public hosting is not an option, so I have to work with the sdcenario as described above for now, unless there's a compelling rationale presented to do otherwise. thanks all... jim

    Read the article

  • C# - Rollback SqlTransaction in catch block - Problem with object accessability

    - by Marks
    Hi there. I've got a problem, and all articles or examples i found seem to not care about it. I want to do some database actions in a transaction. What i want to do is very similar to most examples: using (SqlConnection Conn = new SqlConnection(_ConnectionString)) { try { Conn.Open(); SqlTransaction Trans = Conn.BeginTransaction(); using (SqlCommand Com = new SqlCommand(ComText, Conn)) { /* DB work */ } } catch (Exception Ex) { Trans.Rollback(); return -1; } } But the problem is, that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block. But i think thats a bit risky, since both can throw multiple exceptions. Am I wrong, or do most people just ignore this risk? Whats the best solution to be able to rollback, if an exception happens. Thanks in advance, Marks

    Read the article

  • triggers for generating userID based on user's information.

    - by nectar
    I have complaint table 1. tblProfile with columns userId | name | age | address | mobileno | 2. tblUserId with columns userId | role | status now when user fills the form I want to insert one row in tblProfile, before inserting a new row I want to create userId by combining starting letters of name and mobile no and then insert into tblprofile with userId after this I want to insert that UserId into tblUserId table. for this I have to use two triggers one is before insert trigger and another is after insert trigger.but I dont know how to capture user information to create userId and how to pass that Id to second trigger.

    Read the article

  • Wordpress Database SQL query help needed

    - by i-CONICA
    Hi, I've written a PHP script to access the latest item from the wordpress database, which it does. But I need to use it twice, once for the latest item from a specific category, and another from a differerent category... But right now I cannot figure out how to put the query together. The post has a post_parent, which in another table, called wp_term_relationships, is referred to as object_id, and has a term_taxonomy_id, which then relates to a different table, called wp_terms where the term_taxonomy_id is now term_id and then you have the category slug name available to select... I really cannot understand how this query would work though. I've made a really crap mock up of it, to try to "visually" explain what i'm trying to do... SELECT * FROM wp_posts WHERE post_status = 'publish' AND (SELECT term_taxonomy_id FROM wp_term_relationships WHERE object_id = post_parent) AND (SELECT slug FROM wp_terms WHERE term_id = term_taxonomy_id) ORDER BY ID DESC LIMIT 1 Really would appreciate some help... Thanks.

    Read the article

  • Optimizing MySQL statement with lot of count(row) an sum(row+row2)...

    - by Zombies
    I need to use InnoDB storage engine on a table with about 1mil or so records in it at any given time. It has records being inserted to it at a very fast rate, which are then dropped within a few days, maybe a week. The ping table has about a million rows, whereas the website table only about 10,000. My statement is this: select url from website ws, ping pi where ws.idproxy = pi.idproxy and pi.entrytime > curdate() - 3 and contentping+tcpping is not null group by url having sum(contentping+tcpping)/(count(*)-count(errortype)) < 500 and count(*) > 3 and count(errortype)/count(*) < .15 order by sum(contentping+tcpping)/(count(*)-count(errortype)) asc; I added an index on entrytime, yet no dice. Can anyone throw me a bone as to what I should consider to look into for basic optimization of this query. The result set is only like 200 rows, so I'm not getting killed there.

    Read the article

  • Combining 2 Linq queries into 1

    - by Mike Fielden
    Given the following information, how can I combine these 2 linq queries into 1. Having a bit of trouble with the join statement. 'projectDetails' is just a list of ProjectDetails ProjectDetails (1 to many) PCardAuthorizations ProjectDetails (1 to many) ExpenditureDetails Notice I am grouping by the same information and selecting the same type of information var pCardAccount = from c in PCardAuthorizations where projectDetails.Contains(c.ProjectDetail) && c.RequestStatusId == 2 group c by new { c.ProjectDetail, c.ProgramFund } into g select new { Key = g.Key, Sum = g.Sum(x => x.Amount) }; var expenditures = from d in ExpenditureDetails where projectDetails.Contains(d.ProjectDetails) && d.Expenditures.ExpenditureTypeEnum == 0 group d by new { d.ProjectDetails, d.ProgramFunds } into g select new { Key = g.Key, Sum = g.Sum(y => y.ExpenditureAmounts.FirstOrDefault(a => a.IsCurrent && !a.RequiresAudit).CommittedMonthlyRecords.ProjectedEac) };

    Read the article

  • MySQL Count If using 4 tables or Perl

    - by user1726133
    Hi I have a relatively convoluted query that relies on 4 different tables, unfortunately I do not have control of this data, but I do have to query it. I ran this simpler query and it works using just table 1 and table 2 SELECT actor, receiver, count(IF(t2.group1 = "anxiety behavior", 1,0)) AS 'anxiety' FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code GROUP BY actor; Below are the 4 tables I need and the query I tried that didn't work Table 1 | Table 2 | Table 3 | Table 4 Actor | Behavior | Behavior | type of Behavior | subject | sex | subject |subject_code er frown | frown anxiety behavior | Eric M | Eric | er Here is the query that is failing SELECT actor, count(IF(t2.group1 = "anxiety behavior", 1,0) AND(t3.sex = "M", 1,0)) AS 'anxiety', FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code JOIN subject_code t3 on t1.actor = t3.behavior_code1 JOIN subjects t4 on t3.subject = t4.yerkes_code GROUP BY actor; Any help would be much appreciated!! Thanks :) P.S. if this is easier to do in Perl tips also much appreciated

    Read the article

  • how to Solve the "Digg" problem in MongoDB

    - by user193116
    A while back,a Digg developer had posted this blog ,"http://about.digg.com/blog/looking-future-cassandra", where the he described one of the issues that were not optimally solved in MySQL. This was cited as one of the reasons for their move to Cassandra. I have been playing with MongoDB and I would like to understand how to implement the MongoDB collections for this problem From the article, the schema for this information in MySQL : CREATE TABLE Diggs ( id INT(11), itemid INT(11), userid INT(11), digdate DATETIME, PRIMARY KEY (id), KEY user (userid), KEY item (itemid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Friends ( id INT(10) AUTO_INCREMENT, userid INT(10), username VARCHAR(15), friendid INT(10), friendname VARCHAR(15), mutual TINYINT(1), date_created DATETIME, PRIMARY KEY (id), UNIQUE KEY Friend_unique (userid,friendid), KEY Friend_friend (friendid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; This problem is ubiquitous in social networking scenario implementation. People befriend a lot of people and they in turn digg a lot of things. Quickly showing a user what his/her friends are up to is very critical. I understand that several blogs have since then provided a pure RDBMs solution with indexes for this issue; however I am curious as to how this could be solved in MongoDB.

    Read the article

  • Linq2Sql: query - subquery optimisation

    - by Budda
    I have the following query: IList<InfrStadium> stadiums = (from sector in DbContext.sectors where sector.Type=typeValue select new InfrStadium(sector.TeamId) ).ToList(); and InfrStadium class constructor: private InfrStadium(int teamId) { IList<Sector> teamSectors = (from sector in DbContext.sectors where sector.TeamId==teamId select sector) .ToList<>(); ... work with data } Current implementation perform 1+n queries, where n - number of records fetched the 1st time. I want to optimize that. And another one I would love to do using 'group' operator in way like this: IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } But attempt to launch query causes the following error: Expression of type 'System.Int32' cannot be used for constructor parameter of type 'System.Collections.Generic.IEnumerable`1[InfrStadiumSector]' Question 1: Could you please explain, what is wrong here, I don't understand why 'team_sectors' is applied as 'System.Int32'? I've tried to change query a little (replace IEnumerable with IQueryeable): IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors.AsQueryable()) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IQueryeable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } In this case I've received another but similar error: Expression of type 'System.Int32' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[InfrStadiumSector]' of method 'System.Linq.IQueryable1[InfrStadiumSector] AsQueryableInfrStadiumSector' Question 2: Actually, the same question: can't understand at all what is going on here... P.S. I have another to optimize query idea (describe here: Linq2Sql: query optimisation) but I would love to find a solution with 1 request to DB).

    Read the article

  • is there a tool to see the difference between two database tables in mssql?

    - by reinier
    What is a good tool to see the differences between 2 tables (or even better, the datasets returned by 2 queries). EDIT: I'm not interested in the schema changes. Just assume that the schemas are the same. background as to why: I'm porting some legacy code which can fill a database with some pre-calced data. The easiest way to see if I got everything right, is to check the output of the old program, with the new one. I was thinking that if there is some kind of 'diff' tool for databases, this might be great.

    Read the article

  • How to join nearly identical several queries into one?

    - by Devyn
    Hi, Assume I have an order_dummy table where order_dummy_id, order_id, user_id, book_id, author_id are stored. You may complain the logic of my table but I somehow need to do it that way. I want to execute following queries. SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 1 ORDER BY `order_dummy_id` DESC LIMIT 1 SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 2 ORDER BY `order_dummy_id` DESC LIMIT 1 SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 3 ORDER BY `order_dummy_id` DESC LIMIT 1 Please keep in mind that several numbers of same book is included in one order. Therefore, I list order_dummy_id by descending and limit 1 so only LATEST ORDER of A BOOK is shown. But my goal is to show other books in that way in one table. I used group by like this ... SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 GROUP BY book_id but it only shows order_dummy_id with ascending result. I have no idea anymore. Looking forward your kindness help!

    Read the article

  • postgres SQL - pg_class question

    - by Sachin Chourasiya
    PostgreSQL stores statistics about tables in the system table called pg_class. The query planner accesses this table for every query. These statistics may only be updated using the analyze command. If the analyze command is not run often, the statistics in this table may not be accurate and the query planner may make poor decisions which can degrade system performance. Another strategy is for the query planner to generate these statistics for each query (including selects, inserts, updates, and deletes). This approach would allow the query planner to have the most up-to-date statistics possible. Why postgres always rely on pg_class instead?

    Read the article

  • Oracle (Old?) Joins - A tool/script for conversion?

    - by Grasper
    I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id(+); Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)? Even better.. Is there a tool/script that will do this conversion for me (Preferred Free)? An optimizer of some sort? I have around 500 of these queries to port.. When was this standard phased out? Any info is appreciated.

    Read the article

  • Improving performance in this query

    - by Luiz Gustavo F. Gama
    I have 3 tables with user logins: sis_login = administrators tb_rb_estrutura = coordinators tb_usuario = clients I created a VIEW to unite all these users by separating them by levels, as follows: create view `login_names` as select `n1`.`cod_login` as `id`, '1' as `level`, `n1`.`nom_user` as `name` from `dados`.`sis_login` `n1` union all select `n2`.`id` as `id`, '2' as `level`, `n2`.`nom_funcionario` as `name` from `tb_rb_estrutura` `n2` union all select `n3`.`cod_usuario` as `id`, '3' as `level`, `n3`.`dsc_nome` as `name` from `tb_usuario` `n3`; So, can occur up to three ids repeated for different users, which is why I separated by levels. This VIEW is just to return me user name, according to his id and level. considering it has about 500,000 registered users, this view takes about 1 second to load. too much time, but is becomes very small when I need to return the latest posts on the forum of my website. The tables of the forums return the user id and level, then look for a name in this VIEW. I have registered 18 forums. When I run the query, it takes one second for each forum = 18 seconds. OMG. This page loads every time somebody enter my website. This is my query: select `x`.`forum_id`, `x`.`topic_id`, `l`.`nome` from ( select `t`.`forum_id`, `t`.`topic_id`, `t`.`data`, `t`.`user_id`, `t`.`user_level` from `tb_forum_topics` `t` union all select `a`.`forum_id`, `a`.`topic_id`, `a`.`data`, `a`.`user_id`, `a`.`user_level` from `tb_forum_answers` `a` ) `x` left outer join `login_names` `l` on `l`.`id` = `x`.`user_id` and `l`.`level` = `x`.`user_level` group by `x`.`forum_id` asc USING EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 530415 4 DERIVED n1 ALL NULL NULL NULL NULL 114 5 UNION n2 ALL NULL NULL NULL NULL 2 6 UNION n3 ALL NULL NULL NULL NULL 530299 NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2 DERIVED t ALL NULL NULL NULL NULL 3 3 UNION r ALL NULL NULL NULL NULL 3 NULL UNION RESULT ALL NULL NULL NULL NULL NULL Somebody can help me or give a suggestion?

    Read the article

  • CTE to build a list of departments and managers (hierarchical)

    - by Milky Joe
    I need to generate a list of users that are managers, or managers of managers, for company departments. I have two tables; one details the departments and one contains the manager hierarchy (simplified): CREATE TABLE [dbo].[Manager]( [ManagerId] [int], [ParentManagerId] [int]) CREATE TABLE [dbo].[Department]( [DepartmentId] [int], [ManagerId] [int]) Basically, I'm trying to build a CTE that will give me a list of DepartmentIds, together with all ManagerIds that are in the manager hierarchy for that department. So... Say Manager 1 is the Manager for Department 1, and Manager 2 is Manager 1's Manager, and Manager 3 is Manager 2's Manager, I'd like to see: DepartmentId, ManagerId 1, 1 1, 2 1, 3 Basically, managers are able to deal with all of their sub-manager's departments. Building the CTE to return the Manager hierarchy was fairly simple, but I'm struggling to inject the Departments in there: WITH DepartmentManagers AS ( SELECT ManagerId, ParentManagerId, 0 AS Depth From Manager UNION ALL SELECT Manager.ManagerId, Manager.ParentManagerId, DepartmentManagers.Depth + 1 AS Depth FROM Manager INNER JOIN DepartmentManagers ON DepartmentManagers.ManagerId = Manager.ParentManagerId ) Can anyone help?

    Read the article

  • Oracle (Old?) Joins

    - by Grasper
    I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id(+); Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)? When was this standard phased out? Any info is appreciated.

    Read the article

  • MySQL Delete from 1 table, using multiple tables

    - by nute
    I would like to delete all the rows found by that query: SELECT cart_abandon.* FROM cart_abandon, cart_product, txn_product, users WHERE cart_abandon.cartid = cart_product.cartid AND cart_product.productid = txn_product.productid AND txn_product.username = users.username AND users.id = cart_abandon.userid AND txn_product.txndate >= cart_abandon.abandondate The thing to keep in mind is that the query here uses 4 different tables, however I only want to delete rows from 1 table (cart_abandon). Is there an easy way to do that? Maybe this: ? DELETE cart_abandon FROM cart_abandon, cart_product, txn_product, users WHERE cart_abandon.cartid = cart_product.cartid AND cart_product.productid = txn_product.productid AND txn_product.username = users.username AND users.id = cart_abandon.userid AND txn_product.txndate >= cart_abandon.abandondate Is that valid? Correct?

    Read the article

  • How to update a table with a list of values at a time?

    - by VJ
    I have update NewLeaderBoards set MonthlyRank=(Select RowNumber() from LeaderBoards) I tried it this way - (Select RowNumber() from LeaderBoards) as NewRanks update NewLeaderBoards set MonthlyRank = NewRanks But it doesnt work for me..Can anyone suggest me how can i perform an update in such a way..

    Read the article

  • Auto increment with a Unit Of Work

    - by Derick
    Context I'm building a persistence layer to abstract different types of databases that I'll be needing. On the relational part I have mySQL, Oracle and PostgreSQL. Let's take the following simplified MySQL tables: CREATE TABLE Contact ( ID varchar(15), NAME varchar(30) ); CREATE TABLE Address ( ID varchar(15), CONTACT_ID varchar(15), NAME varchar(50) ); I use code to generate system specific alpha numeric unique ID's fitting 15 chars in this case. Thus, if I insert a Contact record with it's Addresses I have my generated Contact.ID and Address.CONTACT_IDs before committing. I've created a Unit of Work (amongst others) as per Martin Fowler's patterns to add transaction support. I'm using a key based Identity Map in the UoW to track the changed records in memory. It works like a charm for the scenario above, all pretty standard stuff so far. The question scenario comes in when I have a database that is not under my control and the ID fields are auto-increment (or in Oracle sequences). In this case I do not have the db generated Contact.ID beforehand, so when I create my Address I do not have a value for Address.CONTACT_ID. The transaction has not been started on the DB session since all is kept in the Identity Map in memory. Question: What is a good approach to address this? (Avoiding unnecessary db round trips) Some ideas: Retrieve the last ID: I can do a call to the database to retrieve the last Id like: SELECT Auto_increment FROM information_schema.tables WHERE table_name='Contact'; But this is MySQL specific and probably something similar can be done for the other databases. If do this then would need to do the 1st insert, get the ID and then update the children (Address.CONTACT_IDs) – all in the current transaction context.

    Read the article

  • How can I do more than one level of cascading deletes in Linq?

    - by Gary McGill
    If I have a Customers table linked to an Orders table, and I want to delete a customer and its corresponding orders, then I can do: dataContext.Orders.DeleteAllOnSubmit(customer.Orders); dataContext.Customers.DeleteOnSubmit(customer); ...which is great. However, what if I also have an OrderItems table, and I want to delete the order items for each of the orders deleted? I can see how I could use DeleteAllOnSubmit to cause the deletion of all the order items for a single order, but how can I do it for all the orders?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 630 631 632 633 634 635 636 637 638 639 640 641  | Next Page >