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
947 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
706 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
107 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 ...
3
votes
2answers
6k views

MySQL temporary column in SELECT statement

I'd like to do some calculations in a MySQL query. I've created some temporary columns which I'd like to use for the calculation. SQL fiddle for currently working (but discouraged) SQL query: ...
1
vote
1answer
780 views

“copy to tmp table” for 24 hours, then timeout; when ALTERing to add PK & AI

I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL. Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE ...
2
votes
2answers
604 views

SQL Server and SharePoint time problem

I have created a SharePoint application which has a specific time zone. The Windows server where SQL Server and SharePoint are installed are using same time zone. When I create a new item in list ...
0
votes
1answer
531 views

MySQL joining two tables and counting based on conditions

table 1 > cats cat1 cat2 table 2 > sites site1 > lang1 > cat2 site2 > lang2 > cat1 site3 > lang3 > cat1 site4 > lang1 > cat1 site5 > lang2 > cat1 site6 > ...
0
votes
1answer
32 views

How can I write to replicated environments without impacting production? [duplicate]

Are there any methods (mirroring, snapshot replication, change tracking, etc...) available in SQL Server 2k8 which would allow me to replicate production data to several individual development ...
4
votes
1answer
99 views

Query uses Primary Key Index instead of Nonclustered index defined

I have this simple query and I am confused why the index is not working. SELECT lop.RecordID ProspectusID, c.c_code CourseCode, c.c_desc CourseDescription, COALESCE(c.c_major, '') ...
2
votes
1answer
135 views

Confirmation on query optimization and performance understanding (seek and scan)

I am following along with this video on SQL Server query optimization and I would like to check my understanding of the results I am seeing. I am using SQL Server 2012 with the AdventureWorks 2012 ...
6
votes
2answers
295 views

Figuring out the physical size of a group of tables in SQL server

I have a group of about 30 tables, and I want to know the physical size on disk of all of these tables (plus indexes). Is there an easier way of doing this than through the GUI in SQL server 2008 R2? ...
3
votes
0answers
2k views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
0
votes
2answers
2k views

How to auto start instance of sql server 2012 localdb on startup?

How do you auto start instance of sql server 2012 localdb on startup?

15 30 50 per page