Search Results

Search found 27905 results on 1117 pages for 'sql authority'.

Page 582/1117 | < Previous Page | 578 579 580 581 582 583 584 585 586 587 588 589  | Next Page >

• Optimizing Oracle query

  - by Omnipresent

  SELECT MAX(verification_id) FROM VERIFICATION_TABLE WHERE head = 687422 AND mbr = 23102 AND RTRIM(LTRIM(lname)) = '.iq bzw' AND TO_CHAR(dob,'MM/DD/YYYY')= '08/10/2004' AND system_code = 'M'; This query is taking 153 seconds to run. there are millions of rows in VERIFICATION_TABLE. I think query is taking long because of the functions in where clause. However, I need to do ltrim rtrim on the columns and also date has to be matched in MM/DD/YYYY format. How can I optimize this query?

  Read the article

• Do connection string DNS lookups get cached?

  - by joshcomley

  Suppose the following: I have a database set up on database.mywebsite.com, which resolves to IP 111.111.1.1, running from a local DNS server on our network. I have countless ASP, ASP.NET and WinForms applications that use a connection string utilising database.mywebsite.com as the server name, all running from the internal network. Then the box running the database dies, and I switch over to a new box with an IP of 222.222.2.2. So, I update the DNS for database.mywebsite.com to point to 222.222.2.2. Will all the applications and computers running them have cached the old resolved IP address? I'm assuming they will have. Any suggestions along the lines of "don't have your IP change each time you switch box" are not too welcome as I cannot control this aspect of the situation, unfortunately. We are currently using the machine name of the box, which changes every time it dies and all apps etc. have to be updated with the new machine name. It hurts.

  Read the article

• Optimize this MySQL query?

  - by HipHop-opatamus

  The following query takes FOREVER to execute (30+ hrs on a Macbook w/4gig ram) - I'm looking for ways to make it run more efficiently. Any thoughts are appreciated! CREATE TABLE fc AS SELECT threadid, title, body, date, userlogin FROM f WHERE pid NOT IN (SELECT pid FROM ft) ORDER BY date; (table "f" is ~1 Gig / 1,843,000 row, table "ft" is 168mb, 216,000 rows) )

  Read the article

• How to use sum and count functions

  - by Odette

  hi guys I am trying to get the total count and total sum per salesperson but its not working in my WITH query Currently I have: WITH CALC1 AS (SELECT B.OTSMAN AS SALESMAN, COUNT(B.ONINUM) AS COUNT, SUM(B.OVSVAL) AS SVAL FROM @[email protected] AS B WHERE B.OCSGRP <> 'INT' AND B.OCSGRP <> 'INX' AND B.OCPSBR IN (@OCPSBR@) AND B.ICMATX <> '705' AND B.OCSGRP NOT LIKE 'S/%' GROUP BY B.OTSMAN, B.ONINUM ORDER BY B.OTSMAN ASC) SELECT SALESMAN, COUNT, SVAL FROM CALC1 ORDER BY SALESMAN THE RESULT IS: AM 1 79.03 AM 40 1337.70 AM 48 4627.15 AM 42 2727.40 AM 1 111.79 AM 1 1110.00 AM 52 3018.77 How do i change my query so I only get one record with the total count and value per salesperson? AM 1 13,011.84

  Read the article

• How to dynamically choose two fields from a Linq query as a result

  - by Dr. Zim

  If you have a simple Linq query like: var result = from record in db.Customer select new { Text = record.Name, Value = record.ID.ToString() }; which is returning an object that can be mapped to a Drop Down List, is it possible to dynamically specify which fields map to Text and Value? Of course, you could do a big case (switch) statement, then code each Linq query separately but this isn't very elegant. What would be nice would be something like: (pseudo code) var myTextField = db.Customer["Name"]; // Could be an enumeration?? var myValueField = db.Customer["ID"]; // Idea: choose the field outside the query var result = from record in db.Customer select new { Text = myTextField, Value = myValueField };

  Read the article

• Entity Framework Many-To-Many with additional field on Joining Table

  - by Cory G

  I have an entity context that includes three tables (see diagram here). The first is a table that contain products, the second contains recipes. The joining table has fields for IDs in both the products and recipes table as well as a 'bit' field called 'featured'. I've searched and found no example on how to insert only how to select against this type of scenario.Does anyone have any suggestions on how this can be done? Thanks in advance for any help. Cory

  Read the article

• SQL using with clause not working

  - by user1290467

  My question is why this query does not work? Cursor c = db.rawQuery("SELECT * FROM tbl_staff WHERE PMajor = '%" + spin.getSelectedItem().toString() + "%'", null); Cursor c: it is a cursor for handling my query tbl_staff: my table that consist of PName,PMajor,PCert spin: is spinner that has values which I need for my database query. When I use: if (c.moveToNext()) else (log.d("error query","couldn't do the query!");) It goes to else statement and moveToNext() doesn't work.

  Read the article

• How to Set Customer Table with Multiple Phone Numbers? - Relational Database Design

  - by user311509

  CREATE TABLE Phone ( phoneID - PK . . . ); CREATE TABLE PhoneDetail ( phoneDetailID - PK phoneID - FK points to Phone phoneTypeID ... phoneNumber ... . . . ); CREATE TABLE Customer ( customerID - PK firstName phoneID - Unique FK points to Phone . . . ); A customer can have multiple phone numbers e.g. Cell, Work, etc. phoneID in Customer table is unique and points to PhoneID in Phone table. If customer record is deleted, phoneID in Phone table should also be deleted. Do you have any concerns on my design? Is this designed properly? My problem is phoneID in Customer table is a child and if child record is deleted then i can not delete the parent (Phone) record automatically.

  Read the article

• How to create breadcrumbs using Dynamic Data/LINQTOSQL framework?

  - by Chicago

  Hello, I need some EXAMPLES or IDEAS on how to created dynamic breadcrumb control which will dynamically generated breadcrumbs for Dynamic Date framework powered by LINQTOSQL

  Read the article

• Inserting "null" (literally) in to a stored procedure parameter.

  - by Nazadus

  I'm trying to insert the word "Null" (literally) in to a parameter for a stored procedure. For some reason SqlServer seems to think I mean NULL and not "Null". If I do a check for IF @LastName IS NULL // Test: Do stuff Then it bypasses that because the parameter isn't null. But when I do: INSERT INTO Person (<params>) VALUES (<stuff here>, @LastName, <more stuff here>); // LastName is 'Null' It bombs out saying that LastName doesn't accept nulls. I would seriously hate to have this last name, but someone does... and it's bombing the application. We're using SubSonic 2.0 (yeah, it's fairly old but upgrading is painful) as our DAL and stepping through it, I see it does create the parameters properly (for what I can tell). I've tried creating a temp table to see if I could replicate it manually but it seems to work just fine. Here is the example I create: DECLARE @myval VARCHAR(50) SET @myval = 'Null' CREATE TABLE #mytable( name VARCHAR(50)) INSERT INTO #mytable VALUES (@myval) SELECT * FROM #mytable DROP table #mytable Any thoughts on how I can fix this?

  Read the article

• Multiple rows with a single INSERT in SQLServer 2008

  - by Todd

  I am testing the speed of inserting multiple rows with a single INSERT statement. For example: INSERT INTO [MyTable] VALUES (5, 'dog'), (6, 'cat'), (3, 'fish) This is very fast until I pass 50 rows on a single statement, then the speed drops significantly. Inserting 10000 rows with batches of 50 take 0.9 seconds. Inserting 10000 rows with batches of 51 take 5.7 seconds. My question has two parts: Why is there such a hard performance drop at 50? Can I rely on this behavior and code my application to never send batches larger than 50? My tests were done in c++ and ADO.

  Read the article

• Storing a SHA512 Password Hash in Database

  - by Chris

  In my ASP.NET web app I'm hashing my user passwords with SHA512. Despite much SO'ing and Googling I'm unclear how I should be storing them in the database (SQL2005) - the code below shows the basics of how I'm creating the hash as a string and I'm currently inserting it into the database into a Char(88) column as that seems to be the length created consistently Is holding it as a String the best way to do it, if so will it always be 88 chars on a SHA512 (as I have seen some bizarre stuff on Google)? Dim byteInput As Byte() = Encoding.UTF8.GetBytes(sSalt & sInput) Dim hash As HashAlgorithm = New SHA512Managed() Dim sInsertToDatabase As String = Convert.ToBase64String(hash.ComputeHash(byteInput))

  Read the article

• excel:mysql: rs.Update crashes

  - by every_answer_gets_a_point

  i am connecting to mysql from excel and updating a table. as soon as i get to .update (rs.update) excel crashes. am i doing something wrong? Option Explicit Dim oConn As ADODB.Connection Private Sub ConnectDB() Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=pas;" & _ "Option=3" End Sub Function esc(txt As String) esc = Trim(Replace(txt, "'", "\'")) End Function Private Sub InsertData() Dim dpath, atime, rtime, lcalib, aname, rname, bstate, instrument As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ConnectDB With wsBooks rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable Worksheets.Item("Report 1").Select dpath = Trim(Range("B2").Text) atime = Trim(Range("B3").Text) rtime = Trim(Range("B4").Text) lcalib = Trim(Range("B5").Text) aname = Trim(Range("B6").Text) rname = Trim(Range("B7").Text) bstate = Trim(Range("B8").Text) ' instrument = GetInstrFromXML(wbBook.FullName) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = bstate ' .Fields("instrument") = "NA" .Update ' stores the new record End With ' get the last id Set rs = oConn.Execute("SELECT @@identity", , adCmdText) 'MsgBox capture_id rs.Close Set rs = Nothing End With End Sub

  Read the article

• 2 large databases - worth merging into 1?

  - by Ardman

  I have 2 large databases that were sharded before. I now have removed the sharding and have created a new database with all of the data except for the tables that were originally sharded. Is it worth importing this data into the new database, or keeping them as seperate entities that I can just scan through? We are talking around 60million records in each sharded table, of which there are 2 tables. Also, whilst I have an empty table, should I be adding indexes which weren't thought of when the database was originally constructed and now too large to add them?

  Read the article

• Open Connection to SQL Compact File, with other Extension than .sdf

  - by Javier

  I just can't open this connection, if the Extension is different than .sdf. It's there a workaround (Please do not suggest, changing the extension of the File temporarily) ? Thnk u very much. Javier

  Read the article

• Explanation of converting exporting an XML document as a relational database using XSLT

  - by Yaaqov

  I would like to better understand the basic steps needed to a take an XML document like this Breakfast Menu... <?xml version="1.0" encoding="ISO-8859-1"?> <breakfast_menu> <food> <name>Belgian Waffles</name> <price>$5.95</price> <description>two of our famous Belgian Waffles with plenty of real maple syrup</description> <calories>650</calories> </food> <food> <name>Strawberry Belgian Waffles</name> <price>$7.95</price> <description>light Belgian waffles covered with strawberries and whipped cream</description> <calories>900</calories> </food> <food> <name>Berry-Berry Belgian Waffles</name> <price>$8.95</price> <description>light Belgian waffles covered with an assortment of fresh berries and whipped cream</description> <calories>900</calories> </food> <food> <name>French Toast</name> <price>$4.50</price> <description>thick slices made from our homemade sourdough bread</description> <calories>600</calories> </food> <food> <name>Homestyle Breakfast</name> <price>$6.95</price> <description>two eggs, bacon or sausage, toast, and our ever-popular hash browns</description> <calories>950</calories> </food> </breakfast_menu> And "export" it to say, an Access or MySQL database using XSLT, creating two joined tables: Table: breakfast_menu Field: menu_item_id Field: food_id Table: food Field: food_id Field: name Field: price Field: description Field: calories If there are online tutorials on this that you know of, I'd be interesting in learning more, as well. Thanks.

  Read the article

• How do You Get a Specific Value From a System.Data.DataTable Object?

  - by Giffyguy

  I'm a low-level algorithm programmer, and databases are not really my thing - so this'll be a n00b question if ever there was one. I'm running a simple SELECT query through our development team's DAO. The DAO returns a System.Data.DataTable object containing the results of the query. This is all working fine so far. The problem I have run into now: I need to pull a value out of one of the fields of the first row in the resulting DataTable - and I have no idea where to even start. Microsoft is so confusing about this! Arrrg! Any advice would be appreciated. I'm not providing any code samples, because I believe that context is unnecessary here. I'm assuming that all DataTable objects work the same way, no matter how you run your queries - and therefore any additional information would just make this more confusing for everyone.

  Read the article

• Use SQL Result to Query second time and grab more results via php

  - by maxwell

  I am grabbing a list of school names from a database. When the user clicks on the school name, i want the code to fetch 2-3 other attributes related to the school name that the user has clicked on. My code for the single school name list: $query = mysql_query("Select schoolname, product, username, password from credentials c join products p on p.productid = c.productid join schools s on s.schoolid = c.schoolid join icons i on i.productid = p.productid order by s.schoolname"); echo '<ul>'; while($row = mysql_fetch_array($query)) { echo "<li> <a href='#'>" . $row['schoolname'] . "</a> </li>"; } echo ''; Current Output: School A School A School B School B School B School C School C Expected Output: School name displayed only once (distinct won't work because each school name has 2-3 product credentials School A School B School C Ability for User to click on a school. then the school names underneath will move down (toggle effect) and user can see product, username and password displayed for the school clicked (for example: user clicked on School A) School A productname1, username, password productname2, username, password School B School C

  Read the article

• How to ignore blank elements in linq query

  - by Maestro1024

  How to ignore blank elements in linq query I have a linq query var usersInDatabase = from user in licenseUserTable where user.FirstName == first_name && user.LastName == last_name select user; But if I get here and first_name or last_name is blank then I want to still evaluate the other data item.

  Read the article

• Making a DateTime field in a database automatic?

  - by Mike

  I'm putting together a simple test database to learn MVC with. I want to add a DateTime field to show when the record was CREATED. ID = int Name = Char DateCreated = (dateTime, DateTime2..?) I have a feeling that this type of DateTime capture can be done automatically - but that's all I have, a feeling. Can it be done? And if so how? While we're on the subject: if I wanted to include another field that captured the DateTime of when the record was LAST UPDATED how would I do that. I'm hoping to not do this manually. Many thanks Mike

  Read the article

• Isn't INT more efficient than UNIQUEIDENTIFIER?

  - by ck

  I have a parent table and child table where the columns that join them together are the UNIQUEIDENTIFIER type. The child table has a clustered index on the column that joins it to the parent table (its PK, which is also clustered). I have created a copy of both of these tables but changed the relationship columns to be INTs instead, have rebuilt the indexes so that they are essentially the same structure and can be queried in the same way. When I query for a known 20 records from the parent table, pulling in all the related records from the child tables, I get identical query costs across both, i.e. 50/50 cost for the batches. If this is true, then my giant project to change all of the tables like this appears to be pointless, other than speeding up inserts. Can anyone provide any light on the situation?

  Read the article

• tsql getdate conversion

  - by Manjot

  Hi, I know that when i do the following, it converts getdate to int select cast (getdate() as int) Getdate output on my server is "2010-06-11 14:42:20.100" and the int to which the above command is converting to is 40339. What is this integer? Did this int consider minutes and seconds? i am confused. Please help. Regards Manjot

  Read the article

• Best Practices - Stored Procedure Logging

  - by hgulyan

  If you have a long running SP, do you log somehow it's actions or just wait for this message? "Command(s) completed successfully." I assume, that there can be plenty solutions on this subject, but is there any best practice - a simple solution that is frequently used? EDIT I've found an interesting link on this subject http://weblogs.sqlteam.com/brettk/archive/2006/09/21/12391.aspx Article describes using a log table, but there's an issue The logging procedure must be executed outside of any transaction I can't call that insert outside, because of cursor that I use and insert a line to that table on every row. Any ideas?

  Read the article

• Number Sequence in MySQL

  - by illuminatedtiger

  In Python if I wanted a sequence from 0 - 9 (inclusive) I would use xrange(0,10) . Is there a way I can do this in MySQL?

  Read the article

• Random Page Cost and Planning

  - by Dave Jarvis

  A query (see below) that extracts climate data from weather stations within a given radius of a city using the dates for which those weather stations actually have data. The query uses the table's only index, rather effectively: CREATE UNIQUE INDEX measurement_001_stc_idx ON climate.measurement_001 USING btree (station_id, taken, category_id); Reducing the server's configuration value for random_page_cost from 2.0 to 1.1 had a massive performance improvement for the given range (nearly an order of magnitude) because it suggested to PostgreSQL that it should use the index. While the results now return in 5 seconds (down from ~85 seconds), problematic lines remain. Bumping the query's end date by a single year causes a full table scan: sc.taken_start >= '1900-01-01'::date AND sc.taken_end <= '1997-12-31'::date AND How do I persuade PostgreSQL to use the indexes regardless of years between the two dates? (A full table scan against 43 million rows is probably not the best plan.) Find the EXPLAIN ANALYSE results below the query. Thank you! Query SELECT extract(YEAR FROM m.taken) AS year, avg(m.amount) AS amount FROM climate.city c, climate.station s, climate.station_category sc, climate.measurement m WHERE c.id = 5182 AND earth_distance( ll_to_earth(c.latitude_decimal,c.longitude_decimal), ll_to_earth(s.latitude_decimal,s.longitude_decimal)) / 1000 <= 30 AND s.elevation BETWEEN 0 AND 3000 AND s.applicable = TRUE AND sc.station_id = s.id AND sc.category_id = 1 AND sc.taken_start >= '1900-01-01'::date AND sc.taken_end <= '1996-12-31'::date AND m.station_id = s.id AND m.taken BETWEEN sc.taken_start AND sc.taken_end AND m.category_id = sc.category_id GROUP BY extract(YEAR FROM m.taken) ORDER BY extract(YEAR FROM m.taken) 1900 to 1996: Index "Sort (cost=1348597.71..1348598.21 rows=200 width=12) (actual time=2268.929..2268.935 rows=92 loops=1)" " Sort Key: (date_part('year'::text, (m.taken)::timestamp without time zone))" " Sort Method: quicksort Memory: 32kB" " -> HashAggregate (cost=1348586.56..1348590.06 rows=200 width=12) (actual time=2268.829..2268.886 rows=92 loops=1)" " -> Nested Loop (cost=0.00..1344864.01 rows=744510 width=12) (actual time=0.807..2084.206 rows=134893 loops=1)" " Join Filter: ((m.taken >= sc.taken_start) AND (m.taken <= sc.taken_end) AND (sc.station_id = m.station_id))" " -> Nested Loop (cost=0.00..12755.07 rows=1220 width=18) (actual time=0.502..521.937 rows=23 loops=1)" " Join Filter: ((sec_to_gc(cube_distance((ll_to_earth((c.latitude_decimal)::double precision, (c.longitude_decimal)::double precision))::cube, (ll_to_earth((s.latitude_decimal)::double precision, (s.longitude_decimal)::double precision))::cube)) / 1000::double precision) <= 30::double precision)" " -> Index Scan using city_pkey1 on city c (cost=0.00..2.47 rows=1 width=16) (actual time=0.014..0.015 rows=1 loops=1)" " Index Cond: (id = 5182)" " -> Nested Loop (cost=0.00..9907.73 rows=3659 width=34) (actual time=0.014..28.937 rows=3458 loops=1)" " -> Seq Scan on station_category sc (cost=0.00..970.20 rows=3659 width=14) (actual time=0.008..10.947 rows=3458 loops=1)" " Filter: ((taken_start >= '1900-01-01'::date) AND (taken_end <= '1996-12-31'::date) AND (category_id = 1))" " -> Index Scan using station_pkey1 on station s (cost=0.00..2.43 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=3458)" " Index Cond: (s.id = sc.station_id)" " Filter: (s.applicable AND (s.elevation >= 0) AND (s.elevation <= 3000))" " -> Append (cost=0.00..1072.27 rows=947 width=18) (actual time=6.996..63.199 rows=5865 loops=23)" " -> Seq Scan on measurement m (cost=0.00..25.00 rows=6 width=22) (actual time=0.000..0.000 rows=0 loops=23)" " Filter: (m.category_id = 1)" " -> Bitmap Heap Scan on measurement_001 m (cost=20.79..1047.27 rows=941 width=18) (actual time=6.995..62.390 rows=5865 loops=23)" " Recheck Cond: ((m.station_id = sc.station_id) AND (m.taken >= sc.taken_start) AND (m.taken <= sc.taken_end) AND (m.category_id = 1))" " -> Bitmap Index Scan on measurement_001_stc_idx (cost=0.00..20.55 rows=941 width=0) (actual time=5.775..5.775 rows=5865 loops=23)" " Index Cond: ((m.station_id = sc.station_id) AND (m.taken >= sc.taken_start) AND (m.taken <= sc.taken_end) AND (m.category_id = 1))" "Total runtime: 2269.264 ms" 1900 to 1997: Full Table Scan "Sort (cost=1370192.26..1370192.76 rows=200 width=12) (actual time=86165.797..86165.809 rows=94 loops=1)" " Sort Key: (date_part('year'::text, (m.taken)::timestamp without time zone))" " Sort Method: quicksort Memory: 32kB" " -> HashAggregate (cost=1370181.12..1370184.62 rows=200 width=12) (actual time=86165.654..86165.736 rows=94 loops=1)" " -> Hash Join (cost=4293.60..1366355.81 rows=765061 width=12) (actual time=534.786..85920.007 rows=139721 loops=1)" " Hash Cond: (m.station_id = sc.station_id)" " Join Filter: ((m.taken >= sc.taken_start) AND (m.taken <= sc.taken_end))" " -> Append (cost=0.00..867005.80 rows=43670150 width=18) (actual time=0.009..79202.329 rows=43670079 loops=1)" " -> Seq Scan on measurement m (cost=0.00..25.00 rows=6 width=22) (actual time=0.001..0.001 rows=0 loops=1)" " Filter: (category_id = 1)" " -> Seq Scan on measurement_001 m (cost=0.00..866980.80 rows=43670144 width=18) (actual time=0.008..73312.008 rows=43670079 loops=1)" " Filter: (category_id = 1)" " -> Hash (cost=4277.93..4277.93 rows=1253 width=18) (actual time=534.704..534.704 rows=25 loops=1)" " -> Nested Loop (cost=847.87..4277.93 rows=1253 width=18) (actual time=415.837..534.682 rows=25 loops=1)" " Join Filter: ((sec_to_gc(cube_distance((ll_to_earth((c.latitude_decimal)::double precision, (c.longitude_decimal)::double precision))::cube, (ll_to_earth((s.latitude_decimal)::double precision, (s.longitude_decimal)::double precision))::cube)) / 1000::double precision) <= 30::double precision)" " -> Index Scan using city_pkey1 on city c (cost=0.00..2.47 rows=1 width=16) (actual time=0.012..0.014 rows=1 loops=1)" " Index Cond: (id = 5182)" " -> Hash Join (cost=847.87..1352.07 rows=3760 width=34) (actual time=6.427..35.107 rows=3552 loops=1)" " Hash Cond: (s.id = sc.station_id)" " -> Seq Scan on station s (cost=0.00..367.25 rows=7948 width=20) (actual time=0.004..23.529 rows=7949 loops=1)" " Filter: (applicable AND (elevation >= 0) AND (elevation <= 3000))" " -> Hash (cost=800.87..800.87 rows=3760 width=14) (actual time=6.416..6.416 rows=3552 loops=1)" " -> Bitmap Heap Scan on station_category sc (cost=430.29..800.87 rows=3760 width=14) (actual time=2.316..5.353 rows=3552 loops=1)" " Recheck Cond: (category_id = 1)" " Filter: ((taken_start >= '1900-01-01'::date) AND (taken_end <= '1997-12-31'::date))" " -> Bitmap Index Scan on station_category_station_category_idx (cost=0.00..429.35 rows=6376 width=0) (actual time=2.268..2.268 rows=6339 loops=1)" " Index Cond: (category_id = 1)" "Total runtime: 86165.936 ms"

  Read the article

< Previous Page | 578 579 580 581 582 583 584 585 586 587 588 589  | Next Page >