[how to] Can SQL Server Access (Grant/Revoke/Restore) be categorized under DB Backup and Recovery?

---

• Can SQL Server Access (Grant/Revoke/Restore) be categorized under DB Backup and Recovery?
• Stored function checking Booking availability
• SELECT lock only for inserts
• Movie Database, storing multiple genres
• Website is working very slowly
• how to configure MySQL for an application that does all read result buffering? [on hold]
• Greyed out menu options, broken activity monitor
• How to represent it with UNIQUE indexes and foreign keys?
• Data not showing in English, unless you click on it - In sqldeveloper
• Using inner join to get data from multiple tables [on hold]
• Unable to call function [on hold]
• Streaming replication setup, sane setup?
• any documentation on tpcc-mysql benchmark tool
• Splitting a table based on enum value: One, Two or Three Tables?
• Mongo authentication user can't drop database
• SQL Server 2012 Management Studio Books Online - Help
• This sql select must be optimized...how?
• How to run a SELECT query within while loop in PHP?
• Delete from table where multiple fields match select subquery from other table
• MySql one time event never runs?
• -E startup option and SSDs
• ORA-16000 when trying to perform select on read only Oracle database
• Many to Many and Weak Entities
• How to run a cold backup with Linux/tar without shutting down MySQL slave?
• Can I use SQL Server Management Studio 2012 on a 2008 server?
• Run Unix command using PL/SQL

Can SQL Server Access (Grant/Revoke/Restore) be categorized under DB Backup and Recovery? Posted: 04 Jul 2013 07:30 PM PDT Lately I've been performing a lot of backup/recovery for databases from one server to another. Aside from that, I restore the access of sanctioned logins/accounts more than half the times I perform a restore. There are times when I need to restore a database back into a development box as well as grant approved access to a new developer. So when I need to categorize the things I do, is it okay to put access granting/revoking/recovering under "Database Backup/Restore" ? Can't I place it under when I perform a restoration of a database?

Stored function checking Booking availability Posted: 04 Jul 2013 06:05 PM PDT Trying to create a boolean function that checks booking availability. Lets say BOOKING table has attribute BOOK_Start and BOOK_End that are of date type. I want to create a function that receives 2 date parameters and tells if this time frame is available.

SELECT lock only for inserts Posted: 04 Jul 2013 06:29 PM PDT I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the table from which I'm getting the MAX. I researched the isolation modes and lock hints available. The closest seems to be TABLOCKX, which is the only one that avoids inserts in the entire table (also updates and selects). Since I no will be affected by updates or selects in the table I wish to avoid only inserts during the transaction. This is possible?

Movie Database, storing multiple genres Posted: 04 Jul 2013 04:58 PM PDT I'm trying to build a database that will store information on movies. Title Plot Genre Rating Director The only think that is bothering me is that most films don't just have one genre and I'm struggling to figure out how to store this on a MySQL Database. At first I was thinking that I'll just have one table and store all the genres in one column, separating them by a comma and when I want to retrieve them separate them using PHP, but I'm not sure this is the best way as I think I would have trouble sorting and searching for a specific genre e.g. Horror when the collumn contains 'Horror, Thriller, Action'.

Website is working very slowly Posted: 04 Jul 2013 09:01 PM PDT We have replication from Oracle to MySQL via Oracle Golden Gate. Yesterday because of some issues we have rebuilt the replication from Oracle to MySQL (this is called opal replication). Now our website is very slow Website load time is really slow today. 10.72 sec vs 6.4 sec last week". We didn't find any replication issues and there is no lagging. We didn't find the the actual issue causing website load time to be really slow. Could anyone please help me?

how to configure MySQL for an application that does all read result buffering? [on hold] Posted: 04 Jul 2013 08:41 PM PDT The database and css mystify me. I'm more comfortable with the middle, server and client side code, so please bear with me. Also, I'm still a server management novice. My understanding of "buffer" and "cache" is probably different from MySQL's usage, so I think I'll probably misuse them. I'm writing a websocket++ server on ubuntu and am using the application's persistent memory to buffer read data such as custom std::vectors of PRIMARY keys for rapid dynamic single-webpage pagination over entire tables, like stack's question ranking criteria such as votes, newest, frequent, etc, and the most frequently viewed content by connected users. I'd like to limit the database from storing client results in memory while allowing it to use whatever memory's necessary for high performance reading from & writing to disk. All the flags I've found restrict both without distinction. I'd also like to restrict the amount of memory allocated to the amount it uses, to prevent the websocket++ server from using the page file for memory while RAM is unused yet inacessible. All the flags I've found restrict total memory usage and preallocation both without restriction. What settings are necessary to achieve this?

Greyed out menu options, broken activity monitor Posted: 04 Jul 2013 12:31 PM PDT I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself. I no longer see the green "running" icons on the server and on the agent, and several menu options are greyed out (Start/Stop/Restart on server menu and agent menu). Also, when I try to access the Activity Monitor, I get the following error: Error Detail I found the following article about the Activity Monitor. I followed the steps there (although I am a local admin on the sql server and should have had all of those permissions already) to no avail. The server is running Windows 2008 R2 Datacenter SP1. When I remote desktop to the server itself and use SSMS directly I have none of these issues, and when a domain admin connects to the server through SSMS remotely everything also works for him. Any ideas?

How to represent it with UNIQUE indexes and foreign keys? Posted: 04 Jul 2013 01:42 PM PDT "Group" is a group of pupils. "Topic" is a topic of lessons. To every group corresponds exactly one topic. (So there may be multiple groups per topic.) To a pupil corresponds multiple groups, but maximum one group per topic. How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?

Data not showing in English, unless you click on it - In sqldeveloper Posted: 04 Jul 2013 09:22 AM PDT I have installed OracleXE and imported data into it. From SQLPLUS, I can query the data and it all shows up in English. However, in SQLDEVELOPER, if I look at the data, it's in a nonsense format, unless I double click on it, where it turns into english? If I query the data through the SQL Worksheet, it displays in rubbish format, but again click on a data item, and it turns into English again? I am utterly confused. Any ideas?

Using inner join to get data from multiple tables [on hold] Posted: 04 Jul 2013 09:22 AM PDT I have 5 tables: A(a_id, a1, a2); B(b_id, b1, b2, a_id); C(c_id, c1, c2, b_id); D(d_id, d1, b_id); E(e_id, e1, e2, d_id); table b have a_id match with table a(a_id). table c have b_id match with table b(b_id). table d have b_id match with table b(b_id). table e have d_id match with table d(d_id). Now, i want to use inner join to get data from those tables. please help me!

Unable to call function [on hold] Posted: 04 Jul 2013 08:58 AM PDT I've written this function: CREATE FUNCTION check_if_if_exist( id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$ BEGIN RETURN EXECUTE 'SELECT EXISTS(SELECT * FROM table_name WHERE table_column = id)'; END; $$ LANGUAGE plpgsql; but I'm not able to call it (I obtain always an error). How can I call it correctly?

Streaming replication setup, sane setup? Posted: 04 Jul 2013 08:51 AM PDT Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to hear the response from people when faced with my exact configuration. I want to utilize streaming replication, which I have a working setup for. I have turned off WAL archiving, since I believe that wal_keep_segments will contain enough backlog to get a slave up to date if necessary (700, you might think this high, I just calculated how much "buffer" I could spare for replication backlog, please tell me if this is the wrong approach). I have the same configuration on master and slave, which includes the following: wal_level = hot_standby hot_standby = on #(this is ignored on master, as I understand it?) Now, the main question is the first entry here, I could theoretically set this to minimal, correct? However, I'm thinking for ease of failover, I should keep it this way? Finally, I never specify restore_command in my recovery.conf, because as I have understood it, this is for log shipping (when rsyncing the WAL files between the two nodes or similar), or if I were to bring back a slave node from a long disconnect and archive_mode was on. 12 GB of WAL segments should be good for atleast a week (unless I do some big restore of a DB), and I assume that all segments that are in pg_xlog will be streamed to the slave upon reconnect if it is behind? And finally, what would be a good way to surveillance that the replication works as it should? I know about repmgr, but it would be nice to be aware of how the surveillance would consider the slave to be out of date.

any documentation on tpcc-mysql benchmark tool Posted: 04 Jul 2013 11:05 AM PDT Where can I find tpcc-mysql documentation? I google it but I can't find any documentation about it.

Splitting a table based on enum value: One, Two or Three Tables? Posted: 04 Jul 2013 07:05 PM PDT I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in rare cases, clients can revert to being prospects again (usually correction of a user error). Currently there are no suppliers who are clients, but theoretically there's nothing to stop that from happening in the future. As you can see, several fields are only needed in one case or another. CREATE TABLE `companies` ( /* book-keeping fields every data table should have */ `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `created` TIMESTAMP NULL DEFAULT NULL, /* would love to specify DEFAULT CURRENT_TIMESTAMP here but MySQL won't let me */ `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, /* fields for all company types */ `type` ENUM('prospect','client','supplier') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'prospect', `name` VARCHAR(25) DEFAULT NULL, `abbr_name` VARCHAR(8) DEFAULT NULL, `formal_name` VARCHAR(255) DEFAULT NULL, `web_site` VARCHAR(255) DEFAULT NULL, `email` VARCHAR(320) DEFAULT NULL, `logo` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `key` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false', `bust` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false', `head_office` INT UNSIGNED DEFAULT NULL, /* only used for clients */ `accounting_code` VARCHAR(5) CHARACTER SET ascii DEFAULT NULL, /* used for prospects, and clients obtained by marketing */ `marketing_user` INT UNSIGNED DEFAULT NULL, `marketing_datasource` CHAR(2) CHARACTER SET ascii DEFAULT NULL, /* only used for prospects */ `marketing_dead` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false', /* only used for suppliers */ `supplies` SET('materials','plant') CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `account_holding_branch` INT UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`head_office`) REFERENCES sites (`id`), FOREIGN KEY (`marketing_user`) REFERENCES users (`id`) FOREIGN KEY (`account_holding_branch`) REFERENCES sites (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What do people recommend, and importantly, why? A similar question, Splitting a table based on business logic provides one curt answer with two relevant reasons to help decide whether splitting is worth it: Partition hot and cold data for cache efficiency Many columns almost always null As to data 'temperature', key clients and key suppliers are hotter than other clients or suppliers, and hotter than prospects. Any company which has gone bust or for which the marketing trail has died are pretty frigid. As to NULL, it varies, but prospects almost always do not have a logo uploaded, and suppliers presently do not have data in any of the four accounting_code and marketing_* fields, though as I say there's no technical reason why a parts supplier could not also become a client of our business.

Mongo authentication user can't drop database Posted: 04 Jul 2013 12:52 PM PDT my question is - is it possible to define user in the Mongo database who will be able to drop the database? Maximum user privilege is "dbAdmin" but that privilege doesn't allow users to drop the database, only collections can be dropped. I know that "clusterAdmin" has rights to drop the database, but that role can't be defined in regular database, only in admin database...

SQL Server 2012 Management Studio Books Online - Help Posted: 04 Jul 2013 08:05 AM PDT When I use F1 in my SQL Server 2012 Management Studio, it opens Microsoft Help Viewer 1.0 but there is no help content. Adding content is not possible due to the fact that install online and install offline both are grayed out. When I download the Books Online the file is > 200 MB. When I open VS2012 I have in the help section a chapter SQL Server 2012, which is 40 MB. This brings me to these questions: Why is SQL Server 2012 with still using Help Viewer 1.0 per default (and not the latest on the machine installed version, which would be 2.0) Why I'm not able to add content to this Help Viewer 1.0 Why is the SQL Server 2012 Help Content in Help Viewer 2.0 40 MB and the downloaded Books Online are 200 MB ?

This sql select must be optimized...how? Posted: 04 Jul 2013 03:29 PM PDT Here is my query SELECT DISTINCT SM.sales_date, (SELECT SUM(gross_sale) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS, (SELECT SUM(net_sale) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET, (SELECT SUM(cash) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCASH, (SELECT SUM(cheque) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCHEQUE, (SELECT SUM(credit) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCREDIT, (SELECT SUM((SELECT amount FROM sales_main_deduction AS INTERNAL WHERE INTERNAL.instance = '4%' AND INTERNAL.idsales_main = SMinternal.idsales_main)) FROM sales_main AS SMinternal WHERE SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '4%' , (SELECT SUM((SELECT amount FROM sales_main_deduction AS INTERNAL WHERE INTERNAL.instance = '6%' AND INTERNAL.idsales_main = SMinternal.idsales_main)) FROM sales_main AS SMinternal WHERE SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '6%', (SELECT SUM((SELECT amount FROM sales_main_deduction AS INTERNAL WHERE INTERNAL.instance = '10%' AND INTERNAL.idsales_main = SMinternal.idsales_main)) FROM sales_main AS SMinternal WHERE SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '10%', (SELECT SUM((SELECT amount FROM sales_main_deduction AS INTERNAL WHERE INTERNAL.instance = 'M/R' AND INTERNAL.idsales_main = SMinternal.idsales_main)) FROM sales_main AS SMinternal WHERE SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'M/R', (SELECT SUM((SELECT amount FROM sales_main_deduction AS INTERNAL WHERE INTERNAL.instance = 'S/R' AND INTERNAL.idsales_main = SMinternal.idsales_main)) FROM sales_main AS SMinternal WHERE SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'S/R' FROM sales_main AS SM WHERE (SM.sales_date >= '2013-06-01' AND SM.sales_date <= '2013-06-05') AND SM.vehicle_name='Lorry1' and this is the result.. 2013-06-01 59582.03 58336.59 32096.96 11141.02 15098.59 1245.44 2013-06-03 132334.45 126461.84 33138.93 11046.63 82276.28 1774.46 557.02 3541.13 2013-06-04 72184.69 71882.93 27840.74 12410.16 31632.03 152.16 149.6 2013-06-05 93478.20 86721.32 34461.47 24971.24 27288.6 934.48 3553.32 2269.08 But this if this queryextends to 31 days of month it takes 10 mins to return a results from 2000 rows table ....please help..

How to run a SELECT query within while loop in PHP? Posted: 04 Jul 2013 04:47 PM PDT Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == 'X' ) { $result2 = $mysqli->query("SELECT * FROM table2"); ..... } } but this does not work. I cannot JOIN the tables, as the if statement is complicated to perform SELECT from different tables.

Delete from table where multiple fields match select subquery from other table Posted: 04 Jul 2013 09:47 AM PDT I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: DELETE FROM table1 WHERE table1.id IN (SELECT id FROM table1 a JOIN table2 b ON a.field1 = b.field1 AND a.field2 = b.field2 AND a.field3 = b.field3 AND b.id = ? )

MySql one time event never runs? Posted: 04 Jul 2013 12:47 PM PDT Please have a look at below events 1) create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ... 2) create EVENT Test2 ON SCHEDULE EVERY 20 SECOND STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO ... I expect event Test1 to run one time after 20 seconds but it never runs. Event Test2 is working fine. Any idea? Thanks. Ok sorry it is the alter that is not working At first i did create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO then shortly after i did alter EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO Expect event Test1 to run again in another 20 secs but it didn't.

-E startup option and SSDs Posted: 04 Jul 2013 04:23 PM PDT Has anybody seen evidence of the effect of -E when using SSDs? The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might even hurt performance. On servers with a mix of drives (SSD SAN, PCI SSDs and traditional SAN) SQL Server has to decide on startup whether to use -E or not. I have some empirical evidence that the option might be detrimental to performance, but I'd like other people's feedback before I consider taking it off. My setup uses standard 64K RAID stripes, and the NTFS cluster size is also 64K.

ORA-16000 when trying to perform select on read only Oracle database Posted: 04 Jul 2013 08:46 PM PDT My application's SQL encounters an ORA-16000 error when trying to access a read only Oracle database. ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 ### The error may exist in com/xyz/report/dao/Transaction001Mapper.xml This is the query that involves the XMLTYPE, the INTERFACE_CONTENT is a CLOB column: SELECT CONTENTS FROM ERRCODES WHERE CODE=(SELECT xmltype(INTERFACE_CONTENT).extract('/Message/PaymentStatus/ps:FIToFIPmtStsRpt/ps:TxInfAndSts/ps:StsRsnInf/ps:Rsn/ps:Prtry/text()','xmlns="urn:abcde" xmlns:head="urn:iso:std:iso:20022:tech:xsd:head.001.001.01" xmlns:ps="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"').getstringval() APP_CODE FROM MESSAGE_EXTERNAL_INTERACTION MEI WHERE MEI.MSG_TYPE='Pacs_002' AND MEI.MID='MY_ID') I also did a lot of EXTRACTVALUE() method on an XML field type. The SQL is working perfectly if the database is not read only (read write). What is the issue here? Is this related to some missing privileges/grants?

Many to Many and Weak Entities Posted: 04 Jul 2013 02:00 PM PDT I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many to many relationship. Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums. So we have a many to many relationship between the album and the tracks. If the album is also a weak entity, then its primary key, is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing an m to m relationship. The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?

How to run a cold backup with Linux/tar without shutting down MySQL slave? Posted: 04 Jul 2013 03:47 PM PDT I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are updated during the process. What am I missing? The slave machine is in a cold standby machine so there are no client processes running while tar is running. CentOS release 5.6 64bits, MySQL 5.1.49-log source distribution.

Can I use SQL Server Management Studio 2012 on a 2008 server? Posted: 04 Jul 2013 03:21 PM PDT My school is using SQL Server 2008. Last time I used it I only installed (at home) SQL Server Management Studio 2008 part, and used VPN to connect to school's server. Now I want to install SQL Server Management Studio again, but this time the 2012 version is available. Will SQL Server Management Studio 2012 work on the school's SQL Server 2008? Or do I need the 2008 version?

Run Unix command using PL/SQL Posted: 04 Jul 2013 08:47 AM PDT Is it possible to run a Unix command using a query in Oracle? I want to run simple commands (like df -h) using a query. Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610