Saturday, October 5, 2013

[how to] Newly installed Postgresql 9.2 on same box as 9.1

[how to] Newly installed Postgresql 9.2 on same box as 9.1

Newly installed Postgresql 9.2 on same box as 9.1

Posted: 05 Oct 2013 03:39 PM PDT

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed.

I have gotten 9.2 installed and confirmed it runs fine.

However, I can't connect to it.

xxxxxxx@xxxxxxx-desktop-ubuntu:~$ sudo /etc/init.d/postgresql restart  [sudo] password for xxxxxxx:    * Restarting PostgreSQL 9.1 database server                                                                     [ OK ]    * Restarting PostgreSQL 9.2 database server                                                                     [ OK ]   xxxxxxx@xxxxxxx-desktop-ubuntu:~$ su postgres  Password:   postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5432  Password:   psql (9.2.4, server 9.1.9)  WARNING: psql version 9.2, server version 9.1.           Some psql features might not work.  SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)  Type "help" for help.    postgres=# \q  postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5433  Password:   psql: FATAL:  password authentication failed for user "postgres"  FATAL:  password authentication failed for user "postgres"  postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$

So, how do I connect to this new instance? I thought that the Ubuntu/OS postgres user would allow me to connect, but it doesn't.

Other info:

  • 9.1 is on the default port: 5432
  • 9.2 is on port: 5433
  • Using Ubuntu 12.04

Feature SQL Server 2012 - Filetable - The location of the file can not be opened

Posted: 05 Oct 2013 09:40 AM PDT

When trying to access the files available on the virtual directory created for FileTable, you receive the following error message:

"The location of the file can not be opened. Access is not enabled or not you have the permissions for it."

Already granted all permissions, however, the application displays this error.

Mysql high delay in critical peak | Professional Help request

Posted: 05 Oct 2013 12:15 PM PDT

I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33.

My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz, 32 Cores, and 32 Bbs Ram.

My problem is I'm facing serious mysql delay in processing requests in peak hours, where I'm finding over 30 seconds delay in process calls, as asterisk agi need to run about 10 queries, into 10 tables in the DB before processing a call...

Below is my.cnf configuration:

[mysqld]    log-error=/var/log/mysqld.log  log_warnings = 1  user=mysql  back_log = 75  max_connections = 50000  interactive_timeout=1000  skip_innodb  key_buffer_size = 384M  myisam_sort_buffer_size = 1028M  join_buffer_size = 1M  read_buffer_size = 2M  sort_buffer_size = 2M  table_cache = 1028  thread_cache_size = 32  wait_timeout = 7200  connect_timeout = 40  tmp_table_size = 768M  max_heap_table_size = 768M  max_allowed_packet = 1024M  max_connect_errors = 5000  read_rnd_buffer_size = 1024M  bulk_insert_buffer_size = 160M  query_cache_limit = 8098M  query_cache_size =8048M  query_cache_type = 1  query_prealloc_size = 65536  query_alloc_block_size = 131072  default-storage-engine = MyISAM  symbolic-links=0  datadir=/var/lib/mysql  socket=/var/lib/mysql/mysql.sock  thread_concurrency = 128  net_read_timeout=2400

Below is my load in not so overloaded hour:

147 active SIP dialogs

uptime 19:02:21 up 6 days, 13:53, 1 user, load average: 1.63, 1.20, 0.90

top

top - 19:05:34 up 6 days, 13:56, 1 user, load average: 0.33, 1.08, 0.95 Tasks: 674 total, 2 running, 672 sleeping, 0 stopped, 0 zombie Cpu(s): 5.8%us, 1.3%sy, 0.0%ni, 92.8%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 32829392k total, 19988176k used, 12841216k free, 218132k buffers Swap: 92371960k total, 10272k used, 92361688k free, 13327708k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13976 mysql 20 0 36.1g 4.1g 6552 S 196.3 13.0 36:17.48 mysqld
19032 root -11 0 17.1g 575m 15m S 36.5 1.8 3031:40 asterisk

Please, what I'm really bad calculating in this, I'm confused in several values, here... and I'd even require a Professional help in this, please if any are available for help, professionally, I'd really appreciate immediate intervention and advice.

Regards,

ER relation with unique key

Posted: 05 Oct 2013 07:40 AM PDT

I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs:

  1. A student does not have to be a member of any team.
  2. A student could be a member of at most one team.
  3. member has a unique member ID.

I did my ER Diagram:

                                                  member-id (key attribute)                                                              |  --------                          -                    -----------  - team -------------------- -  - mem -   ------------- - student -  --------                          -                    -----------

As I said before, student does not have to be a member of any team. In this case, my diagram works fine? I asked it because of the member_id is a unique key.

Copy all data to slave before mySQL replication connect

Posted: 05 Oct 2013 02:48 PM PDT

I try to set up mySQL replication, however I have question before I connect 2 servers

Q1. Should I import all data from master into slave before I connect 2 servers?

ex. if My db have millions of rows, should I copy all data to slave first?

Q2. I have read few post about when disaster strikes on master, it switch to slave, so you have time to fix master. What kind of disaster could happen? I have set up mySQL only SELECT, ADD and UPDATE, just wondering what could happen?

my mysql stop running and restart after minutes. why?

Posted: 05 Oct 2013 04:40 AM PDT

I have a problem with my mysql. My site was down for a few minutes with this error:

Unable to connect mysql ...

And I saw this log in \var\log\mysqld.log :

   131005 12:23:38 mysqld_safe Number of processes running now: 0      131005 12:23:38 mysqld_safe mysqld restarted      131005 12:23:43  InnoDB: Initializing buffer pool, size = 8.0M      131005 12:23:43  InnoDB: Completed initialization of buffer pool      InnoDB: Log scan progressed past the checkpoint lsn 2 657658929      131005 12:23:43  InnoDB: Database was not shut down normally!      InnoDB: Starting crash recovery.      InnoDB: Reading tablespace information from the .ibd files...      InnoDB: Restoring possible half-written data pages from the doublewrite      InnoDB: buffer...      InnoDB: Doing recovery: scanned up to log sequence number 2 657692436      InnoDB: 1 transaction(s) which must be rolled back or cleaned up      InnoDB: in total 13786 row operations to undo      InnoDB: Trx id counter is 0 288771072      131005 12:23:49  InnoDB: Starting an apply batch of log records to the database...      InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99       InnoDB: Apply batch completed      InnoDB: Starting in background the rollback of uncommitted transactions      131005 12:23:51  InnoDB: Rolling back trx with id 0 288770551, 13786 rows to undo        InnoDB: Progress in percents: 1131005 12:23:51  InnoDB: Started; log sequence number 2 657692436       2 3 4 5 6 7 8 9 10 11 12 13 14 15131005 12:23:54 [Note] Event Scheduler: Loaded 0 events      131005 12:23:54 [Note] /usr/libexec/mysqld: ready for connections.      Version: '5.1.66'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution       16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100      InnoDB: Rolling back of trx id 0 288770551 completed      131005 12:23:58  InnoDB: Rollback of non-prepared transactions completed

Why this is happening? How to fix it? Note: Page Load Speed of my site is very low for a few minutes in day and after this minutes that is ok.

Export a subset of data, from mysql production database, to make a test database

Posted: 05 Oct 2013 02:57 AM PDT

I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data from the whole database (i.e. from all tables), so that foreign key constraints are preserved in the data subset.

How can I do that?

Out-of-Range and Overflow Handling for float values

Posted: 04 Oct 2013 11:41 PM PDT

am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3)

Currently if the user enter the value above 999999.000 MYSQL will save its maximum value,ie 1000000.000 and it will not throw any error.

If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue is greater than 999999.000

Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000.

what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3)..

Amazon RDS - Can't connect to MySQL database server?

Posted: 04 Oct 2013 09:36 PM PDT

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. The database is up and running, and I've created a security group, as well as an EC2 security group. Within the Ec2 security group, I've opened up port 3306 (the MySQL port), and added it as a rule. I've then applied this rule. I've then connected the security group to the EC2 security group, and I connected the database to the security group. Thus the database should have port 3306 open (and for all I know it does!).

So here's the issue. I'm trying to connect to the MySQL server based on the IP of the database I'm given by Amazon Web Services. However, when I attempt to connect via the Terminal command shell, the connection times out, saying that the MySQL connection failed.

Here's what I type in: "mysql -h *****.rds.amazonaws.com" where the asterisks are part of the IP address.

The error is as follows: "ERROR 2003 (HY000): Can't connect to MySQL server on '*****.rds.amazonaws.com' (60)"

I've also tried specifying a username and password to the command, but the same error occurs.

Any thoughts as to why the connection is failing?

Thanks, any help appreciated!

Jake

Merge Fields into one Row with a Query

Posted: 05 Oct 2013 10:57 AM PDT

I am working with Microsoft Office Professional Plus 2010. What I am trying to figure out is how to merge all characters for each DOMAIN field into one row, for example:

Q01                         DOMAIN      Q32  -----------------------------------------------  http://redbull.example.com  example.com F  http://www.example.com      example.com B

What I would like to accomplish through a query is to take the DOMAIN field and create a view which merges the "Q32" into one row, for example:

DOMAIN      Q32  ----------------  example.com B F

Is this possible through a design query or would I have to write a SQL statement? I tried the following SQL statement to create a UNION with two different tables, though I still didn't get the merge displayed correctly.

SELECT DOMAIN, Q32  FROM tblMERGE  UNION  SELECT DOMAIN, Q32  FROM tblASSET;

Backup Oracle DB using TSM server version 6.3.0

Posted: 05 Oct 2013 04:01 PM PDT

I'm new to Oracle and TSM server. I installed Oracle11gR2 and TSM server version 6.3.0 (linux Centos 6.4 OS). I did the configuration and everything works fine. so far I arrive to run the following script using rman:

run {  allocate channel Channel1 type 'SBT_TAPE' parms  'ENV=(DSMO_NODE=TDPOTSMUSR,DSMO_AVG_SIZE=1,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/  tdpo.opt,DSMI_LOG=/opt/tivoli/tsm/client/api/bin64/logs)';  backup          full          format 'b_%u_%p_%c'          database;  release channel Channel1 ;  }

I need to know how to backup theOracle DB to a storagepool name ORADisk? I need to understand a bit what does channel mean in a script and what does it actually do. really appreciate your hel and reply. thank you

Creating Multiple Indexes on SQL Server

Posted: 05 Oct 2013 09:44 AM PDT

I am creating multiple indexes on a table in SQL Server. The script is still executing but all the required indexes are showing as objects in Object Explorer. Do the objects get created before the index creation has been completed or once the index has been created succesfully?

Server Wide DDL Trigger Permissions Issue

Posted: 05 Oct 2013 04:25 AM PDT

I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here

The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message.

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19  The user does not have permission to perform this action.

I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue.

Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS?

EDIT: Some further info

The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows:

CREATE TRIGGER LogDDLEvent  ON ALL SERVER  FOR DDL_EVENTS  AS    DECLARE     @eventInfo XML  SET         @eventInfo = EVENTDATA()    INSERT INTO Tools.audit.DDLEvent  VALUES  (        REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName        , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName        , (SELECT   CAST(client_net_address AS VARCHAR(128))              FROM  sys.dm_exec_connections              WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName        , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType        , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand        , @eventInfo -- DDLEventXML  )

The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above.

After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself.

Mysql compatibility between client and server of different versions

Posted: 05 Oct 2013 06:25 AM PDT

How to know what version of mysql client is compatible with what version of the server?

Is there backward or forward compatibility tables from the client and/or server point of view?

For a given version of the server, how to know which clients are compatible, and conversely?

How to determine when to reduce physical memory for SQL Server?

Posted: 05 Oct 2013 01:25 PM PDT

If I repeatedly notice SQL Server reporting a lot of free memory, can I likely remove about that amount of memory from the server without affecting performance? Or, can/should it use more for caching?

For this particular example, the server is running SQL Server 2012 Standard Edition on Windows Server 2012. It has 20 GB of physical memory and hosts over 100 GB of data. SQL Server is the only application running on the server.

  SQLServer;Buffer Manager      Buffer cache hit ratio       99.737 %      Page life expectancy            874 s  SQLServer:Memory Manager      Database Cache Memory         6,744 MB      Free Memory                   5,937 MB      Optimizer Memory                  5 MB      SQL Cache Memory                 29 MB      Target Server Memory         19,015 MB      Total Server Memory          18,407 MB

Create a trigger to update table data on another Server's database

Posted: 05 Oct 2013 03:25 AM PDT

I am creating a trigger in MySQL and I need a little help.

I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.

These databases have the same tables names.

I want both the user data on both the websites to be the same.

So if one user registers on S1, then that user registration information should be passed to S2.

If a user registration information is updated on S1, the same information should be updated on S2.

And the same applies for S2.

How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated.

And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated.

Is this possible? Could you provide some examples?

creating a report on the mysql jasperReport server

Posted: 05 Oct 2013 10:25 AM PDT

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw?

Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

Posted: 05 Oct 2013 05:25 AM PDT

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew

brew install mysql

Everything seemed to go smoothly, but...not really, as it turns out.

I cannot add a new user (neither through the command line nor through phpmyadmin. I always get the error

Error in processing request: #2006 MySQL server has gone away

I can create new databases without a problem, and I can install, for example Joomla 3.1.1 with no problems.

However, when I try to install a new extension into my Joomla installation, I get errors. For example, I get the following error when I try to install a component from NoNumber:

1062 Duplicate entry '1' for key 'PRIMARY' SQL=INSERT INTO `u5z87_extensions` (`name`,`type`,`element`,`folder`,`client_id`,`enabled`,`access`) VALUES ('plg_system_nnframework','plugin','nnframework','system','0','1','1')

Same thing for many other extensions.

I tried to start mysql with

mysql.server start --log-warnings=2

and it started with no problem

Starting MySQL   SUCCESS!

I looked up issues where error #2006 were being reported and how to solve them, and they referred to modifying /etc/my.cnf but there is no /etc/my.cnf configuration file on my system, and there wasn't one in the previous installation of MySQL 5.5.29 (also installed with homebrew) and I had no problems.

I ran brew doctor and the output is below

$ brew doctor  Warning: "config" scripts exist outside your system or Homebrew directories.  `./configure` scripts often look for *-config scripts to determine if  software packages are installed, and what additional flags to use when  compiling and linking.    Having additional scripts in your path can confuse software installed via  Homebrew if the config script overrides a system or Homebrew provided  script of the same name. We found the following "config" scripts:        /usr/local/opt/php54/bin/php-config

The output from brew --config is also below:

$ brew --config  HOMEBREW_VERSION: 0.9.4  ORIGIN: https://github.com/mxcl/homebrew  HEAD: 86ce842c700a47abdc354e80cc3b0b3938ab2132  HOMEBREW_PREFIX: /usr/local  HOMEBREW_CELLAR: /usr/local/Cellar  CPU: 8-core 64-bit nehalem  OS X: 10.8.3-x86_64  Xcode: 4.6.2  CLT: 4.6.0.0.1.1365549073  LLVM-GCC: build 2336  Clang: 4.2 build 425  X11: 2.7.4 => /opt/X11  System Ruby: 1.8.7-358  Perl: /usr/bin/perl  Python: /usr/bin/python  Ruby: /Users/alisamii/.rvm/rubies/ruby-1.9.3-p374/bin/ruby

Exploring a little further, I tried to revert to my previous install of MySQL using

brew switch mysql 5.5.29

This resulted in the following output:

$ brew switch mysql 5.5.29  Cleaning /usr/local/Cellar/mysql/5.5.29  Cleaning /usr/local/Cellar/mysql/5.6.10  135 links created for /usr/local/Cellar/mysql/5.5.29

I then accessed mysql from the command line and ran status:

mysql> status  --------------  mysql  Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1    Connection id:      13  Current database:  Current user:       alisamii@localhost  SSL:            Not in use  Current pager:      stdout  Using outfile:      ''  Using delimiter:        ;  Server version:     5.6.10 Source distribution  Protocol version:       10  Connection:         Localhost via UNIX socket  Server characterset:    utf8  Db     characterset:    utf8  Client characterset:    utf8  Conn.  characterset:    utf8  UNIX socket:        /tmp/mysql.sock  Uptime:         1 hour 56 min 24 sec

As you can see above, it reports mysql Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1

But, it also says Server version: 5.6.10 Source distribution

So something isn't syncing up.

Furthermore, when I show databases, I get the following output:

mysql> show databases      -> ;  +--------------------+  | Database           |  +--------------------+  | information_schema |  +--------------------+  1 row in set (0.00 sec)

But I know I have more than just an information_schema database. in phpMyAdmin, I have 14 databases displaying.

So, somehow, it seems to me that mysql is not connecting to the right server, as phpmyadmin reports the same server information that mysql's status command reports: Software version: 5.6.10 - Source distribution.

At this point, I am completely lost, and would really need some help.

Plugin 'FEDERATED' is disabled

Posted: 05 Oct 2013 08:25 AM PDT

Im trying to start Mysql using easyPHP : the response is an alert window to a log file. The main error is <-- Plugin 'FEDERATED' is disabled -->.

Through my research on internet i found that the solution is to add the federated option to "my.ini" file. I did this but it is still not working.

Here is an extract from the log file for more information :

 2013-05-03 14:12:57 3776 [Note] Plugin 'FEDERATED' is disabled.      2013-05-03 14:12:57 1b4c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.      2013-05-03 14:12:57 3776 [Note] InnoDB: The InnoDB memory heap is disabled      2013-05-03 14:12:57 3776 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions      2013-05-03 14:12:57 3776 [Note] InnoDB: Compressed tables use zlib 1.2.3      2013-05-03 14:12:57 3776 [Note] InnoDB: Not using CPU crc32 instructions      2013-05-03 14:12:57 3776 [Note] InnoDB: Initializing buffer pool, size = 16.0M      2013-05-03 14:12:57 3776 [Note] InnoDB: Completed initialization of buffer pool      2013-05-03 14:12:57 3776 [Note] InnoDB: The first specified data file .\ibdata1 did not exist: a new database to be created!      2013-05-03 14:12:57 3776 [Note] InnoDB: Setting file .\ibdata1 size to 12 MB      2013-05-03 14:12:57 3776 [Note] InnoDB: Database physically writes the file full: wait...      2013-05-03 14:12:57 3776 [Note] InnoDB: Setting log file .\ib_logfile101 size to 5 MB      2013-05-03 14:12:57 3776 [Note] InnoDB: Setting log file .\ib_logfile1 size to 5 MB      2013-05-03 14:12:57 3776 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0      2013-05-03 14:12:57 3776 [Warning] InnoDB: New log files created, LSN=45781      2013-05-03 14:12:57 3776 [Note] InnoDB: Doublewrite buffer not found: creating new      2013-05-03 14:12:57 3776 [Note] InnoDB: Doublewrite buffer created      2013-05-03 14:12:57 3776 [Note] InnoDB: 128 rollback segment(s) are active.      2013-05-03 14:12:57 3776 [Warning] InnoDB: Creating foreign key constraint system tables.      2013-05-03 14:12:57 3776 [Note] InnoDB: Foreign key constraint system tables created      2013-05-03 14:12:57 3776 [Note] InnoDB: Creating tablespace and datafile system tables.      2013-05-03 14:12:57 3776 [Note] InnoDB: Tablespace and datafile system tables created.      2013-05-03 14:12:57 3776 [Note] InnoDB: Waiting for purge to start      2013-05-03 14:12:57 3776 [Note] InnoDB: 5.6.11 started; log sequence number 0      2013-05-03 14:12:58 3776 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: cadcd135-b3ea-11e2-92bc-e0db55e1304d.      2013-05-03 14:12:58 3776 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306      2013-05-03 14:12:58 3776 [Note]   - '127.0.0.1' resolves to '127.0.0.1';      2013-05-03 14:12:58 3776 [Note] Server socket created on IP: '127.0.0.1'.      2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.      2013-05-03 14:12:58 3776 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.      2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.      2013-05-03 14:12:58 3776 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.      2013-05-03 14:12:58 3776 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.      2013-05-03 14:12:58 3776 [Note] Event Scheduler: Loaded 0 events      2013-05-03 14:12:58 3776 [Note] C:\PROGRA~1\EASYPH~1.1VC\binaries\mysql\bin\mysqld.exe: ready for connections.      Version: '5.6.11-log'  socket: ''  port: 3306  MySQL Community Server (GPL)      2013-05-03 14:15:59 3776 [Note] C:\PROGRA~1\EASYPH~1.1VC\binaries\mysql\bin\mysqld.exe: Normal shutdown

Why does this procedure raise a privilege error?

Posted: 05 Oct 2013 12:25 PM PDT

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.

QUESTIONS

  • Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
  • Is there any way I could have corresponding functionality without a privileged call?

enter image description here

Archive partition before delete it?

Posted: 05 Oct 2013 02:25 AM PDT

I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use percona tools or mysqldump itself?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS     WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =     CONCAT(' p'      ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN        SET @stmt := CONCAT(          'ALTER TABLE testPart1 DROP PARTITION '      ,   ' p'      ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )       );      PREPARE stmt FROM @stmt;      EXECUTE stmt;      DEALLOCATE PREPARE stmt;      END IF;       IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS     WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =     CONCAT(' p'      ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN       SET @stmt := CONCAT(          'ALTER TABLE testPart1 ADD PARTITION ('      ,   'PARTITION p'      ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )      ,   ' VALUES LESS THAN ('      ,   TO_DAYS( CURDATE() ) + 2       ,   '))'      );      PREPARE stmt FROM @stmt;      EXECUTE stmt;      DEALLOCATE PREPARE stmt;     END IF;

Bi-directional replication for the same MySQL table

Posted: 05 Oct 2013 07:25 AM PDT

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA

tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA (assuming both had 5 rows).

row6 was created by AppA (say primary key 6) row7 was created by AppB (say primary key 7)

I would like row7 to be copied to dbA.tableA and row6 to dbB.tableA

  1. Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time.
  2. If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.

MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root

Posted: 05 Oct 2013 11:25 AM PDT

I'm having serious problems with a MySQL 5.6 instance on a Mac Server.

We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the /data directory and the my.cnf file as well. However, when setting an init_file to restore the previous password and its permissions. So we created a text file with this content:

UPDATE mysql.user SET Password=PASSWORD('myOriginalPassword') WHERE User='root';  FLUSH PRIVILEGES;

We have double checked the init_file permissions (we even gave it a chmod 777, to make sure it worked), but something is not working. When we run mysqld_safe --init_file=/usr/local/mysql/bin/init_file it tries to start the server, but then it shuts itself down. The log is as follows:

130308 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data  2013-03-08 13:23:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).  2013-03-08 13:23:53 18979 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive  2013-03-08 13:23:53 18979 [Note] Plugin 'FEDERATED' is disabled.  2013-03-08 13:23:53 18979 [Note] InnoDB: The InnoDB memory heap is disabled  2013-03-08 13:23:53 18979 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins  2013-03-08 13:23:53 18979 [Note] InnoDB: Compressed tables use zlib 1.2.3  2013-03-08 13:23:53 18979 [Note] InnoDB: CPU does not support crc32 instructions  2013-03-08 13:23:53 18979 [Note] InnoDB: Initializing buffer pool, size = 128.0M  2013-03-08 13:23:53 18979 [Note] InnoDB: Completed initialization of buffer pool  2013-03-08 13:23:53 18979 [Note] InnoDB: Highest supported file format is Barracuda.  2013-03-08 13:23:53 18979 [Note] InnoDB: 128 rollback segment(s) are active.  2013-03-08 13:23:53 18979 [Note] InnoDB: Waiting for purge to start  2013-03-08 13:23:53 18979 [Note] InnoDB: 1.2.10 started; log sequence number 19440939  2013-03-08 13:23:53 18979 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'  2013-03-08 13:23:53 18979 [ERROR] Aborting    2013-03-08 13:23:53 18979 [Note] Binlog end  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'partition'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_CONFIG'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DELETED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INSERTED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_METRICS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCKS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_TRX'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'InnoDB'  2013-03-08 13:23:53 18979 [Note] InnoDB: FTS optimize thread exiting.  2013-03-08 13:23:53 18979 [Note] InnoDB: Starting shutdown...  2013-03-08 13:23:54 18979 [Note] InnoDB: Shutdown completed; log sequence number 19440949  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'BLACKHOLE'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'ARCHIVE'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MRG_MYISAM'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MyISAM'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MEMORY'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'CSV'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'sha256_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_old_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_native_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'binlog'  2013-03-08 13:23:54 18979 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete    130308 13:23:54 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.myServerName.com.pid ended

The problem may definitely come from the [Error] lines, but as we haven't used the --skip-locking, it's just confusing. If we try to log into mysql using mysql -r -p and set the password we used, it can't connect to the server, as it is not started. Any ideas where this problem may come from?

PostgreSQL replication for archiving

Posted: 05 Oct 2013 09:25 AM PDT

I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.

I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work.

Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either.

Thanks

Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS?

Posted: 05 Oct 2013 11:11 AM PDT

Is it possible to connect to the DAC using SSMS? If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message:

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

How do I restore a plain text postgres .backup file

Posted: 04 Oct 2013 11:31 PM PDT

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db back if it can be restored?
Posted by at No comments:

[MS SQL Server] Total/Target memory

[MS SQL Server] Total/Target memory

Total/Target memory

Posted: 04 Oct 2013 08:54 PM PDT

Hi AllI am troubleshooting what I think is memory pressure on my SQL Server - SQL 2008R2 Enterprise (64bit)Using the SQLServer:Memory Manager counter, my target server memory is higher than my total server memory. The confusing part is that my Page Life Expectancy is 1022543 which as I understand, is decent. Any ideas?
Posted by at No comments:

[SQL 2012] Looking for a faster count than count(*) for my sp.

[SQL 2012] Looking for a faster count than count(*) for my sp.

Looking for a faster count than count(*) for my sp.

Posted: 04 Oct 2013 05:09 AM PDT

I have the following sp which is being slowed down by the following count statement:-(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM ( I have tried:--(SELECT-- Total_Rows= SUM(st.row_count)--FROM-- sys.dm_db_partition_stats st--WHERE-- object_name(object_id) = 'EMPLOYMENT_OPPORTUNITIES_CTE' AND (index_id < 2))-- as TotalCount FROM ( which is very fast, but does not give me a TotalCount value.I have also tried: (SELECT rowcntFROM sys.sysindexesWHERE id = OBJECT_ID('EMPLOYMENT_OPPORTUNITIES_CTE') AND (indid = 0 OR indid = 1)) as TotalCount FROM ( which is also very fast, but still no TotalCount value.Am i stuck with count(*), or is there a faster way to do it, in this situation?ThanksUSE [JobPortal9_10_13]GO/**********/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ============================================= -- -- Description: <Description,[Get All Jobs Searched Structured SQL],> -- -- ============================================= ALTER Procedure [dbo].[zianGetAllJobsSearched10_1_13FAST] -- Add the parameters for the stored procedure here @Title varchar(250), @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int, @fromRec int, @toRec int, @SortType VARCHAR(50), @SortOrder VARCHAR(10) WITH RECOMPILE AS DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));IF (@Title = '') SET @Title = NULL;WITH EMPLOYMENT_OPPORTUNITIES_CTE(id,title,contactperson,lastmodified,description,workexperiencetypeid, workexperience,jobtypeid,AcademicExperienceTypeId, industryid,industryid2,industryid3,salaryminid,salarymaxid, --jobTitle, city,state, PostalCode,positions,lastmodified2) --,deadline)AS( SELECT e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid, isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3, salaryminid,salarymaxid, --isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle, isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city, isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state, isnull((select comp.[PostalCode] from Companys comp where comp.Id=c.id),'') as PostalCode, positions, substring(cast(e.LastModified as varchar(20)),1,12) as lastmodified2 --ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber from EmploymentOpportunities e With (nolock) --left outer join companys c on e.officeid=c.id --inner join companys c on e.officeid=c.id inner join companys c on e.companyid=c.id where (@Title IS NULL or title = @Title) and (@Industry = 0 OR e.industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate --and e.LastModified >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + '''' --and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) and (@State = 0 OR c.RegionId = @State) and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary) )SELECT id,title as Title,contactperson as ContactPerson,lastmodified,description as Description,workexperience,jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,salaryminid as salary,salarymaxid as maxsalary,--jobtitle as jobTitle,city + ', ' + state + ', ' + PostalCode as Location,positions as Positions,--deadline,rownumber as RowNumber,--(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM ( --(SELECT-- Total_Rows= SUM(st.row_count)--FROM-- sys.dm_db_partition_stats st--WHERE-- object_name(object_id) = 'EMPLOYMENT_OPPORTUNITIES_CTE' AND (index_id < 2))-- as TotalCount FROM ( (SELECT rowcntFROM sys.sysindexesWHERE id = OBJECT_ID('EMPLOYMENT_OPPORTUNITIES_CTE') AND (indid = 0 OR indid = 1)) as TotalCount FROM ( SELECT id,title,contactperson,lastmodified,description,workexperience,jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,--jobtitle,city,state,PostalCode,--Location,positions,--deadline,ROW_NUMBER() OVER (ORDER BY LastModified DESC ) RowNumber FROM EMPLOYMENT_OPPORTUNITIES_CTE ) p1WHERE RowNumber BETWEEN @fromRec AND @toRec ORDER BY CASE WHEN @SortType = 'LastModified' AND @SortOrder = 'DESC' THEN LastModified END DESC, CASE WHEN @SortType = 'LastModified' AND @SortOrder != 'DESC' THEN LastModified END, CASE WHEN @SortType = 'City' AND @SortOrder = 'DESC' THEN City END DESC, CASE WHEN @SortType = 'City' AND @SortOrder != 'DESC' THEN City END, CASE WHEN @SortType = 'State' AND @SortOrder = 'DESC' THEN State END DESC, CASE WHEN @SortType = 'State' AND @SortOrder != 'DESC' THEN State END, CASE WHEN @SortType = 'Title' AND @SortOrder = 'DESC' THEN Title END DESC, CASE WHEN @SortType = 'Title' AND @SortOrder != 'DESC' THEN Title END OPTION(Maxdop 8)

Agents in SQL Server

Posted: 04 Oct 2013 11:25 PM PDT

Hi all,What all agents are available in SQL Server 2012 and is there any agent added / deprecated from earlier versions !!!Thanks.

Determine the license of an existing SQL Server 2012 installation

Posted: 27 Nov 2012 11:56 PM PST

Out product is based on SQL Server 2012 and comes with a core license or with a server+CAL license (based on the customers order). We want to offer our customers the possibility to exchange the SQL Server 2012 instance on which our product is built up (uninstall server+CAL licensed instance and install core licensed instance OR using their own existing SQL Server instance).Therefore I have to find out a way of determining wether an existing SQL Server 2012 installation uses a core license or a server+CAL license. This information is needed for correct user management in our extensions of SQL Server 2012, so the mechanism for getting the information needs to be automatable.Is there any way of getting that information (apart from asking the cutomers IT guy)? Thanks a lot!
Posted by at No comments:

[T-SQL] Create loop to send out emails

[T-SQL] Create loop to send out emails

Create loop to send out emails

Posted: 04 Oct 2013 06:23 AM PDT

What I need to do is create a loop that loops through the data to produce an email to send out. the following is sample data. I need to do it in a way to send one email to a person istead of in the case below up to 3. I want to sumarize the informaiton then send out email. I could do with a cursor easy but I am dealing with thousands of records and a cursor would have negative performance on tables. My attempt would be to pick the issuedID first then put in a cursor and loop around the records conatating to a temp table then selecting from here. Just wna to know if there is an easier way to do what I want. ThanksISSUEDID EMAIL OVERDUE DtISsued ST ITem3631 john.doe@mail 168 2013/04/19 DG5N4G3 DELL PRECISION T35004324 john.doe2@mail 38 2013/08/27 CWJ0181145Y EPSON PROJECTOR EMP 504413 hjane.doe@mail 161 2013/04/26 JDKXVV4 DELL PRECISION T35004413 hjane.doe@mail 161 2013/04/26 2Z1BLN5 DELL LATITUDE D6304413 hjane.doe@mail 161 2013/04/26 10Z2VJ6 DELL LATITUDE E640013581 henry.doe@mail 246 2013/01/31 125R4L4 DELL LATITUDE E6400

JOIN issue

Posted: 04 Oct 2013 02:20 AM PDT

I am having a join issue because of duplicate value in my join keys. I cannot change the data source or the structure of tables. So how can I get my required output as described below.[code="sql"]CREATE TABLE Table1( Id INT, Name VARCHAR(50));CREATE TABLE Table2( Name VARCHAR(50), Code VARCHAR(10));INSERT INTO Table1 VALUES (1, 'Pen');INSERT INTO Table1 VALUES (2, 'Pen');INSERT INTO Table2 VALUES ('Pen', 'p1');INSERT INTO Table2 VALUES ('Pen', 'p2');[/code]Here is my current query[code="sql"]SELECT DISTINCT a.Id, a.Name, b.CodeFROM Table1 a INNER JOIN Table2 b ON a.Name = b.Name[/code]Here is the current outputId Name Code1 Pen p11 Pen p22 Pen p12 Pen p2But here is what i wantId Name Code1 Pen p12 Pen p2So basically i want to join the first record from table1 to first matching record in table2, and second record (with same name) from table1 should match to the second record (with same name) from table2 and so on.

Group on predominant fuzzy time

Posted: 04 Oct 2013 03:22 AM PDT

Ok, this will get you started with sample data:[code="sql"]create table #groupings (ID int,EventName varchar(255), Venue varchar(255),EventDate datetime)insert into #groupings values (1,'Football','Arena','2013-10-04 09:58:42.670'), (2,'Football','Arena','2013-10-04 09:58:42.670'), (3,'Football','Arena','2013-10-04 10:45:42.670'), (4,'Football','Arena','2013-10-04 09:58:42.670'), (1,'Soccer','Field','2013-10-04 09:58:42.670'), (2,'Soccer','Field','2013-10-04 09:38:42.670'), (3,'Soccer','Field','2013-10-04 09:58:42.670'), (4,'Soccer','Field','2013-10-04 09:28:42.670')select min(EventName) EventName, Venue, EventDate, stuff((select ',' + cast(ID as varchar) from #groupings where Venue = g.venue and Eventdate = g.eventdate for xml path ('')),1,1,'') IDsfrom #groupings ggroup by Venue, EventDateorder by venue, eventdate drop table #groupings[/code]This gives me:[quote]EventName Venue EventDate IDsFootball Arena 2013-10-04 09:58:42.670 1,2,4Football Arena 2013-10-04 10:45:42.670 3Soccer Field 2013-10-04 09:28:42.670 4Soccer Field 2013-10-04 09:38:42.670 2Soccer Field 2013-10-04 09:58:42.670 1,3[/quote]and what I need is:[quote]EventName Venue EventDate IDsFootball Arena 2013-10-04 09:58:42.670 1,2,3,4Soccer Field 2013-10-04 09:58:42.670 1,2,3,4[/quote]I know, group on EventName and Venue instead of time but that's not possible as the Event names in my real life data are all different and I have no option but to group on Venue and DateTime (thus the min(EventName) to homogenize them).What I don't know how to do is how to do something like:group by Venue, fuzzy-time(EventDate +/- an hour). Oh, and if I really want the data to be correct I need the incorrect times to yield to the majority. If there are two identical times and one unique time I need the result to be the time the "majority" agrees on.Ideas?Erin
Posted by at No comments:

[SQL Server 2008 issues] Methods and classes are not coming by default when I write code in script task(SSIS)

[SQL Server 2008 issues] Methods and classes are not coming by default when I write code in script task(SSIS)

Methods and classes are not coming by default when I write code in script task(SSIS)

Posted: 04 Oct 2013 05:54 PM PDT

Hi All,Methods and classes are not coming by default when I write code in script task,I am using 2008 SQL server BIDS.Could you please what its need to be done from my end?Thanks

MSDB.mdb file is 50GB

Posted: 04 Oct 2013 04:17 PM PDT

When I run the queryhttp://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-dit only shows the total space of 100 MB.

To enable server proxy account

Posted: 04 Oct 2013 03:53 PM PDT

Hi in sql server properties there is a security tab.in that there is option of enabling server proxy account,is it safe to enable in production environment.Fist of all what its use.Second can it enabled for sql login (i mean local login and not domain/window).Or it compulsory needs windows/domain loginif suppose it its done is there any security hazard

doubt about remote connection in sql server

Posted: 04 Oct 2013 03:30 PM PDT

Hi i a have doubt about remote connection in sql serve.Presently its is enabled suppose i disabled it through sp_configure statement .what would happeni have 2 casesIn one case there is serverA where this option has been disabled throught sp_congigure now there is local workstation(assume it itself is server) from where i have to connect serverA,is it possible to connect to serverA.Secound i take remote through mstsc then is it possible to connect to serverA

to know more about sql server in bulid default trace

Posted: 04 Oct 2013 03:14 PM PDT

Hi after installation of sql server there is inbulid trace which is enabled automatically.Is it very helpfull.or should i disabled it get another trace that are avaliable on net.second this trace file are created in at default location in log folder where error log is stored.Is there any way to move this trace file to other location.i donot want to move error log file Thanks and regardsAnoop Pandey

Error Message: BACKUP failed to complete the command BACKUP LOG msdb

Posted: 30 Sep 2013 02:20 AM PDT

Hi,I am getting following error message daily into my error log.I am running FULL Backup, Diff Backup an T-Log Backup.This message is every morning early I see and looks like during same time my T-log backup job also running.I have also checked that MSDB is in SIMPLE recovery mode and it's not part of my T-Log back up, it's only part of FULL BAckup - weekly.ERROR:BACKUP failed to complete the command BACKUP LOG msdb. Check the backup application log for detailed messages.

t-sql to create simple bar graph image file to be inserted into email

Posted: 04 Oct 2013 05:33 AM PDT

We want to be able to use t-sql to create a simple bar graph image file, to be inserted into an email. I know how to use sp_send_dbmail, but don't really know where to start for the bar graph. So I guess there are maybe 3 different parts to this? - creating a graph, save the graph as image file, encode the image to insert inline into an email. Or let me know if you think I can skip from step 1 right to step 3.Any links to good articles on how to do this? I'm wondering now if it'd be simpler to find out how to use Excel or Access VBA to create and encode the graph to an email...

SQLIO Test file

Posted: 04 Oct 2013 10:00 AM PDT

I used 'fsutil.exe file createnew' to create big testing data file that is 128GB so that it will out number the SAN controller cache. My concern is that is the data file contains all zeros? If so, my array controller is smart enough to detect it and still cache the data. How you created your testing data file in a SQLIO test?ThanksClare

Clustered index behavior with computed columns

Posted: 04 Oct 2013 08:10 AM PDT

Hi all, I was working with an idea for searching through IP ranges efficiently and ran into a problem with building out the indexes required for the table. Here's the general structure:[img]http://i.imgur.com/t3r6lP1.png[/img]After INSERT INTO'ing the base table (red, blue columns)I've been using the following ALTER command to add into the table definition the spatial data I need.[code="sql"] ALTER TABLE Junk..foo ADD geo_range AS geometry::STGeomFromText('LINESTRING('+CONVERT(VARCHAR(20),IPStartNumber)+' 0,'+CONVERT(VARCHAR(20),IPEndNumber)+' 0)',0 ) [/code]As you can see, it's fairly straightforward-- I just want the column to store a 2-D geometric representation of the numbers in the blue column. After running that alter command (the entire table is un-indexed at this point) SELECT * works and returns the expected data.After creating a clustered index on the red columns however, the computed column breaks. It gives the error that 2 distinct points were not supplied for the LINESTRING function.Any thoughts as to why this might be occurring? I know the creation of a clustered index rearranges the data, but why would that affect a column that is built by looking at the values of 2 un-indexed columns? Moreover, why would it affect the syntax of a command already in place in the table definition?Thanks in advance!

Optimizing Update Task in SSIS

Posted: 04 Oct 2013 06:20 AM PDT

I have query but it takes 10 min to update all the routes any one how to optimized itupdate cwlocal.adhoc_route a set route_id = (select trim(route_no) from cwlocal.routes b where b.trip_no = a.trip_number) where EXISTS (select 1 from cwlocal.routes b where b.trip_no = a.trip_number) and to_char(a.filename_date,'yyyyMMdd') = ?RegardsShaun

Require Template !!!

Posted: 03 Oct 2013 11:03 PM PDT

We are starting a New campaign for which we need DBA checklist & Templates for SQL SERVER 2008.. all required checklist DBA uses...which should include Maintenance tasks etc.. etc.. everything which is required.. Please suggest

deadlock graph I cant see teh whole query

Posted: 04 Oct 2013 02:27 AM PDT

HI,I am running SQL profile trace capturing the dead lock graph , when I hover over either SQL statements I can not see the whole query..Please help

Help with Pivot (or Unpivot)

Posted: 03 Oct 2013 07:53 PM PDT

I originally posted this a couple of weeks ago, and (very unusually) got no replies. It has 'Excel' in the title which may have put some people off. , however it is not specifically an Excel issue, as the data is imported to SQL Server. Its just a rather complex pivot/unpivot.See [url]http://www.sqlservercentral.com/Forums/Topic1495433-391-1.aspx?Update=1[/url]Any help appreciated before I go and write some possibly unnecessary CLR thing

IN statement in a stored procedure

Posted: 04 Oct 2013 01:47 AM PDT

Hi All,I have the following stored procedure:CREATE PROCEDURE dbo.ScoreHistory @StartDate int, @EndDate int, @membercode nvarchar(1), @Source nvarchar(2)AS SELECT DISTINCT(S.Score) AS Score, COUNT(U.ScoreID) AS Count FROM UPSELL U INNER JOIN ScoreMap S ON S.ScoreID = U.ScoreID WHERE SEARCHDATE BETWEEN @StartDate AND @EndDate AND membercode = @membercode AND PubCode = @Source GROUP BY S.ScoreWhat I need to be able to do is change the above to use the IN() statement on the variables @membercode and @Source. Any help will be appreciated.EO

SQL Server 2005 - 2008 upgrade question

Posted: 04 Oct 2013 12:00 AM PDT

Hello,This is kind of a basic question, but I'll ask anyway lest I look bad in front of my boss....I have to upgrade a 2005 SQL box to 2008. I have the SQL CD, but I have to install it from my local PC. Is there a way to run the install process from a local PC but actually install SQL on a remote box using the CD?Thanks in advance,Dave

Is there a short cut (Like 'SELECT *') for selecting all but a few fields in a table??

Posted: 03 Oct 2013 07:58 PM PDT

Hello,I have a table of like more than 50 columns, and my problem here is that I want ALL but 2 or 3 column names in my SELECT clause! Is there any way to do this without having to write all the field names in the SELECT clause?What I mean is just as we use '*' to display all the columns, is there any such way like 'SELECT * EXCEPT (Column1,Column2) ' (Just an example!) to do this??

More than 300 SPID sleeping status?

Posted: 03 Oct 2013 11:50 PM PDT

Hi..I have verified thru SP_WHO2 system stored procedure for checking server session process.. It shows 300 SPID sleping status and AWITING COMMAND..Generally sleeing spid is ideal connection also not using any transaction..and wasting CPU and memory resource, also application connection pooling not get it clear by automatically.how can we clear the sleeping SIPD?Is it problem in SQL Instance?how can we identified these connections are reusing or not?Thanksananda

server hostname / alias

Posted: 03 Oct 2013 10:41 PM PDT

We have a new SQL 2008 server. We'd like to refer to it as SERVERNAME. However, the server group insists on naming it NAMINGCONVENTION.We can make an alias in DNS, so that SERVERNAME resolves to the server's IP address.Can we then connect to SQL Server as SERVERNAME\MSSQLSERVER, or will we need to use NAMINGCONVENTION\MSSQLSERVER ?Does SQL Server need to know what the alias is?Thanks....

Create Pivot , Exporting the result into flat file with Column Header

Posted: 03 Oct 2013 09:56 PM PDT

Dear All,I am trying to create pivot of some table.some how i managed to create pivot. Now i am exporting the resultant data into flat file. while doing so i am unable to retrieve column headers since while creating pivot column headers are dynamic.I am using BCP command to export the data into txt.1. Select * from Table : giving me only data not column2. SELECT [Column 1], [Column 2].... ---> I cant use this query since my column are dynamic and that depends on the data i have in my table.How to get dynamic column headers into flat file???? Please help me with this.ThanksRegards,Deepika

t-sql question

Posted: 03 Oct 2013 08:30 PM PDT

Hi,I'm developing a stored procedure (the proc below).When I try to print the @str2 it always returns '' but I pass a value to the @ano variable.Can someone help?ThanksALTER PROCEDURE sp_RetornaCTBDesc (@ANO VARCHAR(5) = NULL, @MES VARCHAR (2) = NULL, @DIA VARCHAR (10) = NULL, @STATUS VARCHAR(2) = NULL, @PROVINCIA VARCHAR(3) = NULL, @SERVICOFISCAL VARCHAR(5) = NULL, @METODOT VARCHAR(6) = NULL, @OPERACAO VARCHAR (5) = NULL)ASBEGIN DECLARE @DB AS VARCHAR(50) DECLARE @STR1 AS VARCHAR(1000) DECLARE @STR2 AS VARCHAR(1000) DECLARE @STR3 AS VARCHAR(1000) DECLARE @STR4 AS VARCHAR(1000) DECLARE @STR5 AS VARCHAR(1000) DECLARE @OPT AS VARCHAR (20) SET @DB = (SELECT CONFIG_VALUE from TBL_BASE_CONFIG WHERE CONFIG_PARAM ='SGCT_BD') IF @OPERACAO = 'INSERIDOS' SET @OPT ='DT_INICIO' ELSE BEGIN IF @OPERACAO = 'INSERIDOS' SET @OPT ='DT_ALTERACAO' ELSE SET @OPT ='CESSADOS' END SET @STR1 = ('SELECT COD_PROVINCIA, COD_MET_TRIBUTARIO, NIF+ISNULL(FILIAL_NUMBER,'''') NIF, NOME, STATUS FROM '+@DB+'.DBO.CONTRIBUINTES ') --PRINT @STR1 SET @STR2 =('SELECT CASE WHEN ('+@ANO+'=NULL AND '+@MES+'=NULL AND '+@DIA+'=NULL) THEN ''UI'' WHEN '+@DIA+' <> NULL THEN ''WHERE '''+@OPERACAO+ '''= ''' + @DIA +''''' WHEN '+@MES+' <> NULL THEN ''WHERE MONTH('+@OPERACAO+ ')' + '=' + @MES + ' AND '+' YEAR('+@OPERACAO+')'+' = ' +@ANO+''' ELSE ''WHERE YEAR('+@OPERACAO+ ')'+' = ' + @ANO +''' END') PRINT @STR2

2008 SQL replication

Posted: 25 Sep 2013 02:48 AM PDT

I have a 2008 SQL server i need to replicate for a reporting server on a Nightly basis. I had the replication working originally but had to break it for a software upgrade. The DB has grown in size from 40 GB to 150 GB ( a lot of imports .When i tried to re-enable snapshot replication the Publisher takes around 40 Minutes the subscriber I let run for over 12 hrs and it never completed. Currently i am doing a manual backup From the publisher and then a manual restoration to the subscriber. When the DB is compressed the backup is 28 GB and transfers to the subscriber in less then 5 minutes backup. Would it be better to do a transnational replication ? Is there ways to have SQL only update the tables that changed ? When all of our imports are done i expect the Db to be approximately 250 GB
Posted by at No comments:

Friday, October 4, 2013

[SQL Server] Finding and picking from Duplicates...

[SQL Server] Finding and picking from Duplicates...

Finding and picking from Duplicates...

Posted: 04 Oct 2013 04:31 AM PDT

This should be simple but I' having a hard time wrapping my head around this one late on a friday afternoon.I have a table containing people and except for one or two fields, the entire record could be a duplicate and should be treated as so. For example:FirstName, LastName, Address, City, State, SSN, SomeNumber***************************************************John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 001John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 004John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007How can I write a select statement to just get the most recently created record like:John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007???

SQL Error message - explanation

Posted: 03 Oct 2013 11:26 PM PDT

What does this error message mean when a application is connected to a SQL database?'Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index'Thanks
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog