2
votes
1answer
4k views

Why does SQL Server fail to create a procedure referencing a linked server?

Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server: 2013-11-04 17:13:22 +00:00 INFO Creating ...
0
votes
1answer
397 views

What performance should I expect from MySQL (Using Amazon RDS)

I'm trying to understand whether I'm doing something wrong, or expecting too much. I have an InnoDB table on a db.m1.large RDS (MySQL 5.6.13. 7.5 GB RAM) with ~1M rows. The primary key is a ...
0
votes
1answer
25 views

Basic metric information for views

I would like to output some basic metric information about my MySQL database. Such as the names and number of rows for all tables and views in a given database. Tables are no problem as I simply ...
2
votes
1answer
3k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
6
votes
3answers
18k views

What permissions are necessary for truncating a table?

I have a sql account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION ...
-2
votes
1answer
466 views

Example creating a PostgreSQL XML data type

I am looking for an SQL example where a table is created in PostgreSQL which has a field with data type XML.
2
votes
2answers
8k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
0
votes
1answer
149 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
236 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
0answers
198 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
1answer
2k views

Create temporary tables per session in a DB other than tempdb

Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb? Using tempdb is fine in most cases, but some of the ...
1
vote
0answers
946 views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
3
votes
0answers
362 views

Why are we suddenly getting time-outs on multiple transactions? [closed]

Two days ago the transaction log filled up and my user's couldn't update or insert. One of my techs truncated the log, shrank the db, then did a full backup. The next day (Monday) we noticed that ...
0
votes
0answers
126 views

MySQL overtaking system memory and forced to restart

I'm facing a frustrating issue. I have a MySQL Server 5.5 running on top of Ubuntu Server 12.04.3 LTS (VMWare guest w/ 16GB RAM - 8 Cores) mysqltuner.pl says [OK] Maximum possible memory usage: ...
1
vote
1answer
259 views

Access Linked Tables and Their Network Connections

I need to know some simplistic details about how Microsoft Access' linked tables work. I think I know the answer, and this question is more about asking for a confirmation than anything else. A ...
4
votes
1answer
4k views

SQL Server cluster not available after failure of the “main” server

I aksed this question on stackoverflow, but was asked to repost here so here it is: I have a database that I'm setting up in a clustered environment. It'll be two servers with a shared storage. ...
1
vote
1answer
433 views

Check all MySQL tables at once

I'd like to run a CHECK TABLE on all my tables from the MySQL command-line. I can do it for a single table: mysql> check table Restaurant; +--------------------+-------+----------+----------+ | ...
0
votes
0answers
2k views

Occasional disk I/O errors in SQLite

I have a very simple website running PHP and SQLite 3.7.9 (with PDO). After establishing the SQLite connection I immediately execute the following queries: PRAGMA busy_timeout=0; PRAGMA ...
2
votes
1answer
51 views

Order results from query to have a certain result at the top

I'm not sure there is even a way to do this, but thought I would ask anyway. I have the following example output from a query: Name Country Number -------------------------- Harry Germany ...
2
votes
1answer
2k views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, ...
0
votes
3answers
3k views

SQL Server Agent: QUOTED_IDENTIFIER

The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...". The question is, ...
0
votes
2answers
1k views

SQL Query is sometimes slow, sometimes fast

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms ...
1
vote
1answer
66 views

Accessing MySQL Database

I am designing a database for an application, which will be accessed by different front end programming languages, such as php, asp.net, android applications and iphone applications. I am confused, ...
1
vote
0answers
261 views

How to use variables in file paths in MySQL statements?

I have now a problem that I have to set paths through variables in queries like load data infile '/my/file/path/to/file.txt' into table database1.table2 fields terminated by '' lines terminated ...
0
votes
1answer
1k views

Oracle - how to reference to a field%TYPE using database links?

I have this database which includes a couple of schemas. Say I need to access schemaONE from schemaTWO. To do this, I have created a database link named "DB_ONE_LINK". I am able to access to ...
1
vote
1answer
2k views

Query: select all rooms which are not booked

I have a simple room booking system consisting of following tables: booking | id | user | begin | end | room | id | name | booking_map | id | room_id | I can list all rooms that are booked at ...
0
votes
2answers
718 views

Importing MySQL database using Linux Terminal

I have an existing database, named dlti.sql I want to transfer it to a Linux-based server. I tried using this code, however I get no response: mysql -u root -p Desktop < dlti.sql The .sql file ...
3
votes
1answer
705 views

How do I find the longest string in a row amongst various text fields?

Does anyone have an easy query to find the longest string of characters in a data row from ALL alphanumeric (text, nchar, varchar, etc) type columns from ALL tables in a single SQL Server 2008 R2 ...
1
vote
2answers
10k views

INSERT INTO with SELECT subquery db2 9.5

I have the following query that takes 4 seconds to execute: SELECT TX.* FROM TABLE_X TX INNER JOIN TABLE_Y TY ON TY.VALUE_ID = VALUE_ID_IN AND TY.PROD_ID = TX.PROD_ID WHERE (TX.PLACE_ID1 ...
7
votes
3answers
8k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
3
votes
1answer
3k views

Table-valued function runs fast, but slow from another function [closed]

Disclaimer: I'm a senior sysadmin who's trying to help our development team speed up the production database. Our users are hurting, bad. Please don't shoot me for not being great with SQL The ...
0
votes
3answers
173 views

Why is my SQL server using ~4 GBs of memory?

I notice that pretty often my SQL server will eat up 50% of my 8GB of memory. This usually happens after I finish running some SQL queries, not while they're running. The most common query is one that ...
1
vote
1answer
339 views

To use a cursor or something else in my while loop?

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select ...
1
vote
3answers
3k views

GUID stored in a varchar field

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by ...
3
votes
2answers
2k views

How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?

What is the recommended way to perform an upgrade from PostgreSQL 9.3.0 to 9.3.1 (minor upgrade) using the Enterprise DB built windows installer? Should I uninstall first or just install over the ...
1
vote
2answers
293 views

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Is it possible to configure an Oracle user in Enterprise Manager, so that he can later create views on any future, yet unknown table in his schema? I am currently running into "ORA-01031 insufficient ...
0
votes
1answer
151 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
2
votes
0answers
106 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
5
votes
1answer
2k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
1
vote
1answer
1k views

MySQL select query result set changes based on column order

I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results ...
0
votes
0answers
2k views

MySQL: High sort_buffer_size setting value

Thing is I was getting plenty of 131104 20:10:36 [ERROR] /usr/sbin/mysqld: Sort aborted with 256M and rather erratic behaviour in my load test. Now at sort_buffer_size = 1024M ...
1
vote
2answers
438 views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
1
vote
1answer
219 views

sp_grantdbaccess cannot be run in a stored proc owned by DBO?

Maybe I'm getting this wrong, but... I have a SQL Server 2000 database with a stored procedure that is owned by DBO. This stored procedure is calling sp_grantdbaccess, which can be run by DBO, ...
-1
votes
3answers
204 views

Alternative of a query

I have a query : SELECT TOP 21 * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY contacts.id ) AS row_number, contacts.id, contacts.first_name, ...
1
vote
1answer
952 views

ORACLE file I/O withouth explicit flushing on 10g and 11g

what happens, if I open a file (foo := utl_file.fopen(... ), write multiple times to a file ( utl_file.put_line (foo,bar) ) and close it ( utl_file.fclose(foo) ) without any flushing? Is there a ...
1
vote
1answer
310 views

Squeezing Maximum Out Of My Server

I have a dedicated server only built to hold MySQL databases. Currently I have a database that has 173 million records and increasing by 5-15+ million records very day. My question is what and why I ...
-1
votes
1answer
63 views

Relation that combines entries with their subsequent entries, in order of date

I need to come up with a query that can do the following: I have a table with a bunch of transaction records, whose attributes are not important, other than the attribute Date. I need to come up ...
3
votes
0answers
405 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
4
votes
1answer
106 views

How can I script out a copy of the backup to multiple locations?

Is there a way to script a copy of the backup file to multiple destinations? For example, the following will split the backup into 3 segments which make up the entire backup file. But, I'm looking to ...
0
votes
3answers
659 views

Handling case-insensitive queries with case-sensitive data

What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries? For example, I have a Products table that holds household goods, and ...

15 30 50 per page