Friday, August 16, 2013

[how to] Mailing list system database schema design

[how to] Mailing list system database schema design

Mailing list system database schema design

Posted: 16 Aug 2013 05:16 PM PDT

I'm working on a simple mailing list system so to store messages and keep track of each message recipients so i have 3 tables:

  • Mails (MessageId, MessageSubject, MessageBody)
  • Mails-Recipients (MessageId, RecipientId)
  • Recipients (RecipientId, RecipientName, RecipientEmail)

The problem is: when sending messages to a large number of recipients like 1000 ,i have to add 1000 rows to the Mails-Recipients table then to get the recipients info for a single message i have to use join query with 1000 rows and with a large number of message this table will grow rapidly causing performance issues.

Is there any better schema design for this system that can help maintain a good performance?

Database utilizing the whole of swap space

Posted: 16 Aug 2013 04:29 PM PDT

When I load my progress database, it almost instantly eats up all available RAM, as well as swap space. I've been told that adding extents might fix this, but I'm not sure of how/where to identify if this is true. I additionally need to identify where/how to add extents. I did find some documentation, but without being able to identify the source of the problem I don't think I can execute it. I would post some additional data, but unfortunately I don't even know what is of use. Thanks

MySQL Grouping Order

Posted: 16 Aug 2013 03:29 PM PDT

Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each language, for each product_id. However it doesn't seem possible to get MySQL to return that concatenated string in any particular order of language_id's. What I'd like is to be able to order the grouping by language id, so that they'll always come out in a pre-determined order.

Is this possible? If so, how? If not possible within the SELECT statement, is there a modification I can make to the table to adjust how the GROUP BY would order the result?

Dynamic SQL is not allowed error for creating function in MySQL

Posted: 16 Aug 2013 01:35 PM PDT

According to this question and answer of Angelo I create a MySQL function as below:

DELIMITER $$  CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT  NOT DETERMINISTIC  READS SQL DATA  BEGIN              DECLARE _id INT;              DECLARE _parent INT;              DECLARE _next INT;              DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;              SET _parent = @id;              SET _id = -1;              SET @table_name = reqdTable;              IF @id IS NULL THEN                  RETURN NULL;              END IF;              LOOP                    SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');                  PREPARE stmt FROM @sql_text;                  EXECUTE stmt;                  DEALLOCATE PREPARE stmt;                    IF @id IS NOT NULL OR _parent = @start_with THEN                    SET @level = @level + 1;                    RETURN @id;                  END IF;                  SET @level := @level - 1;                    SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');                  PREPARE stmt FROM @sql_text;                  EXECUTE stmt;                  DEALLOCATE PREPARE stmt;                END LOOP;       END

But It has following Error:

 #1336 - Dynamic SQL is not allowed in stored function or trigger

What should I do?

Run Multiple Scripts In One Transaction Across Multiple Servers

Posted: 16 Aug 2013 05:22 PM PDT

We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.

SQL Server 2012 High Availability on VMware virtualization

Posted: 16 Aug 2013 02:15 PM PDT

We're going to host a large SQL Server 2012 under a VMware virtualization and we need it to be highly available.

These are the three options I found supported, but couldn't find a lot of info and comparison on who's better and why (and mostly - what's the disadvantages):

  1. VMware HA - pro: cheap and easy to use and configure, just let the VM team deal with it. con: no HA for system upgrades, e.g. SQL upgrades, Windows services-packs\KBs etc.
  2. SQL Server Failover Cluster (FCS) - pro: well known, supported, saves space (compared to AlwaysOn Availability Group), HA for system upgrades. con: I haven't heard of successful clusters over VM, or at least good ones. Heard it's a mess and to "stay away from it". A bit harder to admin, some problems may take days to figure out (no-one with a lot of knowledge).
  3. SQL Server AlwaysOn Availability Group - pro: HA for everything! system, DB etc. con: New, added administering and hard to confiugre, new problems to deal with, double the space(!), "no strings attached" (to other servers).

That's pretty much it.

I would appreciate any info you may have about this, and would appreciate even more recommendations (hopefully based on test cases).

~Thanks.

Which databases can have row and column level security based upon Active Directory?

Posted: 16 Aug 2013 01:27 PM PDT

I have various database types, Oracle 11g, MySQL 5, Sql Server 2012. I have direct access. I can have other databases if I need them such as PostgreSQL.

What I'm doing is protecting data at the database and table level as opposed to the application level though I there will be some mixing.

What I'm hoping to do is control all the security from Active Directory. I don't want to do security in MySQL, security in Oracle, etc. That is a lot of duplicate entry.

I will have a webpage send credentials to the database engine to determine which bits of data the user can see.

Thanks for ideas.

After subscribing to a replication set, why can't I see the replicated tables on the local database?

Posted: 16 Aug 2013 12:16 PM PDT

I'm pretty new to databases in general (about two weeks), but I'm learning fast. If I'm using the wrong terminology anywhere, let me know in the comments. Bear with me. :)

Background

The company I work for is using PostgreSQL 9.1 (with pgAdmin III) to manage their data. The scenario is this. Say we have a master database M. Only M will be receiving direct submissions of data. However, on a regular basis (or even a push-notification basis?), another database, T, needs to pull the data from M such that specific tables of M are replicated in T. As far as this problem is concerned, this is one-way communication.

The (slight) catch: T is not read-only. Changes in T still need to be persistent to T and T only. So, this problem is not strictly replication, but I believed it was a start. (In my mind, T could replicate M but not push its own changes to M, thus preserving integrity.) If there is a better way to go about this, please don't hesitate to comment some links or keywords. :)

Problem Statement

I need to set up Master-Slave replication of a subset of tables in the Master database. That is, specific tables from the master must always be pushed onto the slave, but the slave is free to make its own local changes.

I am using pgAdmin III with Slony-I. On the master, I've set up a Slony cluster and added my admin node, my master node, and my slave node. I believe I correctly made paths and listens for open communication between the master and the slave. I'll post their configuration as screenshots at the end.

I've created a table in the postgres database called replicateme. (For simplicity, I'm using the defaults username/password/databases for this research.)

\c postgres  create database replicateme (    product_no integer PRIMARY KEY,    name       text    price      numeric  );

I created a new replication set and created a new replicated table, public.replicateme.

At this point, after refreshing, I do not see the replication set show up on the pgAdmin instance on the slave node. This is odd because the nodes themselves do show up. If I connect to the master database 'normally,' the set (and table) show up, obviously. From the requirements though, I don't believe I'll be able to connect directly to the master database.

Thinking 'Oh, it's because I'm not subscribed,' I went on to create the subscription. I was only able to create it on the Master database though. Even after creation, I was left depressingly data-less.

Is there an extra step I'm missing? I'm hardly CLI averse, but pgAdmin/Slony was advertised to the development team as completely workable through the GUI. I'm getting the feeling it's not, but I cannot find any documentation that makes it clear where pgAdmin stops and the terminal begins. What is left to be done?

Master:
enter image description here

Slave:
enter image description here

Ghost Cleanup process has high CPU usage in SQL Server 2012 SP1

Posted: 16 Aug 2013 11:21 AM PDT

Has anyone encountered this? We are using SQL Server 2012 Enterprise Edition with SP1. During a time of low/no activity, I noticed the Ghost Cleanup process is utilizing CPU at a very high rate, from 75% to 99%, these aren't bursts but sustained usage.

Is this a bug with SQL 2012? I have seen this in SQL 2008 R2 and there was a fix for it but I haven't seen anything for 2012.

ERROR: text search configuration name "english" must be schema-qualified

Posted: 16 Aug 2013 11:06 AM PDT

Trying to do some full text searching with postgres and when running the following command

UPDATE "EMAIL" SET PostText = to_tsvector('pg_catalog.english', "TEXT");

I'm getting the error:

ERROR:  text search configuration name "english" must be schema-qualified

Is there something I'm missing? I've also tried

UPDATE "EMAIL" SET PostText = to_tsvector('english', "TEXT");

Edit 1: Table EMAIL has the column "TEXT" in it. They are both in all caps FWIW

MySQL - unique constraints design

Posted: 16 Aug 2013 03:27 PM PDT

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community.

Please consider the diagram:

enter image description here

I would like to add a unique constraint such that only one User can have only one Role per Event (Which is stored in the UserEventRoles table). Not sure how this can be solved? Perhaps with different design? Also the goal is not have a scenario where keys can be "out of sync", such as users role would point to an event where that role does not exist.

EventRoles table describes which Roles are valid at each Event.

I am sure this problem has come up before with other database designs and would like know what the best approach may be.

Also here is a link to sql fiddle to play around schema and some sample data.

sqlFiddle

multiple line text values in mysqldump text export file

Posted: 16 Aug 2013 10:19 AM PDT

I'm trying to export +100mil record table into txt file. My plan is split up txt file to small pieces by size or line then import.

I have one text field has multiple line like blog post text, in txt export file it exported as multiple lines which I want it to be 1 line 1 row so I can process it by lines.

I tried various fields-terminated-by, lines-terminated-by, fields-escaped-by parameters for export but nothing made that multiple line text into single, quoted and comma separated line.

It does quote well when I export the data in sql format but I haven't succeeded to convert new line characters in the text field to \n\r or \n whatever those characters are. Even if I escape it, still exported as new line with the quote.

Relations, dependencies and normal form

Posted: 16 Aug 2013 02:54 PM PDT

I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know:

Assume the relation GTX {G,T,X} with the following dependencies: 

G,X -> T  T -> X

or this one:

Relation {W,X,Y,Z}:

X,Y -> Z  Z -> W
  • So to my question, how do I properly draw a dependency diagram for this kind of relations? (tutorials or/and explanations are much appreciated)
  • How do I see what the primary key is? (I know what a pk is)
  • In which normal form are the relations?

I have never been able to understand this fully and always got confused by the tutorials I found. I appreciate all answers!

MySQL: ERROR 126 (HY000): Incorrect key file for table

Posted: 16 Aug 2013 09:46 AM PDT

I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error:

mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')      -> ;  ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

-

# df -h /tmp/  Filesystem            Size  Used Avail Use% Mounted on  tmpfs                 2.0G   12K  2.0G   1% /tmp  # df -i /tmp/  Filesystem            Inodes   IUsed   IFree IUse% Mounted on  tmpfs                2041621       7 2041614    1% /tmp  # mount | grep /tmp  tmpfs on /tmp type tmpfs (rw,size=2048M)

Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.

Regards

MySQL Access Denied, tried a few things, pulling hair

Posted: 16 Aug 2013 02:21 PM PDT

I'm trying to get to know Django (my first attempts at a framework, or any backend work for that matter), and I'm seriously stumped by MySQL, and SQL in general.

I'm trying to create a new database and I get:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'dbname'

So I've tried the advice here: Access denied for user localhost

Which might work, but using: mysql -uroot -p I can't seem to remember my password. I don't recall setting one, but leaving it blank doesn't work either.

Also, I have to run mysql using:

/Applications/MAMP/Library/bin/mysql

Because:

mysql

results in:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

I'm guessing that's related to the error above, right?

I'm in a right mess here, SQL and the command line intimidate the heck out of me and it seems so easy to break stuff. If anyone could offer some pointers that would be great.

EDIT: Update on the issue;

Ugh, moved on to the next step of the Django tut. I get:

raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e) django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb

So I run:

pip install MySQL-python

and I get:

/System/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7/Python.h:33:10: fatal error: 'stdio.h' file not found

isn't stdio.h something to do with C?

I'm clearly not cut out for this back end stuff. I'm going back to JavaScript where it's all fluffy and forgiving!

Setting up SQL Server service broker from one database to multiple databases

Posted: 16 Aug 2013 09:55 AM PDT

I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance.

I have looked at the following examples, however, they only show me how to setup a SQL broker service between two databases:

I would be grateful if anyone could provide any assistance on this.

Why does my SQL Server show more than half a million active tasks?

Posted: 16 Aug 2013 06:21 PM PDT

select task_state,count(*)  from sys.dm_os_tasks  group by task_state

I ran the above statement on a SQL Server instance, and found it had about 633,000 records.

task_state       -------------- -----------  RUNNABLE       2  RUNNING        32  DONE           633115  SUSPENDED      99

How can I Close/Kill the useless tasks?

The MDW data collector have about 4000 page allocate in tempdb per time.

And this cause the IO pressure when server in busy time.

This is production server, We do not want to restart the service. And the version number is 11.0.3000.

The Max Worker Count is :1216  CPU Count:48  Hyperthread ratio:12  MaxDop: 8

Scheduler_Id is 0 - 47 and the amount of rows are average. and other columns are null.

What's stored in data_dir/base of postgresql

Posted: 16 Aug 2013 06:48 PM PDT

I suddenly found found my postgresql out of space. It's a tmp instance running on ram disk. Limited space (around 800M) allowed. Data are not actually stored on it, all table will be dropped after some calculation done.

I checked the folder and found tons of data under base folder:

  $ du -h  6.0M    ./pgsql_tmp  706M    ./16384  6.0M    ./12780  6.0M    ./12772  6.0M    ./1  730M    .

My question is: what is inside that folder? Any change I can clean it out?

View with fallback (performance/optimization question)

Posted: 16 Aug 2013 03:57 PM PDT

I have a table with tariffs for stuff; the tariffs table is not important in this scenario, the "tariff values" are. In this Demonstration SQL Fiddle the tariff_plan is the FK to the tariffplans table (not included in the example). The tariff for each "thing" is the tariff_type (simplified to a simple char for demonstration purposes).

I have, for example, a default tariffplan (key = default); this is the tariff that goes for each customer unless another value is defined for the same tariff_type for that customer. A customer is assigned a tariffplan (key = plan_x in my example).

If have tariffs defined for items a, b, c and d in the default plan. In plan_x I define "override" values for a and c.

So, what I do is I select the default plan (alias p below for primary) and left-join the "override" plan (plan_x) to it (alias s below for secondary):

select *  from tariff_values as p  left outer join tariff_values s       on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')  where (p.tariff_plan = 'default')

This results, as expected, in:

id   tariff_plan tariff_type tariff_foo tariff_bar id   tariff_plan tariff_type tariff_foo tariff_bar  ---- ----------- ----------- ---------- ---------- ---- ----------- ----------- ---------- ----------  1    default     a           0.10       0.20       5    plan_x      a           0.09       0.19  2    default     b           0.55       0.66       NULL NULL        NULL        NULL       NULL  3    default     c           1.99       2.99       6    plan_x      c           0.99       1.99  4    default     d           9.99       6.33       NULL NULL        NULL        NULL       NULL

Because I want to abstract this away I want to put this into a table valued function so I can create a "dynamic view":

select * from dbo.get_tariffplan_for('plan_x', default);

This should result in a "virtual table" (or "dynamic view") similar to the tariff_values table, thus: not having two tariff_foo's and two tariff_bar's and let the application decide which one to use. And so, I resort to ISNULL and Case when... constructs to "override" the default values:

select p.tariff_type,      ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,       ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,      ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,      CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback  from tariff_values as p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) and (s.tariff_plan = 'plan_x')  where (p.tariff_plan = 'default')

This results in:

tariff_type tariff_foo tariff_bar tariff_plan isfallback  ----------- ---------- ---------- ----------- -----------  a           0.09       0.19       plan_x      0  b           0.55       0.66       default     1  c           0.99       1.99       plan_x      0  d           9.99       6.33       default     1

All I need to do now is stuff this query into a TVF:

CREATE FUNCTION get_tariffplan_for  (         @customerplan as varchar(50),      @defaultplan as varchar(50) = 'default'  )  RETURNS TABLE   AS RETURN   (      select p.tariff_type,          ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,           ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,          ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,          CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback      from tariff_values as p      left outer join tariff_values s          on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)      where (p.tariff_plan = @defaultplan)  );

And there we have it. We can call our function ("dynamic view") as intended (and also use it in selects/joins etc.)

select * from dbo.get_tariffplan_for('plan_x', default);    --or:    select *  from foo  inner join dbo.get_tariffplan_for('plan_x', default) bar      on foo.tariff_type = bar.tariff_type

Now my first question is:

I have a feeling all these ISNULL (or COALESCE) and/or CASE WHEN ... stunts seem to complicate things unnecessarily and something tells me this can be done more efficiently. However, I can't come up with a better and/or more efficient alternative.

So I'm hoping someone here has some ideas on how to improve this.

My second question is:

What if I had a product (tariff_type q for example) that I sold exclusively to some customer; the tariff wouldn't be in the default tariff-plan so I'd have to add another select to the above (with a union) to get all exclusive tariffs for that customer in the resultset. That would result in a query like this:

select p.tariff_type,      ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo,       ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,      ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,      CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,      0 as isexclusive  from tariff_values as p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)  where (p.tariff_plan = @defaultplan)    UNION    --Exclusive values  select p.tariff_type,      p.tariff_foo,       p.tariff_bar,      p.tariff_plan,      0 as isfallback,      1 as isexclusive  from tariff_values p  left outer join tariff_values s      on (p.tariff_type = s.tariff_type) AND (s.tariff_plan = 'default')  where p.tariff_plan = 'plan_x'      and s.id is null

(Demonstrated in this SQL fiddle)

In the above example I use another left join with s.id is null (but that could be rewritten in other ways (using count, exists, not in, having...whatever)) to retrieve the customer-exclusive tariffs. But maybe there's a better way instead of the union?

Altering the location of Oracle-Suggested Backup

Posted: 16 Aug 2013 02:05 PM PDT

On one database, the Oracle-Suggested Backup scheduled from Enterprise Manager always ends up in the recovery area, despite RMAN configuration showing that device type disk format points elsewhere.

As far as I can see, the scheduled backup job is simply:

run {  allocate channel oem_disk_backup device type disk;  recover copy of database with tag 'ORA_OEM_LEVEL_0';  backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;  }

Asking RMAN to show all reveals that device type disk is indeed configured to store elsewhere:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/s01/backup/PROD11/PROD11_%U';

If I run the script manually, the backupset is placed at the above location, when the script is run from the job scheduler the backupset goes to the RECO group on ASM,

Why might Oracle still choose to dump the backupset to the db_recovery_file_dest?

Ultimately, how can I change the backup destination?

Database restructure - beneficial?

Posted: 16 Aug 2013 09:06 PM PDT

I have a table for email messages. Then, I have a table that has the message parts.

The Parts table contains a 

   field_id,  message_id, and data

At the time I used Parts to name the table and yet used field_id for the column. Just an FYI

So for example, a part_id of 2 would be the subject of the message. I have parts for subject, date, htmlbody, and textbody. Due to this structure, I have approximately 2 more queries per email (one for the parts, and another for the email addresses associated to the email) than if I were to push all the data into the messages tables. I found this structure to be best, but I'm beginning to think it might be wrong and not best for performance.

My question is, will it be in my best interests to restructure the database? Id rather not.

I was thinking about moving the htmlbody and textbody and subject and date to the messages table. Another solution would be to grab all the emails and their data from the Parts table in one query. I could grab all the ids in one query and then do an IN(ids) for the second query.

CREATE TABLE IF NOT EXISTS `messages` (    `id` int(10) NOT NULL AUTO_INCREMENT,    `user_id` int(10) NOT NULL,    `account_folder_id` int(10) NOT NULL,    `hash` varchar(255) NOT NULL,    `uid` int(10) NOT NULL,    `seen` tinyint(1) NOT NULL,    `flagged` tinyint(1) NOT NULL,    `date_created` int(11) NOT NULL DEFAULT '0',    `last_modified` int(11) NOT NULL DEFAULT '0',    PRIMARY KEY (`id`),    UNIQUE KEY `hash` (`hash`)  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;    CREATE TABLE IF NOT EXISTS `message_parts_data` (    `id` int(10) NOT NULL AUTO_INCREMENT,    `message_id` int(10) NOT NULL,    `field_id` int(10) NOT NULL,    `data` text NOT NULL,    `date_created` int(11) NOT NULL DEFAULT '0',    `last_modified` int(11) NOT NULL DEFAULT '0',   PRIMARY KEY (`id`)  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;    CREATE TABLE IF NOT EXISTS `fields` (    `id` int(10) NOT NULL AUTO_INCREMENT,    `name` text,    PRIMARY KEY (`id`)  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;    INSERT INTO `fields` (`id`, `name`) VALUES  (1, 'To'),  (2, 'Subject'),  (3, 'Date'),  (4, 'From'),  (5, 'CC'),  (7, 'ReplyTo'),  (8, 'textHtml'),  (9, 'textPlain'),  (11, 'Forward');

Thanks

MYSQL Timezone support

Posted: 16 Aug 2013 04:06 PM PDT

We are having a shared hosting plan and they are saying that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with required data(data from from our local MYSQL Timezone related tables. How to view the code syntax for MySQL "CONVERT_TZ" function?

Thanks Arun

replication breaks after upgrading master

Posted: 16 Aug 2013 12:05 PM PDT

I have a set up of replication with master 5.1.30 and slave 5.5.16 and the replication is working good

Now i have upgraded mysql master to 5.1.47

As far as i know we have to turn off the log bin with sql_log_bin=0 before using mysql_upgrade program in order to up grade the replication setup as well

but the problem here is the binary log was not turned off while mysql_upgrade program is running

The reason i found is in 5.1 the sql_log_bin is a session variable and mysql_upgrade program runs in another session

so how to upgrade the replication as well along with the server with any breakage on replication setup.

any suggestions are really useful.....

Need to suppress rowcount headers when using \G

Posted: 16 Aug 2013 01:05 PM PDT

Is there a command to suppress the rowcount headers and asterisks when using '\G' to execute a SQL statement? I am executing mysql with the -s and --skip-column-name options, but these don't suppress the rowcounts.

How to search whole MySQL database for a particular string

Posted: 16 Aug 2013 05:06 PM PDT

is it possible to search a whole database tables ( row and column) to find out a particular string.

I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

Using MySQL

i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.

multivalued weak key in ER database modeling

Posted: 16 Aug 2013 03:05 PM PDT

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as well as the quantity of each format, so i came up with this:

example

Is this correct from a design perspective? Does this implies redundancy? I dont understand how will this be stored in a table. Would it be better to do it like this :

enter image description here

Thanks in advance.

EDIT : I add some more descriptive information about what i want to store in this point of the design. I want to store information about sales. Each movie that exist in the company i need to store format, price and stock quantity. I will also need to store customer information with a unique id, name, surname, address, movies that he/she has already bought and his credit card number. Finally i will have a basket that temporary keeps items (lets suppose that other items exist apart from movies) that the customer wants to buy.

Microsoft Office Access database engine could not find the object 'tableName'

Posted: 16 Aug 2013 06:06 PM PDT

First a little background: I am using MS access to link to tables in an advantage database. I created a System DSN. In the past in Access I've created a new database, and using the exteranl data wizard, successfully linked to tables. Those databases and the linked tables are working fine.

Now I am trying to do the same thing, create a new access db, and link to this same DSN. I get as far as seeing the tables, but after making my selection, I get the error, " The Microsoft Office Access database engine could not find the object 'tableSelected'. Make sure the object exists and that you spell its name and the path name correctly.

I've tried creating another datasource (system and user) with no luck. Environment is Wn XP, Access 2007, Advantage DB 8.1

MYSQL 5.5 Fail start Fedora 16

Posted: 16 Aug 2013 10:05 AM PDT

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error.

[root@server]# service mysqld start  Redirecting to /bin/systemctl start  mysqld.service  Job failed. See system logs and 'systemctl status' for details.

Here is the log: 

121118  2:41:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  121118  2:41:38 [Note] Plugin 'FEDERATED' is disabled.  121118  2:41:38 InnoDB: The InnoDB memory heap is disabled  121118  2:41:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins  121118  2:41:38 InnoDB: Compressed tables use zlib 1.2.5  121118  2:41:38 InnoDB: Using Linux native AIO /usr/libexec/mysqld: Can't create/write to file '/tmp/ibhsfQfU' (Errcode: 13)  121118  2:41:38  InnoDB: Error: unable to create temporary file; errno: 13  121118  2:41:38 [ERROR] Plugin 'InnoDB' init function returned error.  121118  2:41:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.  121118  2:41:38 [ERROR] Unknown/unsupported storage engine: InnoDB  121118  2:41:38 [ERROR] Aborting    121118  2:41:38 [Note] /usr/libexec/mysqld: Shutdown complete    121118 02:41:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Fresh installation, nothing changed prior to that, just ran yum update.

Here is the systemctl status trace

[root@linyansho /]# systemctl status mysqld.service  mysqld.service - MySQL database server    Loaded: loaded (/lib/systemd/system/mysqld.service; disabled)    Active: failed since Sun, 18 Nov 2012 02:45:19 +0300; 5min ago    Process: 864 ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID (code=exited, status=1/FAILURE)    Process: 863 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)    Process: 842 ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n (code=exited, status=0/SUCCESS)    CGroup: name=systemd:/system/mysqld.service

Sql Anywhere 11: Restoring incremental backup failure

Posted: 16 Aug 2013 11:05 AM PDT

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible with SQL Anywhere network servers.

We are doing a full backup as follows:

dbbackup -y -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"      c:\backuppath\full

This makes a backup of the database and log files and can be restored as expected. For incremental backups I've tried both live and incremental transaction logs with a renaming scheme if there are multiple incremental backups:

dbbackup -y -t -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"      c:\backuppath\inc    dbbackup -y -l -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"       c:\backuppath\live

However, on applying the transaction logs on restore I always receive an error when applying the transaction logs to the database:

10092: Unable to find table definition for table referenced in transaction log

The transaction log restore command is:

dbeng11 "c:\dbpath\dbname.db" -a "c:\backuppath\dbname.log"

The error doesn't specify what table it can't find but this is a controlled test and no tables are being created or dropped. I insert a few rows then kick off an incremental backup before attempting to restore.

Does anyone know the correct way to do incremental backup and restore on Sql Anywhere 11?

UPDATE: Thinking it may be related to the complexity of the target database I made a new blank database and network service. Then added one table with two columns and inserted a few rows. Made a full backup, then inserted and deleted a few more rows and committed transactions, then made an incremental backup. This also failed with the same error when attempting to apply the incremental backups of transaction logs after restoring the full backup ...

Edit:

You can follow this link to see the same question with slightly more feedback on SA: http://sqlanywhere-forum.sybase.com/questions/4760/restoring-incrementallive-backup-failure
Posted by at No comments:

[SQL Server] Beginner Using Procedures

[SQL Server] Beginner Using Procedures

Beginner Using Procedures

Posted: 16 Aug 2013 12:05 AM PDT

[code]CREATE PROCEDURE [dbo].[Procedure_table] (@records INT OUTPUT, @location nvarchar(18) = NULL, @exclude tinyint = NULL, @exclude2 tinyint = NULL, @locationName nvarchar(50) = NULL, @Adult tinyint = NULL, , @Youth nvarchar(8) = NULL, @Case nvarchar(50) = NULL, @explanation nvarchar(50) = NULL, @FirstName nvarchar(50) = NULL, @LastName nvarchar(50) = NULL, @PhoneNumber nvarchar(50) = NULL, @orderField nvarchar(50) = NULL, @orderDir nvarchar(4) = NULL)ASIf @orderField IS NULLBEGIN SET @orderField = 'ID'ENDIf @orderDir IS NULLBEGIN SET @orderDir = 'ASC'ENDBEGINSELECT ID, FirstName, LastName, PhoneNumber, Adult, Youth, DateParticipation, DateExit, Code, LocationName, ReportDate, Explanation FROM dbo.tableWHERE 1 = 1 AND Code = ISNULL(@location, Code) AND Exclusion <= ISNULL(@exclude, Exclusion) AND Exclusion >= ISNULL(@exclude2, Exclusion) AND Adult = ISNULL(@Adult, Adult) AND Youth LIKE ISNULL('%' + @Youth + '%', Youth) AND LocationName LIKE ISNULL ('%' + @LocationName + '%', LocationName) AND......[/code]I am currently rewriting a database to learn more about sql, I have hit a point in procedures that I can't piece together. Any direction much appreciated,Essentially what this procedure is saying pull ID through explanation from dbo.table, and placing the data in procedure_table. I don't understand what the @variables are after Create Procedure? Once I understand that I can better interpret what the 'WHERE' is truly doing. Thank you.

Changing a User Defined Function into a View

Posted: 16 Aug 2013 06:22 AM PDT

Hi. I inherited a User Defined function I'm trying to modify and having some trouble. :crazy: I would like to modify it into a regular query and then make it into a view. The function is [b]ufn_B2H_Enrolled_Clients_List[/b]. This function pulls a list of clients enrolled in program during a time period selected. Within [b]ufn_B2H_Enrolled_Clients_List[/b] is a function [b]ufn_B2H_STATUSES_THROUGH_DT[/b] . This selects the maximum status change a client had during the time range selected so if a client was enrolled then disenrolled and then enrolled again the function is not function them into the count.What I would like is to take [b]ufn_B2H_Enrolled_Clients_Lis[/b]t and turn it into a regular query/view:[code="sql"]**ufn_B2H_Enrolled_Clients_List Function**CREATE FUNCTION [dbo].[ufn_B2H_Enrolled_Clients_List](@From_DT datetime, @To_DT datetime)RETURNS @retB2H_Enrolled_Clients_List TABLE ( CLT_NBR int )AS BEGIN INSERT INTO @retB2H_Enrolled_Clients_List --Include all clients enrolled prior to the period end date SELECT CLT_NBR FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 WHERE s1.B2H_STATUS=4 AND s1.Max_Effect_DT <DATEADD(d,1,@To_DT) --Exclude all clients disenrolled or transferred out prior to the start date EXCEPT SELECT s2.CLT_NBR FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s2 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s2.CLT_NBR WHERE s2.B2H_STATUS IN (7,9) AND s2.Max_Effect_DT <@From_DT AND s2.Max_Effect_DT>s1.Max_Effect_DT AND s1.B2H_STATUS IN (4,8) --Exclude all clients who transferred in after the prior end date. EXCEPT SELECT s3.CLT_NBR FROM ECMS.dbo.B2H_STATUS s3 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s3.CLT_NBR WHERE s3.B2H_STATUS = 8 AND s1.B2H_STATUS IN (4,8) AND s3.EFFECT_DT > @To_DT AND s3.Effect_DT>s1.Max_Effect_DTRETURNENDGOCREATE FUNCTION [dbo].[ufn_B2H_STATUSES_THROUGH_DT](@Through_DT datetime)RETURNS @retB2H_STATUSES_THROUGH_DT TABLE ( CLT_NBR int ,B2H_STATUS int ,Max_EFFECT_DT datetime )AS BEGIN INSERT INTO @retB2H_STATUSES_THROUGH_DT S ELECT CLT_NBR ,B2H_STATUS ,MAX(EFFECT_DT) AS Max_Effect_DT FROM [ECMS].[dbo].[B2H_STATUS] WHERE EFFECT_DT<DATEADD(d,1,@Through_DT) GROUP BY CLT_NBR, B2H_STATUSRETURNENDGO[/code]
Posted by at No comments:

[Articles] Should He Stay or Should He Go?

[Articles] Should He Stay or Should He Go?

Should He Stay or Should He Go?

Posted: 15 Aug 2013 11:00 PM PDT

Read the first page of the linked article and then cast your vote in this Friday's poll.

SQL Compare Need to compare and sync database schemas?
Let SQL Compare do the hard work. "With the productivity I'll get out of this tool, it's like buying time." Robert Sondles. Download a free trial.
Posted by at No comments:

[MS SQL Server] Number of logins

[MS SQL Server] Number of logins

Number of logins

Posted: 15 Aug 2013 07:13 PM PDT

Hello,Following on from a similar post I saw on here earlier ([url]http://www.sqlservercentral.com/Forums/Topic951703-391-1.aspx[/url]) I was wondering how people prefer to manage the number of logins on their instances.Do you simply permit as many logins as are needed, or do you try to restrict that number?Just wondering.

sqlserver 2008R2 installation - Service account error in Error log

Posted: 15 Aug 2013 05:44 AM PDT

Hello,I have installed sqlserver 2008R2 on new m/c as a part of migrating from sql 2005 to sql 2008.Next day I was looking the log and found the error message:Login failed for user 'MSSqlDB2K8\sqlsrvAcct'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]I have created this account during installation but didn't add account int oadministrator group.I have also tried to login using same credential in SSMS and I can't connect.When tried to sto pand start the service i can do it.I have added user into Administer group but still not working.I have also assigned account in Sysadmin server Role into Security.Thank you!

Any chance to change authentication mode via registry

Posted: 15 Aug 2013 08:17 PM PDT

Hi All,Can you please advise.. Do we have chance to change authentication mode via registry....
Posted by at No comments:

[SQL 2012] Backups \ Maint Jobs AlwaysOn SQL 2012

[SQL 2012] Backups \ Maint Jobs AlwaysOn SQL 2012

Backups \ Maint Jobs AlwaysOn SQL 2012

Posted: 16 Aug 2013 03:00 AM PDT

Hello all, I have installed and completed a AlwaysOn 2 node VM failover cluster, what fun :-D! I was starting to setup the backup jobs and ran across how AlwaysOn works with this. A little back ground, we slammed this in to meet a deadline and boot contractors out so my learning was put on hold until after we went live, Ya me as the DBA!So I went and change the back preference to Any Replica, this allowed me to back up the primary node and place the tlog BU, re-index, checkdb, update stats, clean files jobs. Now the secondary replica I want to backup as well. The notes I had read from a MVP DBA had said that if I used Any Replica I could backup both...No Dice! I can put a copy only full backup on the secondary but it will not backup the user database, just master and MSDB.I also found the Exclude Replica option but cannot get a clear explanation on it, it could be the lack of sleep I have had over the past week slamming this into Prod, which I am not happy about but thus is the life of a DBA, right! I am sure we all know!Besides the backup, I want to setup Maint. Jobs on both. Now I know that AlwaysOn is Mirroring and FCI, it works, great, I know if I rebuild some indexes it will copy over to the secondary, that's great too however this is two separate databases so I want them both the be clean. :)So my questionsa. How can I backup both P and S?b. What Maint. Jobs should be set up on each?Thanks!

Differential Backup Failed

Posted: 15 Aug 2013 09:03 PM PDT

Hi,I've scheduled M-Plan to perform Full & Diff. backups, till yesterday those plans were ran fine, but Today Diff. backup job failed with the reason below --Executing the query "BACKUP DATABASE "" TO DISK = N'..." failed with the following error: "Cannot perform a differential backup for database "", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly (Error -- "-1073548784")I already performed Full backup job, but still failing with the same reason...please help me..it is an emergency...

Tempdb gets full when I run query

Posted: 16 Aug 2013 02:08 AM PDT

Hi AllI have 5 temp tables that are inner joined to load 6 million rows into a master temp table.The query that loads 6-7 million rows from these 5 primary temptables into master table is taking more than 2 hrs and throwing an error [i]Msg 9002, Level 17, State 4, Line 201The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases[/i]Below are the temp tables and the query used to load data into master temp table.#noncalc_Data -- 160033 rows#CHTYAMT_DATA -- 11538 rows #BADAMT_DATA --73783 rows#INSADJ_DATA -- 554835 rows#INSOSB_DATA -- 47871 rows#OSPATBAL_DATA -- 83934 rows#PRECALC_DATA -- 972092 RowsSELECT #NonCalc_DATA.accountNumber, #NonCalc_DATA.mrn, #NonCalc_DATA.guarantorNumber, recordAccountId , patientType , insurance1FinancialClass , insurance2FinancialClass , insurance3FinancialClass , #NonCalc_DATA.currentFinancialClass, payer, accountstatus , hospitalId , serviceType , #NonCalc_DATA.guarantorLastName, #NonCalc_DATA.guarantorFirstName, #NonCalc_DATA.guarantorMiddleInitial, #NonCalc_DATA.guarantorAddress1, #NonCalc_DATA.guarantorAddress2, #NonCalc_DATA.guarantorCity, #NonCalc_DATA.guarantorState, #NonCalc_DATA.guarantorZip, #NonCalc_DATA.guarantorHomePhone, #NonCalc_DATA.guarantorSSN, #NonCalc_DATA.guarantorDOB, #NonCalc_DATA.guarantorGender, #NonCalc_DATA.guarantorEmployer, #NonCalc_DATA.patientLastName, #NonCalc_DATA.patientFirstName, #NonCalc_DATA.patientMiddleInitial, #NonCalc_DATA.patientAddress1, #NonCalc_DATA.patientAddress2, #NonCalc_DATA.patientCity, #NonCalc_DATA.patientState, #NonCalc_DATA.patientZip, #NonCalc_DATA.patientHomePhone, #NonCalc_DATA.patientSSN, #NonCalc_DATA.patientDOB, #NonCalc_DATA.patientGender, #NonCalc_DATA.patientEmployer, #NonCalc_DATA.admitDate, #NonCalc_DATA.dischargeDate, #NonCalc_DATA.arPostingDate, #NonCalc_DATA.lastBillingDate, #NonCalc_DATA.lastPaymentDate, #NonCalc_DATA.selfPayDate, #NonCalc_DATA.closingDate, #NonCalc_DATA.returnMailFlag, #PRECALC_DATA.totalCharges_48 , #PRECALC_DATA.accountBalance_49, #PRECALC_DATA.totalPayments_50, #PRECALC_DATA.TotalAjustments_51, #CHTYAMT_DATA.charityAmount -- 52 , #PRECALC_DATA.InitialPatientResponsibility_53, #OSPATBAL_DATA.outstandingPatientBalance, #PRECALC_DATA.patientPayments_55, patientAdjustments_56 , #BADAMT_DATA.badDebtAmount --57, #PRECALC_DATA.insurancePayments_58, #NonCalc_DATA.insurance1Payments --59, #NonCalc_DATA.insurance2Payments -- 60, #NonCalc_DATA.insurance3Payments -- 61, #INSADJ_DATA.insuranceAdjustments --62, #INSOSB_DATA.insuranceOutstandingBalance --63, insurance1OutstandingBalance , insurance2OutstandingBalance , insurance3OutstandingBalance , hisUserId , agencyCode , earlyOutPlcmtDate , badDebtPlcmtDate , collectorId , billingType , adminHold , balanceVisible, billingIndicator , fileType , firstStatementDate , lastStatementDate , ins1PlanCode , ins2PlanCode, ins3PlanCodeINTO #MASTER_DATA FROM #NonCalc_DATA INNER JOIN #PRECALC_DATA ON #NonCalc_DATA.accountNumber = #PRECALC_DATA.ACCOUNT INNER JOIN #CHTYAMT_DATA ON #PRECALC_DATA.ACCOUNT = #CHTYAMT_DATA.ACCOUNT INNER JOIN #BADAMT_DATA ON #CHTYAMT_DATA.ACCOUNT = #BADAMT_DATA.ACCOUNT INNER JOIN #INSADJ_DATA ON #BADAMT_DATA.ACCOUNT = #INSADJ_DATA.ACCOUNT INNER JOIN #INSOSB_DATA ON #INSADJ_DATA.ACCOUNT = #INSOSB_DATA.ACCOUNT INNER JOIN #OSPATBAL_DATA ON #INSOSB_DATA.ACCOUNT = #OSPATBAL_DATA.ACCOUNTthe temp tabels do not have any indexes but I tried adding and it didnt help.Does it help if I use table variable or how can I resolve this Issue.

Strange Duration numbers for a server-side trace

Posted: 15 Aug 2013 11:55 PM PDT

Trying to hone in a few procedures to identify reads and durations. The reads are undercontrol after some indexes tweaked but still seeing high durations (with low reads). 1) If I add up Duration for SP:Statement Completed - it should equal SP:Completed.:SP:Completed PROCCHILD 1000SP:Completed FUNCTIONCHILD 0SP:Completed PROCPARENT 2000The parent stored proc called PROCPARENT Calls the 2 above it - PROCCHILD and FUNCTIONCHILD. Duration here should be 1000, but it seems to double. 2) Similarly - If I add up SP:StmtCompleted for a give proc. I would expect SP:Completed to equal all of the statements. Its the sum of all the parts. Unless I am missing something which I clearly am. Is there a recompile event or something like that thats not attributable directly to a statement? If so, then I need to include that in my trace.

SSRS 3.0 adding percentage field (calculated field)

Posted: 15 Aug 2013 09:39 PM PDT

Hi all,I am doing a report and in one of the datasets I have to do a calculated field named % Current AR, this should be a simple calculation (Current AR/Total AR) as I normally do in excel, but now I can't get it to do it, anyone has a suggestion? Do you need more information than the one in provided:-).I would really appreciate some assistance since this is driving me crazy...Regards,Daniel

The way to link 2012 with 2008 R2 throuh merge replication

Posted: 15 Aug 2013 11:14 PM PDT

Hello folks!I am going to create the merge replication between 2 instances one of which runs on 2008 R2, and second on 2012.Surely, 2012 is publisher and distributer, 2008 R2 - subscriber.The goal is to organyze 2-way merge replication.The question is hypotetical: Is there a way to restore the 2012's state on 2008 R2? backup/restore won't work as MS doesn't support backward compatibility. I don't like the Generate Scripts service either. Could I use the replication snapshot to make the state of 2008 R2 identical to 2012? How would it be working? Are there the other ways (including 3Dparty's products)?

Good Book On SSRS 2012

Posted: 15 Aug 2013 09:22 PM PDT

http://goo.gl/aXPZU

data tier application vs database project

Posted: 15 Aug 2013 01:06 PM PDT

Can someone explain when to use one over the other please?Thanks

Mapping Dissimilar Data

Posted: 02 Aug 2013 10:35 AM PDT

For a busy OLTP application, what is the most efficient SQL method/process/tool/technique to map two dissimilar databases in real time? Queries from a new application need a homogenous interface whether connected to an old, inefficient legacy DB or to a new one with different structure. Is this what SCHEMABINDING does?See attached diagram.

Importing data from an AS400 database in SSIS

Posted: 15 Aug 2013 04:15 AM PDT

So I am attempting to import data from an IBM AS400 database into SQL Server. I can do it easily enough via linked server - however the processing time is off the chart. In an attempt to speed things up i am trying to create an SSIS package that will import the data. The problem I am having is that the date on the AS400 is stored in the format CYYMMDD. So I need to pull 90 days back when I import the data into SQL. Does anyone have any experience in converting a SQL date to CYYMMDD in SSIS? I have 2 functions in SQL that will convert it to the appropriate format: The Main Fuction is below:CREATE FUNCTION [dbo].[fnStdToDate](@SQL_Date DATETIME)RETURNS INTAS BEGIN RETURN CAST(CASE WHEN YEAR(@SQL_Date) > 1999 THEN '1' ELSE '' END + SUBSTRING(CAST(YEAR(@SQL_Date) AS VARCHAR(4)),3,2) + dbo.udf_padl(CAST(MONTH(@SQL_Date) AS VARCHAR(2)),2,'0') + dbo.udf_padl(CAST(DAY(@SQL_Date) AS VARCHAR(2)),2,'0') AS INT)ENDCREATE function [dbo].[udf_padl] ( @cString nvarchar(4000) , @nLen smallint , @cPadCharacter nvarchar(4000) = ' ' )returns nvarchar(4000)as begin declare @length smallint , @lengthPadCharacter smallint if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0 set @cPadCharacter = space(1) select @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode if @length >= @nLen set @cString = left(@cString, @nLen) else begin declare @nLeftLen smallint set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString end return (@cString) endCREATE function [dbo].[udf_padr] (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )returns nvarchar(4000)as begin declare @length smallint, @lengthPadCharacter smallint if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0 set @cPadCharacter = space(1) select @length = datalength(@cString)/ (case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode select @lengthPadCharacter = datalength(@cPadCharacter)/ (case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode if @length >= @nLen set @cString = left(@cString, @nLen) else begin declare @nRightLen smallint set @nRightLen = @nLen - @length -- Quantity of characters, added on the right set @cString = @cString + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen) end return (@cString) endSo i can run the following sql and get the converted date: select dbo.fnStdToRdcDate(getdate()-90)Result: 1130517So my question is how can I make this compatible with SSIS? It seems I am somewhat limited with the expression builder. Thanks in advance for any help and comments.

SSIS: FTP task fails when called from execute package task

Posted: 15 Aug 2013 09:39 AM PDT

Hi All,So we've got a standard FTP package that pulls a file from an FTP server and deposits it nicely in the folder of your choosing, pretty standard stuff. When I execute the package in SSDT it runs through no problems and grabs my file like a good little package.However! When I call this FTP package using an Execute Package Task from within another SSIS task the execute package task fails giving the wonderfully useful error message: FTP Package Failed. Eventvwr is similarly useless and I've even had a look at procmon with no pointers in the right direction.I'm using the standard FTP transfer and not SFTP. Has anyone come across similar issues?Cheers,Jim.
Posted by at No comments:

[T-SQL] while loop

[T-SQL] while loop

while loop

Posted: 15 Aug 2013 01:23 PM PDT

hii want to do batch and insert records from 1 table to another.code :declare @min intdeclare @end intset @min = select min(id) from empset @end = select max(id) from empwhile(@min<@end)insert into table bselect * from tableawhere id between @min and @endset @min = @min +1000here when i am inserting i need to do it with increment,there is no need forset @min = @min +1000.example,if @min =1 and @max = 1000.first i want to do from 1 to 1000then 1001 to 2000here its taking from 1 to 10000 directly,not doing in batchalso i need to make sure that @max<=@endplease help me

Add Check Constraint in Two Columns

Posted: 15 Aug 2013 04:46 PM PDT

Hi All,I have a database called EmployeeDB and now in this database I have two tables (dbo.employeeDetails) and dbo.couresDetails.Now In course details table I have only columns Emp_id and IsEmployee. In Emp_id column there is a foreign key and the primary is located on employeedetails table. And IsEmployee column contains only 2 values either true or False.Now I am trying to insert the data into dbo.couresDetails table. I want to add a check constraint on Isemploee column. As Emp_id has a foreign column the value should be multiple. Where the value of isdelted will be [b]TRUE [/b] it should be inserted and where the value is False, only 1 time it should be inserted for a id. Output should be like the following. Empid Isdeleted 1 True -------it should be inserted 1 Flase ------- it should be inserted 1 False-- when trying to insert False value for 1 it should stopped--error 1 True -------it should be inserted ---Similarly for for other ids 2 True -------it should be inserted 2 Flase ------- it should be inserted 2 False-- when trying to insert False value for 1 it should stopped--error 2 True -------it should be insertedHow do I apply check constraint here? Will I use some other function like triggers?Please help!!Thanks in advance

display two different resultset from single query or SP

Posted: 15 Aug 2013 11:22 PM PDT

Hi,I want to display capusewise cout of students as per attached excel.i.e. 1) first resultset will display statuswise cout of student in each campus 2) Second result set will display Subjectwise + statuswise cout of student in each campusPlease guide.ThanksAbhas.

Help Required!!

Posted: 15 Aug 2013 03:31 AM PDT

I have the values in a column like 'email=abc@microsoft.com;rota=mon-fri'And i need in my select statements to pickup email value and rota value.Can anybody help please? or can anybody come up with a better solution to store the values in a column to easily retrieve those two values.Thank you.

Query Performance

Posted: 15 Aug 2013 02:21 PM PDT

Hi All,I have written a T SQL statement and i estimate it will take 8 hours to complete, it has been running now for 3 hrs 20 mins and it has produced around half the expected results. I am querying a data warehouse with read only access at the moment, so i can't use the query analyzer. Can anyone look at my code suggest some improvements?[code="sql"];WITHcteClosingBalance(CLINAME1,CLICODE,AGPNAME1,COANAME1,DIVNAME1,COACODE2CODE,COACODE2NAME1,CLTNAME1,closingBalance)AS (select c.CLIName1 ,c.CLICode ,g.AGPName1 as [GL Account Group] ,a.COAName1 as [GL Account] ,d.DIVName1 ,a.COACode2Code as [GLChart Code 2] ,a.COACode2Name1 as [GLChart Code 2 Name] ,t.CLTName1 as [Client Type] ,SUM(l.GNLBalanceBase) AS 'Closing Balance' from DW.vwChartOfAccount ainner join DW.vwChartOfAccountGroup gon a.COAAGPID = g.AGPIDinner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (127,128,129,130)GROUP BY CLIName1 ,CLICode ,AGPName1 ,COAName1 ,DIVName1 ,COACode2Code ,COACode2Name1 ,CLTName1),cteOpeningBalance(DIVNAME1,OpeningBalance,CLTNAME1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'Opening Balance' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwChartOfAccountGroup gon a.COAAGPID = g.AGPIDinner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (127)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1 ),cteJan13(DIVNAME1,JBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'January' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210') and l.GNLFSMID IN (128)GROUP BY DIVName1 ,t.CLTName1 ,COACode2Code ),cteFeb13(DIVNAME1,FBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'February' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210')and l.GNLFSMID IN (129)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1 ),cteMar13(DIVNAME1,MBalance,CLTName1,COACODE2CODE)AS( select d.DIVName1 ,SUM(l.GNLBalanceBase) AS 'March' ,t.CLTName1 ,a.COACode2Code as [GLChart Code 2] from DW.vwChartOfAccount ainner join DW.vwGeneralLedger lon a.COAID = l.GNLCOAIDinner join DW.vwDivision don l.GNLDIVID = d.DIVIDinner join DW.vwClient con l.GNLCLIID = c.CLIIDinner join DW.vwClientType ton c.CLICLTID = t.CLTIDwhere COACode2Code in ('CA100','CA180','CA200','CA210')and l.GNLFSMID IN (130)GROUP BY DIVName1 ,COACode2Code ,t.CLTName1)select cb.CLIName1 as [Client] ,cb.CLICode as [Client Code] ,cb.AGPName1 as [GL Account Group] ,cb.COAName1 as [GL Account] ,cb.closingBalance as [Closing Balance] ,cb.COACode2Code as [GLChart Code 2] ,cb.COACode2Name1 as [GLChart Code 2 Name] ,cb.DIVName1 as [Division] ,cb.CLTName1 as [Client Type] ,ob.OpeningBalance as [Opening Balance] ,j.JBalance as [January] ,f.FBalance as [Feburary] ,m.MBalance as [March] ,SUM(ISNULL(ob.OpeningBalance,0) + ISNULL(j.JBalance,0) + ISNULL(f.FBalance,0) + ISNULL(m.MBalance,0)) as [March End] from cteClosingBalance cb inner join cteOpeningBalance obon ob.DIVNAME1 = cb.DIVNAME1 AND ob.COACODE2CODE = cb.COACODE2CODE AND ob.CLTNAME1 = cb.CLTNAME1left outer join cteJan13 Jon j.CLTName1 = cb.CLTNAME1 and j.DIVNAME1 = cb.DIVNAME1 AND j.COACODE2CODE = cb.COACODE2CODE left outer join cteFeb13 fon f.CLTName1 = cb.CLTNAME1 and f.DIVNAME1 = cb.DIVNAME1 AND f.COACODE2CODE = cb.COACODE2CODEleft outer join cteMar13 mon m.CLTName1 = cb.CLTNAME1 and m.DIVNAME1 = cb.DIVNAME1 AND m.COACODE2CODE = cb.COACODE2CODEGROUP BY cb.CLIName1 ,cb.AGPName1 ,cb.COAName1 ,cb.DIVName1 ,cb.COACode2Code ,cb.COACode2Name1 ,cb.CLTName1 ,cb.closingBalance ,ob.OpeningBalance ,cb.CLICODE ,JBalance ,FBalance ,MBalanceorder by cb.DIVName1[/code]Thanks for any help.

Dynamic Sorting Issue

Posted: 15 Aug 2013 07:54 AM PDT

Hello Everyone,I'm pretty sure that this is possible but I am stuck. I am trying to write a query that sorts based on a couple variables... This query works perfectly:[code="sql"]--WORKINGDECLARE @Sort1 varchar(10)='val3';DECLARE @Sort2 varchar(10)='val2';DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 varchar(10));INSERT @x VALUES ('xxx','ccc','1a'),('yyy','bbb','5a'),('zzz','aaa','2a'), ('xxx','ccc','5a'),('yyy','bbb','5a')SELECT * FROM @xORDER BY CASE @Sort1 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END, CASE @Sort2 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END[/code][u]The issue is that val3 needs to be an[b] int.[/b][/u] In the updated sample below the query works if @Sort1 = 'val3'. If @Sort='val1' or 'val2' the query will fail with the error: 'Conversion failed when converting the varchar value 'ccc' to data type int.' I can get it to work if I change[b] [i]WHEN 'val3' THEN val3[/i][/b] to [b][i]WHEN 'val3' THEN CAST(val3 AS varchar(10))[/i][/b] but then it will sort it as a string (1,11,2,21,22,3,4,etc...) which is not what I need.[code="sql"]DECLARE @Sort1 varchar(10)='val2'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1--DECLARE @Sort1 varchar(10)='val3';DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11), ('xxx','ccc',5),('yyy','bbb',5)SELECT * FROM @xORDER BY CASE @Sort1 WHEN 'val1' THEN val1 WHEN 'val2' THEN val2 WHEN 'val3' THEN val3 END[/code]I have been CASTing and CTEing by brains out with no luck. Any help would be appreciated. Thanks!

Pivot and Merge Columns

Posted: 15 Aug 2013 07:03 AM PDT

Is there any way to take the following:Part Whse11c ATL11c Day11c BHAM21E ATL21E CINCY23M DAYand merge the various WHSE into a single column.. like11c ATL, Day, BHAM21e ATL, CINCY23m DAYWhat I want to do.. is the above in a CTE then join for the main query on CTE.part = main.part.

Need help in query...

Posted: 15 Aug 2013 04:42 AM PDT

[u]Number[/u] 10 20 30 40 50table has a column 'Number'expected result is[u]Numer-range[/u] 10 - 20 20 - 30 30 - 40need to diaplay as it is

Help on inserting results into Table

Posted: 15 Aug 2013 02:02 AM PDT

Hi everyone.Could someone show my how to insert this result into an SQL table.DECLARE @total_buffer INT;SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';;WITH src AS( SELECT database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id)SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)FROM srcORDER BY db_buffer_MB DESC; I know the error is in relation to declaring the variable , but I am at a bit of loss of how to correct it ?Any help appreciated.Thank you
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog