1
vote
1answer
559 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 ...
3
votes
1answer
1k 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 ...
5
votes
2answers
2k 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
978 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
2k 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) values(@name)...
1
vote
0answers
1k 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
533 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
322 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
1k 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
1answer
648 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 ...
4
votes
2answers
3k 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
85 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 ...
3
votes
0answers
736 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 ...
2
votes
1answer
2k 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
689 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 ...
1
vote
2answers
423 views

Install MySQL on Ubuntu 12.0.4

This is my first time using Linux. I am trying to install MySQL 5.6.15 on an Ubuntu box. I download MySQL from their website and put it on a USB stick then plugged the stick into the box. How can I ...
1
vote
0answers
2k views

Amazon RDS - Can't access MySQL DB from local machine

I read many questions/answers here but they are from a different interface from AWS Console. I created a DB Instance but can't create a rule in the security groups that's allows me to connect from my ...
1
vote
2answers
584 views

MySQL server wont' start. Reason: Internal error

The very vague error message Reason: Internal error I've had this happen before, and it usually fixes itself after I restart my machine, but after the 4th time now it still is showing the error :( ...
1
vote
0answers
135 views

Diagnosing high cpu load

Let me start by saying our SQL servers are hosted by a third party. Every once in a while our monitoring software will show that cpu usage on one of our servers goes way out of line with normal ...
0
votes
1answer
2k views

Restore PostgreSQL data using PITR

I am unable to restore the database to a previous time using the PITR steps mentioned on PostgreSQL website Article 23.3.3. Recovering with an On-line Backup (http://www.postgresql.org/docs/8.1/static/...
0
votes
1answer
31 views

Saving output of \ commands to disk

When I try to run the following command: my_db> \copy \dt *. to /home/john/list.txt I get an error: \copy: parse error at "*" Why? How can I save the output to disk?
18
votes
3answers
18k views

psql: Show current database

In psql I can use \c <database_name> to connect to a given database. How can I see what database I am currently connected to? When I do: my_db> current_database(); I get: ERROR: ...
5
votes
2answers
289 views

Is there a well-known name for this “poor man's ref. integrity” schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin ...
0
votes
0answers
1k views

Storing currency with variable floating point in SQLite

We are working with currency and handle currencies from various countries. As a result, we may work with currencies that require no decimal values, two decimal places, three, sometimes four. We also ...
1
vote
0answers
336 views

OLAP Advanced custom filters date

How do i filter, in a Mondrian MDX Query, just the FACTS that have ocurred in a Range of Months over last 3 years? I need to make it dynamic so i can use parameters latter, so i need the following: ...
4
votes
1answer
1k views

Database under heavier load — all SQL queries start timing out after a period of hours/days

I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it. You can see that everything seemed to be OK until about 3AM this ...
0
votes
1answer
891 views

Why bulk loading a Vertica database table does not load all lines?

I have a small csv file I would like to bulk load into a Vertica 6.1 database table, this file has 92 lines. dbadmin=> COPY games FROM '/tmp/games.csv' DELIMITER ','; Rows Loaded ------------- ...
1
vote
2answers
683 views

SQL Server storage limits on Azure

Looking at SQL Server maximum capacity limits on here: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.120).aspx Database size can be as big as 524,272 terabytes. Here, you could see VM size ...
2
votes
1answer
727 views

Comparing Agent Jobs on Two Servers

I'm currently building a process in Powershell where we can copy SQL Server Agent jobs from one server to another server just by entering the server names. The script uses the SMO library to obtain ...
0
votes
1answer
54 views

SQL Server 2008 R2 Resource Managment

I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM. I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM. Occasionally it will ...
7
votes
2answers
3k views

How to drop empty tables

Is it possible to drop all empty tables from my huge database (mysql)? I'm looking for a sql command to automatically remove all those empty tables. Currently, I have 305 tables in my dataset, and ...
1
vote
1answer
2k views

error: must be owner of relation USER_ACCOUNT_ID_seq

I have granted priviledges to a user for testing in PostGreSql: GRANT USAGE ON SCHEMA "MySchema" TO "Tester"; GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester"; GRANT ALL ON ALL SEQUENCES IN ...
3
votes
1answer
204 views

Jobs for cycling error logs

Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs: cycle the error log ...
0
votes
1answer
1k views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
1
vote
1answer
960 views

What is lock pinger in mongodb

Can somebody explain me what is lock pinger in MongoDB [LockPinger] cluster XXXXXXX:27019 pinged successfully at Wed Feb 5 04:46:01 2014 by distributed lock pinger 'XXXXXX:27019/ip-XX-XX-XX-...
4
votes
1answer
2k views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
1
vote
1answer
60 views

What are the SQL standards [closed]

Some job descriptions mention required knowledge of SQL standards. What do they mean by that? I found something on wiki http://en.wikipedia.org/wiki/SQL#Standardization but I don't really know what ...
6
votes
1answer
404 views

Hierarchical structure with limited number of records per user

I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem. Any user can refer only 3 other users at max. So for ...
1
vote
1answer
494 views

Lock time not involved in MySQL slow query selection?

I wish to analyze some read-write contentions problems on a very huge table where writes are very fast, less than 1ms in most case. The point is that the contention makes those write queries sometimes ...
1
vote
1answer
53 views

Rebuild the Fulltextcatalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this 30 hours. Our business users ...
0
votes
1answer
496 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
1
vote
1answer
3k views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
-1
votes
1answer
735 views

delete in stored procedure [closed]

i am write this stored procedure to delete one row DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT) BEGIN DELETE FROM students WHERE ID=...
0
votes
0answers
415 views

Installing oracle 9i on windows 7

Is it possible to install oracle 9i on windows 7 32 bit? And where can i download the standalone oracle http server?
3
votes
0answers
132 views

SQL server job has not run and also no entry logged in its history [closed]

I have scheduled a SQL job to run daily at 3am in the morning which creates a batch. It has not run today and i am also not able to fathom what's the reason behind its failure. There is no entry in ...
1
vote
1answer
568 views

SQL SERVER Virtual Log Files

I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the ...
0
votes
0answers
123 views

Replication with no network connectivity

I have SQL Servers on two isolated networks that cannot be connected directly. However, there is a need to keep data in sync between the two systems. Obviously SQL Server replication won't work. It ...
1
vote
2answers
3k views

Convert Login from Windows Authentication to SQL Server authentication?

I have an account that is setup for Windows authentication. The account is used by SSRS for running reports and has various permissions in multiple databases. I need to convert this account to use SQL ...
4
votes
2answers
4k views

Should I pause Mirroring and stop SQL Server services before running windows updates?

I am running Windows update on my SQL Servers (which are in a Principal-Mirror partnership, in High Safety mode). I am starting with updating the mirror and I was wondering, to increase safety, ...
5
votes
1answer
301 views

Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev ...

15 30 50 per page