Saturday, September 21, 2013

[how to] How to reduce table duplication, while ensuring integrity of the database?

[how to] How to reduce table duplication, while ensuring integrity of the database?

How to reduce table duplication, while ensuring integrity of the database?

Posted: 21 Sep 2013 08:57 PM PDT

For example, in my app I have some class called Contact:

case class Contact(name: String, email: String, phone: String)

And several other classes have one-to-many relation to that contact (for example, there are Companies and Houses).

Currently, I have database table for Contacts set up as something like this:

CREATE TABLE `Contacts` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `entityId` bigint(20) NOT NULL,   `entityType` tinyint(4) NOT NULL,   `name` varchar(300),   `phone` varchar(300),   `email` varchar(300),    PRIMARY KEY (`id`)  )

where entityType is storing the "code" that determines the entity type this contact belongs to (1 for Company, 2 for House, etc).

Now, I understand that this is awful - at the very least, I can't create a foreign key on entityId, thus I can't ensure that my data is consistent.

The alternative will be to create a separate Contacts table for each entity type (CompanyContacts, HouseContacts, etc) but that will result in lots of duplication in both database and application code.

Is there a better way to do this?

How can I find and clean up unused database files?

Posted: 21 Sep 2013 07:34 PM PDT

How can I find and clean up unused (i.e. not used by any attached database) SQL Server database files (.mdf, .ldf, .ndf) that take up extra space on my server?

Help in choosing DBMS [on hold]

Posted: 21 Sep 2013 12:20 PM PDT

I would like to ask you some help. Well, we need to choose DBMS (on the Back-End side) for the project. But the problem is that I don't know all features of different DBMSes really good. So, after some research and spending time with Google, I decided to ask a little bit of help here : )

Well, the project, if fact, is some kind of internet service, all Back-End (and database) is on our side.

We have answered some questions (for ourselves) and our main criterions are:

  • Not Oracle DB;
  • Doesn't matter, SQL or NoSQL;
  • DBMS has to support C# (.NET);
  • Scalability is important, because we really don't know how many users will be - 1 000 or 1 000 000;
  • We don't keep media content on the Back-End side;
  • Fast transactions and getting results are important (there is no possibility to wait 10-20-40 seconds for result of query - users are so impatient :)
  • Approximate ratio of queries: Number of SELECT > Number of UPDATE > Number of INSERT

I'm new in DBA, so I will be very grateful for detailed answers!

After spending time for research I decided that we can use MySQL/PostgreSQL or MongoDB (the last one has great scalability by sharding!).

And one more question - what questions we must answer to make right choice?

And thanks a lot!

query cache hit values is not getting change in my database

Posted: 21 Sep 2013 02:57 AM PDT

query cache hit values is not getting change in my database.It showing zero values even i execute same query for two to three times.

Thanks.

Custom Auto-Generated Sequences with SQL Server

Posted: 21 Sep 2013 03:02 AM PDT

I have Employee table with Empolyee ID column EmpID (nvarchar(5)) ,
and I want to make EmpID's value to 

00001    00002    00003    00004    ....  ....

How can I make Custom Auto-Generated Sequences likes this ?

How to Recover corrupt SQL Sever database?

Posted: 21 Sep 2013 03:03 AM PDT

I am running Windows 2000 OS and while using my SQL application a message popped-up, can anyone figure it out, the message is like that.

Msg 5172, Level 16, State 15, Line 1

How to UPDATE a table for the level of parents in a column?

Posted: 21 Sep 2013 12:03 AM PDT

I have a table with internal parents as

id    parent    parent_level  1     NULL      1  2     1         2  3     1         2  4     3         3

where parent has a FK to id.

How can I update the table to assign the values of parent_level, according to the parent, and parent of parent of each row.

Import only selected column from tsv into mongodb

Posted: 21 Sep 2013 12:09 AM PDT

Edit

I am uploading very big tsv file thats why i dont want to import unnecessary column in mongodb, I know how to upload file into mongodb(just as attachment) as:-

mongoimport --db xyztsv--c review1 --type tsv --file /Reviews1.tsv -f member_id,product_id,date1,no_helpfeedback,no_feedback,rating,title,body

where I just want title and body column to be import on mongodb,is their any command or do I have to write any JSP function for it ? I know it can be done through cygwin on windows but Is it possible to do it directly while importing on mongoDb. many thanks.

Can scheduled and continous replication configurations exist side-by-side on the same master/slave servers?

Posted: 21 Sep 2013 09:22 AM PDT

Environment

We have a core sql server cluster. This cluster contains some databases that get replicated to a load-balanced sql cluster of currently 3 servers. These databases are replicated each 12 hours but will eventually be replicated every 4 hours.

Requirement

On this cluster a new database is created and we need this database to be replicated asap to the load-balanced sql cluster. A delay of seconds or minutes is allowed and writes to this database are currently and in the future low (a few per hour).

Questions

Can two different replication plans coexist side-by-side on the same environment?

Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?

Does this create a high risk for a large existing scheduled replication job?

Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).

My brainwaves

I can't imagine that this minor write activity with continuous transaction replication can create issues for the large existing replication job. I can imagine the other way around that our continuous replication will suffer twice a day due to the large replication job. We are perfectly fine with that as replication is required ASAP during regular operation.

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

Posted: 21 Sep 2013 05:48 AM PDT

I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found.

Commands ran:

CREATE TABLE mytable (id1 int, id2 int, score float) ENGINE=MyISAM;  LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);  ALTER TABLE mytable ADD INDEX id1_index (id1);    SELECT COUNT(*) FROM mytable; # returns 50 billion  SELECT COUNT(DISTINCT id1) FROM mytable; # returns 50K, should be about 50M  SELECT COUNT(*) FROM mytable WHERE id1 = 49302; # returns 0 results    ALTER TABLE mytable DISABLE KEYS;  SELECT * FROM mytable WHERE id1 = 49302 LIMIT 1; # returns 1 row

Is this a bug with MySQL, or does this behavior make sense for some reason?

Note: When I ran ALTER TABLE mytable ENABLE KEYS; just now, the command is acting like it is building an index for the first time (it's still running after 30 minutes, and memory usage is at 80 GB, which matches my setting of myisam_sort_buffer_size=80G. I'll reply when this command finishes running (the original ALTER .. ADD INDEX.. took 7.5 hours, so it may be a bit).

Update: Running SHOW PROCESSLIST indicates "Repair with keycache" is taking place with my ENABLE KEYS command.

Update 2: I killed the repair job on the original index because after several hours, the memory and IO seemed pretty constant, and I hoped if I started over, it may just work. So in second pass, I rebuilt the table and index, and after doing so, the exact same result occurs.

As requested, here is explain for count queries with index enabled:

mysql> explain select * from mytable where id1 = 49302;  +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+  | id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra       |  +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+  |  1 | SIMPLE      | mytable   | ref  | id1_index     | id1_index | 5       | const |    1 | Using where |  +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+  1 row in set (0.00 sec)    mysql> explain SELECT COUNT(DISTINCT id1) FROM mytable;  +----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+  | id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows      | Extra                    |  +----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+  |  1 | SIMPLE      | mytable   | range | NULL          | id1_index | 5       | NULL | 170331743 | Using index for group-by |  +----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+  1 row in set (0.01 sec)

Here is explains after disabling indexes (Note: 25 billion is correct number of records in table, not 50 billion as mentioned above):

mysql> explain select * from mytable where id1 = 66047071;  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+  | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows        | Extra       |  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+  |  1 | SIMPLE      | mytable   | ALL  | NULL          | NULL | NULL    | NULL | 25890424835 | Using where |  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+  1 row in set (0.00 sec)    mysql> explain SELECT COUNT(DISTINCT id1) FROM mytable;  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+  | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows        | Extra |  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+  |  1 | SIMPLE      | mytable   | ALL  | NULL          | NULL | NULL    | NULL | 25890424835 |       |  +----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+  1 row in set (0.00 sec)

Update 3: Still hoping to solve this oddity. Is there something I can do with myisamchk that might fix this? Since I completely repopulated the table and rebuilt the index (i.e. starting from scratch) and got the same result, I assume this was not just some freak occurrence, and that it is due to some internal limit I'm unaware of. On a side note, I've tried switching to Postgres for this dataset, but running into some other unrelated issues (that I'll leave to a different question), so fixing this index is still a top priority for me. Thanks!!

Update 4: Running CHECK TABLE currently. Will post back with updates as I have them

Inline edit SQL Server database rows from Visual Studio

Posted: 21 Sep 2013 05:22 PM PDT

I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 instances, but from the Server Explorer I can no longer right click on a table "Show Table Data", pop open the SQL pane, query the data then perform inline edits on the results (as if I were modifying a spreadsheet).

Show Table Data

This means I now need to go into SSMS to make these kind of quick updates. Does anybody know of a plugin I can use with VS 2012 to bring back this functionality? It seems odd to me that Microsoft have two different development trajectories with SSDT and SSMS? Are they designed to serve different purposes? Can SSMS be integrated into the Visual Studio IDE? I'd rather have a unified development environment if possible.

Any thoughts on a workaround for this problem would be much appreciated.

EDIT

I know some purists would quiver at the thought of treating a database table like a spreadsheet, but semantically they are not a world apart, plus this is supported in SSMS. I'm more in favour of relying on GUI based approaches where I can to speed up routine tasks, why some would balk at this I have no idea?

Dealing with data stored as arrays in a MySQL DB

Posted: 21 Sep 2013 08:22 PM PDT

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to deal with it.

It basically seems to link 2 tables and add a view count. Here is an example of the data:

a:4:{i:4;i:196;i:26;i:27;i:5;i:155;i:34;i:4;}

So I think this means there are 4 entries in the array, each with 2 attributes. The first - i.e. 4, 26, 5, 34 are "store codes". The second lot (196, 27, 155, 4) are number of plays. God knows why they are stored like this as there is already another table that links the video with the stores and they could've just stuck another column there for view count.

Anywho, what I want to do is order by view count based on store id within that array. Do you think this is possible and does anyone have any ideas how to do this? If storing data like this is a standard, do you know the name for it as I could probably take it from there?

Thanks!

Where to install Oracle client software on SharePoint 2010 Server

Posted: 21 Sep 2013 06:22 AM PDT

We have our ASP.net web application hosted on a SharePoint Server 2010. Our application uses oracle database to save and retrieve data.For our application to work on SharePoint server we need to install Oracle client software.

Our SharePoint Server architecture consists as following

  • 2 web front ends
  • Index Server
  • Crawl Server
  • Application server
  • Load Balancer.

I am not the admin on the SharePoint server and trying to figure out if i have to install Oracle client on server where do i install it? Do i need to install it on all our servers or just the web fronends. Can some one please help me with this?

Unable to connect oracle as sysdba tables have been dropped

Posted: 21 Sep 2013 03:22 PM PDT

I have a script which lists all tables belonging to the user and executes DROP for all of them.

By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba tables are dropped.

Now i can not startup database instance. In alert log, it gives following error:

Sat Jul 20 15:28:21 2013

Errors in file orcl_ora_4276.trc:

ORA-00942: table or view does not exist

Error 942 happened during db open, shutting down database

USER: terminating instance due to error 942

I tried to flashback one droppd table, but it is giving error:

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> FLASHBACK TABLE MAP_OBJECT TO BEFORE DROP;

ERROR:

ORA-03114: not connected to ORACLE

Please suggest if there is any way to restore all these tables. Or if creating new database is the only way?

Creating a database redundancy based on Mysql

Posted: 21 Sep 2013 12:22 AM PDT

I have this requirement where my web application running on a tomcat is behind a loadbalancer and I am planning to deploy this application on multiple servers.

This webapp though needs a database ( mysql ) for managing profile and other similar stuff, now i can only have a single master database and I want all my front end servers to talk to this database. obviously if I do this it becomes my single point of failure , hence the need to run a redundancy/ dynamic failover.

Can you please guide me on same. my requirement though becomes a little more complicated something which i couldn't find on available articles is how to set up a connection to this cluster. Below is the example of how the server.xml is configured in my environment when it talks to single DB

=========================================================================================================================

    <Resource name="jdbc/master" auth="Container" type="javax.sql.DataSource" maxActive="10"              maxIdle="10" validationQuery="SELECT 1" testOnBorrow="false" testWhileIdle="true" validationQueryTimeout="5"              timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000" maxWait="10000"              username="XXX" password="XXXXXX" driverClassName="com.mysql.jdbc.Driver" removeAbandoned="true"

removeAbandonedTimeout="60" logAbandoned="true" **url="jdbc:mysql://localhost:53306/master?autoReconnect=true" />**

Now only thing I can replace is the URL but how is the question. and what should be the localhost pointing to.

Really appreciate any response/suggestions here. please feel free to ask me any information you feel i haven't supplied , suggesting an alternative approach to my problem is equally welcome but please note i cant change the webapp as it is a 3rd party application.

Analyse MySQL General Query Log in Real-time?

Posted: 21 Sep 2013 06:22 PM PDT

We want to use mysql general query log to do real-time monitoring and auditing.

Currently our approach is:

  • set general_log=on;
  • sleep 15m;
  • set general_log=off;
  • scp & rm xxx.log;
  • set general_log=on;...

But the main problem is when turn on/off general log it'll cause a peak of slow query.

I also thought of another approach: turn on genlog; tail -f it and send out the log; periodically truncate the logfile (with "> xxx.log" or "cat /dev/null >xxx.log").

I'm wondering whether it's practical.

If only mysql would provide some built-in general log message queue stuff...

Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

Posted: 20 Sep 2013 10:53 PM PDT

Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

There would be two nodes in the AlwaysOn Failover Cluster Instances setup and two Domain Controllers.

We are not using AlwaysOn Availability Groups.

Will CDC work? and will it failover?

Mysql settings for query_cache_min_res_unit

Posted: 21 Sep 2013 12:22 PM PDT

What is the best setting for query_cache_min_res_unit for these results:

+-------------------------+-----------+  | Variable_name           | Value     |  +-------------------------+-----------+  | Qcache_free_blocks      | 35327     |  | Qcache_free_memory      | 295242976 |  | Qcache_hits             | 236913188 |  | Qcache_inserts          | 49557287  |  | Qcache_lowmem_prunes    | 0         |  | Qcache_not_cached       | 7128902   |  | Qcache_queries_in_cache | 195659    |  | Qcache_total_blocks     | 426870    |  +-------------------------+-----------+

Do I need to change any other settings?

My website creates very large results. This is the current setting:

query_cache_min_res_unit = 4096

Info on the mysql dev website

If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

DB2 db2fm proccess

Posted: 21 Sep 2013 07:22 PM PDT

Server is been up for 365 days, however i got some weird repeated procceses.

Are these normal?

ps -fea | grep db2fm

  db2inst1  643284  229516  29   May 25      - 212564:06 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S  db2inst1  671770  229516  56   May 14      - 227447:02 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S  db2inst1  757794 1237058   0   Apr 19  pts/7  0:00 /bin/sh /home/db2inst1/sqllib/bin/db2cc  db2inst1  774232  229516  30   Sep 25      - 94218:54 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S  db2inst1  962750  229516  30   Jul 18      - 145256:01 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S  db2inst1  999450  229516  29   Aug 17      - 117710:27 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S  db2inst1 1179898  229516  58   Nov 02      - 75788:49 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S

ps -fea | grep db2agent

  db2inst1  409770  680100   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1  450750  778412   0   Apr 18      -  0:03 db2agent (idle) 0  db2inst1  618688  680100   0   Apr 19      -  0:00 db2agent (idle) 0  db2inst1  651440  680100   0   Nov 17      -  0:20 db2agent (DATABASEA) 0  db2inst1  655508  491676   0   Apr 19      -  0:04 db2agent (idle) 0  db2inst1  684038  680100   0   Mar 23      -  0:03 db2agent (DATABASEA) 0  db2inst1  790706  491676   0   Apr 19      -  0:00 db2agent (idle) 0  db2inst1  880672  680100   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1  913438  778412   0   Nov 16      -  0:20 db2agent (idle) 0  db2inst1  946182  491676   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1  991312  778412   0   Apr 17      -  0:16 db2agent (idle) 0  db2inst1 1077466  491676   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1 1134726  680100   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1 1142964  491676   0   Apr 19      -  0:00 db2agent (idle) 0  db2inst1 1233112  491676   0   Apr 19      -  0:00 db2agent (idle) 0  db2inst1 1261748  778412   0   Jun 15      -  0:18 db2agent (idle) 0  db2inst1 1384678  778412   0   Mar 23      -  0:27 db2agent (idle) 0  db2inst1 1404936  680100   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1 1421368  778412   0   Mar 22      -  0:04 db2agent (idle) 0  db2inst1 1445936  491676   0   Apr 19      -  0:00 db2agent (DATABASEA) 0  db2inst1 1482864  491676   0   Jun 16      -  0:31 db2agent (idle) 0  db2inst1 1503440  778412   0   Jun 15      -  0:56 db2agent (idle) 0  db2inst1 1519842  778412   0   Mar 23      -  0:00 db2agent (DATABASEA) 0  db2inst1 1531946  680100   0   Apr 19      -  0:00 db2agent (idle) 0  db2inst1 1572884  680100   0   Apr 19      -  0:00 db2agent (idle) 0

Other info

  oslevel -g  Fileset                                 Actual Level        Maintenance Level  -----------------------------------------------------------------------------  bos.rte                                 5.3.0.40            5.3.0.0    db2fm -s -S  Gcf module 'fault monitor' is NOT operable  Gcf module '/home/db2inst1/sqllib/lib/libdb2gcf.a' state is AVAILABLE      uptime    02:14PM   up 365 days,  12:51,  6 users,  load average: 6.69, 6.89, 6.97     db2level  DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL08020"  with level identifier "03010106".  Informational tokens are "DB2 v8.1.1.64", "s040812", "U498350", and FixPak "7"

Generic SQL Job Scheduler for multiple RDBMS's?

Posted: 21 Sep 2013 08:22 AM PDT

I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex dataflows between the databases. Right now, the scheduling is done through independent systems, but I want to have one scheduler that controls the dataflows from beginning-to-end, and is able to script flows from MS SQL to RedShift, etc. Is there a system that can accomplish this already? I'm not a DBA, so I am guessing someone has had this problem before...

Thanks in advance!

EDIT: So one of our dataflows might look like this - file posted on SFTP --> run normal ETL routines --> compile final complete file --> send to customer/push to S3 --> Run SQL commands on Redshift to load* --> Nightly batch processing on RedShift* --> Unload to S3* --> Load into MySQL*

*These are manually run using a tool that just connects via jdbc (can't remember the program)

My DB-related experience is very light, so I was about to write some python scripts and schedule them in CRON, but that is custom and hard to expand - surely someone has had this problem before. We would like to be able to see a status of the job in one place, create new dataflows/ETL's between all three systems (like an SSIS job).

Login failed for user Error: 18456, Severity: 14, State: 11

Posted: 21 Sep 2013 11:22 AM PDT

I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.

The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I recently added a new user to this group (at AD level), but this person is not able to access SQL Server (through Mgmt Studio) and he's getting the error below

Login failed for user. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

Error: 18456, Severity: 14, State: 11.

I have already verified AD permissions are setup properly, user has restarted his machine, he is not part of any group that has DENY access and the SQL Server XYZ group has been removed and readded to the SQL Server instance in Mgmt Studio and server has been restarted.

Any ideas on how to proceed further?

Thanks!

MySQL data too long error

Posted: 20 Sep 2013 10:22 PM PDT

One of the column in my table was initially created as a varchar(1000). Now there is a need to increase the size of that column to hold around 5000 characters. I used the alter statement to increase the size of that column. DESC table_name as well as SHOW CREATE TABLE table_name clearly indicates the new size of that column to be 5000 characters. However, interestingly when I try to insert any data exceeding 1000 characters ERROR 1406 (22001) : Data too long for column error shows up. Out of desperation I changed the datatype to text, and still it's limited to 1000 characters. Any suggestion?

I created an other dummy table with a coloumn size of varchar(5000) and that works just fine. Engine used is InnoDB and Default Charset is UTF8.

Rent weekly cost database design

Posted: 21 Sep 2013 04:22 PM PDT

I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a foreign key to the belonging BUILDING.ID. The BUILDING_UNIT table also refers to a table CATEGORY which tells whether the BUILDING_UNIT is of category A,B,C,D again with a foreign key pointing to CATEGORY.ID.

Now the final cost of renting the building unit depends on its building, category and on the number of days it is rented and specific period of the year. We only rent them weekly so I might as well use weeks only however I'd like it to be as flexible as possible in the future.

I cannot convince myself on a table which can represent this situation.

Do I have to use a table with coefficients for each day of the year and then a table with coefficients for A,B,C,D and then a table with coefficients for each Building and then somehow calculate a result?

Is there some standard and recognized implementation for problems of this type?

Thank you

EDIT: Notice the solution should abstract from the formula for calculating the cost which might change in the future. However I might be asked to make a specific week of the year, for building unit X inside building Y to cost 300$ while the week after 600$. Generally building units inside the same building and in the same week cost the same, however that might change in future so I'd like to treat already all specific cases.

Proper procedure for migrating a MySQL database to another Debian machine?

Posted: 21 Sep 2013 02:22 PM PDT

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL.

I've created a backup of all databases on the first server like so:

mysqldump -uuser -ppass --all-databases > dump.sql

On the other server, I did a:

mysql -uuser -ppass < dump.sql

At first, everything seemed great. I could browse my databases in phpMyAdmin, but as soon as I tried logging in again, it failed. Turns out, my root password had been overwritten with the one from the older database.

I wanted to reset it, but in order to do so, I would have needed to start mysqld_safe. Which I couldn't because the password for the debian-sys-maint user had been overwritten as well in the database. When I thought all hell had broken loose, I somehow reset both the root and debian-sys-maint passwords to the original values of the new server, and I managed to revert to a clean state.

Since I obviously don't want to go down that road again, here's the question(s):

  • Was I right with my approach of using a complete --all-databases dump?
  • Was there something I needed to do in advance to reading in that dump to prevent this desaster from happening? Or even before creating the dump?

If I'm going about this the wrong way:

  • What is the proper procedure for migrating all databases and their users to another server?

Note that I'm not that experienced with MySQL and server administration at all, so I might be missing something obvious. All the tutorials and how-tos I've found never mention anything like this and just talk about importing the complete dump.

How to add 'root' MySQL user back on MAMP?

Posted: 21 Sep 2013 01:22 PM PDT

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?

Slow insert with MySQL full-text index

Posted: 21 Sep 2013 10:22 AM PDT

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right?

How can I get better performance from the INSERT? Is there perhaps an option to set when MySQL rebuilds the full-text index?

How to design a relationship database table to store friendship relationship?

Posted: 21 Sep 2013 09:57 AM PDT

I want to design a table to store friendship relationship in my web project

It should satisfy at least the following 4 conditions:

who send the add-friend request e.g.(if A TO B then this column will be A)

who receive the add-friend request e.g.(if A TO B then this column will be B)

current status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed

our friend relationship is bilateral

If any of you are experienced with this , any suggestion is welcomed

my current design (I think bad right now) is like this

frienshipId fromUserId toUserId status requestTime these are the columns
Posted by at No comments:

[MS SQL Server] MOVE DB Online

[MS SQL Server] MOVE DB Online

MOVE DB Online

Posted: 20 Sep 2013 08:29 AM PDT

HiIs there any way to move DB to different location with out downtime.It means We have to move DB online with zero downtime. This move can be different hard drive or different server.Currently I am using sql server 2008 R2 Enterprise edition.ThanksAswin
Posted by at No comments:

[SQL 2012] screenshot on view

[SQL 2012] screenshot on view

screenshot on view

Posted: 20 Sep 2013 02:08 PM PDT

I had a table like this:[code="other"]Name StartTime EndTime--------------------------------abc 10:00 14:00bcd 10:05 12:32cde 13:10 14:08dfe 11:00 11:08[/code]We planning to create the view using sql commend to view the total clients in every 30 mins.[code="other"]Time Clients--------------------------------10:00 110:30 211:00 311:30 212:00 212:30 213:00 113:30 214:00 214:30 0[/code]May I know how to create this view....Thanks for your help....

Add additional SQL 2012 instance to Windows Failover Cluster

Posted: 20 Sep 2013 07:07 AM PDT

Hi All,I have a Windows Failover Cluster setup across 3 nodes with only 1 service/application running on it - which is SQL Server 2012. Everything works great, however now I need to add another SQL instance for an entirely different project. I have added an additional iSCSI volume to the failover cluster and it shows up just fine. At this point, how to I add an additional instance of SQL 2012 to this cluster using the new storage disk?I assume I'd just pop the SQL Server 2012 disc in one of the nodes and install a new SQL Server Cluster instance and then go around to the rest of the nodes and just use the 'add node' option, but I just wanted to verify first. I attempted to do this and got cold feet when it came to the part about installing the setup and support files. The reason I got cold feet was because we have a group of people using the existing SQL 2012 instance and I didn't want to disrupt anything.Can anybody please provide any instructions or steps for adding a new instance to my failover cluster?Thank you all!

Setting up multiple AG's on 1 server

Posted: 20 Sep 2013 04:31 AM PDT

Hi all, question for you please:i am upgrading sql from 2008 to 2012 and on one instance we have 3 databases setup with mirroring.The plan is to use AAG but i am not sure if i need to create one availabilty group with 3 databases or i can create 3 separate availabilty groups with 1 database in each availability group.What will be disadvantages/advantages with creating 3 separate availabilty groups with 1 database in each availability group? how about performance and server resources? it is used more resources when you have more AG's on the instance opposed to just 1?This AG will be setup as synchronous with auto failover.The reason why i am thinking to setup 3 separate availabilty groups with 1 database in each availability group is because if for some reason the database is not in synchronous mode i can easily remove replica until the problem is solved for just 1 database instead of 3.
Posted by at No comments:

[T-SQL] Restore Database - is File= optional?

[T-SQL] Restore Database - is File= optional?

Restore Database - is File= optional?

Posted: 20 Sep 2013 03:41 PM PDT

Hello, I am currently working with SQL Server 2008 R2 SP1 I am trying to find out if anyone knows whether the "FILE=" is optional in a database restore for SQL. Currently i've done online restores, offline restores, online page restores etc. I've noticed that this option usually specifies FILE=1 which i am assuming means file 1. Most of the databases i've worked with only have one filegroup. My other assumption is that you can restore pieces of database backups such as FILEGROUP=1 FILE=2 to restore the second file in filegroup 1. this seems pretty powerful.My next question is, if this option is left blank what should happen? I've read the technet article found here:http://technet.microsoft.com/en-us/library/ms186858(v=sql.105).aspxas well as the naming conventions here:http://technet.microsoft.com/en-us/library/ms177563(v=sql.105).aspxI would just like to know if what im reading and what my understanding of that reading is correct. I just dont think i am understanding correctly what the definition of what is in chevron's <> means.Thank you, Tac

SQL Agent Job Error - String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)

Posted: 20 Sep 2013 04:39 PM PDT

I'm running into the following message, "String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)" when running a sql agent job. I'm attempting to execute a stored procedure through the job. Keep in mind that when I run the stored procedure in a normal query window, it works fine and only fails when running it as a scheduled job. My guess is that it has to do with how SQL Jobs execute procedures (especially long procedures) but I have no idea how to fix this. If I use Set Ansi_Warnings OFF, the job will work fine, however, I don't know what other issues this may cause.Thanks for the help.

Comparing records against multiple rows

Posted: 20 Sep 2013 03:57 AM PDT

I'm completely lost on how to do this. I basically want to do a CASE statement that looks at multiple rows.I need look at all the people based on what the main person has (RelCode = 18). Example for Sue: Case when Joe Smith's MedBen = M and Sue's MedBen = '' then SpBen = D-DExample for Kate: Case when Jay Evans's MedBen = M and Kate's MedBen = M then SpBen = DSample data:[u]SubsNum | FName | LName | PlanNum | MedBen | RelCode | SpBen[/u]123 ++++| Joe ++++| Smith | FH +++| M ++++| 18 ++++|123 ++++| Sue ++++| Smith | DH +++| +++++| 19 ++++|123 ++++| Ale ++++ | Smith | DH +++| +++++| 19 ++++|245 ++++| Jay ++++| Evans | FH +++| M ++++| 18 ++++|245 ++++| Kate ++++| Evans | FH +++| M ++++| 19 ++++|245 ++++| Mike ++++ | Evans | FH +++| M ++++| 19 ++++|[code="sql"]CREATE TABLE [dbo].[tbl_SubscriberTest]([SubscriberNum] [varchar](50) NULL,[SSN] [varchar](11) NULL,[FirstName] [varchar](50) NULL,[LastName] [varchar](50) NULL,[PlanNum] [varchar](50) NULL,[MedBen] [varchar](1) NULL,[DenBen] [varchar](1) NULL,[RelCode] [varchar](3) NULL,[SpBen] [varchar](4) NULL) ON [PRIMARY] INSERT [dbo].[tbl_SubscriberTest] ([SubscriberNum],[SSN],[FirstName],[LastName], [PlanNum],[MedBen],[DenBen],[RelCode],[SpBen]) VALUES( '1234', '1111', 'MILAN','WHITEHURST','DENT','', 'D','01',''), ( '1234', '2222', 'NICOLAS','WHITEHURST','DENT','', 'D','19', ''), ( '1234', '1234', 'MONIQUE','WHITEHURST','DENT','', 'D','18',''), ( '2468', '2468', 'WILLIAM','CARPENTER','FCNB', 'M','D','18',''), ( '2468', '3333', 'ALEXANDRIA','CARPENTER','FCNB', 'M','D','19',''), ( '2468', '4444', 'SHAYLENA', 'CARPENTER','FCNB', 'M','D','19', ''), ( '2468', '7894', 'JOANN','CARPENTER','FCNB', 'M','D','01',''), ( '3692', '3692', 'JOE','WALKER','FHL','M', 'D','18',''), ( '3692', '9632', 'JOHN','WALKER','DENT', '', 'D','19',''), ( '3692', '3574', 'JAMES','WALKER','DENT','', 'D','19',''), ( '3692', '7531', 'JAKE','WALKER','DENT', '', 'D','19', '')[/code]Does that make sense? In the data from the SQL code,anyone with a RelCode of 18 is the main employee. So if the MedBen for the main employee is M but any of the other people in that group of same SubscriberNum have '' for MedBen then I'd need to change the SpBen to D-D. Example: Joe, John, James, and Jake Walker would have D-D for SpBen but the Whitehurst family would just have D since Monique's MedBen field is blank.I'm desperate!
Posted by at No comments:

[SQL Server 2008 issues] How to Update Column based on Due and collection as explained in post

[SQL Server 2008 issues] How to Update Column based on Due and collection as explained in post

How to Update Column based on Due and collection as explained in post

Posted: 19 Sep 2013 08:47 PM PDT

Create Table #temp ( Number Int, Princ_Due Int, Int_Due Int, Other_Due Int, Collectionn Int, Princ_Adj Int, Int_Adj Int ) Insert Into #Temp(Number,Princ_Due,Int_Due,Other_Due,Collectionn) Values(1,0,100,200,300), (2,100,200,300,800), (3,100,200,-100,200), (4,100,200,100,1000) Select * from #Temp /* Following is the column description, Princ_Due --> princple due( means amount yet to be collected) Int_Due --> Interest due Other_Due --> Other due Collectionn -- Total amount collected. My requirement is ,Collection Amount should be get adjusted according to priority,first Other,Interest and then Principle. For example in --> record 1 , Collection = 300, Other_Due = 200 Int_Due = 100 Princ_due = 0 So my Desired output should be, Int_Adj = 0 Princ_Adj = 0 -->record 2 , Collection = 800, Other_Due = 300 Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = -200 -->record 3 , Collection = 200, Other_Due = -100 (Negative means it is not due it is collected ) Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = 100 -->record 3 , Collection = 1000, Other_Due = 100 Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = -600 */ Please help me .Thanks in Advance!!

Varchar to numeric ???

Posted: 19 Sep 2013 10:18 PM PDT

Recently I was playing with different datatype conversions and saw one strange thing while converting one of the numeric value in varchar datatype to numeric datatype. Here is the code:declare @varchar varchar(50)select @varchar = '8E10'select @varchar -- Returns 0E10select isnumeric(@varchar) -- Returns 1select convert(numeric(28,10), @varchar) -- Error converting data type varchar to numeric.select cast(@varchar as numeric(28,10)) -- Error converting data type varchar to numeric.goAs you can see, '8E10' return 1 as a result of IsNumeric function but while using convert or cast, it gives error. Probably it's meant to but can anyone put light on this ?

Script to find Replicated column

Posted: 20 Sep 2013 08:54 AM PDT

It may sound pretty easy , still I can't figure I want to list all the replicated columns in database or publication . Iam sure somebody must have quick script in box.

Conditional Formatting in SSRS Based on a Range of Values

Posted: 20 Sep 2013 08:07 AM PDT

I am building a backup status report in SSRS 2008. Here is a screenshot of what I have so far:[img]http://skreebydba.files.wordpress.com/2013/09/backupstatus.png[/img]I want to change the font color of the Backup Status to red if the status is FAILED using this conditional logic:=IIF(Fields!backupstatus.Value <> "SUCCESS", "Red", "Black")What I want to do now is change the font color of the Instance Group value to red if any of the Backup Status values in that group are FAILED. So in the screenshot above, DEVSQL08 should be displaying in red as well in the left-hand column.Any assistance would be appreciated.Thanks,Frankblog [url=http://skreebydba.com]skreebydba.com[/url]twitter [url=https://twitter.com/skreebydba]@skreebydba[/url]

Considerations for location of Reporting Services databases

Posted: 20 Sep 2013 08:30 AM PDT

Hello experts,I'm working on designing a SQL Server 2008 R2 Reporting Services (SSRS) topology, and a couple of my colleagues asked to see if SSRS can have its databases installed remotely, that is, separately from the other SSRS components. It looks like this is possible, but the question was also raised as to whether we could install it on our main OLTP db server. 1. My instinct tells me that this wouldn't be advisable for performance reasons, but is this feasible depending on the expected load for the SSRS installation?2. Also, does it cost more in licenses to have SSRS host its own databases, or is the cost of an SSRS database instance for the ReportServer and ReportServerTempDB databases included in the license for the main OLTP db server that doesn't have SSRS on it? For example, according to this page, scaling out SSRS seems to increase the cost dramatically.[url=http://www.networkworld.com/community/node/43349]http://www.networkworld.com/community/node/43349[/url]3. That same article also suggests that it is best to separate the web server for SSRS as well, meaning at least one more server for that component. Is this really the necessary best practice across the board, or can it depend on the size of the expected user base of SSRS users (in our case, something like 20-30 users)?Thanks for any help - I am in the middle of reading up on the related documents myself (for example here [url=technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx]technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx[/url] and here [url=http://technet.microsoft.com/en-us/library/cc966418.aspx]http://technet.microsoft.com/en-us/library/cc966418.aspx[/url]), but thought I would put this question out there in case someone happens to provide information while I'm researching.Thanks again.- webrunner

How to add more partitions to existed table ?

Posted: 31 Dec 2011 02:54 AM PST

I have a table that contains records of transactions with ID column is primary keyI use partition follow ID column, each partition have 1 million records.CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?Thanks

Partitioning tables has made them much larger (triple in size)

Posted: 20 Sep 2013 03:43 AM PDT

Hi, we recently partitioned some large tables in a database and now the table sizes are 3 times what they used to be. It is rougly the same number of rows as before. we lost a terabyte of space after partitioning them with basically no new data to account for it.I analyzed one of the smaller tables that we partitioned, and the table that had occupied 80,000 pages now occupies over 200,000 pages at the leaf level on the clustered index for the same number of rows. I think we were getting 60 rows per page and now we are getting about 23 rows per 8kb page on average. There are only 4 new columns to this 45 column table and they are small like ints (no large new columns).The tables are clustered and have several non-clustered indexes. I checked fragmentation levels and they are defragmented, and over 80% page space is utilized per page. At most I would expect a 20% size increase due to the fullness of the pages. I rebulit the clustered index and the number of pages actually increased a bit, so its definitely not fragmentation.Is there anything else I can check? Is the table size expected to grow this big after partitioning for some reason?P.S. the table has about 40 partitions (1 per month of data).

SQL 2008 Audit Logs to Text

Posted: 19 Sep 2013 11:41 PM PDT

Is there a way to dump all SQL server 2008 audit logs to a flat text file?

Locking a non existent row

Posted: 20 Sep 2013 04:54 AM PDT

I have a stored procedure that takes a while to run that returns a single result. What I was finding was that this stored procedure was getting called a number of times with the exact same parameters. What I did was create a cache that would save the result. So the next time the SP was called with those parameters it would get the result from the cache instead of running the entire SP.That was working great. There is however 1 issue that I'm trying to resolve.Here are the basic steps the SP takes1. Check the cache2a. If there's a hit, return the result. 2b. If not in the cache, do the processing. 3. Write result to cacheThe problem happens when there are 2 identical calls (from different sessions) at the same time when there isn't a result in the cache. The first call will check the cache and see there's no hit and continue. The second call will check and see there's no hit and continue. The problem happens at step #3. The first session will write to the cache and then right behind it the second session will write to the cache. That second write to the cache fails because there's a unique constraint on the parameters. So what I'd like to do is when that first session makes the initial check in the cache to put a lock on the row it's going to create. That way when the second session makes the check to the cache it will wait until the first session has written to the cache.Any suggestions?Thanks!

testin

Posted: 20 Sep 2013 05:26 AM PDT

testin

Error Message

Posted: 20 Sep 2013 02:50 AM PDT

Hi All,Im moving csv file to oracle database and getting an error, even though the input columns i am convertingf to double-precision float r[8] = output column[Data Conversion [9564]] Error: Data conversion failed while converting column "KioskID" (9296) to column "KIOSKID" (12698). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (9564) failed with error code 0xC0209029 while processing input "Data Conversion Input" (9565). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.[Data Conversion [9564]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "KIOSKID" (12698)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "KIOSKID" (12698)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.Any Idea guysShuan

Why does this code only work if executed with other statements?

Posted: 20 Sep 2013 02:13 AM PDT

I'm trying to run a check for column existence prior to updating a column. My sample code is as follows:[code="sql"]-- DROP AND CREATE Source tableIF OBJECT_ID(N'dbo.DoesNotIncludeColumn') IS NOT NULL AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DoesNotIncludeColumn' and TYPE = 'U' ) DROP TABLE dbo.DoesNotIncludeColumnGOCREATE TABLE DoesNotIncludeColumn ( Column1 INT PRIMARY KEY NOT NULL , Column2 nvarchar(4000) NULL )-- DROP AND CREATE Destination tableIF OBJECT_ID(N'dbo.DestinationTable') IS NOT NULL AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DestinationTable' and TYPE = 'U' ) DROP TABLE dbo.DestinationTableGOCREATE TABLE DestinationTable ( SourceColumn1 INT PRIMARY KEY NOT NULL , SourceColumn2 nvarchar(4000) NULL )INSERT INTO DoesNotIncludeColumn ( Column1 , Column2 )VALUES ( 254 , 'Source table text string' )INSERT INTO DestinationTable ( SourceColumn1 , SourceColumn2 )VALUES ( 254 , 'Destination table text string' )IF EXISTS ( SELECT 1 FROM sys.objects objz INNER JOIN sys.columns colz ON objz.object_id = colz.object_id WHERE SCHEMA_NAME(objz.schema_id) = 'dbo' AND objz.name = N'DoesNotIncludeColumn' AND colz.name = N'ThisColumnDoesNotExist' ) BEGIN UPDATE e SET e.SourceColumn2 = t.ThisColumnDoesNotExist FROM DoesNotIncludeColumn t JOIN DestinationTable e ON t.Column1 = e.SourceColumn1 END [/code]As I've written it, the code works as expected.However, if I separately run the last part (from 'If Exists down) on its own, I get a message stating[i]Msg 207, Level 16, State 1, Line 14Invalid column name 'ThisColumnDoesNotExist'.[/i]Is there something I can do to run the last bit separately?

Parsing a summary / detail flat file

Posted: 13 May 2013 12:29 AM PDT

My team has a flat file from another system that we need to parse and import into 2 tables. We're currently importing this into a staging table then parsing the staging table RBAR style in WHILE loop. As you can imagine, this is causing us an extraordinary amount of pain. It's taking several days to process one file and the bigger they get...Each line on the file has a record id. There's 1 (the header), 2 (the vendor info), and 3 (the details). A vendor can have 1-N number of 3 records listed after it but (and here's the kicker) none of the detail records have any identifying information that connect it to the vendor info in record 2. The only way we know they are connect is by the order.Example Data:1MyFile051220132VendorID123 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 2VendorID456 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 2VendorID789 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 So all the 3 records contain all the details of the vendor that are above it. But again, there's no identifying information between the 3 records and the 2 records. To add to this burden, the report is a rolling 3 month report of all records, so we can't trucate the tables and start over from scratch because it's not inception to date. Also, we have to delete out the current month (though I'm working to get that changed). Our Staging table looks like this:[code]CREATE TABLE [dbo].[Staging]( [ID] [int] IDENTITY(1,1) NOT NULL, [Extract_Record] [varchar](2000) NULL, [UNID] [int] NULL) ON [PRIMARY]GO[/code]Because we can't immediately tell what record is what and we don't want to mess up the order, we insert all the lines into the Extract_Record column and parse everything out later with substrings. We use UNID to create a unifying ID for all these records and insert them into their tables.Forgive me for scrubbing the heck out of all my column names, but I'm erroring on the side of protecting our business. Here's what our code looks like:[code]BEGIN DECLARE @i INT, @max INT, @rec SMALLINT, @newid INT, @TransactionID INT, @unid INT; --Below code sets unid to identify complete record sets SELECT @i=1,@max=MAX(id),@newid=0 FROM dbo.Staging; WHILE @i<=@max BEGIN SELECT @rec=CONVERT(SMALLINT,LEFT(extract_RI,1)) FROM dbo.Staging WHERE id=@i; IF @rec>1 AND @rec<4 BEGIN IF @rec=2 BEGIN SET @newid=@newid+1; UPDATE dbo.Staging SET unid=@newid WHERE id=@i; END ELSE BEGIN UPDATE dbo.Staging SET unid=@newid WHERE id=@i; END END SET @i=@i+1; END SELECT @i=1,@unid=NULL, @rec=NULL; WHILE @i<=@max BEGIN SELECT @unid=unid FROM dbo.Staging WHERE unid IS NOT NULL and id=@i; IF @unid IS NOT NULL BEGIN SELECT @rec=CONVERT(SMALLINT,LEFT(extract_RI,1)) FROM dbo.Staging WHERE id=@i; IF @rec=2 BEGIN --SELECT @RI=Substring(extract_RI, 2,10) --FROM dbo.Staging --WHERE id=@i; --Below code deletes current month data. Reporting team only requires previous months data WITH CurrentMonth AS(select * from dbo.StagingWHERE LTRIM(RTRIM(Substring(Extract_Record, 97,6))) = Substring(CONVERT(varchar,GETDATE(),112),1,6))DELETE FROM dbo.Staging FROM dbo.Staging serINNER JOIN CurrentMonth cmON ser.UNID = cm.UNID SELECT @TransactionID=@@IDENTITY; --Below code loads differential data from staging table to final reporting table INSERT INTO Summary (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9 ) SELECT LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1, LTRIM(RTRIM(Substring(Extract_Record, 6,10))) AS Col2, LTRIM(RTRIM(Substring(Extract_Record, 16,3))) AS Col3, LTRIM(RTRIM(Substring(Extract_Record, 19,20))) AS Col4, LTRIM(RTRIM(Substring(Extract_Record, 39,8))) AS Col5, LTRIM(RTRIM(Substring(Extract_Record, 47,10))) AS Col6, LTRIM(RTRIM(Substring(Extract_Record, 57,10))) AS Col7, LTRIM(RTRIM(Substring(Extract_Record, 67,30))) AS Col8, LTRIM(RTRIM(Substring(Extract_Record, 97,8))) AS Col9 FROM dbo.Staging ri LEFT OUTER JOIN Summary rit ON LTRIM(RTRIM(Substring (ri.Extract_Record, 2,4))) = LTRIM(RTRIM(rit.Col1)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 6,10))) = LTRIM(RTRIM(rit.Col2)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 16,3))) = LTRIM(RTRIM(rit.Col3)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 19,20)))= LTRIM(RTRIM(rit.Col4)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 39,8))) = LTRIM(RTRIM(rit.Col5)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 47,10)))= LTRIM(RTRIM(rit.Col6)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 57,10)))= LTRIM(RTRIM(rit.Col7)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 67,30)))= LTRIM(RTRIM(rit.Col8)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 97,8)))= LTRIM(RTRIM(rit.Col9)) WHERE id=@i AND rit.Col1 IS NULL AND rit.Col2 IS NULL AND rit.Col3 IS NULL AND rit.Col4 IS NULL AND rit.Col5 IS NULL AND rit.Col6 IS NULL AND rit.Col7 IS NULL AND rit.Col8 IS NULL AND rit.Col9 IS NULL; SELECT @TransactionID=@@IDENTITY; END ELSE IF @rec=3 BEGIN --Below code loads differential data from staging table to final reporting table INSERT INTO Detail (TransactionID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16) SELECT @TransactionID, LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1, LTRIM(RTRIM(Substring(Extract_Record, 6,4))) AS Col2, LTRIM(RTRIM(Substring(Extract_Record, 10,20))) AS Col3, LTRIM(RTRIM(Substring(Extract_Record, 30,8))) AS Col4, LTRIM(RTRIM(Substring(Extract_Record, 38,24))) AS Col5, LTRIM(RTRIM(Substring(Extract_Record, 62,8))) AS Col6, LTRIM(RTRIM(Substring(Extract_Record, 70,8))) AS Col7, LTRIM(RTRIM(Substring(Extract_Record, 78,24))) AS Col8, LTRIM(RTRIM(Substring(Extract_Record, 102,10))) AS Col9, LTRIM(RTRIM(Substring(Extract_Record, 112,4))) AS Col10, LTRIM(RTRIM(Substring(Extract_Record, 116,10))) AS Col11, LTRIM(RTRIM(Substring(Extract_Record, 126,4))) AS Col12, LTRIM(RTRIM(Substring(Extract_Record, 130,24))) AS Col13, LTRIM(RTRIM(Substring(Extract_Record, 154,3))) AS Col14, LTRIM(RTRIM(Substring(Extract_Record, 157,17))) AS Col15, LTRIM(RTRIM(Substring(Extract_Record, 174,5))) AS Col16 FROM dbo.Staging ri2 LEFT OUTER JOIN sap.tblRITransactionDetail rit2 ON LTRIM(RTRIM(Substring(ri2.Extract_Record, 2,4))) = LTRIM(RTRIM(rit2.Col1)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 6,4))) = LTRIM(RTRIM(rit2.Col2)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 10,20))) = LTRIM(RTRIM(rit2.Col3)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 30,8))) = LTRIM(RTRIM(rit2.Col4)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 38,24)))= LTRIM(RTRIM(rit2.Col5)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 62,8))) = LTRIM(RTRIM(rit2.Col6)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 70,8))) = LTRIM(RTRIM(rit2.Col7)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 78,24))) = LTRIM(RTRIM(rit2.Col8)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 102,10))) = LTRIM(RTRIM(rit2.Col9)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 112,4))) = LTRIM(RTRIM(rit2.Col10)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 116,10))) = LTRIM(RTRIM(rit2.Col11)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 126,4))) = LTRIM(RTRIM(rit2.Col12)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 130,24))) = LTRIM(RTRIM(rit2.Col13)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 154,3))) = LTRIM(RTRIM(rit2.Col14)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 157,17)))= LTRIM(RTRIM(rit2.Col15)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 174,5))) = LTRIM(RTRIM(rit2.Col16)) WHERE id=@i AND rit2.Col1 IS NULL AND rit2.Col2 IS NULL AND rit2.Col3 IS NULL AND rit2.Col4 IS NULL AND rit2.Col5 IS NULL AND rit2.Col6 IS NULL AND rit2.Col7 IS NULL AND rit2.Col8 IS NULL AND rit2.Col9 IS NULL AND rit2.Col10 IS NULL AND rit2.Col11 IS NULL AND rit2.Col12 IS NULL AND rit2.Col13 IS NULL AND rit2.Col14 IS NULL AND rit2.Col15 IS NULL AND rit2.Col16 IS NULL; ENDEND SET @i=@i+1; ENDEND[/code]So, messy, slow and very very annoying. I've got a few thoughts on how we can start to fix it, but before I share I wanted to see what everyone else was thinking. I'm hoping someone has an ephiphany that can help.So, any ideas?Just an FYI: there's no fixing the input file in our immediate future. Just getting this much information was like pulling teeth and the other team takes months upon months (if even that soon) to work changes through their SDLC. So I have to do what I can to mitigate the load issue now with the file that I have.

ScanCount Difference

Posted: 19 Sep 2013 11:27 PM PDT

See below query, i have read this scenario at many place but couldnt collect it .Please explain Why the ScanCount is different [code="sql"]CREATE TABLE ScanCount (Id INT IDENTITY(1,1),Value CHAR(1))INSERT INTO ScanCount (Value ) VALUES ('A') ,('B'),('C'),('D'), ('E') , ('F') CREATE UNIQUE CLUSTERED INDEX ix_ScanCount ON ScanCount(Id)SET STATISTICS IO ON--Unique clustered Index used to search multiple valueSELECT * FROM ScanCount WHERE Id IN(1,2,3,4,5,6)--Unique clustered Index used to search multiple valueSELECT * FROM ScanCount WHERE Id BETWEEN 1 AND 6drop table ScanCount[/code][quote](6 row(s) affected)Table 'ScanCount'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(6 row(s) affected)Table 'ScanCount'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]

Function returns table from multiple selects

Posted: 19 Sep 2013 10:40 PM PDT

Hi All,OK I have a function that is working exactly as I had wanted it to, but I want to be sure that what I have done has the correctly syntax and that I will not causing performance problems later. The function searches the PersonTable with a list of family names. By using the left and right ID's of the PersonTable it can determine all of the family and sub family members. (e.g. provide the grandparents' sir name(s) and the function will return all of the generations below the grandparents. So the function parses an input string of names into multiple strings of names and then runs individual queries using each parsed name. For each query I insert the results into a table and then return that table from the function.My question is, based on the function below is there anything that I am doing completely wrong by inserting the sub queries into the return table like I have done?Execute the function like this:[code="sql"]SELECT * FROM [fnGetPersons] ('Name 1|Name 2','|') order by name[/code]The function definition[code="sql"]CREATE FUNCTION [fnGetPersons]( @sPersonNames nvarchar(MAX), @sParseChar varchar)RETURNS @PersonList TABLE( ID int, name nvarchar(255), description nvarchar(255))AS BEGIN DECLARE @sPersonName varchar(255) = NULL WHILE LEN(@sPersonNames) > 0 BEGIN IF PATINDEX('%' + @sParseChar + '%',@sPersonNames) > 0 BEGIN SET @sPersonName = SUBSTRING(@sPersonNames, 0, PATINDEX('%' + @sParseChar + '%',@sPersonNames)) INSERT INTO @PersonList SELECT ID, name, description FROM PersonTable WITH (nolock) WHERE ( left_ID BETWEEN ( SELECT left_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) AND ( SELECT right_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) ) SET @sPersonNames = SUBSTRING(@sPersonNames, LEN(@sPersonName + @sParseChar) + 1, LEN(@sPersonNames)) END ELSE BEGIN SET @sPersonName = @sPersonNames SET @sPersonNames = NULL INSERT INTO @PersonList SELECT ID, name, description FROM PersonTable WITH (nolock) WHERE ( left_ID BETWEEN ( SELECT left_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) AND ( SELECT right_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) ) END END RETURNEND[/code]

sql cluster installtion failed

Posted: 20 Sep 2013 12:54 AM PDT

Hi All,When i am installing sql cluster 2008 r2 on win 2008 r2 ,the installtion failed with below error.Overall summary: Final result: Failed: see details below Exit code (Decimal): -2067791871 Exit facility code: 1216 Exit error code: 1 Exit message: Failed: see details below Start time: 2013-09-20 14:21:27 End time: 2013-09-20 14:54:53 Requested action: InstallFailoverClusterDetailed results: Feature: Database Engine Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1 Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: SQL Client Connectivity SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: SQL Server Replication Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: Full-Text Search Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1 Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: Integration Services Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools Connectivity Status: Passed MSI status: Passed Configuration status: Passed Feature: Management Tools - Complete Status: Passed MSI status: Passed Configuration status: Passed Feature: Management Tools - Basic Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools Backwards Compatibility Status: Passed MSI status: Passed Configuration status: Passed Feature: Microsoft Sync Framework Status: Passed MSI status: Passed Configuration status: Passed""IN EVENTvwr system log i could see the errors below:"Cluster network name resource 'SQL Network Name (xxxx01)' failed to create its associated computer object in domain 'xxxxx.xxxx.net' for the following reason: Unable to create computer account.The text for the associated error code is: Your computer could not be joined to the domain. You have exceeded the maximum number of computer accounts you are allowed to create in this domain. Contact your system administrator to have this limit reset or increased.Please work with your domain administrator to ensure that:- The cluster identity 'xxxxxxxxo1$' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed.- The quota for computer objects has not been reached.- If there is an existing computer object, verify the Cluster Identity 'xxxxxxx$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.Could any one help me out here....THanks in advance
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog