4
votes
2answers
719 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
3
votes
1answer
83 views

Simple method to setup SQL Server for development access

I've created a virtual development box and installed SQL Server 2008 with mixed authentication. Then I connect to SQL Server via SQL Server Management Studio using my Windows account but when I try to ...
7
votes
3answers
7k views

SQL Calculated Field in both SELECT and GROUP BY clause

Often in querying my MS SQL Server databases, I need to create a calculated field, such as this (CASE WHEN A.type = 'Workover' THEN 'Workover' ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' ...
0
votes
0answers
632 views

What problems can be caused by dropping a rollback segment manually?

Oracle version: 11.2.0.1 I have the following error: "Transaction recovery: lock conflict caught and ignored" I can't upgrade my database now. I had a RAC. Removed one node, but the undo tablesapce ...
4
votes
1answer
122 views

Settings for a MySQL backup only slave instance

I have two instances of MySQL running one a server, set up as master and slave. It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live ...
0
votes
1answer
239 views

Problem connecting to AlwaysOn Cluster

Good day, I created a AlwaysOn HA cluster in Azure, 2 Server 2012s both with SQL 2012 Ent updated and patched on the same subnet.. These are the guides I followed: Tutorial: AlwaysOn Availability ...
5
votes
1answer
493 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
1
vote
0answers
3k views

Oracle restore backup on other server

I have following situation: One server with Oracle has to be backed up and recovered on other Oracle server. Second Oracle DB has the same user/schemas, but with a different older data. I intend to ...
0
votes
1answer
774 views

ORA-44001: invalid schema

I have a db dump which containts db dump for three schemas for e.g. alpha bravo charlie in one file, exported through EXPDP. now when i am trying to import these through following command: IMPDP ...
1
vote
1answer
818 views

SSIS: Get Date for Day of the Week

In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date? I assume I have to do some subtraction on the day-of-week, but not sure: DATEADD("d", ...
0
votes
1answer
1k views

Partition tables based on month and year in SQL Server

I have a sales table which I need to partition based on month and year of sales table structure S_ID numeric(18,0) S_CustID numeric(18,0) S_TotalCash numeric(18,0) S_Paid ...
1
vote
1answer
105 views

Recipe Health risk Database ERD structure

I have these tables: First table(Recipe table), Recipe recipeid(PK,AI) recipename ingredients Second table(Ingredients table), Ingredients ingredientid(PK,AI) ...
1
vote
2answers
778 views

Can I disable an account in mysql after x failed login attempts?

The question is in the title. I would like to disable accounts after x failed login attempts. Is it possible? Thank you
2
votes
1answer
250 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
1
vote
1answer
157 views

Is it possible to restrict SQL Server DB_Owner when connecting using SQL Server Mgt Studio? [duplicate]

Here is the back story. A developer created an EXE program. The exe program has the ability to access a SQL Servre database. In order to do that we create an Active Directory group. Let's call this ...
2
votes
0answers
188 views

Use Matlab or SQL Server to do mathematical computations? [closed]

Current situtation I am working with a dozen of CSV files containing financial simulations in form of 100'000x100 matrices. Each file is around 100Mb large. Until now, only analyse these files with ...
0
votes
1answer
598 views

Installing Oracle Database 12c in Windows 7, INS-08109 error [closed]

I'd like to learn PL/SQL so I'm trying to install Oracle. I downloaded the 2 zip files and extracted to the same directory. I run the "setup.exe" file and the install GUI starts: In step 1 I enter ...
0
votes
0answers
55 views

SQL snapshot and execution plans [closed]

I have a question.. when a query is executed the execution plan is compiled if the snapshot is reloaded does that mean the snapshot compiled is blown away? and if so is there a way to keep the ...
0
votes
0answers
164 views

Multiple update of records in the same table

I have an update query. E.g. UPDATE table SET name = ? WHERE id = ? Now I have all the relevant ids collected. What I am doing is looping over the ids and execute the update query for each one. E.g ...
1
vote
1answer
147 views

DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons

Sorry - I feel like this is a newb question, but I can't find anything on it. In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38. However, on closer examination ...
6
votes
3answers
1k views

Cube processing either takes too long or fails

I have a SSAS Cube with 35 dimensions and 10 measures. Some of the dimensions are quite large in size. In almost all dimensions data either get updated and inserted. Measures have high volume of ...
0
votes
1answer
331 views

Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a ...
1
vote
1answer
427 views

automatic transactional replication failover to noce2 with AG failover

I am working on to set up high availability group like SQL01,SQL02,SQL03(DR) and SQLRPT01 as the reporting server which has our reporting DB. * From SQL01 to RPT01, I am running transnational ...
0
votes
1answer
422 views

Which MongoDB administration UIs offer a table view (OS X)?

The only admin UI we found that offers a table view is MongoVue, which is only available on Windows. We're on Macs with OS X. Do any exist?
1
vote
3answers
396 views

AlwaysOn Availability Groups Delayed Replica

With AO SQL 2012, is it possible to configure one of the member as delayed replica? I know ideally we would like this to be synced but in case of human error, it would give us the ability to check on ...
1
vote
0answers
102 views

Data replication vs parallel shard query

I'm not a DBA, and I'm wondering how a SQL Server database can scale to fit the needs of a social network. I'm not interested in NOSQL, and I understand that caching plays an important role in ...
1
vote
2answers
277 views

Mysql select ends with 'killed'

Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA. I've got a largish table, about 163 million rows and something like 44 GiB. After some experiments with ...
1
vote
1answer
3k views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
1
vote
2answers
128 views

How to use a column's latest date in query?

SELECT smart_allusers_karen.lastlogin, smart_allusers_karen.supemail, smart_allusers_karen.regionname FROM smart_allusers_karen WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 ...
4
votes
1answer
1k views

Why is SQL Server 2012 working set memory low?

I have recently migrated a 100gb database for a busy website from: MS SQL 2008 Standard Edition R2 Windows Server 2008 48gb RAM to MS SQL 2012 Web Edition Windows Server 2012 64gb RAM I have ...
1
vote
1answer
241 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
0
votes
1answer
899 views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
2
votes
2answers
20k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
1
vote
3answers
8k views

Literal does not match format string error in oracle

I was given a SELECT statement that is running in a Crystal Report to investigate a data problem but when I try to run it against the database I get the error: ORA-01861: literal does not match ...
0
votes
2answers
37 views

Implementing older version of mySQL on RH9

I'm building a virtual machine running Red Hat Linux 9 (Shrike) and MySQL. This is to virtualize a current production system that needs new hardware. Questions: Can I copy the existing mysql ...
1
vote
1answer
365 views

Transaction Replication Without Snapshot

So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing. Prod server ...
2
votes
1answer
760 views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...
4
votes
2answers
1k views

if-then-else gives a boolean headache

I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2: I need to concatenate three columns into one but in three different ways depending on the result in ...
3
votes
1answer
622 views

Logging Questions for SSIS 70-463

I have been looking at sample questions on logging for 70-463 exam. For the below, the answers given do not make sense to me. The first answer given does not even say how to implement logging. I ...
3
votes
1answer
1k views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) ...
1
vote
0answers
882 views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
0
votes
2answers
296 views

Why we can backup postgresql database backup without password?

I want to backup my PostgreSQL database with pg_dump command that takes a lot of parameters. One of them is --no-password. Why this parameter is provided? I mean if anyone can backup database, so ...
0
votes
0answers
218 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
-2
votes
1answer
530 views

Stored procedure syntax error [closed]

I am writing a procedure, independently the queries run fine with no errors.. But, when I run it as a procedure I get errors. Given below is my procedure create or replace PROCEDURE xyz(inuserid ...
0
votes
0answers
301 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
3
votes
2answers
2k views

MySQL on SSD - what are the disadvantages? [closed]

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost. Given that 20% of all queries in my database are INSERTs and ...
1
vote
0answers
58 views

Pg_restore table size differences

What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file? On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my ...
2
votes
0answers
332 views

Fastest way to get the length of the intersection of two text arrays in PostgreSQL

This is what I am currently using: CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ select array ( select unnest($1) intersect select unnest($2) order by 1 ...
1
vote
1answer
1k views

Oracle: How can I add ROWDEPENDENCIES to an existing table?

If I have a table t create table t (x var number); How can I modify the table so that it appears to have been created with rowdependencies? create table t (x var number) rowdependencies;
2
votes
1answer
346 views

MariaDB: Created_tmp_disk_tables high after start

today i looked in the status of my MySQL server which is based on MariaDB and i noticed that Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try ...

15 30 50 per page