0
votes
3answers
2k views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
0
votes
1answer
62 views

Allocating Filegroups and Storage

I have upto 10 databases running on a single SQL Server instance on a single server. I have read about best practice for storage and concluded the following... 1) Transaction Log on own RAID array ...
0
votes
1answer
36 views

Conditional when creating a domain

How can I define or change a domain based on another attribute value? F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be ...
5
votes
2answers
337 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
2
votes
0answers
412 views

Tool to visualize data differences between two DB tables [closed]

I work as a tester. I found some of the defects I investigate can be traced down to differences in configuration between local and customer's environments. As the tested system has major part of ...
1
vote
1answer
232 views

SQL Server log file continues to grow? [duplicate]

I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this ...
0
votes
3answers
4k views

How can I find who has allocated a user lock via dbms_lock?

We have a print queue process that acquires a user lock via dbms_lock.allocate_unique. It performs the work and then releases the lock. The process works very well. Today, we have a process that ...
1
vote
3answers
830 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
2
votes
3answers
2k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
496 views

Backing up MySQL w/MyISAM on Amazon RDS

So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since ...
9
votes
3answers
14k views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
1
vote
2answers
7k views

How to sync two databases (alternative to mysql replication)?

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This ...
0
votes
2answers
97 views

Can I improve this query?

SELECT * FROM SameLogTable WHERE ID_Table IN (SELECT ID_Table-1 FROM SameLogTable WHERE <SameCondition>) OR ID_Table IN (SELECT ID_Table ...
2
votes
1answer
402 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
1
vote
2answers
2k views

Automatically create ER diagram from database schema

I have a database with 193 tables and I need to create an ER diagram from them. There are no formal relationships between the tables with respect to FKs. The relationships are mapped with 1-* tables. ...
1
vote
2answers
77 views

What does enabling containment on server incur?

Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step": "Benefits and limitations of contained databases As with most features, there are benefits and ...
3
votes
1answer
3k views

VS2012 setup spoils SQL Server 2012 Data Tools for everybody or just me?

On Windows 7 Prof I have installed SQL Server 2012 R2 with, as I am pretty well remember, client tools. Then, I tried to install Visual Studio 2013 Ultimate but failed. Then I've installed VS2012 ...
1
vote
2answers
135 views

db2 executing command crash shell

I have a simple ksh script that executes db2 queries, such as #!/bin/ksh #some initialization #step1,2,3 set -e db2 -x "select * from table a " #step4 The main issue seems to be caused by the db2 ...
0
votes
1answer
42 views

Oracle sql plan for a select with procedure in terms section

Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query ...
0
votes
0answers
88 views

Select with IN list and date comparison not picking up index

explain select sum(principal_received) principal_received, sum(interest_received) interest_received, sum(advance_received) advance_received, ...
0
votes
1answer
43 views

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped?

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? Also, what happens when a new primary key is added?
0
votes
1answer
329 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
1
vote
1answer
362 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
1
vote
2answers
263 views

query to check all SCD tables that have data for specific date

It's easy to find all SCD tables through select * from syscat.TABLES t join syscat.COLUMNS s on t.tabname = s.tabname where s.COLNAME = 'EFFECTIVE_DTE' and t.type='T' ...
1
vote
1answer
303 views

How is parallelism threshold (seconds) used if query CPU cost is actually unitless

How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly ...
0
votes
1answer
355 views

Oracle 11g INDEX_SS Hint

Given a table with columns A, B, and C of type CHAR(15) and an unique index If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I ...
1
vote
1answer
7k views

Different execution plan for the same query if I change a value in the predicate

I am getting two different execution plans for the same query only by changing the value in the predicate, the first one uses nested loops and the other one hash join which takes a lot longer. ...
4
votes
1answer
225 views

SQL query filled Temdb complete Harddisk [closed]

I have a query which was earlier fine but now it is running me out of hard disk space with message that tempdb is full. My Harddisk has 90 gb of space as soon as i run query temdb runs out of space in ...
1
vote
0answers
114 views

DB2 crashes when XML columns used in Cursors inside a SP

This is the Stored Procedure code written in DB2 V9.7 LUW FP5 **CREATE OR REPLACE PROCEDURE USP_TEST ( IN LOC_LIST XML) P1: BEGIN ATOMIC DECLARE IV_SOV_LOC_ID BIGINT; DECLARE ...
0
votes
0answers
914 views

MySQL query conditional return using TRIM()

I am trying to SELECT from a table, but I need to trim from leading and trailing string for comparison. For example, My table/data is very simple CREATE TABLE `lcr` ( `id` int(11) NOT NULL ...
2
votes
1answer
84 views

What is the appropriate SQL method for “Safe to SUM”?

In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data. In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a ...
2
votes
1answer
491 views

SQL Server Replication - setting up scheduler issue

We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. ...
0
votes
1answer
1k views

Trigger After Insert is preventing insertion

I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger: ALTER ...
1
vote
1answer
1k views

Oracle RMAN substituting variables

I'm playing around with the USING clause in RMAN, but cannot seem to get it to work. Here is my RMAN command: rman target username/password @${RMAN_DIR}/backup.rman using "db1" When I run that ...
0
votes
0answers
221 views

Minimizing possible trigger impact

I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof? Details: We have a huge table we don't control ...
1
vote
1answer
1k views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
2answers
233 views

Daily snapshot fact table

I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer. My first approach is to ...
1
vote
2answers
411 views

Table not dropped correctly in SQL Server Express 2008

I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I ...
0
votes
3answers
118 views

SQL Server RAID Storage [closed]

I have been reading about recommended SQL Server storage and RAID 5 or RAID 10 controllers have been recommended. On my existing Database server I can see 4 Storage controllers under Device Manager. ...
0
votes
0answers
288 views

Install 10g XE on 64bit linux

As I know there is only 32bit version of 10g XE, am I right? And the installation file is called "oracle-xe-univ-10.2.0.1-1.0.i386.rpm". I want to install it on Centos 6.4 64bit. But during the ...
0
votes
1answer
532 views

MySQL Master Slave Replication and backup

I would like to implement the following backup solution as highlgihted on the mysql site: To use replication as a backup solution, replicate data from the master to a slave, and then back up the ...
0
votes
0answers
497 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
1
vote
1answer
658 views

Problem setting MSDTC on client computer

I created an application that is making use of MSDTC (to open a transaction on multiple databases within the same MS SQL server), our local environment was setup successfully and both DTCPing, ...
1
vote
1answer
291 views

What is the default value of ANSI Padding Enabled, and what should value should I set it to?

When creating a new database, ANSI Padding Enabled is set to False by default. According to this however, the default is ON (which I assume is the same as True). They also recommend to set it to ON ...
0
votes
1answer
120 views

Oracle DBMS Scheduler - what if database fails?

We are moving some Oracle overnight jobs off a cron server and running them via Oracle DBMS Scheduler instead. It is my understanding that if the database fails overnight then the job email ...
1
vote
0answers
876 views

Oracle: How to find non-local (background) processes in Windows Machine

Following link advises to find non-local background processes and kill them using following bash script. Solution 1) : without shutdown anything just kill that database processes which are not ...
0
votes
1answer
52 views

Having Issues Exporting to online SQL Server database [closed]

I successfully initiated the export wizard of Microsoft SQL Server 2008 Studio and it was exporting fine from the database on my PC to online server database. But suddenly my internet network went ...
0
votes
2answers
957 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
1
vote
1answer
390 views

what is the structure of a Mysql multi-column index? [closed]

For example: CREATE TABLE test( col_1 INT, col_2 INT, col_3 VARCHAR(11), col_4 VARCHAR(11) )ENGINE = INNODB; CREATE INDEX mul ON test(col_2,col_3,col_4); Sorry,my English is not good,I mean ...
0
votes
1answer
201 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...

15 30 50 per page