Tuesday, September 24, 2013

[how to] ERROR 1166 (42000) at line 65203: Incorrect column name ''

[how to] ERROR 1166 (42000) at line 65203: Incorrect column name ''

ERROR 1166 (42000) at line 65203: Incorrect column name ''

Posted: 24 Sep 2013 08:49 PM PDT

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message:

  $ mysql -u root < mydata.sql  ERROR 1166 (42000) at line 65203: Incorrect column name ''

MySQL Version is 5.5.32-0ubuntu0.13.04.1 and this is a head from SQL file:

  $ head -n 10 mydata.sql   -- MySQL dump 10.13  Distrib 5.5.22, for Linux (x86_64)  --  -- Host: localhost    Database:   -- ------------------------------------------------------  -- Server version   5.5.22-55-log

This is a snapshot of the line that making an issue:

  $ sed -n '65200,65203p' < mydata.sql   /*!50001 SET character_set_client      = latin1 */;  /*!50001 SET character_set_results     = latin1 */;  /*!50001 SET collation_connection      = latin1_swedish_ci */;  /*!50001 CREATE ALGORITHM=UNDEFINED */

How can I resolve this issue?

MySQL 1366 Error on Replication Slave

Posted: 24 Sep 2013 06:01 PM PDT

I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs. Running MySQL 5.5.34 on Windows Server 2008 R2.

I'm trying to track down a problem that has occurred twice now on on of the pairs.

I get a 1366 error "Incorrect integer value '\x00' for column Flag at row 1" for the following call:

UPDATE mytable SET Flag = 0 where Id = 10

When I issue the show slave status command.

On the other pair I don't ever get this error when replicating.

The type of Flag is tinyint(1).

I haven't been able to identify any differences between the two pairs yet.

Ideas?

Mysterious merge replication problems creating a subscription

Posted: 24 Sep 2013 09:08 PM PDT

We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got error messages during initialization and found in the verbose error logs this problem:

Msg 1934, Level 16, State 1, Procedure DDLTrigger_Index, Line 19 INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

...fast forward to now. In experiments we found that yes it is possible to create a merge publication on the publisher and subscribe on our subscriber server, but only if using a different database. Went through this just to rule out permissions/etc and to determine it was truly the subscriber db that was the problem.

So, it seemed logical to remove replication from afflicted DB in hopes of cleaning out any buried metadata mess:

sp_removedbreplication @dbname = 'sickDB' @type = 'all'

So far so good. However now, I get the exact same error, but much earlier in the process. Specifically, when executing:

/****** Begin: Script to be run at Subscriber ******/  use [sickDB]  exec sp_addmergepullsubscription   @publisher = N'publisher',   @publication = N'publication',    @publisher_db = N'goodDB',    @subscriber_type = N'Local',     @subscription_priority = 0,      @description = N'hope this works', @sync_type = N'Automatic'

(above edited slightly).

Is there some remaining prob lurking in the metadata? The original theory on the ANSI_PADDING stuff was there were in fact some tables in the subscriber DB w/ computed columns thus that seemed related. However some flipping of ANSI_PADDING settings (and Googling) didn't really lead to a conclusive answer.

Thanks so much for reading and let me know of any other info I can provide that may help.

Oh yeah, publisher and subscriber are both SQL Server 2005.

Does it make a difference to add the DEFAULT keyword in CREATE TABLE?

Posted: 24 Sep 2013 10:12 AM PDT

The MySQL manual says:

CREATE DATABASE db_name      [[DEFAULT] CHARACTER SET charset_name]      [[DEFAULT] COLLATE collation_name]    ALTER DATABASE db_name      [[DEFAULT] CHARACTER SET charset_name]      [[DEFAULT] COLLATE collation_name]

The database character set and collation are used as default values for table definitions if the table character set and collation are not specified in CREATE TABLE statements.

Someone made a point in the comments, saying:

It's not at all clear what difference it makes to specify DEFAULT or to omit it. So if you specify DEFAULT then that charset becomes the default for tables for which you don't specify an explicit charset. But what if you omit DEFAULT? Does that prevent tables from using a different charset from the database? This should really be explained above.

Does it make any difference if the DEFAULT keyword is specified, or are CHARACTER SET and DEFAULT CHARACTER SET just exact synonyms?

dcexec login failure

Posted: 24 Sep 2013 11:23 AM PDT

I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec.

Isolating that out, I can reproduce the issue by running:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1

I run the above command from a PowerShell window that I've opened with RunAs Administrator. The account I am running the above with is a domain account that is a local administrator on the server. In addition, the account also has the sysadmin role on the SQL instance.

The error that I get is:

SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540,   Subcomponent: Generate T-SQL Package Task,   Description: An error occurred with the following error message:   "An error occurred while verifying the result set schema against the output table schema.   The data collector cannot connect to the management data warehouse. : Login failed.  The login is from an untrusted domain and cannot be used with Windows authentication."..    The master package exited with error, previous error messages should explain the cause.

That is the same error I see in the history on the SQL Server Agent job.

Note that if I execute dcexec with a bad instance name, such as: 

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1

Then, as expected, I get a different error:

Failed to get the SQL server instance id from the given instance name.

Any ideas on how to successfully login to the SQL instance with dcexec?

Max Memory settings on Active/Active SQL Server 2008 R2 Cluster

Posted: 24 Sep 2013 02:04 PM PDT

  • SQL Server 2008 R2 active/active cluster (on VMware vSphere 5.1)
  • 2 instances on each node
  • 16GB RAM per node.

The only time the instances are on the same node is when I am patching the other node.

Brent Ozar's "maximum server memory" recommendation is to leave Windows 4GB or 10%, whichever is more. http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/

Since this a cluster, how should I set the max memory on each node? Should I treat each as a standalone server? This would make sure memory on each node is not wasted. However, in a node failure, the max memory total for all 4 instances would exceed system memory of the single node. Will this cause any issues in the timeframe until we get the second node recovered? Do I need to lower the max memory setting on the 4 instances until the secondary node is recovered? Or is SQL Server smart enough to keep working (using page file if necessary).

Why does MySQL allow HAVING to use SELECT aliases?

Posted: 24 Sep 2013 05:01 PM PDT

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount  FROM Sales.Orders  GROUP BY YEAR(orderdate)   HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5  Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

  • Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
  • Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?

Understanding disk requirement to make fail over cluster

Posted: 24 Sep 2013 10:14 AM PDT

Please take a look at the following screenshot.

enter image description here

I am trying to make SQL Server 2012 Fail Over Cluster using two nodes. As you know we need to have a shared SAN which will be used by both nodes to store data but I have some confusion regarding it.

I was under the impression that we need one drive for e.g. Z for "Temp DB directory" which will be shared by both nodes to store data.

But someone told me that these will be two drives actually for each node and for these nodes it will appear as one. So I am not sure what is true. May be we are both saying same thing!?

Problem with PostgreSQL and the geqo optimizer

Posted: 24 Sep 2013 11:33 AM PDT

I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types. These are the variables I use for geqo:

SET geqo = true;  SET geqo_threshold = 2;

My queries have 8-12 FROM items involved, so the optimizer would definitely use the geqo algorithm.

I tried to add more variables to my queries to force the optimizer to choose an other plan:

SET geqo_effort = 2;  SET geqo_pool_size = 2;  SET geqo_generations = 1;

But like I said, I always get the same plan as the plan from exhaustive search.
Is there any possibility to show which algorithm (exhaustive search or geqo) the optimizer used to create the execution plan? Or are there other ways to force the optimizer to use geqo?

SQL Server 2008 Convert String to Datetime question

Posted: 24 Sep 2013 09:24 PM PDT

I have a string column in a table that display data as "CXL P/D 08/15/13"

I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type.

Cast(RIGHT(RTRIM(Trade_Date) ,8)as datetime) I'm trying this statement but it doesn't work

Conversion failed when converting date and/or time from character string.

Thank you

When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

Posted: 24 Sep 2013 02:33 PM PDT

There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option.

I believe Checksum was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

The problem involved is that we have a production database that went into production using SQL Server 2000 and has since moved to a SQL Server 2008 R2 server. Page Verify is set to None when I had been expecting it to be Torn Page Detection. Going back this amount of time we seem to think the DB was originally developed in SQL Server 7.0 then migrated to SQL Server 2000 and this may explain the observed result.

I was wondering when Torn Page Detection and Checksum became a feature of SQL Server, and how they behaves when migrated or upgraded to newer versions.

Enforce a constraint problem

Posted: 24 Sep 2013 12:54 PM PDT

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages. A phase can only have one stage that is "Current" or open at any one point in time. A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information.

Problem:

  • the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
  • You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
  • You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
  • the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
  • Is there anyway to enforce these business rules?
CREATE TABLE PHASE_FIRST_STAGE  (    PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE    STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table  );  ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01    FOREIGN KEY (PHASE_ID)     REFERENCES PHASE (ID),    FOREIGN KEY (STAGE_ID)     REFERENCES STAGE (ID));  COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';    CREATE TABLE STAGE  (    ID               NUMBER(9)           NOT NULL, --PRIMARY KEY    PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE    DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)    IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)    IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)  );    COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';  --not shown is a similar table called PHASE with a one phase => many type of stage relationship

Does TRIGGER improve the performance?

Posted: 24 Sep 2013 03:26 PM PDT

I understand that TRIGGERs are useful, as they take care of subsequent queries, but does they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...  UPDATE table2 ...  UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test  AFTER INSERT ON table1  FOR EACH ROW BEGIN  UPDATE table2 ... WHERE id=NEW.id;  UPDATE table3 ... WHERE id=NEW.id;  END

  1. Does we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?

  2. What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.

Restrict access to rows by usename

Posted: 24 Sep 2013 01:22 PM PDT

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,    PRIMARY KEY (`_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;    CREATE TABLE IF NOT EXISTS `_Address` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `owner` int(10) unsigned NOT NULL,    PRIMARY KEY (`_id`),    KEY `owner` (`owner`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;    CREATE TABLE IF NOT EXISTS `_Invoice` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `owner` int(10) unsigned NOT NULL,    `address` int(10) unsigned NOT NULL,    PRIMARY KEY (`_id`),    KEY `owner` (`owner`),    KEY `address` (`address`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS   SELECT `a`.`_id` AS `_id`        FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)       WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));    CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS   SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`      FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)      WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`    ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;      ALTER TABLE `_Invoice`    ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,    ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Managing constants changes to database

Posted: 24 Sep 2013 03:22 PM PDT

Here's the deal, I'm in charge of creating a website that will host small web apps. This apps will be photo contests, creatives sentences and other similar gibberish. The deal is, since apps we'll be constantly changing and some new ones will come up what's the best way to save the config specs for each one? Should I create new tables and fields as I need? Or should I serialize this data and put it on one field?

In this case instead of having a table apps, I was thinking having a table for each app, this way I could have a more modular way of dealing with apps, making changes more easy, or more painfull (in case of the changes apply to all the apps)

Or if I serialize everthing, then I would only have one table and I won't have the need to make changes to the table, but this option seems a bit lame to me.

Alter mysql database engine doesn't work from cluster to other

Posted: 24 Sep 2013 10:22 AM PDT

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked.

It did not throw any error but did not work. I just want to know why this did not work as I got it from MySQL site that it won't work. I just need to know the reason for the same.

For client requirement I may need to alter the engine on installed database without losing any data.

Any Ideas or help that anybody can provide to resolve issue.

MS Access: error 3045 while linking to back-end

Posted: 24 Sep 2013 12:22 PM PDT

In our environment multiple users each work with their own copied front-end, with tables linked to a single networked back-end (both files are .mdb).

Each user has permissions to read and write to the location of BE, and both FE and BE are set to "shared access".

Recently a single user started getting error: 3045 "Could Not Use Database; File Already in Use" while trying to open forms using linked data.

The same error appears while trying to re-link the tables using the manager. All other users can still normally work.

Tried restarting his workstation, removing local files and re-copying the FE and it didn't work.

There is no .ldb file left after all users turn off the FE, compacting after that and restarting the FE of the unlucky user didn't help either.

Said user is working on AC2010, but some others are working on AC2007 and AC2003.

Please help!

edit: BE is stored on Windows Server 2003.

Security for Oracle linked server from SQL Server

Posted: 24 Sep 2013 04:22 PM PDT

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data from linked server.

So I tried mapping SQL Security User to Oracle user/pass by inserting it in "Local server login to remote server login mappings:" area then "For a login not defined in the list above, connection will:" set to "Not be made".

When I click OK, I get:

Access to the remote server is denied because no login-mapping exists

Is it not possible to map SQL login to a Oracle login? Is there any way to get this to work?

Optimizing mysql queries running on 50 million rows

Posted: 24 Sep 2013 12:31 PM PDT

Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip  FROM SystemEventsR  WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND   SysLogTagflag=1 AND   username !='')  GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,  COUNT(id) AS Records,   explanation, action   FROM SystemEventsR,oa_syslogtags  WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND   flagid = 1 AND DATE(timestamp) = DATE(NOW()))  GROUP BY SystemEventsR.SysLogTag   ORDER BY Records DESC

Any help would be appreciated.

Statistical Analysis of Data that has to be done in an order?

Posted: 24 Sep 2013 09:22 PM PDT

Bear with me - that is the first time try that in SQL Server, normally I have been doing that on the front end ;)

I a implementing some analysis on time coded data series. This is not super complicated stuff, but some of it requires some numbers we do not store in the database and that has to be calculated by aggregating the numbers in a specific algorithm IN ORDER.

To give an example:

  • I have a list of trades and I need to know the maximum loss we had in the account, so i need to aggregate the plus/minus and then take the most extreme negative and positive.

This can not be pre-calculated due to dynamic filtering - there are a number of filters that can be applied to the data.

So far - past - I pulled the data to the application, now for the standard stuff I plan to try to keep that in the sql server.

My problem now is - I can see how that works (acceptable) in SQL Server:

[TradeBegin],  Pnl,  SUM (Pnl) OVER (ORDER BY [TradeBegin] ROWS UNBOUNDED PRECEDING)  [Total]

But if I put that into a view... and then filter out rows, the Sum is still calcualted from the beginning. And I need a view because I want (need) to map that standard analysis data into an ORM (so dynamic SQL is out). Anyone an idea how to do that?

Record versioning and promotion

Posted: 24 Sep 2013 07:22 PM PDT

Let's say we have this hierarchy: 

   -World            --USA          ---WA          ----Seattle          -----Downtown          ------1st Ave          -------945 1st ave          ------3rd Ave          -----South          ----Bellevue            ---TX            ---MI            --Canada             ---BC

Now, this will not reflect real life completely, but.

  1. At location World we have some global settings. Let's say: list of Races, list of Languages, etc. All locations underneath it can access this data.

  2. Country level holds data about laws, currency, etc.

3.Each city can modify laws. After they modified the law, it is available to the city itself and to the regions within the city. After 3-step approval process, it can become a country level law, and all children will inherit new version of the law. Each city still can modify new version of a law to have changes.

  1. Every City has sandboxes. This means that different organizations within city can test out editing different versions of laws without affecting City master revision.

Currently we solve this issue like this:

We have table Location with HierarchyId
Then we have table Law that has columns StateType, Location_Id, etc.
StateType can be: Master (Available to all within country), Edited (Edited for a specific location), Approved (Approved at a specific location).

Because of this we have to use Table-valued functions in our application to filter specific records that are relevant to the current location. (Some laws can come from country level, some laws come from city level, some laws come from organization level). This makes database much slower.

We cannot use native unique constraints on our fields, because all fields must be unique only within a branch, so we have to use custom check constraints to keep data clean.

Also, insertion and editing records is much slower, since we have to promote some records to a country level, and then apply data transformation for other countries.

Currently we have around 150 tables, and we use a lot of joins.

I'm not sure what's the best way to model complex hierarchical data with versioning. (I'm thinking we need an enchanced version of source control..)

Are there any disadvantages to partitioning on financial year?

Posted: 24 Sep 2013 08:22 PM PDT

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.

The majority of queries are within the financial year/one partition. Either select * from sales where date time between '2013-05-01' and '2014-04-30 23:59:59' or some period within that year.

My plan is to have a range partition on an InnoDB table. e.g. 

PARTITION BY RANGE COLUMNS(datetime)  (PARTITION p0 VALUES LESS THAN ('1999-05-01') ENGINE = InnoDB,   PARTITION p1 VALUES LESS THAN ('2000-05-01') ENGINE = InnoDB,   PARTITION p2 VALUES LESS THAN ('2001-05-01') ENGINE = InnoDB,  ...

This means that the PK has to become PRIMARY KEY (index,datetime).

Are there any significant disadvantages to partitioning compared to having an unpartitioned table? I know that means the PK is now length 12 and all further indexes will have that prepended to it. Does that make a difference? The table needs to work faster on reads than writes, and there are a fair few indexes on it.

  • Is there any functional difference between partitioning on RANGE COLUMNS(datetime) and RANGE TO_DAYS(datetime) using MySQL 5.5+?
  • Is partitioning on datetime OK? One alternative is adding in a column for financial year and indexing/partitioning on that? (And then adding another index on datetime for other queries).

We do sometimes need to query the time across all time or over "the last X months", but this is pretty rare. The main advantages of moving to a single table is to eliminate the logic in the application working out which table to insert/update/select and not needing to calculate unions in those situations where we need more than one table.

MongoDB: move documents before capping

Posted: 24 Sep 2013 11:22 AM PDT

The cappedCollection concept works well for most of my projects where cleaning old data without care makes sense.

For another projects, I need a more complex and safe concept. The requirement is nearly the same as logrotate. The data is appended to the main collection, without compression/compact and no index except a timestamp for simple queries by time. => The focus is on writing and persistent data.

Similar to the logrotate rules, I'd like the main collection not to become too large => capped by size; if possible, capping by timestamp might be a plus.

This sounds like a cappedCollection, but I do not want any data loss when it's capped. The old data should be stored into another db's collection that must be compact:true and a non-capped collection. It's name depends on the current month and makes sure that there will be max 12 "archive" collections per year.

Example:

liveDB.mainCollection_capped grows and starts capping.

Before removing old documents, these are savely moved into archiveDB.compactArchiveCollection201303.

No data is lost and the main collection remains small and fast. Storing the data in another database avoids db locks, e.g. repairDatabase tasks on an archive file will not affect or delay the main collection.

Is there a good practice or how to achieve this - as reliable and automated as possible - without writing all the data transfer for a cronjob which handles the data transfer but should never ever be missed because data is lost if capping starts before old data is copied into the archive.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server

Posted: 24 Sep 2013 02:14 PM PDT

This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;  GO  RECONFIGURE;  GO  sp_configure 'Ad Hoc Distributed Queries', 1;  GO  RECONFIGURE;  GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?

SUPER privilege not defined for master user in Amazon MySQL RDS

Posted: 24 Sep 2013 05:22 PM PDT

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me following error

ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with variable using

SET GLOBAL log_bin_trust_function_creators = 1;

it gives me another error

Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I don't know how to solve this error.

Can anybody help???

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Posted: 24 Sep 2013 06:22 PM PDT

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Conceptually, I was thinking something like:

mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql

I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :)

Thanks for your time!

Deleting Data From Multiple Tables

Posted: 24 Sep 2013 03:01 PM PDT

Suppose,I've a table called UNIVERSITY containing universities name:

universityID    universityNAME  isACTIVE       7            GNDU             1       6            PU               1       5            PTU              1       8            LPU              1

Now these universities ID's has been(obviously) used in many tables within the database(name e.g.Education),Suppose 10 tables.

Q.Now what happen if i delete one university?

A.The universityID field in other tables becomes NULL.

But I don't want these,rather when I delete 1 university from UNIVERSITY TABLE,all its occurrences with Rows in all 10 table should get deleted.

What will be the shortest and easiest MySQL Query for this operation.

NOTE:I'm using PHP language.

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Posted: 24 Sep 2013 02:31 PM PDT

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:

SELECT COALESCE(c.FirstName                  ,(SELECT TOP 1 b.FirstName                    FROM TableA a                     JOIN TableB b ON .....)                  )

If, for example, c.FirstName = 'John', would SQL Server still run the sub-query?

I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).
Posted by at No comments:

[SQL Server] How to search?

[SQL Server] How to search?

How to search?

Posted: 24 Sep 2013 12:46 PM PDT

Hi Everyone ;-)I hope everyone is having a nice day ahead ;-)I have a question guys...I want to search for example a Sex whether Male or Female regardless of their status..How should i do that??...here is my Code in SQL 2005 and my Front-end is Visual Studio 2008see my UI attachment[code="plain"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SearchBiography] @firstname varchar(50), @middlename varchar(50), @lastname varchar(50), @sex varchar(50), @status varchar(50), @sexID int, @statusID intASBEGIN SET NOCOUNT ON; DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max), @SqlSexIDQuery varchar(max), @SqlStatusIDQuery varchar(max) SET @SqlQuery = '' IF LEN(@sex) > 0 SET @SqlQuerySex = ' sex = ''' + @sex + '''' ELSE SET @SqlQuerySex = '' IF LEN(@status) > 0 SET @SqlQueryStatus = ' AND status = ''' + @status + '''' ELSE SET @SqlQueryStatus = '' IF @sexID <> 0 SET @SqlSexIDQuery = ' AND sexID IN(SELECT sexID FROM sex WHERE sexID = ' + convert(varchar(20), @sexID) + ' )' ELSE SET @SqlSexIDQuery = '' IF @statusID <> 0 SET @SqlStatusIDQuery = ' AND statusID IN(SELECT statusID FROM status WHERE statusID = ' + convert(varchar(20), @statusID)+ ')' ELSE SET @SqlStatusIDQuery= '' IF LEN(@firstname) > 0 SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstName = '' IF LEN(@middlename) > 0 SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddleName = '' IF LEN(@lastname) > 0 SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastName = '' SET @SqlQuery = 'SELECT * FROM TestMyView WHERE ' SET @SqlQuery = @SqlQuery + @SqlQuerySex + @SqlQueryStatus + @SqlSexIDQuery + @SqlStatusIDQuery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName EXEC(@SqlQuery) PRINT(@SqlQuery)END[/code]

Breaking up a string of text

Posted: 24 Sep 2013 05:20 AM PDT

Hello again,I have some very dirty data here. I'm working on a Customers table which I plan to clean up and insert into a new table. Currently, the table looks like this:LastName, FirstName, MiddleInt-----------------------------------------Smith,John,Q * *So "Smith,John,Q" IS the last name and "*" are stored in the First Name and Middle Int fields.Yuk.I am able to just select the last name based on the comma like so:[font="Courier New"]LEFT(LastName,(CHARINDEX(',',LastName))) AS LastNameOnly[/font]However, I am having trouble pulling out the first name. Hell, at this point, I'd be fine with it if first name contained any possible Middle Int. I'd be fine if FirstName was "John,Q". I can clean that up in a second step. I'd be grateful if someone could help just getting the "First Name" out of the string. I've tried various methods of SUBSTRING, LEN, and what not. Just can't seem to connect the dots....Thanks

Creating a new column and inserting data on it from an existing column

Posted: 23 Sep 2013 03:51 PM PDT

Is there any way we can create a new column based on the results of a select. i am trying to create a new column that would store users address that is currently stored in his name as name (nvarchar 500) "this,guy (Chicago)" into three new columns as firstname lastname address guy this newyorkthanks,

sql server 2008 r2 installation error

Posted: 23 Sep 2013 08:18 PM PDT

hi while installingsql server 2008 r2 at the end its giving error service 'sql browser' start errorand installation failing. please can anybody help me I have tried 3 to 4 timesI have searched the net they said use default instance nothing workingos:windows 7 home premiumregardssudarshan
Posted by at No comments:

[MS SQL Server] Application stopped during Rebuild Index?

[MS SQL Server] Application stopped during Rebuild Index?

Application stopped during Rebuild Index?

Posted: 23 Sep 2013 09:05 PM PDT

Hi,Version - SQL Server 2008 R2 64 bit.I created Maintenance plan for Re-organize and Rebuild index in single jobs.. Jobs successfully completed..During running job at that time application not able to connect the database. what could be issues? can check the option Index keeps available online in maintenance plan wizard?Thanksananda

Replication Databses are not Showing

Posted: 23 Sep 2013 08:43 PM PDT

Hi,I have some replication databases on some servers and those replicated databases are not showing in my table, i don't find any status of those databases. but i know manually which databases are replicated on servers. on my monitring tool i have quried to show status (replication xps) this is system command. actually in facets replication xps is enabled as a false in my replicated servers.can anyone have better idea to find replicated databases, please let me know. Cheers,
Posted by at No comments:

[Articles] The Human Cost of Managing Data

[Articles] The Human Cost of Managing Data

The Human Cost of Managing Data

Posted: 23 Sep 2013 11:00 PM PDT

There's a cost to managing data that isn't always considered: the human cost. Additional stress, work, and load can result in less than efficient management, even when you contain hard costs.
Posted by at No comments:

[SQL 2012] Alias for testing

[SQL 2012] Alias for testing

Alias for testing

Posted: 24 Sep 2013 03:15 AM PDT

I'm trying to make my remote development environment look very much like my client's environment. I want to set it up so when my programs seek a server called "MTF", they actually get pointed to a network server in my development environment called "LION". LION (SS2012) is up and running, and I can connect to it from my laptop using SSMS2008R2 with the server name "LION".I can't seem to connect using the server name "MTF". I've set up an alias on the server called MTF, and I've set up the local client with the "MTF" alias following instructions found here: [url]http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/[/url]Am I forgetting something?

Weird slowdown issue, possible table lock

Posted: 24 Sep 2013 12:26 AM PDT

Can anyone help me understand what is happening here? I have a tall table containing a list of IDs and a numeric value that refers to a type of demog. It's being queried through a series of subqueries that are joined together eg....[b]select distinct ID from (select subquery1.RID RID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1[/b]This might seem like an inefficient way of doing it but due to the way the table is set up, it returns about 45000 IDs in approx 5 seconds.The problem comes when I try to use this results set as a subquery to update another table e.g....[b]update Outputtable set queryID=3483 where ID in (select distinct ID from (select subquery1.ID ID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null[/b]This query takes several hours to complete and causes the CPU to run at 99%. I can't understand why though, as the subquery, when run in isolation is so fast. The outputtable is about 50k, and has only just been created by the process, so isn't locked.Even stranger, I can make the query run fast again, if I apply a TOP to the subquery. e.g.....[b]update Outputtable set queryID=3483 where ID in (select distinct top 10000000 ID from (select subquery1.ID ID from ((Select ID from demogtable where (demogID='133766')) Subquery1 inner join (Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join (Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join (Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join (Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join (Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null[/b]Someone told me that using the TOP statement can be a good trick to make SQL actualise a subquery in tempdb before attempting to move on, and that appears to be what's happening here, but I can't understand why the subquery would cause issues when run inline.If I run sp_lock when the first update statement is running, it appears that there are 4k+ locks going on.Can anyone explain this behaviour?

temp table vs permanent table performance

Posted: 24 Sep 2013 12:01 AM PDT

Hi There ,Im handling cores of data which will refreshed in every run.for this which one I can go with ? temp table or permanent table ?

Set up jobs on SQL Server 2012 Servers in AlwaysOn configuration

Posted: 24 Sep 2013 02:04 AM PDT

Hello everybody,I am a SQL DBA and need some help.We currently have two 2012 SQL Servers in High Availability.I scripted and placed the jobs on both Servers and added the 1st step to all them utilizing the following script:IF dbo.fn_hadr_is_primary_replica ('db_name') <> 1RAISERROR('This is not the preferred replica, exiting with success',11,1)So, the jobs are basically running according to their schedules on both Servers, and exiting the 1st step with Success if: dbo.fn_hadr_is_primary_replica ('db_name') <> 1It works fine. but this is not a fancy solution at all.For example, since the SSIS Packages reside in the Catalogs that I created, for executing them it does not matter from which job and on which Server they are running.Is there a way do something like that with the jobs; anotherwords set some Centralized location for them and run all the jobs from this location?Or maybe there anybody is aware of a better way to set up the jobs in AlwaysOn?Any help with this matter will be greatly appreciated.Thank you.Alex

ssis vs jobs

Posted: 24 Sep 2013 01:28 AM PDT

I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL process done using SSIS. I'm looking the ssis over. it's ssis done in sql server 2008, running from sql server 2008 instance however it is pushing all of it's data t sql server 2012. I suppose he didnt want to convert it to 2012 even though the destination db was upgrated. It basically calls a few .exe's to bring down files, it then transofmes the data, loads it into sql server, runs several procs on a handful of db's, then archives the data to history tables. I don't see any parallel processing nor anything that would warrent using ssis, everything is going to one destination instance. It could easily be create within sql server as job w/ steps.Does anyone have any input on why i should keep this running as an ssis? Just curious.

activity monitor in SSMS Unable to access -- error.

Posted: 23 Sep 2013 08:15 AM PDT

I receive the following messages when trying to use activity monitor in SSMS Error:The activity monitor is unable to execute queries against server.activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the Activity Monitor.Additional Information: The RPC server is unavailable. (Exception for the HRESULT: 0x800706BA) (mscorlib)

crazy replication question

Posted: 23 Sep 2013 04:36 AM PDT

I have replication running with a 2 publications coming out of the same database. Publication number 1 has all but 300 of a 8000 table database, publication number two has only the 300 of the 8000 tables. So basically I have all the data published, i just have 300 tables subscribed to one database and the remaining 7700 subscribed to the other database.Both are transactional push. When I monitored the Log Reader Agent Status, I was very surprised to see that both publications were delivering the exact same transactions/commands which leads me to believe that with transactional replication, everything is delivered to the distributor and then from there the decision is made as to which articles are pushed to the subscriber.So in other words if I have an 8000 table, 2TB database, and I only want 1 table to be transactionally replicated. All changes for all 8000 tables will be delivered to the distributor and only the changes for the 1 table will be applied to my subscription database.Does this seem accurate?thanks!

Doubts with Log Shipping

Posted: 23 Sep 2013 09:34 PM PDT

When logshipping is configured, we know that along with LS Backup/copy and restore jobs, LS ALert jobs are also created, which alerts whenever the defined threshold for the backup or restore has crossed. I want to know where these alert messages are logged apart from msdb..
Posted by at No comments:

[T-SQL] Seeking Explanation for Query Performance difference...

[T-SQL] Seeking Explanation for Query Performance difference...

Seeking Explanation for Query Performance difference...

Posted: 23 Sep 2013 10:10 PM PDT

Just after some advice if possible.I had a Stored Procedure which had a join to a Scalar Valued Function, returning 40 rows in its final data set. It was doing 300 thousand reads and taking 9 seconds to return its data.Soooooooo….I ripped the Function out of the Join and populated the results of the Function into a Local Temporary Table at the top of the Procedure. Reads came down to 1982, took less than 1 second to run. Now I know Scalar Functions are poor in SELECT and WHERE clauses due to the row by row processing and executes the Function, but I didn't see why this would be a problem in the Join?!Soooooooo…I tested one other thing, I swapped the Local Temporary Table out for a Table Variable and ran the query again. This time the performance was back to how it was originally with the ridiculous amount of reads being performed. Cache and Buffers dropped each time. STATISTICS IO showed the massively reduced reads when using the Local Temp Table.The Execution Plan highlighted the difference, the Local Temporary Table version was using Parallelism and the Table Variable/Function versions were not. Both procedures were over the cost Threshold for Parallelism so not sure why one chose to use it and not the other?!Any ideas?Cheers guys

Query performance

Posted: 23 Sep 2013 07:25 PM PDT

Hi,I have a query which takes more than 2 min whenver it runs.Is there any other way to wrtie this query so that performance can be improved[code="sql"]SELECT A.OFFICEID,A.PROJECTID,A.PROPOSALID,A.SOLUTIONID,A.UNITID,A.PRICEITEMID,A.ESTIMATIONGROUPID,A.SRNO,A.PRICEITEMNAME,A.LOCALPURCHASETYPEID,A.PRI_PRICE,A.Sec_Price,A.PRI_PRICEWOR,A.SEC_PRICEWOR,A.PRIMARYCURRENCYID,A.SECONDARYCURRENCYID,A.DELETEFLAG,A.DATEADDED,A.ADDEDBY,A.DateChanged,A.ChangedBy,A.Quantity,A.SrNoDetail,A.Pri_PriceWM,A.Sec_PriceWM ,A.Code,A.MOrITypeFROM ESTIMATIONOUTPUTPRICE A WITH (NOLOCK),VW_ESTIMATIONOUTPUTPRICE_ETL_COMPANY B WITH (NOLOCK), UNIT C WITH (NOLOCK)WHERE A.OFFICEID = B.OFFICEID and A.PROJECTID = B.PROJECTID and A.PROPOSALID = B.PROPOSALID and A.SOLUTIONID = B.SOLUTIONID and A.UNITID = B.UNITID andB.OFFICEID = C.OFFICEID andB.PROJECTID = C.PROJECTID andB.PROPOSALID = C.PROPOSALID andB.SOLUTIONID = C.SOLUTIONID andB.UNITID = C.UNITID and C.ISARCHIVED=0 [/code]

Query

Posted: 23 Sep 2013 08:35 PM PDT

Dear All I have one scnario. One column i am having Following records 1a2a2b.......10a10bBut i want following output through Query122....1010.

inconsistently wrong query results

Posted: 23 Sep 2013 08:50 PM PDT

Hi All - I have a count/grouping problem and for the life of me can't make sense of it. Involved in the query is one table (plus a time-dimension table as a variation) which holds info on enquiries made, each enquiry has its int key and an FK referencing the table which contains info on cases, it's a straight forward one-to many relationship. What I want the query to do is count distinct cases and count enquiries per month. It shouldn't be hard, but I get inconsistent and partly wrong results. Below is the sql with result sets. The first two snipets are variations on achiving the count/group operations in one query, and the third bit looking up each month seperately and puts it in one result set, where the months are in the same order as in the two queries above. The last result set is also the correct one.I have checked the data and there are no abnormalities, so I must be to do with just not understanding the way sql count and groups. Any help would be most welcome, thanx. [code="sql"]SELECT TOP (100) PERCENT COUNT(DISTINCT CaseID) AS Cases, COUNT( distinct CallID) AS Enquiries, RIGHT(CONVERT(varchar(10), CallDatAdd, 5), 5) as [Month-Year]FROM tbldCallsWHERE (CallDatAdd BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-06-30 00:00:00', 102)) GROUP BY RIGHT(CONVERT(varchar(10), CallDatAdd, 5), 5)order by [Month-Year] asc[/code][code="plain"]Cases Enquiries Month-Year723 945 01-13646 866 02-13773 937 03-13675 829 04-13758 928 05-13632 782 06-13[/code][code="sql"]SELECT COUNT(DISTINCT tbldCalls.CaseID) AS Cases, COUNT(tbldCalls.CallID) AS Enquiries, dimension_time.Month_TextFROM dimension_time INNER JOIN tbldCalls ON CONVERT(date, dimension_time.Day_Timestamp) = CONVERT(date, tbldCalls.CallDatAdd)WHERE (tbldCalls.CallDatAdd BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-06-30 00:00:00', 102))GROUP BY dimension_time.Month_Text, dimension_time.Month_KeyORDER BY dimension_time.Month_Key asc[/code][code="plain"]Cases Enquiries Month_Text723 945 Jan646 866 Feb773 937 Mar675 829 Apr758 928 May632 782 Jun[/code][code="sql"]SELECT count(distinct CaseID) as Cases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-01-01 00:00:00', 102) and CONVERT(DATETIME, '2013-01-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as FebCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-02-01 00:00:00', 102) and CONVERT(DATETIME, '2013-02-28 00:00:00', 102))Union allSELECT count(distinct CaseID) as MarchCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-03-01 00:00:00', 102) and CONVERT(DATETIME, '2013-03-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as AprilCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-04-01 00:00:00', 102) and CONVERT(DATETIME, '2013-04-30 00:00:00', 102))Union allSELECT count(distinct CaseID) as MayCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-05-01 00:00:00', 102) and CONVERT(DATETIME, '2013-05-31 00:00:00', 102))Union allSELECT count(distinct CaseID) as JuneCases, count (callid) as EnquiriesFROM tbldCallsWHERE (CallDatAdd between CONVERT(DATETIME, '2013-06-01 00:00:00', 102) and CONVERT(DATETIME, '2013-06-30 00:00:00', 102))[/code][code="plain"]Cases Enquiries680 888618 825773 937630 772678 831632 782[/code]

Phone number question - detecting and replacing

Posted: 23 Sep 2013 07:58 AM PDT

Hello,I have a website/database app with a text column that stores a user's bio where users are not allowed to embed their phone number (e.g. similar to a dating website). I need to write a query that detects phone numbers and replaces them with XXXs. Thanks,MattFor example, I have written a query that detects records with phone numbers in (xxx) xxx-xxxx format, but the replacing is tough. Maybe RegExp?select userid, bio from userswhere bio like ('%([0-9][0-9][0-9])%')

Incomprehensible error in OVER clause with self-JOIN and GROUP BY

Posted: 23 Sep 2013 02:38 AM PDT

I've encountered a very weird error when using an OVER clause, which i cannot explain. I'd be interested if anyone else can tell me why this happened.I was running the following code:[code="sql"]SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentProjects.ProjectID ORDER BY ParentProjects.ProjectID) = 1 THEN 1 ELSE 0 ENDFROM Projects INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectIDGROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost[/code]and i got the following error:[quote]Msg 8120, Level 16, State 1, Line 1Column 'Projects.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.[/quote]Note that the column "Projects.ProjectID" is [b]NOT[/b] in the select list and it [b]IS[/b] in the GROUP BY clause, exactly the opposite of what the error claims!Now, if if replace the PARTITION BY element and use an equivalent one from the primary Projects table (instead of the self join column), the code runs just fine:[code="sql"]SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY Projects.TopLevelParentProjectID ORDER BY Projects.ProjectID) = 1 THEN 1 ELSE 0 ENDFROM Projects INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectIDGROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost,Projects.TopLevelParentProjectID[/code]Can anyone explain to me why this is happening?!?

CLUSTERED INDEX SCAN (EmpNonPrjTime)

Posted: 23 Sep 2013 05:15 AM PDT

I have a report that calls procedure.Procedure is simple select from VIEW:...[code="sql"]FROM vwNzEmpNonPrjTimeWHERE co_code = @L_CO_CODE AND ('**all**' IN (@L_ORG) OR org_code IN (SELECT * FROM getValuesAsTable(@L_ORG, ','))) AND ('**all**' IN (@L_EMPLOYEE) OR EmployeeNumber IN (SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ',')))[/code]@L_ORG and @L_EMPLOYEE are input parameters for "Multi Value" SSRS parameters.Inside stored porcedure I cannot use IN @var method.I have to convert comma delimited strings into temp table.but this seems to create a problem with execution code.If I change procedure code and have:WHERE org_code = @L_ORGAND EmployeeNumber = @L_EMPLOYEEcode executes in 1 sec, no clustered index scan.As sooon as I change it back to the code at the topI get clustered index scan and it executes more than 10 sec.By the way, SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ','))shouldn't be a problem. It runs in 200 miliseconds even with a lot of values.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog