Search Results

Search found 36756 results on 1471 pages for 'mysql real query'.

Page 301/1471 | < Previous Page | 297 298 299 300 301 302 303 304 305 306 307 308  | Next Page >

• how can i set up a uniqueness constraint in mysql for columns that can be null?

  - by user299689

  I know that in MySQL, UNIQUE constraits don't treat NULL values as equal. So if I have a unique constraint on ColumnX, then two separate rows can have values of NULL for ColumnX and this wouldn't violate the constraint. How can I work around this? I can't just set the value to an arbitrary constant that I can flag, because ColumnX in my case is actually a foreign key to another table. What are my options here? Please note that this table also has an "id" column that is its primary key. Since I'm using Ruby on Rails, it's important to keep this id column as the primary key. Note 2: In reality, my unique key encompasses many columns, and some of them have to be null, because they are foreign keys, and only one of them should be non-null. What I'm actually trying to do is to "simulate" a polymorphic relationship in a way that keep referential integrity in the db, but using the technique outlined in the first part of the question asked here: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association

  Read the article

• can this problem be solved with a single SQL query?

  - by PierrOz

  I have the two following tables (with some sample datas) LOGS: ID | SETID | DATE ======================== 1 | 1 | 2010-02-25 2 | 2 | 2010-02-25 3 | 1 | 2010-02-26 4 | 2 | 2010-02-26 5 | 1 | 2010-02-27 6 | 2 | 2010-02-27 7 | 1 | 2010-02-28 8 | 2 | 2010-02-28 9 | 1 | 2010-03-01 STATS: ID | OBJECTID | FREQUENCY | STARTID | ENDID ============================================= 1 | 1 | 0.5 | 1 | 5 2 | 2 | 0.6 | 1 | 5 3 | 3 | 0.02 | 1 | 5 4 | 4 | 0.6 | 2 | 6 5 | 5 | 0.6 | 2 | 6 6 | 6 | 0.4 | 2 | 6 7 | 1 | 0.35 | 3 | 7 8 | 2 | 0.6 | 3 | 7 9 | 3 | 0.03 | 3 | 7 10 | 4 | 0.6 | 4 | 8 11 | 5 | 0.6 | 4 | 8 7 | 1 | 0.45 | 5 | 9 8 | 2 | 0.6 | 5 | 9 9 | 3 | 0.02 | 5 | 9 Every day new logs are analyzed on different sets of objects and stored in table LOGS. Among other processes, some statistics are computed on the objects contained into these sets and the result are stored in table STATS. These statistic are computed through several logs (identified by the STARTID and ENDID columns). So, what could be the SQL query that would give me the latest computed stats for all the objects with the corresponding log dates. In the given example, the result rows would be: OBJECTID | SETID | FREQUENCY | STARTDATE | ENDDATE ====================================================== 1 | 1 | 0.45 | 2010-02-27 | 2010-03-01 2 | 1 | 0.6 | 2010-02-27 | 2010-03-01 3 | 1 | 0.02 | 2010-02-27 | 2010-03-01 4 | 2 | 0.6 | 2010-02-26 | 2010-02-28 5 | 2 | 0.6 | 2010-02-26 | 2010-02-28 So, the most recent stats for set 1 are computed with logs from feb 27 to march 1 whereas stats for set 2 are computed from feb 26 to feb 28. object 6 is not in the results rows as there is no stat on it within the last period of time. Last thing, I use MySQL. Any Idea ?

  Read the article

• Why does this simple MySQL procedure take way too long to complete?

  - by Howard Guo

  This is a very simple MySQL stored procedure. Cursor "commission" has only 3000 records, but the procedure call takes more than 30 seconds to run. Why is that? DELIMITER // DROP PROCEDURE IF EXISTS apply_credit// CREATE PROCEDURE apply_credit() BEGIN DECLARE done tinyint DEFAULT 0; DECLARE _pk_id INT; DECLARE _eid, _source VARCHAR(255); DECLARE _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count DECIMAL; DECLARE commission CURSOR FOR SELECT pk_id, eid, source, lh_revenue, acc_revenue, project_carrier_expense, carrier_lh, carrier_acc, gross_margin, fsc_revenue, revenue, load_count FROM ct_sales_commission; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DELETE FROM debug; OPEN commission; REPEAT FETCH commission INTO _pk_id, _eid, _source, _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count; INSERT INTO debug VALUES(concat('row ', _pk_id)); UNTIL done = 1 END REPEAT; CLOSE commission; END// DELIMITER ; CALL apply_credit(); SELECT * FROM debug;

  Read the article

• Implementing Tagging System with PHP and mySQL. Caching help!!!

  - by Hamid Sarfraz

  With reference to this post: http://stackoverflow.com/questions/2122546/how-to-implement-tag-counting I have implemented the suggested 3 table tagging system completely. To count the number of Articles per tag, i am using another column named tagArticleCount in the tag definition table. (other columns are tagId, tagText, tagUrl, tagArticleCount). If i implement realtime editing of this table, so that whenever user adds another tag to article or deletes an existing tag, the tag_definition_table is updated to update the counter of the added/removed tag. This will cost an extra query each time any modification is made. (at the same time, related link entry for tag and article is deleted from tagLinkTable). An alternative to this is not allowing any real time editing to the counter, instead use CRONs to update counter of each tag after a specified time period. Here comes the problem that i want to discuss. This can be seen as caching the article count in database. Can you please help me find a way to present the articles in a list when a tag is explored and when the article counter for that tag is not up to date. For example: 1. Counter shows 50 articles, but there are infact 55 entries in the tag link table (that links tags and articles). 2. Counter shows 50 articles, but there are infact 45 extries in the tag link table. How to handle these 2 scenerios given in example. I am going to use APC to keep cache of these counters. Consider it too in your solution. Also discuss performance in the realtime / CRONNED counter updates.

  Read the article

• How To Correctly Specify A Default Value For A String Field In A PHP/MySQL Prepared Statement

  - by Joshua

  I'm trying to debug some auto-generated code, but I am a mySQL noob. Everything goes fine ultil the "prepare" line below, and then for some reason $mysqli_stmt is false, yielding the stated error. Could it have something to do with the SQL_MODE = 'ANSI'? The failure seems to have something to do with the string 'xxx' below, but it still happens no matter what I change it to. This value is meant to be a default value for the TickerDigest field, but strangely if I change 'xxx' to 'c_u_TickerDigest', then it suddenly works, but the TickerDigest field is inserted as 'null' when I look in the database. $mysqli = mysqli_init(); $mysqli->options(MYSQLI_INIT_COMMAND, "SET SQL_MODE = 'ANSI'"); $mysqli->real_connect(SR_Host,SR_Username,SR_Password,SR_Database) or die('Unable to connect to Database'); $sql_stmt = 'INSERT INTO "t_sr_u_Product"("c_u_Name", "c_u_Code", "c_u_TickerDigest") VALUES (?, ?, "xxx")'; $mysqli_stmt = $mysqli->prepare($sql_stmt); Fatal error: Uncaught exception 'Exception' with message 'INSERT INTO "t_sr_u_Product"("c_u_Name", "c_u_Code", "c_u_TickerDigest") VALUES (?, ?, "xxx"): prepare statement failed: Unknown column 'xxx' in 'field list'' in P:\StarRise\SandBox\GateKeeper\Rise\srIProduct.php on line 18 I'm hopeful what's going wrong is fairly simple, since I'm almost completely ignorant about SQL.

  Read the article

• [php,mysql] insert only adds upto 1000 records and ignoresall records after that.

  - by user560559

  Hello i have a large database where the client stores personal messages and fire email notifications [if allowed by the users]. certain users have the option of sending messages to their entire network of friends. some users have over 5000 friends in their network so if they select the whole network they'll be sending messages to over 5000 friends and system will store all the messages into a table. the problem is this that it does not insert more than 1000 records and ignores all inserts after the first 1000. i have increased the packet size, bulk_insert_buffer_size but still no luck. since the system stores some of the info in another table for reports, every insert returns its new message id. due to this i can not use the "insert into table (column1,column2) values (value1,value2) , (value1,value2)....etc." table engine is innodb, mysql version is 5.1.3 and is hosted on amazon web services. all i want is to fix this issue of inserting more than 1000 records at a time. as mentioned earlier, it works fine but only up to 1000 records and simply ignores all the records after that. i'm using php foreach(){} to insert message for each friend and if email is available, send notification to the user. this foreach(){} also inserts the same record in another table [with only 3 columns] for generating reports. thank you in advance for all the help and support. WMA.

  Read the article

• How can I output my MySQL data into following structures?

  - by sky

  Here is the question, how can I output MySQL data into format like : userAge, userMid are the column name, table name Users. <script type="text/javascript"> userAge = new Array('21','36','20'), userMid = new Array('liuple','anhu','jacksen'); </script> Which I'll be using later with following code : var html = ' <table class="map-overlay"> <tr> <td class="user">' + '<a class="username" href="/' + **userMid[index]** + '" target="_blank"><img alt="" src="' + getAvatar(signImgList[index], '72x72') + '"></a><br> <a class="username" href="/' + **userMid[index]** + '" target="_blank">' + userNameList[index] + '</a><br> <span class="info">' + **userSex[index]** + ' ' + **userAge[index]** + '?<br> ' + cityList[index] + '</span>' + '</td> <td class="content">' + picString + somethings[index] + '<br> <span class="time">' + timeList[index] + picTips + '</span></td> </tr> </table> '; PS: I Just been told that i coulnt use JSON to output that format, so i have no idea now .< Thanks!

  Read the article

• How to remove empty tables from a MySQL backup file.

  - by user280708

  I have multiple large MySQL backup files all from different DBs and having different schemas. I want to load the backups into our EDW but I don't want to load the empty tables. Right now I'm cutting out the empty tables using AWK on the backup files, but I'm wondering if there's a better way to do this. If anyone is interested, this is my AWK script: EDIT: I noticed today that this script has some problems, please beware if you want to actually try to use it. Your output may be WRONG... I will post my changes as I make them. # File: remove_empty_tables.awk # Copyright (c) Northwestern University, 2010 # http://edw.northwestern.edu /^--$/ { i = 0; line[++i] = $0; getline if ($0 ~ /-- Definition/) { inserts = 0; while ($0 !~ / ALTER TABLE .* ENABLE KEYS /) { # If we already have an insert: if (inserts > 0) print else { # If we found an INSERT statement, the table is NOT empty: if ($0 ~ /^INSERT /) { ++inserts # Dump the lines before the INSERT and then the INSERT: for (j = 1; j <= i; ++j) print line[j] i = 0 print $0 } # Otherwise we may yet find an insert, so save the line: else line[++i] = $0 } getline # go to the next line } line[++i] = $0; getline line[++i] = $0; getline if (inserts > 0) { for (j = 1; j <= i; ++j) print line[j] print $0 } next } else { print "--" } } { print }

  Read the article

• Is it possible to have a mysql table accept a null value for a primary_key column referencing a diff

  - by Dr.Dredel

  I have a table that has a column which holds the id of a row in another table. However, when table A is being populated, table B may or may not have a row ready for table A. My question is, is it possible to have mysql prevent an invalid value from being entered but be ok with a NULL? or does a foreign key necessitate a valid related value? So... what I'm looking for (in pseudo code) is this: Table "person" id | name Table "people" id | group_name | person_id (foreign key id from table person) insert into person (1, 'joe'); insert into people (1, 'foo', 1)//kosher insert into people (1, 'foo', NULL)//also kosher insert into people(1, 'foo', 7)// should fail since there is no id 7 in the person table. The reason I need this is that I'm having a chicken and egg issue where it makes perfect sense for the rows in the people table to be created before hand (in this example, I'm creating the groups and would like them to pre-exist the people who join them). And I realize that THIS example is silly and I would just put the group id in the person table rather than vice-versa, but in my real-world problem that is not workable. Just curious if I need to allow any and all values in order to make this work, or if there's some way to allow for null.

  Read the article

• MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A

  - by Chez

  Hi All, Have been searching high and low for a simple solution to a mysql insert problem. The problem is as follows: I am putting together an organisational database consisting of departments and desks. A department may or may not have n number of desks. Both departments and desks have their own table linked by a foreign key in desks to the relevant record in departments (i.e. the pk). I have a temporary table which I use to place all new department data (n records long)...In this table n number of desk records for a department follow the department record directly below. In the TEMP table, if a column department_name has a value,it is a department, if it doesn't it will have a value for the column desk and therefore will be a desk which is related to the above department. As I said there maybe several desk records until you get to the next department record. Ok, so what I want to do is the following: Insert the departments into the departments table and its desks into the desks table , generating a foreign key in the desk record to the relevant departments id. In pseudo-ish code: for each record in TEMP table if Department INSERT the record into Departments get the id of the newly created Department record and store it somewhere else if Desk INSERT the desk into the desks table with the relevant departments id as the foreignkey note once again that all departments desks directly follow the department in the TEMP Table Many Thanks

  Read the article

• A very interesting MYSQL problem (related to indexing, million records, algorithm.)

  - by terence410

  This problem is pretty hard to describe and therefore difficult to search the answer. I hope some expert could share you opinions on that. I have a table with around 1 million of records. The table structure is similar to something like this: items{ uid (primary key, bigint, 15) updated (indexed, int, 11) enabled (indexed, tinyint, 1) } The scenario is like this. I have to select all of the records everyday and do some processing. It takes around 3 second to handle each item. I have written a PHP script to fetch 200 items each time using the following. select * from items where updated unix_timestamp(now()) - 86400 and enabled = 1 limit 200; I will then update the "updated" field of the selected items to make sure that it wont' be selected again within one day. The selected query is something like that. update items set updated = unix_timestamp(now()) where uid in (1,2,3,4,...); Then, the PHP will continue to run and process the data which doesn't require any MYSQL connection anymore. Since I have million records and each record take 3 seconds to process, it's definitely impossible to do it sequentially. Therefore, I will execute the PHP in every 10 seconds. However, as time goes by and the table growth, the select getting much slower. Sometimes, it take more than 100 seconds to run! Do you guys have any suggestion how may I solve this problem?

  Read the article

• How can I pull multiple rows from a MySQL table and use all of them automatically for the same thing

  - by Rob

  Basically, I have multiple URL's stored in a MySQL table. I want to pull those URLs from the table and have cURL connect to all of them. Currently I've been storing the URL's in the local script, but I've added a new page that I can add and remove them from the database, and I'd like the page to reflect it appropriately. Here is what I currently have: $sites[0]['url'] = "http://example0.com "; $sites[1]['url'] = "http://example1.com"; $sites[2]['url'] = "http://example2.com"; $sites[3]['url'] = "http://example3.com"; foreach($sites as $s) { // Now for some cURL to run it. $ch = curl_init($s['url']); //load the urls and send GET data curl_setopt($ch, CURLOPT_TIMEOUT, 2); //No need to wait for it to load. Execute it and go. curl_exec($ch); //Execute curl_close($ch); //Close it off } Now I assume it can't be too amazingly difficult to do, I just don't know how. So if you could point me in the right direction, I'd be grateful. But if you supply me with some code, please comment it appropriately so that I can understand what each line is doing.

  Read the article

• What is a good solution to log the deletion of a row in MySQL?

  - by hobodave

  Background I am currently logging deletion of rows from my tickets table at the application level. When a user deletes a ticket the following SQL is executed: INSERT INTO alert_log (user_id, priority, priorityName, timestamp, message) VALUES (9, 4, 'WARN', NOW(), "TICKET: David A. deleted ticket #6 from Foo"); Please do not offer schema suggestions for the alert_log table. Fields: user_id - User id of the logged in user performing the deletion priority - Always 4 priorityName - Always 'WARN' timestamp - Always NOW() message - Format: "[NAMESPACE]: [FullName] deleted ticket #[TicketId] from [CompanyName]" NAMESPACE - Always TICKET FullName - Full name of user identified by user_id above TicketId - Primary key ID of the ticket being deleted CompanyName - Ticket has a Company via tickets.company_id Situation/Questions Obviously this solution does not work if a ticket is deleted manually from the mysql command line client. However, now I need to. The issues I'm having are as follows: Should I use a PROCEDURE, FUNCTION, or TRIGGER? -- Analysis: TRIGGER - I don't think this will work because I can't pass parameters to it, and it would trigger when my application deleted the row too. PROCEDURE or FUNCTION - Not sure. Should I return the number of deleted rows? If so, that would require a FUNCTION right? How should I account for the absence of a logged in user? -- Possibilities: Using either a PROC or FUNC, require the invoker to pass in a valid user_id Require the user to pass in a string with the name Use the CURRENT_USER - meh Hard code the FullName to just be "Database Administrator" Could the name be an optional parameter? I'm rather green when it comes to sprocs. Assuming I went with the PROC/FUNC approach, is it possible to outright restrict regular DELETE calls to this table, yet still allow users to call this PROC/FUNC to do the deletion for them? Ideally the solution is usable by my application as well, so that my code is DRY.

  Read the article

• MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

  - by Aaron

  I'm using the America/New York timezone. In the Fall we "fall back" an hour -- effectively "gaining" one hour at 2am. At the transition point the following happens: it's 01:59:00 -04:00 then 1 minute later it becomes: 01:00:00 -05:00 So if you simply say "1:30am" it's ambiguous as to whether or not you're referring to the first time 1:30 rolls around or the second. I'm trying to save scheduling data to a MySQL database and can't determine how to save the times properly. Here's the problem: "2009-11-01 00:30:00" is stored internally as 2009-11-01 00:30:00 -04:00 "2009-11-01 01:30:00" is stored internally as 2009-11-01 01:30:00 -05:00 This is fine and fairly expected. But how do I save anything to 01:30:00 -04:00? The documentation does not show any support for specifying the offset and, accordingly, when I've tried specifying the offset it's been duly ignored. The only solutions I've thought of involve setting the server to a timezone that doesn't use daylight savings time and doing the necessary transformations in my scripts (I'm using PHP for this). But that doesn't seem like it should be necessary. Many thanks for any suggestions.

  Read the article

• Postfix "warning: cannot get RSA private key from file"

  - by phew

  I just followed this tutorial to set up a postfix mailserver with dovecot and mysql as backend for virtual users. Now I got the most parts working, I can connect to pop3 pop3s imap and imaps. Using echo TEST-MAIL | mail [email protected] works fine, when I log into my hotmail account it shows the email. It also works in reverse hence my MX entry for mydomain.com finally has been propagated, so I am being able to receive emails sent from [email protected] to [email protected] and view them in Thunderbird using STARTTLS via IMAP. Doing a bit more research after I got the error message "5.7.1 : Relay access denied" when trying to send mails to [email protected] using Thunderbird being logged into [email protected], I figured out that my server was acting as an "Open Mail Relay", which - ofcourse - is a bad thing. Digging more into the optional parts of the tutorial like shown workaround.org/comment/2536 and workaround.org/ispmail/squeeze/postfix-smtp-auth I decided to complete these steps aswell to be able to send mails via [email protected] through Mozilla Thunderbird, not getting the error message "5.7.1 : Relay access denied" anymore (as common mailservers reject open relayed emails). But now I ran into an error trying to get postfix working with SMTPS, in /var/log/mail.log it reads Sep 28 17:29:34 domain postfix/smtpd[20251]: warning: cannot get RSA private key from file /etc/ssl/certs/postfix.pem: disabling TLS support Sep 28 17:29:34 domain postfix/smtpd[20251]: warning: TLS library problem: 20251:error:0906D06C:PEM routines:PEM_read_bio:no start line:pem_lib.c:650:Expecting: ANY PRIVATE KEY: Sep 28 17:29:34 domain postfix/smtpd[20251]: warning: TLS library problem: 20251:error:140B0009:SSL routines:SSL_CTX_use_PrivateKey_file:PEM lib:ssl_rsa.c:669: That error is logged right after I try to send a mail from my newly installed mailserver using SMTP SSL/TLS via port 465 in Thunderbird. Thunderbird then tells me a timeout occured. Google has a few results concerning that problem, yet I couldn't get it working with any of those. I would link some of them here but as a new user I am only allowed to use two hyperlinks. My /etc/postfix/master.cf looks like smtp inet n - - - - smtpd smtps inet n - - - - smtpd -o smtpd_tls_wrappermode=yes -o smtpd_sasl_auth_enable=yes and nmap tells me PORT STATE SERVICE [...] 465/tcp open smtps [...] my /etc/postfix/main.cf looks like smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU) biff = no append_dot_mydomain = no readme_directory = no #smtpd_tls_cert_file = /etc/ssl/certs/postfix.pem #default postfix generated #smtpd_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key #default postfix generated smtpd_tls_cert_file = /etc/ssl/certs/postfix.pem smptd_tls_key_file = /etc/ssl/private/postfix.pem smtpd_use_tls = yes smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache smtpd_sasl_type = dovecot smtpd_sasl_path = private/auth smptd_sasl_auth_enable = yes smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination myhostname = mydomain.com alias_maps = hash:/etc/aliases alias_database = hash:/etc/aliases myorigin = /etc/mailname mydestination = localhost.com, localhost relayhost = mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128 mailbox_size_limit = 0 recipient_delimiter = + inet_interfaces = all virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf virtual_transport = dovecot dovecot_destination_recipient_limit = 1 mailbox_command = /usr/lib/dovecot/deliver The *.pem files were created like described in the tutorial above, using Postfix To create a certificate to be used by Postfix use: openssl req -new -x509 -days 3650 -nodes -out /etc/ssl/certs/postfix.pem -keyout /etc/ssl/private/postfix.pem Do not forget to set the permissions on the private key so that no unauthorized people can read it: chmod o= /etc/ssl/private/postfix.pem You will have to tell Postfix where to find your certificate and private key because by default it will look for a dummy certificate file called "ssl-cert-snakeoil": postconf -e smtpd_tls_cert_file=/etc/ssl/certs/postfix.pem postconf -e smtpd_tls_key_file=/etc/ssl/private/postfix.pem I think I don't have to include /etc/dovecot/dovecot.conf here, as login via imaps and pop3s works fine according to the logs. Only problem is making postfix properly use the self-generated, self-signed certificates. Any help appreciated! EDIT: I just tried this different tutorial on generating a self-signed certificate for postfix, still getting the same error. I really don't know what else to test. I also did check for the SSL libraries, but all seems to be fine: root@domain:~# ldd /usr/sbin/postfix linux-vdso.so.1 => (0x00007fff91b25000) libpostfix-global.so.1 => /usr/lib/libpostfix-global.so.1 (0x00007f6f8313d000) libpostfix-util.so.1 => /usr/lib/libpostfix-util.so.1 (0x00007f6f82f07000) libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f6f82cb1000) libcrypto.so.0.9.8 => /usr/lib/libcrypto.so.0.9.8 (0x00007f6f82910000) libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0x00007f6f826f7000) libdb-4.8.so => /usr/lib/libdb-4.8.so (0x00007f6f8237c000) libnsl.so.1 => /lib/libnsl.so.1 (0x00007f6f82164000) libresolv.so.2 => /lib/libresolv.so.2 (0x00007f6f81f4e000) libc.so.6 => /lib/libc.so.6 (0x00007f6f81beb000) libdl.so.2 => /lib/libdl.so.2 (0x00007f6f819e7000) libz.so.1 => /usr/lib/libz.so.1 (0x00007f6f817d0000) libpthread.so.0 => /lib/libpthread.so.0 (0x00007f6f815b3000) /lib64/ld-linux-x86-64.so.2 (0x00007f6f83581000) After following Ansgar Wiechers instructions its finally working. postconf -n contained the lines as it should. The certificate/key check via openssl did show that both files are valid. So it indeed has been a permissions problem! Didn't know that chown'ing the /etc/ssl/*/postfix.pem files to postfix:postfix is not enough for postfix to read the files.

  Read the article

• Using Terminal Services 'query' in batch file

  - by dboarman-FissureStudios

  I have a batch file that checks several of our servers for a user. From the command: query user %userID% /server:ServerName I want to capture the output before it goes to the screen. Is there a way to redirect the output to a variable? The basic gist of what I want to accomplish is this - we iterate through our servers: query user %userID% /server:Server1 query user %userID% /server:Server2 query user %userID% /server:Server3 query user %userID% /server:Server4 Instead of outputting a message that the user could not be found on a specific server, I would like for it to only notify me if it finds the user on a server.

  Read the article

• SQL SERVER – Concat Function in SQL Server – SQL Concatenation

  - by pinaldave

  Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a good enterprise solution, it does not have some basic functions. I naturally asked him for example and he suggested CONCAT() which exists in MySQL and Oracle. The answer is very simple – the equalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes). Method 1: Concatenating two strings SELECT 'FirstName' + ' ' + 'LastName' AS FullName Method 2: Concatenating two Numbers SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10)) Method 3: Concatenating values from table columns SELECT FirstName + ' ' + LastName FROM AdventureWorks.Person.Contact Well, this may look very simple but sometime it is very difficult to find the information for simple things only. Do you have any such example which you would like to share with community? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – A Successful Performance Tuning Seminar – Hyderabad – Nov 27-28, 2010 – Next Pune

  - by pinaldave

  My recent SQL Server Performance Tuning Seminar in Colombo was oversubscribed with total of 35 attendees. You can read the details over here SQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010. I had recently completed another seminar in Hyderabad which was again blazing success. We had 25 attendees to the seminar and had wonderful time together. There is one thing very different between usual class room training and this seminar series. In this seminar series we go 100% demo oriented and real world scenario deep down. We do not talk usual theory talk-talk. The goal of this seminar to give anybody who attends a jump start and deep dive on the performance tuning subject. I will share many different examples and scenarios from my years of experience of performance tuning. The beginning of the second day is always interesting as I take attendees the server as example of the talk, and together we will attempt to identify the bottleneck and see if we can resolve the same. So far I have got excellent feedback on this unique session, where we pick database of the attendees and address the issues. I plan to do the same again in next sessions. The next Seminar is in Pune.I am very excited for the same. Date and Time: December 4-5, 2010. 10 AM to 6 PM The Pride Hotel 05, University Road, Shivaji Nagar, Pune – 411 005 Tel: 020 255 34567 Click here for the agenda of the seminar. Instead of writing more details, I will let the photos do the talk for latest Hyderabad Seminar. Hotel Amrutha Castle King Arthur's Court Pinal Presenting Seminar Pinal Presenting Seminar Seminar Attendees Pinal Presenting Seminar Group Photo of Hyderabad Seminar Attendees Seminar Support Staff - Nupur and Shaivi Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Training, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology

  Read the article

• SQLAuthority News – SQL Server Performance Series Hyderabad / Pune – Nov/Dec 2010

  - by pinaldave

  Just a quick note that SQL Server Performance Tuning and Optimizations Seminar series which I am offering at Hyderabad and Pune are almost all sold out. Read the details of the earlier successful seminar conducted at Colombo, Sri Lanka over here. Hyderabad Nov 27-28, 2010 (Last 3 Seats Left) Best Western Amrutha Castle 5-9-16, Opp. Secretriat, Saifabad, Khairatabad Hyderabad, Andhra Pradesh Pune Dec 04-05, 2010 (Last 6 Seats Left) Location TBA as we are looking for larger capacity room. I promise that this is going to be great fun as this sessions are very different then any usual sessions you have ever attended. This sessions are absolutely interactive and all the attendees will feel part of the event. As larger group are not convenient we are limited this seminars to very small group of people. This way attendees can go to instructors any time and feel connected. This 2-day seminar will cover the best of the best concepts and practices from popular courses offered by Solid Quality Mentors. Instead of learning theory only, the seminar focuses on providing real world experience by using demos and scenarios derived from customer engagements. The seminar is uniquely structured and well-thought-out. Sessions are discussion- based and are designed to be an interactive gateway between the instructor and the participants for an optimal learning experience. The seminar is intended to be immersion-based where participants will have plenty of opportunities to get deeply involved in the concepts presented by the instructor. Agenda of the event To join the seminars drop me an email. My email address is pinal “at” SQLAuthority.com and IndiaInfo “at” SolidQ.com. If you specify SQLAuthority.com in Title, you will avail special discount in overall rates on specified price. Yes, a sure 20% I promise. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: About Me, Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – Find First Non-Numeric Character from String

  - by pinaldave

  It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character. -- How to find first non numberic character USE tempdb GO CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100)) GO INSERT INTO MyTable (ID, Col1) SELECT 1, '1one' UNION ALL SELECT 2, '11eleven' UNION ALL SELECT 3, '2two' UNION ALL SELECT 4, '22twentytwo' UNION ALL SELECT 5, '111oneeleven' GO -- Use of PATINDEX SELECT PATINDEX('%[^0-9]%',Col1) 'Position of NonNumeric Character', SUBSTRING(Col1,PATINDEX('%[^0-9]%',Col1),1) 'NonNumeric Character', Col1 'Original Character' FROM MyTable GO DROP TABLE MyTable GO Here is the resultset: Where do I use in the real world – well there are lots of examples. In one of the future blog posts I will cover that as well. Meanwhile, do you have any better way to achieve the same. Do share it here. I will write a follow up blog post with due credit to you. Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Question to You – When to use Function and When to use Stored Procedure

  - by pinaldave

  This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject. Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*) Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once Keeping the same spirit up, I am asking the third question over here. Q3) When to use User Defined Function and when to use Stored Procedure in your development? Personally, I believe that they are both different things - they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question. When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other? If you are going to answer that ‘To avoid repeating code, you use Function’ - please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ - again think harder! (link). Now, what do you say? I will post the answers of all the three questions with due credit next week. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, Readers Question, SQL, SQL Authority, SQL Function, SQL Puzzle, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLServer, T SQL, Technology

  Read the article

• SQL SERVER – Where Can YOU Get My Books – SQL Server Interview Question and Answers

  - by pinaldave

  Earlier month I released by third book SQL Server Interview Question and Answers. The focus of this book is ‘master the basics’. If you rate yourself 10 out of 10 in SQL Server – this book is not for you but if you want to learn fundamentals or want to refresh your fundamentals this book is for YOU. Earlier I was overwhelmed by love you all have shown to this book on release date leading our three digit inventory to run out of stock. Read detail blog post about the subject over here A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available. Well, we learn the lesson from the experience and have made sure that the inventory does not run out any more. Since then we are now available on multiple outlets. Pretty much anywhere in USA and India the book is available. Additionally, where ever Amazon ships internationally. I have created dedicated page where I have listed where one can avail this book from Details of SQL Server Interview Question and Answers. Even though I keep on getting common question like – where one can get this book. You can get this book from: USA: Amazon India: Flipkart | IndiaPlaza | Crossword In India now you can walk into any crossword store and ask this book, if they do not have it, you can ask them get one for you. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Best Practices, Pinal Dave, PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority Book Review, SQLAuthority News, T SQL, Technology

  Read the article

• Creating an email notification system based on polling database rows

  - by Ashish Sharma

  I have to design an email notification system based on the following requirements: The email notifications would be created based on polling rows in a Mysql 5.5 DB table when they are in a particular 'Completed' state. The email notification should be sent out in no more than 5 minutes from the time the row was created in the DB table (At the time of DB table row creation the state of the row might not be 'Completed'). Once 5 minutes for the DB table row expire in reaching the 'Completed' state, separate email notification need to be sent (basically telling the user that the original email notification would be delayed) and then sending the email notification as and when the row state reaches to being 'Completed'. The rest of the system requirements are : Adding relevant checks to monitor the whole system via MBeans interface. The system should be scalable so that if the rate of DB table rows creation increases so does the Email notification system be able to ramp up. So I request suggestions on following lines: What approach should I take in solving the problem described from a programming/Design pattern point of view? Suggestion for any third party plugin/software that can be used to solve the problem described? Points to take care regarding scalability and monitoring the health of the system? Java is the language of preference but I am open to using off the shelf components that can be interfaced with Java language or provide standard ports for communication. Currently I do have an in house grown system (written in Java) that is catering to the specified requirements, but it's now crumbling under increased load and now I want to give the problem a fresh look. thanks in advance Ashish

  Read the article

• SQL SERVER – Database in RESTORING State for Long Time

  - by Pinal Dave

  A very interesting question I received the other day. “Our database has been in restoring stage for a long time. We have already restored all the necessary files there. After restoring the files we are expecting that  the database will be in operational mode, however, it is continuously in the restoring mode. Any suggestion?” The question is very common. I sent user follow up emails to understand what is actually going on with the user. I realized after restoring their bak files and log files their database was in the restoring state because they had not restored the latest log file with RECOVERY options. As they had completed all the database restore sequence (bak and log in order), the real need for them was to recover the database from norecovery state. User can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored. There are three different ways to recover the database. 1) Recover the database manually with following command. RESTORE DATABASE database_name WITH RECOVERY 2) Recover the database with the last log file. RESTORE LOG database_name FROM backup_device WITH RECOVERY 3) Recover the database when bak is restored RESTORE DATABASE database_name FROM backup_device WITH RECOVERY To understand how the backup restores timeline works read Backup Timeline and Understanding of Database Restore Process in Full Recovery Model. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Apache VERY high page load time

  - by Aaron Waller

  My Drupal 6 site has been running smoothly for years but recently has experienced intermittent periods of extreme slowness (10-60 sec page loads). Several hours of slowness followed by hours of normal (4-6 sec) page loads. The page always loads with no error, just sometimes takes forever. My setup: Windows Server 2003 Apache/2.2.15 (Win32) Jrun/4.0 PHP 5 MySql 5.1 Drupal 6 Cold fusion 9 Vmware virtual environment DMZ behind a corporate firewall Traffic: 1-3 hits/sec avg Troubleshooting No applicable errors in apache error log No errors in drupal event log Drupal devel module shows 242 queries in 366.23 milliseconds,page execution time 2069.62 ms. (So it looks like queries and php scripts are not the problem) NO unusually high CPU, memory, or disk IO Cold fusion apps, and other static pages outside of drupal also load slow webpagetest.org test shows very high time-to-first-byte The problem seems to be with Apache responding to requests, but previously I've only seen this behavior under 100% cpu load. Judging solely by resource monitoring, it looks as though very little is going on. Here is the kicker - roughly half of the site's access comes from our LAN, but if I disable the firewall rule and block access from outside of our network, internal (LAN) access (1000+ devices) is speedy. But as soon as outside access is restored the site is crippled. Apache config? Crawlers/bots? Attackers? I'm at the end of my rope, where should I be looking to determine where the problem lies?

  Read the article

< Previous Page | 297 298 299 300 301 302 303 304 305 306 307 308  | Next Page >