Friday, September 6, 2013

[how to] Query_cache doesn't work with join

[how to] Query_cache doesn't work with join

Query_cache doesn't work with join

Posted: 06 Sep 2013 07:13 PM PDT

I have a simple join query, but for some reason query_cache won't cache it! 

SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id  WHERE approve =1 AND allow_main =1 ORDER BY FIXED DESC , DATE DESC  LIMIT 7 , 7;

without the JOIN post_plus ON ( post.id = post_plus.news_id ) it's working.

query_cache is on

| query_cache_limit              | 10485760             |  | query_cache_min_res_unit       | 4096                 |  | query_cache_size               | 536870912            |  | query_cache_strip_comments     | OFF                  |  | query_cache_type               | ON                   |  | query_cache_wlock_invalidate   | OFF                  |

Server version: 5.6.12-56 Percona Server (GPL), Release rc60.4, Revision 393

both tables are innodb with utf8 charset

SSRS switch statement

Posted: 06 Sep 2013 04:17 PM PDT

I have a report with a variable number of columns. I can hide columns with the ismissing function based on an expression like Iif(Fields!Site.ismissing, true, false). However, the hidden columns still show when it's exported to Excel and the main purpose of this report is to be mailed out monthly via SharePoint.

My thought was to create a multivalue column whose value depends on a switch statement:

=Switch(  Fields!State.IsMissing = true, Fields!Region.Value    , Fields!Site.IsMissing = true, Fields!State.Value    , Fields!Site.IsMissing = False, Fields!Site.Value)

The idea is:

  • if the state is missing show the region field
  • if the site is missing show the state field
  • if the site is present show the site field

It works if I run the report at the maximum drill down, which is to say if I run it for a specific state the sites in the state show. But, if I go up levels in the hierarchy I get #error returned. Any help would be appreciated.

Calculating the number of times a group occures

Posted: 06 Sep 2013 04:17 PM PDT

I'm trying to figure out how many times in my database a particular grouping has occurred. I have two relevant tables.

mysql> describe logins;  +-------------+-------------+------+-----+---------------------+----------------+  | Field       | Type        | Null | Key | Default             | Extra          |  +-------------+-------------+------+-----+---------------------+----------------+  | id          | int(11)     | NO   | PRI | NULL                | auto_increment |  | username    | varchar(10) | NO   | MUL |                     |                |  | login_time  | datetime    | NO   | MUL | 0000-00-00 00:00:00 |                |  | logout_time | datetime    | YES  |     | NULL                |                |  | duration    | int(11)     | YES  |     | NULL                |                |  | location    | varchar(15) | YES  | MUL | NULL                |                |  +-------------+-------------+------+-----+---------------------+----------------+

and

mysql> describe login_apps;  +-------------+-------------+------+-----+---------+----------------+  | Field       | Type        | Null | Key | Default | Extra          |  +-------------+-------------+------+-----+---------+----------------+  | id          | int(11)     | NO   | PRI | NULL    | auto_increment |  | sid         | int(11)     | YES  |     | NULL    |                |  | programName | varchar(63) | YES  |     | NULL    |                |  | duration    | int(11)     | YES  |     | NULL    |                |  +-------------+-------------+------+-----+---------+----------------+

Lets say I want to know what applications were used in a given session. I can use:

mysql> select distinct programName from login_apps where sid="35" and programName!="NULL";  +--------------------+  | programName        |  +--------------------+  | Acrobat  Distiller |  | FireFox            |  | Minitab 16         |  +--------------------+

to get the distinct program names, but I'm interesting in how often particular groups of applications are used in a given session. So essentially, how often are these three applications grouped together. Is there a way that can get me results in the form of 

application x-y-z, 12  application x-z, 9  application y-z, 7

mysql doesnt have write access in data dir?

Posted: 06 Sep 2013 03:16 PM PDT

I am running a MySQL on my Windows 7 64 bit development machine at work and I can't seem to get around a database corruption issue. In my unit tests it is telling me that the storage engine (which is innodb) is issuing an error message of -1.

In my mysql error log I am reading a log message that says

130906 13:31:04  InnoDB: Operating system error number 5 in a file operation.  InnoDB: The error means mysqld does not have the access rights to  InnoDB: the directory. It may also be you have created a subdirectory  InnoDB: of the same name as a data file.  InnoDB: File name .\databaseName\employee_leastfavoriterealnumber.ibd  InnoDB: File operation call: 'create'.  130906 13:31:04  InnoDB: Error creating file '.\databaseName\employee_leastfavoriterealnumber.ibd'.  130906 13:31:04  InnoDB: Operating system error number 5 in a file operation.  InnoDB: The error means mysqld does not have the access rights to  InnoDB: the directory. It may also be you have created a subdirectory  InnoDB: of the same name as a data file.

but looking through my file permissions it is showing that both myself and SYSTEM have full access. I have run mysqld from command line as well as from a service, restarted my computer, and even reinstalled mysql in a vain attempt to be able to fix things so I can get back to work. I changed my my.ini file to see if that would fix it.

I am at a loss as to how to fix this? Did I miss something in my my.ini file that I should change? Should I be looking for gremlins mucking around with my user permissions? Is this the beginning of the end?

Which schema is better for a shopping project?

Posted: 06 Sep 2013 03:06 PM PDT

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized properties for different products.

I'm looking for a reasonable solution for handling different types of products like cell phones and air conditioners. For example a cell phone has a property called CPU whereas an air condition doesn't have that property. Also an air condition has a property called BTU whereas a cell phone doesn't have a property like BTU.

The thing I want to do is a classic category based product system. Users will be able to create categories that contains different fields like CPU of BTU and when they want to create a product, they will be able to enter values for these fields to that product.

The SQL world, I see that many projects use a schema like this one:

products -> (id, name, brand_id, description, category_id)  categories -> (id, name)  category_fields -> (id, category_id, field_name, field_type)    product_fields -> (product_id, category_field_id, value)

However I wonder that if there is a better solution for this problem. There are two different solutions came into my mind.

products -> (id, name, brand_id, description, category_id, (json) category_values)  categories -> (id, name)  category_fields -> (id, category_id, field_name, field_type)

As you may noticed there is a column called category_values in products table. This column is JSON type (which Postgresql has) and I will index the keys that I need to search.

The other schema:

products -> (id, name, brand_id, description, category_table)  products_cellphones -> (product_id, memory, cpu, gps ...)  products_...

In this schema, when a user creates a category the system will create a new table with its fields on database in runtime. And I will create a view that generates the sql selects on the fly and executes them.

Which way should I choice, or do you have any other solutions?

MSG 666 when running an insert query on 80M-row indexed table

Posted: 06 Sep 2013 02:20 PM PDT

Strangely, my stored procedure started to receive Msg 666 for some input data.

Stored procedure fails on the last step when it tries to insert a row into a table with the following structure:

Columns:  A_Id: PK, int  B_Id: PK, FK, int  C_Id: PK, FK, int  D_Id: PK, smallint

This is essentially a table that connects all referenced entities together. I've got this design from previous developer :)

Indexes:  IX_TableName_D_id - Clustered index on D_id column  PK_TableName - Unique non-clustered index on all columns (A_Id, B_Id, C_Id, D_Id)

Fragmentation for both indexes is low (<25%). However PK_TableName fragmentation quickly grows, since the amount of operation on the table is quite intense.

Table size: 

Row count: ~80,000,000 rows

So, when I try to run a veeery simple query, for some of D_Id's I get the following message:

Msg 666. The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422223771074560. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Query example:

INSERT INTO TableName  (A_Id,B_Id,C_Id,D_id)  VALUES (1,1,1,14)

For example, when I set D_Id to some values - it fails, '14' for example. If I set D_ID to other values (1,2,3,...13, 15,16,...), the query runs fine.

I suspect there's something really bad going on with indexes... But I cannot get to the bottom of this... :( Why it fails?

Locking the table so that no transactions can complete

Posted: 06 Sep 2013 01:37 PM PDT

I am using following procedure to do some duplicate checking before inserting into master table from staging table and if there any duplicate records, these records should be moved to duplicate table instead of master table. 

CREATE  PROCEDURE `SP_MOVE_STAGING_TO_MASTER`()  BEGIN        //creating index on staging table to make sure inner join to find duplicate records bit speed up       CREATE INDEX ind_staging      ON tblstaging (SP);        //find duplicate records and insert them into duplicate table       insert INTO tblspduplicate      SELECT T2.SP,T1.FileImportedDate,T2.XYZFileName FROM  tblspmaster T1      INNER JOIN tblstaging T2 ON T1.SP=T2.SP;        //creating temporary table to hold the duplicate records Id which need to be deleted from staging table       CREATE TEMPORARY TABLE Temp (id int);         //insert into temp table duplicate records Id.        INSERT INTO Temp SELECT T2.CSN FROM  tblspmaster T1      INNER JOIN tblstaging T2 ON T1.SP=T2.SP;        //delete duplicate records from staging table       delete from tblstaging where CSN IN(      SELECT id FROM  Temp      );        //insert into master table remaining records(after deleted duplicated records)      insert INTO tblspmaster(SP,FileImportedDate,XYZFileName,XYZBatch,BatchProcessedDate,ExpiryDate,Region,FCCity)      SELECT T2.SP,T2.FileImportedDate,T2.XYZFileName,T2.XYZBatch,T2.BatchProcessedDate,T2.ExpiryDate,T2.Region,T2.FCCity       FROM  tblstaging T2;        //blank the tblstaging so next time no old records.       truncate table tblstaging;      ALTER TABLE tblstaging DROP INDEX ind_staging;  END$$  DELIMITER ;

This stored procedure is not throwing any error but is locked in an infinite state ..

It may be there's some problem with my staging table schema so my table schema as follow 

CREATE TABLE `tblstaging` (    `CSN` bigint(20) NOT NULL AUTO_INCREMENT,    `SP` varchar(50) NOT NULL,    `FileImportedDate` date NOT NULL,    `XYZFileName` varchar(50) NOT NULL,    `XYZBatch` varchar(50) NOT NULL,    `BatchProcessedDate` date NOT NULL,    `ExpiryDate` date NOT NULL,    `Region` varchar(50) NOT NULL,    `FCCity` varchar(50) NOT NULL,    `VendorID` int(11) NOT NULL,    `LocationID` int(11) NOT NULL,    PRIMARY KEY (`CSN`),    KEY `ind_staging` (`SP`)  ) ENGINE=InnoDB AUTO_INCREMENT=458746 DEFAULT CHARSET=latin1;

In the master table there will be millions of records, and at once in the staging table there will be a max of 800 k records.

Export/Import Subset of data in postgres

Posted: 06 Sep 2013 01:04 PM PDT

Using the following query, I'm able to export two tables, selecting 500 items randomly from the first:

copy (select * from (            select 1 + floor(random() * 1040000)::integer as "documentUUID"            from generate_series(1, 600) g            group by 1) r         join "Document" using ("documentUUID")        inner join "Citation" on ("Document"."citation_id" = "Citation"."citationUUID")        limit 500)   to '/tmp/data.sql';

I want to import this data into my testing database, but COPY FROM doesn't seem to be able to handle multiple table imports.

How can I make this work?

How do I set up high availability at the instance level? [on hold]

Posted: 06 Sep 2013 12:26 PM PDT

I have two database instances (Instance A, Instance B) of SQL Server, each one hosted on a separate VM using windows server 2012. Instance A has SQL Server 2008 R2, instance B has Sql server 2012. I'm not a DBA, and I'm completely lost as to how to provide high availability for both of these instances.

Ideally, I'd like someone to point me in the right direction to a solution that allows these instances to automatically fail over gracefully if we have problems, preferably at the instance level, since I have a bunch of databases in each instance. I have enough hardware to host these instances somewhere else on the network, and several gigabits of network bandwidth to get this done.

Please go ahead and assume that I am an idiot.

Mew your cat into the sun, brother!

SQL Server - How to determine ideal memory for instance?

Posted: 06 Sep 2013 11:46 AM PDT

We have some virtual machines that have X memory allocated to them. This amount of memory is somewhat random. It was allocated to the machine because that was the amount of memory the physical machine had, because of a vendor recommendation, or because we threw out a best guess as to how much memory we thought the instance would require.

I understand the more memory the better, but I would also like to avoid over allocating memory to the VM when it isn't necessary. The memory could be better utilized by another machine.

What would be the best way to determine an ideal amount of memory per instance that is actually active and being used? Are there other counters we should be looking at in addition to page life expectancy? We have instance that have PLE's of 10k+ and others that have 100k+.

Any insight is much appreciated. Thanks, Sam

How to use OR in query with priority?

Posted: 06 Sep 2013 09:56 AM PDT

In a simple query like

SELECT *   FROM table  WHERE id='x' OR id='y'

How to give a priority to get only one of the WHERE clauses?

I mean getting rows with id='x'. Only if there is no row, get row with id='y'.

In other words, do not use id='y' if there is a row for id='x'.

Change MySQL database name in SQL file

Posted: 06 Sep 2013 09:36 AM PDT

I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches can I use to tackle this issue ? I have used sed but it ends up making changes where it should not.

sed 's/CREATE DATABASE \/*!32312 IF NOT EXISTS*\/ `mydb`/`mydb2`/g' all_databases.sql > out.sql

Can SQL update script update all rows even though there is where clause

Posted: 06 Sep 2013 09:16 AM PDT

I ran this script earlier today 

update table   set message =     'XXXXXXXx'      where bundleid =41059       and bundletype = 4     and  BundleCampaign = '00000000-0000-0000-0000-000000000000'

The result said 4 rows affected, but when I checked the entire table was updated. The entire message column in that table was updated even though only 4 rows satisfy the where condition.

Has anyone ever faced this before, I cannot seem to find a way to explain what would have caused this. Any help would be great.

MySQL : Extract SQL statements from binlog

Posted: 06 Sep 2013 11:08 AM PDT

I am trying the following command to extract SQL statements from binlog for using it in another database (Amazon RDS) 

mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=NEVER > /home/ec2-user/output2.sql

But I got the following error:

ERROR: --base64-output=never specified, but binlog contains a Table_map event which must be printed in base64.

I read in the documentation that mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG. Is there any work around to extract SQL statements from binlog?

Update 1 : I run the command again with option --verbose but I did not get any return in the command line

[root@server ec2-user]# mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=DECODE-ROWS --verbose  > /home/ec2-user/output4.sql  [root@server ec2-user]#   [root@server ec2-user]#

When I browse output4.sql, I can see some valide SQL + some commented SQL like this:

#130906 14:54:55 server id 1  end_log_pos 269447893     Query   thread_id=1221718733    exec_time=0 error_code=0  SET TIMESTAMP=1378479295/*!*/;  BEGIN  /*!*/;  # at 269447893  # at 269447950  #130906 14:54:55 server id 1  end_log_pos 269447950     Table_map: `dbname`.`tablename` mapped to number 102  #130906 14:54:55 server id 1  end_log_pos 269448102     Update_rows: table id 102 flags: STMT_END_F  ### UPDATE `dbname`.`tablename`  ### WHERE  ###   @1='edited by me'  ###   @2=0  ###   @3=1373178370  ### SET  ###   @1='edited by me'  ###   @2=0  ###   @3=1378479295  # at 269448102  #130906 14:54:55 server id 1  end_log_pos 269448162     Query   thread_id=1221718733    exec_time=0 error_code=0

Update 2 : I am following the tutorial here https://engineering.gosquared.com/migrating-mysql-to-amazon-rds

I want to update Amazon RDS from the output of binlog. Currently Amazon RDS do not allow writing from binlog. I am trying to extract the SQL statments from binlog so I can update Amazon RDS. Is there any way to update Amazon RDS from binlog?

How to drop SQL Server database currently in use and in Single user mode

Posted: 06 Sep 2013 11:35 AM PDT

I have a Database on SQL Server 2008, which I want to drop. Currently it is in single user mode and it is currently in use.

select * from sys.sysprocesses

returns

Msg 924, Level 14, State 1, Line 1  Database 'G_MAIN_DE' is already open and can only have one user at a time.

and I do not know how to identify the session I have to kill.

An attempt to set it offline

ALTER DATABASE G_MAIN_DE SET OFFLINE WITH ROLLBACK IMMEDIATE

yields

Msg 5064, Level 16, State 1, Line 1  Changes to the state or options of database 'G_MAIN_DE' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.  Msg 5069, Level 16, State 1, Line 1  ALTER DATABASE statement failed.

inner joins with where clause for no values?

Posted: 06 Sep 2013 09:22 AM PDT

I have 2 tables :

Table1

  Parent(varchar) Child (varchar)

Table2

  C1(varchar)    PC (varchar)

Sample data:

enter image description here

Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , but Child values must be different from Table1.Parent's values..

I'm using below query in mysql:

select distinct(C1),Child,PC     from Table2 inner join Table1       on Table2.PC=Table1.Child    where Table1.Child not in (select Parent from Table1);

It is giving empty set, but there are values in Child which is same as in PC, but not in Parent....

Where I'm getting wrong?

PostgreSql allocate memory for each connection

Posted: 06 Sep 2013 09:10 AM PDT

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ?

I know 25% of the memory of the Server should be allocated. But how do we allocate based on the DB connections? How do we know, what is the maximum number of connections should be allocated?

Also in a multi-nodes environment can we allocate more connections for each node (in Postgres-ds.xml max connections) than what is actually allocated in the DB?

Combining multiple left join for one single line

Posted: 06 Sep 2013 10:50 AM PDT

We have a query as below.

SELECT tblSData.header,  tblEAlert.eMessage  FROM tblSData  LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID   Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc

Structure for the tables are as below:

CREATE TABLE IF NOT EXISTS `tblSData` (    `sDataID` int(11) NOT NULL AUTO_INCREMENT,     `header` varchar(255) NOT NULL,       `aID` int(5) NOT NULL,    `gFInID` int(5) NOT NULL,    `gFOutID` int(5) NOT NULL,    `gFAInID` int(5) NOT NULL,    `gFAOutID` int(5) NOT NULL,    `eAlertID` int(5) NOT NULL,    `dateTimer` datetime NOT NULL     PRIMARY KEY (`sDataID`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;      CREATE TABLE IF NOT EXISTS `tblEAlert` (    `eAlertID` int(11) NOT NULL AUTO_INCREMENT,    `sDataID` int(5) NOT NULL,    `eID` int(5) NOT NULL,    `aID` int(5) NOT NULL,    `eDateTime` datetime NOT NULL,    `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    `eMessage` varchar(255) NOT NULL,    PRIMARY KEY (`eAlertID`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;      Sample data for tblSData    1,"A1",1122,100,102,1,2,1,2013-07-13 15:30:19  2,"A3",1122,104,103,3,4,3,2013-07-13 15:45:19  3,"A4",1122,105,108,5,6,7,2013-07-13 15:55:19      Sample data for tblEAlert    1,1,1,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 1"  2,1,2,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 2"  3,2,2,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 3"  4,2,3,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 4"  5,2,4,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 5"  6,2,5,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 6"

Sample output is.

"A1","Alert 1"  "A1","Alert 2"  "A2","Alert 3"  "A2","Alert 4"  "A2","Alert 5"  "A2","Alert 6"

The problem now is how to show say for e.g. A1 all the Alert Message in one line and same goes for A2 etc. Now each alert message is on a different line.

psql, record separators in the data

Posted: 06 Sep 2013 11:20 AM PDT

I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:

psql -l -A -t

but the output shows an obvious issue: the records are separated by newlines, but also contain newlines.

$ psql -l -A -t  postgres|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|  template0|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres  postgres=CTc/postgres  template1|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres  postgres=CTc/postgres

Using the -R option I can change the record separator, but it seems like no matter what I change it to, there's the risk of that string appearing in the data. Is it possible to instead tell psql to replace the newlines in the data with something else? (and then what if that string also appears in the data?)

I'd also tried to set the record separator to a null character with such sequences as -R '\000' and -R "\0", but it doesn't seem to interpret escape sequences in the parameter at all, and just uses the literal string \000 instead.

The other option I know of to list all databases is:

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

but that requires me to give the password for the postgres user, so it's not desirable. Perhaps there's another way to get a list of the names of all databases?

Incorrect information in .frm file after a power outage?

Posted: 06 Sep 2013 09:55 AM PDT

  • CentOS release 6.3 (Final)
  • x86_64
  • mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1

SHOW SLAVE STATUS\G:

             Slave_IO_Running: Yes              Slave_SQL_Running: No                Replicate_Do_DB:             Replicate_Ignore_DB:              Replicate_Do_Table:          Replicate_Ignore_Table:         Replicate_Wild_Do_Table:     Replicate_Wild_Ignore_Table:                      Last_Errno: 1696                     Last_Error: Error executing row event: 'Failed to read from the .par file'

/var/log/mysqld.log:

130706  2:26:04 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './reportingdb/v3_ban_date.frm'  130706  2:26:04 [ERROR] Slave SQL: Error executing row event: 'Failed to read from the .par file', Error_code: 1696  130706  2:26:04 [Warning] Slave: Failed to read from the .par file Error_code: 1696  130706  2:26:04 [Warning] Slave: Incorrect information in file: './reportingdb/v3_ban_date.frm' Error_code: 1033

v3_ban_date is a partitioned table. The .par file is... empty:

# ls -l v3_ban_date.par  -rw-rw---- 1 mysql mysql 0 Jul  6 01:54 v3_ban_date.par

and this is the .frm format (hexdump -v -C v3_ban_date.frm):

|partition.... PA|  |RTITION BY RANGE|  | (TO_DAYS(dt)).(|  |PARTITION p00 VA|  |LUES LESS THAN (|  |0) ENGINE = Inno|  |DB,. PARTITION p|  |1 VALUES LESS TH|  |AN (734653) ENGI|  |NE = InnoDB,. PA|  |RTITION p02 VALU|  |ES LESS THAN (73|  |4745) ENGINE = I|  |nnoDB,. PARTITIO|  |N p031 VALUES LE|  |SS THAN (734863)|  | ENGINE = InnoDB|  |,. PARTITION p04|  | VALUES LESS THA|  |N (734959) ENGIN|  |E = InnoDB,. PAR|  |TITION p05 VALUE|  |S LESS THAN (735|  |103) ENGINE = In|  |noDB,. PARTITION|  | p06 VALUES LESS|  | THAN (735160) E|  |NGINE = InnoDB,.|  | PARTITION p07 V|  |ALUES LESS THAN |  |(735210) ENGINE |  |= InnoDB,. PARTI|  |TION MERGER_2013|  |324 VALUES LESS |  |THAN (735316) EN|  |GINE = InnoDB,. |  |PARTITION pcurre|  |nt_2013324 VALUE|  |S LESS THAN (735|  |317) ENGINE = In|  |noDB,. PARTITION|  | MERGER_201375 V|  |ALUES LESS THAN |  |(735419) ENGINE |  |= InnoDB,. PARTI|  |TION pcurrent_20|  |1375 VALUES LESS|  | THAN (735420) E|  |NGINE = InnoDB).|

The partition definitions on the first slave looks like this:

00000000  1f 00 00 00 74 00 01 07  0c 00 00 00 0c 0c 0c 0c  |....t...........|  00000010  0c 0c 0c 0c 0c 0c 0c 0c  60 00 00 00 70 30 30 00  |........`...p00.|  00000020  70 31 00 70 30 32 00 70  30 33 31 00 70 30 34 00  |p1.p02.p031.p04.|  00000030  70 30 35 00 70 30 36 00  70 30 37 00 4d 45 52 47  |p05.p06.p07.MERG|  00000040  45 52 5f 32 30 31 33 33  32 34 00 70 63 75 72 72  |ER_2013324.pcurr|  00000050  65 6e 74 5f 32 30 31 33  33 32 34 00 4d 45 52 47  |ent_2013324.MERG|  00000060  45 52 5f 32 30 31 33 38  32 38 00 70 63 75 72 72  |ER_2013828.pcurr|  00000070  65 6e 74 5f 32 30 31 33  38 32 38 00              |ent_2013828.|  0000007c

I've tried to copy this file to the second slave, then used bvi to edit the date to 201375 (corresponding with what is in the .frm file), then tried to start the second slave, but it didn't work.

So the questions are:

  1. How do I know what information is incorrect in the .frm file?
  2. Can I re-create the .par file from the one on the first slave?

PS: I know I can rebuild the second slave but I think this is an interesting challenge.

How to access a SQL Server database from other computer connected to the same workgroup?

Posted: 06 Sep 2013 10:31 AM PDT

I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is 

Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?

Please help.. Thank you..

Queryplan changes depending on filter values

Posted: 06 Sep 2013 01:17 PM PDT

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered index and one does not.

My question is: What can I do to make the DBMS use the better query plan? Or better yet, should I change my schema to something better?

Details:

  • I'm using Amazon's RDS (Server version: 5.5.31-log)
  • I executed optimize table on each table (expecting it to "rebuild" the clustered index and reset the statistics), sometimes it does not change anything, sometimes the DBMS will use worse query plans, sometimes makes it faster because it will use the clustered index.
  • explain extended followed by a show warnings did not produce anyting interesting/useful
  • I'm aware of index hinting. I tested it and the query plan used the primary index but I don't know if it always works, also, I'm using django and django's ORM does not support index hinting, so a solution that did not require it would be nice.

Tables:

-- DDL  create table api_route (     id int(11) not null auto_increment primary key,     origin_id int(11) not null,     destination_id int(11) not null,     group_id int(11) not null,     foreign key (origin_id) references api_area (id),     foreign key (destination_id) references api_area (id),     foreign key (group_id) references api_group (id)  ) engine=innodb, collate=utf8;    create table api_area (    id int(11) not null auto_increment primary key,    name varchar(50) not null  ) engine=innodb, collate=utf8;    create table api_location (    id int(11) not null auto_increment primary key,    area_id int(11),    foreign key (area_id) references api_area (id)  ) engine=innodb, collate=utf8;    create table api_locationzip (     location_ptr_id int(11) not null,     zip_start int(11) not null,     zip_end int(11) not null,     foreign key locationzip_to_location (location_ptr_id) references api_location (id),     constraint primary key using btree (zip_start, zip_end, location_ptr_id)  ) engine=innodb, collate=utf8;    create table api_locationkey (    location_ptr_id int(11) not null,    key varchar(10) not null,    foreign key locationkey_to_location (location_ptr_id) references api_location (id)  ) engine=innodb, collate=utf8;

Query:

An area has many locations, every location has either a zip or key.

select * from    api_route,    api_area origin,    api_area destination,    api_location location_origin,    api_location location_destination,    api_locationzip origin_zip,    api_locationzip destination_zip  where    api_route.group_id IN (1,2,3,...) and    -- filter origin by zip code    api_route.origin_id = origin.id and    origin.id = location_origin.area_id and    location_origin.id = origin_zip.location_ptr_id and    origin_zip.zip_start <= <zipcode_origin> and    origin_zip.zip_end >= <zipcode_origin> and    -- filter destination by zip code    api_route.destination_id = destination.id and    destination.id = location_destination.area_id and    location_destination.id = destination_zip.location_ptr_id and    destination_zip.zip_start <= <zipcode_destination> and    destination_zip.zip_end >= <zipcode_destination>  limit 100

Execution plans:

Here is an explain of a slow query (~1.6s):

*************************** 1. row ***************************             id: 1    select_type: SIMPLE          table: destination           type: index  possible_keys: PRIMARY            key: api_area_group_id_599f0627e68b9613_uniq        key_len: 156            ref: NULL           rows: 3794          Extra: Using index  *************************** 2. row ***************************             id: 1    select_type: SIMPLE          table: api_route           type: ref  possible_keys: api_route_0261d0a2,api_route_8de262d6            key: api_route_8de262d6        key_len: 4            ref: master.T6.id           rows: 9          Extra:   *************************** 3. row ***************************             id: 1    select_type: SIMPLE          table: origin           type: eq_ref  possible_keys: PRIMARY            key: PRIMARY        key_len: 4            ref: master.api_route.origin_id           rows: 1          Extra:   *************************** 4. row ***************************             id: 1    select_type: SIMPLE          table: location_origin           type: ref  possible_keys: PRIMARY,api_location_a4563695            key: api_location_a4563695        key_len: 4            ref: master.origin.id           rows: 39          Extra: Using where; Using index  *************************** 5. row ***************************             id: 1    select_type: SIMPLE          table: origin_zip           type: ref  possible_keys: PRIMARY,locationzip_to_location             key: locationzip_to_location         key_len: 4            ref: master.location_origin.id           rows: 1          Extra: Using where; Using index  *************************** 6. row ***************************             id: 1    select_type: SIMPLE          table: location_destination           type: ref  possible_keys: PRIMARY,api_location_a4563695            key: api_location_a4563695        key_len: 4            ref: master.destination.id           rows: 39          Extra: Using index  *************************** 7. row ***************************             id: 1    select_type: SIMPLE          table: destination_zip           type: ref  possible_keys: PRIMARY,locationzip_to_location             key: locationzip_to_location         key_len: 4            ref: master.location_destination.id           rows: 1          Extra: Using where; Using index  7 rows in set (0.00 sec)

Here is the explain of a fast query (~100ms):

*************************** 1. row ***************************             id: 1    select_type: SIMPLE          table: destination_zip           type: range  possible_keys: PRIMARY,locationzip_to_location             key: PRIMARY        key_len: 4            ref: NULL           rows: 119268          Extra: Using where; Using index  *************************** 2. row ***************************             id: 1    select_type: SIMPLE          table: location_destination           type: eq_ref  possible_keys: PRIMARY,api_location_a4563695            key: PRIMARY        key_len: 4            ref: master.destination_zip.location_ptr_id           rows: 1          Extra:   *************************** 3. row ***************************             id: 1    select_type: SIMPLE          table: api_route           type: ref  possible_keys: api_route_0261d0a2,api_route_8de262d6            key: api_route_8de262d6        key_len: 4            ref: master.location_destination.area_id           rows: 9          Extra:   *************************** 4. row ***************************             id: 1    select_type: SIMPLE          table: origin           type: eq_ref  possible_keys: PRIMARY            key: PRIMARY        key_len: 4            ref: master.api_route.origin_id           rows: 1          Extra:   *************************** 5. row ***************************             id: 1    select_type: SIMPLE          table: location_origin           type: ref  possible_keys: PRIMARY,api_location_a4563695            key: api_location_a4563695        key_len: 4            ref: master.origin.id           rows: 39          Extra: Using where; Using index  *************************** 6. row ***************************             id: 1    select_type: SIMPLE          table: origin_zip           type: ref  possible_keys: PRIMARY,locationzip_to_location             key: locationzip_to_location         key_len: 4            ref: master.location_origin.id           rows: 1          Extra: Using where; Using index  *************************** 7. row ***************************             id: 1    select_type: SIMPLE          table: destination           type: eq_ref  possible_keys: PRIMARY            key: PRIMARY        key_len: 4            ref: master.location_destination.area_id           rows: 1          Extra:   7 rows in set (0.00 sec)

Edit: Added create table code and full query

How to build a database that contain only the delta from yesterday

Posted: 06 Sep 2013 07:18 PM PDT

I need to know what has been changed on my database since last night. Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records 1. The new user (and to add a new column to know if it's new or update field) 2. The user that update his record (it will be nice to know which record has been update)?

BTW, I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance

Why does Log Shipping .TRN file copy just stop

Posted: 06 Sep 2013 02:18 PM PDT

I apologize in advance for a long post but I have had it up to here with this error of having to delete LS configuration and starting it over for any DB thats got this error.

I have LS setup on 3 win2k8r2 servers(pri,sec,monitor) with 100 databases transactions backed up and shipped from the primary to secondary and monitored by monitor. Back ups and copies are run every 15min and then the ones older than 24hrs are deleted. Some DBs are very active and some not so much but shipped regardless for uniformity sake(basically to make secondary server identical to primary). Some DBs are for SP2010 and majority for inhouse app.

The issue is that after all LS configs are setup, all works well for about 3 to 4 days then i go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status because the time since last copy is over 45min so no restore has occured. This seems random and the only errors i see is from an SP2010 DB(WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a reports db that gets created weekly and LS cannot just figure which the last copy to backup or to restore is. I posted here regarding that and i have yet to find a permanent solution. For my main error(time since last copy) i have not seen anything that could have caused that and i dont get any messages(even though some alert statuses have been ignored for 3 days). Anyway, I would really appreciate any input on understanding whats causing this and how i could fix it. Thanks.

Is it a bad practice to always create a transaction?

Posted: 06 Sep 2013 09:34 AM PDT

Is it a bad practice to always create a transaction?

For example, it is a good practice to create a transaction for nothing but one simple SELECT?

What is the cost of creating a transaction when it is not really necessary?

Even if you are using an isolation level like READ UNCOMMITTED, is it a bad practice?

Oracle schema import is not importing all the tables present in the schema dump file

Posted: 06 Sep 2013 10:18 AM PDT

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:  ---------------  exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log     Import Command:  ---------------  imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.

Selecting with varbinary(max) criteria (in the where clause)

Posted: 06 Sep 2013 10:58 AM PDT

Basic info

  • Database: SQL Server Express 2008 R2
  • Client: SQL Server Management Studio

Backround (skip if not interested):

A project I'm maintaining uses an ORM, which apparently stored my enum values (which inherit from Byte) into binary serialized .Net objects stored in a varbinary(max) field. I only found out this was happening after a new requirement emerged dictating my code to run under medium trust. Since the .Net binary formatter needs full trust to be called, it started crashing on the enums.

To clean the mess up I need to create migration scripts that will convert these (varbinary(max)) values back to integer values. There are only a handful of different values so it shouldn't be a big problem (I thought).

The problem:

I am able to get string representations of the blobs when selecting:

SELECT BinValue FROM MyTable where Type = 'Object';

It returns a string '0x...(an array of hexadecimal values)'.

But when I try to select on the column using copy-and-paste making sure I have the exact binary equivalent:

SELECT ItemId FROM MyTable WHERE Value=convert(varbinary(max), '0x...')

it does not return any records.

So is it possible to solve this using a client (such as Management Studio)?

If so, what would be the correct syntax?
Posted by at No comments:

[MS SQL Server] SELECT INTO running for 22 hours

[MS SQL Server] SELECT INTO running for 22 hours

SELECT INTO running for 22 hours

Posted: 06 Sep 2013 03:55 AM PDT

Some executed a SELECT INTO and it has been running for 22 hours.He was running an SSIS Package and he tried to stop it. SSIS did not respond so he killed the package so it retained the connection.It involves a large number of record. I replaced the columns with a COUNT(*) and I got an overflow on a int column.It is impacting other processes.If I kill the SPID it will go into a rollback for at least 22 hours.Any ideas on a safe course of action?

How to fix Logon trigger issue

Posted: 05 Sep 2013 07:32 AM PDT

hi,I have this trigger (see below) and I get the login failure due to trigger execution (SQL error 17892) every time I try to login. If I drop the trigger (drop trigger <trigger name> on all server) the error is gone but I need to have that trigger and also need the user's to log in.Can anyone look at the code and help me to fix the trigger so that I can use that trigger to capture login information into that table.create trigger [Tr_ServerLoginAudit]on all server for logonasbeginINSERT INTO PG_LoginAuditselect @@SPID, SYSTEM_USER, HOST_NAME(), HOST_ID(), CURRENT_TIMESTAMP, APP_NAME (), DB_NAME()ENDGOthanks

Database Backup Issue

Posted: 18 Aug 2011 02:39 AM PDT

Hello All,I'm running SQL 2008 R2. I have an intermediate issue with my backup for this one database. Some days the backup completes successfully with no problem and other days - could be 3 or 4 in a row - the backup fails with the following error:Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SMS.BAK'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.The command used to backup the database is:BACKUP DATABASE [SMS] TO DISK = N'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SMS.BAK' WITH NOFORMAT, INIT, NAME = N'SMS- Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOIs there any way to determine what that process is that is preventing the backup from occurring? Or are there any suggestions on how I can ensure that the backup device isn't causing an issue with the backup?Let me know if you have any questions.Thanks in advance for your help.Ronnie
Posted by at No comments:

[Articles] Developer Pressure

[Articles] Developer Pressure

Developer Pressure

Posted: 05 Sep 2013 11:00 PM PDT

As we ask developers to deliver software quicker, are we helping improve the quality of software? Steve Jones asks the question today.
Posted by at No comments:

[SQL 2012] Inplace Upgrade from SQL 2005 Cluster to SQL 2012 Cluster

[SQL 2012] Inplace Upgrade from SQL 2005 Cluster to SQL 2012 Cluster

Inplace Upgrade from SQL 2005 Cluster to SQL 2012 Cluster

Posted: 06 Sep 2013 01:50 AM PDT

I need to do in place upgrade from SQL 2005 EE cluster?(OS Windows 2008 R2 ) to SQL 2012 EE cluster. Can anyone suggest me the procedure, pre and post steps.

Can't update SQL 2012 instance

Posted: 05 Sep 2013 06:57 AM PDT

Hi, I have an instance of SQL Server 2012 that is not being detected by the installer. I need to apply SP1 + CU5 to the instance but when I run SP setup, it does not see the instance. It finds the other instances on the box but not the one that needs the updates.If I run from command line with /allinstances or even /instancename=xxx, it does not find it either. The instance runs fine and shows up as it should in SQL Server Configuration Manager but I can't upgrade or add features. Any suggestions would help.Thanks

performance

Posted: 05 Sep 2013 04:26 PM PDT

Hi all,Tableaid product_id brand_id1 1 12 2 13 3 24 1 15 4 2I need to select the product which is there all brand[b]USING SET OPERATOR[/b]select product from tablea WHERE brand_id=1INTERSECTselect product from tablea WHERE brand_id=2[b]USING JOIN[/b]SELECT product from tablea tJOIN tablea t1 ON t.product=t1.product and t.brand_id=1 and t2.brand_id=2which one is better in performance.or any other optimised way to achieve the samethankssathiyan

Profiling Linked Server Query

Posted: 06 Sep 2013 01:54 AM PDT

Hi allI was profiling a DB for queries against it but have noticed that when a table in the DB is accessed through a linked server this does not show in profiler. Can anyone advise on why this is or what events I need to select to ensure this is captured in future?Thanks

How to Remove an Instance from SQL Server 2012

Posted: 05 Sep 2013 04:27 PM PDT

Hi All,I am facing challenges to remove 2012 SQL Server Instances from my Systems. I am using windows 7 and service pack-1. I am going to control panel and then clicking on programs and features and then uninstall Microsoft 2012 and then click on Remove.After that I got a message which is below"The operating system on this computer does not meet the minimum requirement for SQL server 2012. For Windows 7 or Windows server R2 , Service pack 1 or later is required. "I have already Service Pack 1 is installed on my machine. I tired to Install another but it tells me service pack 1 is already installed on your system.I have also run the windows update. nothing is working for me. I have BI edition and Enterprise Edition for sql server 2012 Installed on my machine. Does it cause this problem? Then what to do?Can any one please help me? I am in a deep trouble.It is giving to install another instance But I can not remove the instances.Please help!!!
Posted by at No comments:

[T-SQL] Reindex script alteration

[T-SQL] Reindex script alteration

Reindex script alteration

Posted: 05 Sep 2013 08:10 PM PDT

Morning! I have a script which reindex's\reorgs index's depending on their fragmentation. But, it doesnt take into account the schema name, so anything other than DBO and the script fails.I have no idea how to incorporate this into the script. Below is step 1 of the process, which reorganised indexs fragmented between 5 and 40%. Thanks in advance for the help.CREATE procedure [dbo].[DefragIndexStep1] as --Create temp table for list of indexsCREATE TABLE #IndexFrag( database_id int, object_ID int, index_id int, name ntext, avg_fragmentation_In_Percent real )--Fill the table with all the indexs and fragmentation levelinsert into #IndexFrag (database_id, object_ID, index_id, name, avg_fragmentation_In_Percent)SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id WHERE ps.database_id = DB_ID() ORDER BY ps.OBJECT_ID --select * from #IndexFrag--drop table #indexfrag--Selecting all index's between 5% and 40% fragmenteddeclare @cnt intdeclare @Result nvarchar(128)declare @cmd nvarchar(500) declare @tablename nvarchar(500)declare FindFragment cursor forSELECT name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')--Cursor to go through each index which are between 5% and 40% fragmented and rebuildopen FindFragment fetch next from FindFragment into @resultwhile @@fetch_status = 0BEGINset @tablename = ( select OBJECT_NAME(object_id) FROM sys.indexes WHERE name = @Result) set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE ' --print @cmd EXEC sp_executeSQL @cmd fetch next from FindFragment into @resultENDclose FindFragment deallocate FindFragmentdrop table #IndexFragGO

get value from a function in a Stored Procedure

Posted: 05 Sep 2013 09:56 PM PDT

Hi all,I was creating a stored procedure but in this sp for some columns I need to call a function and then assign that value to a column.here is procedure that I am writing:[code="sql"]select OFC.CompanyID, PRAD.ProjectID, PRAD.ProposalID, P.ProposalNo, P.ProposalName, PRAD.DateAdded, (P.Pri_Actual_TSP + PINFO.Pri_Actual_TSP) AS TSP, P.Pri_QSP + PINFO.Pri_QSP AS QSP, DiscountPercent -- for this column I need to call a function to get the value fn_GetPercentile ( Proposal.Pri_Actual_TSP + ProposalInformation.Pri_Actual_TSP, Proposal.Pri_QSP + ProposalInformation.Pri_QSPFROM ProposalRequestApprovalDetails PRADINNER JOIN ProcessProposalApprovalDetailsCLC PDC ON PDC.ProjectID = PRAD.ProjectID AND PDC.ProposalID = PRAD.ProposalIDINNER JOIN Proposal P ON P.ProjectId = PRAD.ProjectID AND P.ProposalId = PRAD.ProposalID AND P.OfficeId = PRAD.OfficeIDINNER JOIN ProposalInformation PInfo ON PINFO.OfficeId = P.OfficeId AND PINFO.ProjectID = P.ProjectId AND PINFO.ProposalId = P.ProposalId INNER JOIN vw_Office OFC ON OFC.OfficeID = PRAD.OfficeID[/code]How can i achieve this?

Query help to show a column

Posted: 05 Sep 2013 07:41 PM PDT

Please help on this:I want to show the value of ISNULL(TCBOV.cboValueName, '') AS PhaseType,even if the TCBOV.cboValueIncId and TCBOV.cboValueSqlId, are not present. I have tried to replace the INNER JOIN with LEFT JOIN for the CboValues TCBOV table, but still have not worked.SELECT S.studyCode AS studyCode, A.activityCode AS activityCode, ISNULL(TCBOV.cboValueName, '') AS PhaseType FROM Activities A WITH(NOLOCK) INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0 INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0 INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0 INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0 -- To Get PhaseType having the extract name "TrialFieldType"-- LEFT JOIN ActivitiesAdditionalFieldsValues AS TAFV WITH(NOLOCK) ON A.activityIncId=TAFV.activityIncId AND A.activitySqlId=TAFV.activitySqlId AND TAFV.isDeleted=0x0 INNER JOIN ActivitiesAdditionalFields AS TAF WITH(NOLOCK) ON TAFV.activityAdditionalFieldIncId=TAF.activityAdditionalFieldIncId AND TAFV.activityAdditionalFieldValueSqlId=TAF.activityAdditionalFieldSqlId AND TAF.isDeleted=0x0 AND (TAF.extractName = 'TrialFieldType') INNER JOIN CboValues TCBOV ON TCBOV.cboValueIncId = TAFV.cboRecordIncId AND TCBOV.cboValueSqlId = TAFV.cboRecordSqlId AND TCBOV.isDeleted=0x0

Help Required on the given scenario

Posted: 05 Sep 2013 09:19 PM PDT

I have a table called T1 and column called C1.It contains one code like TCR-ABCDE12345.I have another table T2 ,which has 4 columns and its values as shown below.C1 C2 C3 C4 ABCD 12345 PQRST 98765What I need to get from T1 ,when I query as below isSelect C1 from T1 Query Result : TCR-PQRST98765ie select SUBSTRING(C1,5,5) from T1 - Will be replaced with its corresponding value from T2 (ABCD will be replaced with PQRST)i.e.from fifth character till 10th of C1 of T1 needs to be replaced with its corresponding value C3 from T2select SUBSTRING(C1,10,len(C1)) from T1 Will be replaced with its corresponding value from T2 (12345 will be replaced with 98765)i.e.from 10 th character of C1 of T1 needs to be replaced with its corresponding value C4 from T2.

Query help

Posted: 05 Sep 2013 07:23 PM PDT

Hi friends,When I run the below procedure am getting as CPU utilization of the server is 185% ,300%. Where am I wrong in the script..create procedure CPUasbegindeclare @a as bigint, @b as bigint, @val floatselect @a=sum(cntr_value)from sys.dm_os_performance_counterswhere object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 537003264select distinct @b=cntr_valuefrom sys.dm_os_performance_counterswhere object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 1073939712set @val=(select cast(@a as float) / cast(@b as float)*100 as [cpu])print @valdeclare @temp varchar(max)set @temp='CPU utilization of server is '+CONVERT(VARCHAR(3),LEFT(@val,3))+'%'+' Check what is running in the server'if(@val>50)BEGINExec msdb.dbo.sp_send_dbmail @recipients='isdm@plintron.com',@subject='Server utilization is high',@body=@tempENDendThanks in advance.

How to generate data in lakhs format in SQL Server 2008

Posted: 05 Sep 2013 05:37 AM PDT

Hi, I have a issue where we got to report money column in string in the format 1,00,000.00Example..Input-100000.00 My required output is 1,00,000.00Input-10000.00 My required output is 10,000.00Can anyone please help me on this.Thanks in advance.

Working with Strings

Posted: 05 Sep 2013 04:19 AM PDT

From what I can find... it looks like working with strings is more of a headace .. in SQL than Pick. How do you test for a delimiter and then pull the string apart to show Code and Description?With the data, provided below.. I would be looking to show the following:Code Desc4925208 alcohol4921598 ethanol0196532001 WaxTable (keeping it simple):CREATE TABLE [dbo].[A_Test]( [Text_Code] [varchar](254) NOT NULL, CONSTRAINT [PK_A_Test] PRIMARY KEY CLUSTERED ( [Text_Code] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOData:insert into A_Test (Text_Code)values ('alcohol ~ 4925208'),('ethanol ~ 4921598'),('01965'),('Wax ~ 32001')
Posted by at No comments:

[SQL Server 2008 issues] Moving a database log file question

[SQL Server 2008 issues] Moving a database log file question

Moving a database log file question

Posted: 05 Sep 2013 06:20 PM PDT

SQL Service was down, our storage engineer created a new LUN and then moved the log file to the new location ...Database is therefore unaccessible ...after running a Alter database script to point the log file location, The only way to get the DB online is to restart SQL Service??? this will in effect take the whole instance DB's down ...Is there another way to just get that 1 DB online???I know the sequence of events is not exactly best practice but is there a way around this scenario???

script needed

Posted: 05 Sep 2013 06:29 PM PDT

Hi ,Can any one send me the script of this scenario I would like you to develop a script that creates a SQL user and sets the password (to be set as part of script, maybe in a parameter). The script will also create schema and the schema will be bound to the sql account being created and provide read only access to the table objects Thanks.

Cumulative total column wise

Posted: 05 Sep 2013 02:12 AM PDT

I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.I have currently dataset abosulate total) and I need the one in second line: Opco Region Country Jan Feb MarAbsolute total OP1 EMEA GB 0.3 1.3 0.7Cumulative total OP1 EMEA gb 0.3 1.6 2.3RegardsMeghna

backupset table in msdb database not updating backupfile name in the column name

Posted: 05 Sep 2013 06:05 PM PDT

Hello,My SQLserver version is SQlserver 2008 R2 Sp2.Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)I just identified that if I take backups using scripts from SQLserver management studio, the backupfile name in[msdb].[dbo].[backupset] table is not getting updated.However if I execute the same backup using the maintenance plan I don't see this issue.Is that a bug? Thank you.

How to find preceding 7th date of any given date.

Posted: 05 Sep 2013 05:43 PM PDT

Hi, My requirement is,I want preceding 7th date of any given date.For example,1)Declare @AsonDate As datetime = '2013-08-31'Then my output should be '2013-08-07'2)Declare @AsonDate As datetime = '2013-09-06'Then my output should be '2013-08-07'Kindly help me!!Thanks in Advance!!

sp_UpdateStats missing in database

Posted: 05 Sep 2013 05:48 PM PDT

HiWe recently installed Lync at our company.Lync creates 4 SQL instances and I have set up maintenance tasks for these instances.Strange thing is that sp_UpdateStats does not exist in any of the databases created by the Lync installation.If I create a new database on the server, I can run sp_UpdateStats with no problem.I have not found one article out there about anyone having this problem. Strange. Can anyone help?Server OS: Windows 2012 DataCenterSQL: 2012 SP1Please, please can someone help.Thanks,TDP

Database structure question

Posted: 05 Sep 2013 03:40 PM PDT

I don't know the best way to structure my database.I have a data feed that comes in a CSV format, its financial data about 65,000 different mutual funds with daily prices. In each CSV i get, each mutual fund is listed once with the price for the current day.What i do first is i import the CSV. But then what i want is a table for each mutual fund with prices and dates inside each table. this means i have to create 65,000 tables in a separate database, then every day read from the imported CSV file and write to the tables. The problem is this ajax query takes almost an hour each time (for each imported CSV or in other words each day)The logic behind this is when i want to forecast data on an individual fund later, i can just go to that fund's table, query the data out, and do what i want with the prices for that fund over a range of time which is the whole point.The alternative approach i think is instead of creating 65,000 tables, just leave the imported CSV tables and draw data from there on demand. To do that though i would have to join together all my imported CSV with each query before grabbing fund data, because i'd want data over a course of many months even years for a particular fund.Which approach is the most efficient at runtime? The way i have it now with the 65,000 tables i have an index on the date column in each case. I'm thinking if i did the other option and just queried the imports, i'd need some sort of dynamic way to index them once they're joined so i can use the date column effectively. I would need that i think. I'd need to have them all indexed by fund name and then by date in order to efficiently grab data from a query that would literally be billions or maybe trillions of rows. Is there a dynamic way to do indexes when you join tables?Your thoughts are much appreciated. Thanks.

Maintenance Plan Help!!!!!!

Posted: 05 Sep 2013 11:10 AM PDT

Dear SQL experts,I am planning to setup some Maintenance Plan including System database backup, users databases backups and Maintenance clean up tasks.my question is:1. Should I creating one Maintenance Plan or separate such as 1 plan for system dbs and another users db? 2. Should I also create check integrity 3. How should I create Maintenance clean up tasks for all DBs?would someone plz help me with easy steps to setup sysDBs and UsersDB?Thank you

Cannot login to Analysis Services 2008 from SQL Server Management Studio

Posted: 27 Jul 2010 02:23 AM PDT

Last week I installed SQL Server 2008 developer edition with service pack 1 on windows 7 Home Premium. Before install the SQL Server, I already installed .net framework 3.5 service pack 1. But when I login to the Analysis Services, following error is coming.A connection cannot be made. Ensure that the server is running (Microsoft.AnalysisServices.AdomdClient) No Connection could be made because the target machine actively refused it 127.0.0.1:2383This is my personal laptop and I tried with local system and network service. SQL Server Browser is also running.But I can login to Database Engine and Integration Services. But Analysis Services and Reporting Services are not working. Please help me to solve this problem.

Update Stats Job causing Dropped Connections and Cursor not Declared errors

Posted: 05 Sep 2013 08:27 AM PDT

Has anyone ran across or have seen dropped connections from TIBCO and getting a 'cursor not declared' error message when running an Update Stats job.The issue started about 4 weeks ago and only occurred when our weekly maintenance job was running which 'Update Statistics' on most of our tables. The SQL job runs fine with no errors but we are dropping connections and receiving the following type of error message from the drivers.ErrorCode: BW-JDBC-100014, ErrorMessage: "JDBC error reported: (SQLState = HY000) - java.sql.SQLException: [tibcosoftwareinc][SQLServer JDBC Driver][SQLServer]The cursor was not declared."It only happens with the SQL job runs. Any suggestions?Thanks,Daniel

Duplicate column names not allowed in result sets obtained through OPENQUERY and OPENROWSET

Posted: 05 Sep 2013 07:34 AM PDT

Hi All,Please I need someone to help me with this problem. I have a table variable which I need to insert data into. The data I want to insert comes from a query for example SELECT r.decode, b.decode FROM Sales. employee. The insert is not working because there's duplicate column name in the select list.The error is this : "Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "decode" is a duplicate.Please I need helpEO

Change Data Capture as a long-term change-tracking solution

Posted: 05 Sep 2013 03:28 AM PDT

Has anyone used CDC as a long-term solution for change-tracking? It seems to me as though this was not intended as a platform to be used to capture and retain changes for longer than a few days. I'm very concerned that it would start becoming unmanageable/unsearchable before too long, unless you were regularly flushing the data out to a user table.

SQL Server Link to Oracle Inconsistent metadata problem

Posted: 03 Dec 2012 12:35 AM PST

HiI have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can query the Oracle database using OPENQUERY but when I try a simple SELECT COUNT(*) from LK_ORA..USR.TAB1 I get an error:Msg 7356, Level 16, State 1, Line 1The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LK_ORA" supplied inconsistent metadata for a column. The column "COL_CODE" (compile-time ordinal 1) of object ""USR"."TAB1"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.I've found an article that says when SQL Server retrieves metadata from Oracle in order to compile the query, Oracle reports the data type to be varchar(255), but when it actually returns the data, the data type is in fact nvarchar(255). (But it may not be called nvarchar in Oracle.)Anyone know how to get around this? I've tried various options on my linked server connection in SQL with no luck so farMany Thanks

Query to Return each field that is Not Null

Posted: 05 Sep 2013 02:57 AM PDT

Hello everyone,I have a database (third party application) that has a table with 30 or so "free fields" that can be customized for our use. I have created some forms that use these free fields and I want to create a query that would show each record where one of the free fields is not NULL. In fact I would like to show all of them so if a record had all 30 free fields as Not Null I would like to have 30 records in the dataset returned. I thought of using a For Loop of some sort but perhaps I am making this more complicated that it needs to be. Basically for every one of these free fields I am using a trigger to create a new task for a user. Any advice or suggestions are greatly appreciated.Paul

[TDE] Creating master key causes "fatal windows error"

Posted: 05 Sep 2013 02:55 AM PDT

We have a SQL Server 2008 R2 database in a very locked down (govt) environment. I am unsure what features have been disabled in Windows or SQL Server, and that is probably next on my list to continue investigation but here is the general gist of the issue:We are trying to enable transparent database encryption. In doing so we are attempting to create the master key:[code="sql"]create master key encryption by password ='PUT THE PASSWORD HERE';[/code]This appears to create the master key HOWEVER it also causes Windows to pop up a message to the gist of "Windows has encountered a Severe error and will shutdown." About 60 seconds later the system is rebooting. The windows application and system event logs don't really seem to shed any light on this issue.We are able to drop the key without crashing after the restart, however I am afraid that if this crash does occur when creating it something isn't happening correctly and I would be worried about actually encrypting each of our databases. My initial thought was something in the Windows Data Protection API that is encrypting the master key is causing the error, either because it is locked down to prevent usage or something to that affect.

Cumulative total column wise

Posted: 05 Sep 2013 02:08 AM PDT

I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.I have currently dataset abosulate total) and I need the one in second line: Opco Region Country Jan Feb MarAbsolute total OP1 EMEA GB 0.3 1.3 0.7Cumulative total OP1 EMEA gb 0.3 1.6 2.3RegardsMeghna

Problem with grouping in SSRS 2008

Posted: 05 Sep 2013 01:31 AM PDT

When I create a report in SSRS 2008 and I add a group, the group appears in the object as it should and it shows as a group in the Properties Dropdown but nothing appears in the Grouping Pane. I have tried reinstalling SQL Server and nothing changes.Joe Wolfe

xp_regread execute permission denied

Posted: 16 Sep 2011 05:18 AM PDT

Hello All,I am having the issue described here:https://connect.microsoft.com/SQLServer/feedback/details/515132/management-studio-generates-scripts-that-use-unsupported-undocumented-proceduresMore specifically the user right-clicks on a table and gets 'EXECUTE permission denied on object xp_regread, database amaster, owner dbo'The version of management studio is 2008 R2 SP1. The user is attempting to manage a 2000 SP4 database.Can someone help me analyze what the proposed solution does?When the poster says 'This is the code that needs to be modified to remove the need of xp_instance_regread' do they mean:1) something in the clients SSMS install needs to be changed; or2) something server side needs to be changedIf the former how do I do this?If the latter it looks like the only thing the code does is exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUTWouldn't xp_instance_regread only read from the registry? How does that accomplish anything?Thanks!

Knowing which tables to drop - HouseKeeping

Posted: 04 Sep 2013 08:26 PM PDT

Hi,Over the years our DWarehouse has expanded and there are a few tables on the Databases that we think are no longer required but the question is how do we know which tables these are?I could look at the table sys.dm_db_index_usage_stats to see when a user or ssis package has accessed this last and then take a guess from this which table is not required. I was then thinking of renaming this table by adding "To_be_Deleted" at the end of the table.But I'm a bit scared if this table is required by a ssis package and then something else would fail.Has anyone any good ideas or scripts that would show me a league table of some sort of which table is popular and which is not?Thanks
Posted by at No comments:

Thursday, September 5, 2013

[SQL Server] SSIS package - Send email if dates are off

[SQL Server] SSIS package - Send email if dates are off

SSIS package - Send email if dates are off

Posted: 05 Sep 2013 11:31 AM PDT

I'm looking to create an SSIS package that sends me an email if certain dates aren't where they are supposed to be within our 2008 database.After nightly system processing, 7 date fields need to be "todays" date and 2 of the date fields need to be "yesterdays" date. If all 7 don't equal "todays" date OR the other 2 fields don't equal "yesterdays" date, I want the SSIS package to send me an email.Me getting an email would be sufficient (I know how to create that part of the package) but if I could get an email with the dates, that would be even better.I'm not looking for a step by step suggestion but rather a suggestion on how I'd start this type of a package to see if I could take it from there....such as what control flow items I'd need to start out with. I already have the script to pull the dates, just not sure where to take it from there.TIA,John

case statements and UDF's

Posted: 05 Sep 2013 05:54 AM PDT

Hi there. It has been a while since being on here but I have a question. I am trying to create a table that has to be backward compatible with previous versions and it is being a pain. I need to convert and age field into an age group field. Or make a new field for it. either way works. I am trying to use a UDF because I need this to work on 6 different tables and I dont want to have to type it in all the time, also because i dont like "messy" code. here is what i have so far. [code="sql"]USE [Test]GO/****** Object: UserDefinedFunction [dbo].[AGE_GROUP] Script Date: 09/05/2013 11:40:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(MAX))RETURNS VARCHAR(MAX)BEGINRETURN CASE @STRING WHEN 0 THEN ' 0' WHEN 1 THEN ' 1' WHEN 2 THEN ' 2' WHEN 3 THEN ' 3' WHEN 4 THEN ' 4' WHEN 5 THEN ' 5' WHEN 6 THEN ' 6' WHEN 7 THEN ' 7' WHEN 8 THEN ' 8' WHEN 9 THEN ' 9' WHEN 10 THEN ' 10' WHEN 11 THEN ' 11' WHEN 12 THEN ' 12' WHEN 13 THEN ' 13' WHEN 14 THEN ' 14' WHEN 15 THEN ' 15' WHEN 16 THEN ' 16' WHEN 17 THEN ' 17' WHEN 18 THEN ' 18' WHEN 19 THEN ' 19' WHEN BETWEEN 20 AND 24 THEN '20-24' ENDEND[/code]There would be age groups like the "when age(between XX and xx) then 'xx-xx' Does anyone have any ideas on how to do this?Also it works for the 0-19 just fine.

SQL Server 2012 Activity Monitor

Posted: 05 Sep 2013 05:23 AM PDT

I opened SQL Server 2012 Activity Monitor and was trying to understand the data it was displaying. I saw a statement under "Recent Expensive Queries" and I clicked the dropdown for databases. I then opened the "Processes" section in Activity Monitor and clicked the dropdown for "Database". The database that was using the query in the "Recent Expensive Queries" section was not in the list. How is it possible for the database to be using a query, yet not in the database list in the dropdown under "Processes"?Thank you in advance,Charlie

Recalculate Wages (Reposted for SQL 2008 with additional columns)

Posted: 05 Sep 2013 01:56 AM PDT

Recalculate "Amount" Column--------------------------------------------------------------------------------Our Salaried Employees have to log in their time by department and by type of Earnings (Regular, Holiday, Sick, etc). We are on a semi monthly pay period - therefore the number of hours worked each pay period vary - but the amount of pay is exactly the same each pay period.On each employee's pay record there are 2 fields, Pay Per Period and Equivalent Hourly Rate.When the payroll is calculated the, the payroll application calculates the Amount column based on a prorated hourly rate - so the total amount of payroll is exactly the same each pay period. (The prorated hourly rate may be different if the pay period has 80 hours vs. 104 hours - as the pay should be the same.In the example below there are 5 employees each with a pay per period of $2000.The business requirements are such that if an Employee has Vacation on their timecard - the Amount for the Vacation Hours should be calculated based on the "Equivalent Hourly Rate" and the balance on a prorated rate. The total of Vacation and Regular/Sick, etc. should equal the employee's pay for pay period - accounting also for any rounding differences. Below is a sample table - the amount column needs to be recalculated whenever there is VACATION used by Employee. For the Vacation Row the Amount should be the Hours times the Equivalent Hourly Rate and the other earnings items should be based on a prorated rate. I only need to Update the values of the Amount column.For clarity sake I have added 2 additional columns - newrate and newamount. The newamount column values are the ones that I want to be replaced in the amount column. The newrate is a prorated rate that is based on number of hours worked less the vacation hours. For employees who do not have Vacation - those records should be ignored. Lastly, the round error should be resolved so the employee gets their gross wages in this example as 2000. create TABLE Payroll(EmpID int,EarningCode varchar(255),PayPerPeriod varchar (255),EQHourlyRate varchar(255),HoursWorked varchar(255),Amount varchar(255),Dept varchar (255),NewRate varchar (255),NewAmount varchar (255));Insert Into Payroll Values ( '100','Regular','2000','23.0769','52','1000','SALES','18.315','952.38');Insert Into Payroll Values ( '100','Holiday','2000','23.0769','12','230.77','SALES','18.315','219.78');Insert Into Payroll Values ( '100','Sick','2000','23.0769','12','230.77','SALES','18.315','219.78');Insert Into Payroll Values ( '100','Jury Duty','2000','23.0769','8','153.85','SALES','18.315','146.52');Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.31','SALES','23.0769','230.77');Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.3','MKT','23.0769','230.77');Insert Into Payroll Values ( '200','Regular','2000','23.0769','104','2000','ADMIN','n/a','2000');Insert Into Payroll Values ( '300','Regular','2000','23.0769','88','1692.31','ACCT','n/a','1692.31');Insert Into Payroll Values ( '300','Holiday','2000','23.0769','8','153.85','ACCT','n/a','153.85');Insert Into Payroll Values ( '300','Sick','2000','23.0769','8','153.84','ACCT','n/a','153.84');Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','HR','18.077','723.08');Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','ADMIN','18.076875','723.08');Insert Into Payroll Values ( '400','Vacation','2000','23.0769','24','461.54','HR','23.0769','553.84');Insert Into Payroll Values ( '500','Regular','2000','23.0769','66','1269.23','ACCT','18.0136708860759','1188.9');Insert Into Payroll Values ( '500','Holiday','2000','23.0769','8','153.85','ACCT','18.0136708860759','144.11');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','5','96.15','ACCT','18.0136708860759','90.07');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','10','192.31','ADMIN','23.0769','230.77');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','15','288.46','HR','23.0769','346.15');

Adding User to SQL Server

Posted: 05 Sep 2013 02:13 AM PDT

Hi,I got a scenario where I am using [b]Windows Application[/b].I want to create a SQL Server User satisfying the password policy. I have couple of questions related to it.1. How can I create such a user?2. After creation of the user, I want to store its password somewhere at a central location[don't know where? can somebody help?]. While connecting to DB, read this password from central location on the network and login to SQL Server.Please help.Thanks,Paresh

Using IIF to test for Null

Posted: 05 Sep 2013 01:56 AM PDT

This should be very easy and I am predicting a big forehead slap in just a few minutes after I post this.I am simply trying to test a field for being null in my SELECT and display a true or false condition. Neither example below works and I've crossed referenced a couple books and looked online as well. No luck. Thanks for looking! (MSSL 2008)SELECT IIF(Field1 IS NULL, 'NULL', 'NOT NULL') *** XXFROM MyTable[color="#FF0000"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'IS'.[/color]SELECT IIF(IsNothing(Field1), 'NULL', 'NOT NULL') AS XXFROM MyTable[color="#FF0000"]Msg 195, Level 15, State 10, Line 5'IsNothing' is not a recognized built-in function name.[/color]Slap in 5...4....3...
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog