6
votes
3answers
21k views

Read XML file in SQL Server 2008

How can I read an XML file and store the data in XML to our table in SQL Server 2008?
6
votes
1answer
2k views

COUNT(*) on InnoDB whenever phpMyAdmin loads

It's a known issue that innodb is slow in SELECT count(*) FROM Table Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb ...
0
votes
2answers
1k views

How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

SQL Server DELETE only marks as DELETED though leaves as data in the data pages. Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
3
votes
2answers
2k views

MySQL 5.5.9 Wont Start

I installed mysql version 5.5.9 on my mac and I tried to start it using this command : sudo /usr/local/mysql/support-files/mysql.server start mysql didn't start with this command. I checked the ...
0
votes
2answers
853 views

PostgreSQL CREATE TABLE error

I'm new in PostgreSQL. I try to create table in database. In psql i write: CREATE TABLE mail_user ( user char(50) NOT NULL, domain char(50) NOT NULL, password char(50) NOT NULL ); But i ...
0
votes
2answers
523 views

How wrong is my web app modeling schema?

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years ...
6
votes
3answers
2k views

How to version control PostgreSQL schema with comments?

I version control most of my work with Git: code, documentation, system configuration. I am able to do that because all my valuable work is stored as text files. I have also been writing and dealing ...
17
votes
4answers
584 views

What is your workflow for planning a data migration?

So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be ...
1
vote
2answers
379 views

Get a list of databases that have a certain table

I'm trying to get a list of databases from a Postgresql instance that contains a certain table. I've started using the system catalog tables to try and figure this out. I was trying to use ...
5
votes
1answer
6k views

How to combine date and time to datetime in SQL Server?

I just found the following code: CREATE FUNCTION dbo.JoinDateAndTime ( @d datetime, @t datetime ) RETURNS Datetime as BEGIN declare @res datetime set @res = convert ...
1
vote
1answer
172 views

Multiple Distinct Columns

I have a table named 'DATAENTRY' with the following columns Title FName MName LName Add1 Add2 Add3 City Now I want to select records which have different (FName, MName ,LName) so I tried SELECT ...
5
votes
1answer
8k views

64-Bit OLEDB Provider for ODBC (MSDASQL) issues

I need to create a Linked Server from SQL 2008 SP1 64 bit instance located on a Windows 64 bit 2003 SP2 server towards a Mysql Server. As many of you know MSDASQL provider for 64 bit systems was put ...
3
votes
2answers
138 views

SQL server 2005 backup statistic

I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.
7
votes
5answers
2k views

Are there opensource hierarchical database management systems

I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.
0
votes
2answers
684 views

Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

Think of x as a column containing null, 0, 1. Only less than 1 % of the rows contain 1. There is no logical difference between 0 and Null. Is it a good idea to insist, that isnull rather than NVL is ...
2
votes
1answer
735 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
31
votes
2answers
76k views

How to the get current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
13
votes
5answers
1k views

Needing A Database Design Book

I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
4
votes
1answer
12k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
5
votes
2answers
566 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
4
votes
1answer
182 views

Establishing Baselines & Benchmarks

When you are hired as a DBA in a new shop, what are the important tools that you would use for establishing baselines and implementing benchmarks for 50+ instances? You advice would be highly ...
9
votes
2answers
811 views

How do I implement a database/table as a stack

I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I ...
29
votes
3answers
2k views

Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
4
votes
1answer
418 views

Help with Query Tuning

EDIT - drachenstern: see this question for more information on this problem and on the source of his query below: Group data by non-unique keys by distinct time range Can someone please advise ...
5
votes
1answer
2k views

Illegal XML character out of the extended event

I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up: Msg 9420, Level 16, State 1, Line 28 XML parsing: line 1, character ...
4
votes
3answers
1k views

Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM) ...
4
votes
2answers
3k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
4
votes
1answer
132 views

Is there a non-DDL way to preserve back-references when staging data into production?

Let's say I've got this hypothetical schema: Source (OLTP) DB: Table Orders ------------ OrderID int IDENTITY (PK), CustomerID int NOT NULL, OrderAmount decimal NOT NULL Destination (DSS) DB: ...
2
votes
3answers
240 views

multi-table mysql query

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project. A User is associated with Message/Thread/Project as either the Creator ...
5
votes
1answer
3k views

MySQL INNODB SELECT query hanging on copying to tmp table

I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
1
vote
1answer
1k views

What is the purpose of sqlbulkcopy's useinternaltransaction property?

I'm just testing my PowerShellScript to do bulkcopy of multiple tables between sql-server and oracle Databases. When the destination is Oracle, than I can monitor the progress by by executing select ...
5
votes
1answer
533 views

How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?

I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating ...
2
votes
2answers
5k views

How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this SPROC there is a query that selects data from ...
2
votes
1answer
4k views

“Procedure: XXXX has an unresolved reference to object XXXX” errors

After using for some time MS SQL Server Management Studio for creating and editing databases I tried to use Visual Studio 2010 "SQL Server 2008 Database Project" for this. I used the SQL 2008 Wizard ...
2
votes
1answer
272 views

databases/tables with nonconventional names

Disclaimer: not an administrator How does one deal with non-kosher names such as 'data?base' ? I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to ...
9
votes
5answers
3k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
3
votes
2answers
2k views

PostgreSQL: SET-ing from psql arguments

Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution: SET statement_timeout TO '1min'; This is great, but ...
6
votes
3answers
2k views

Problem with MySQL subquery

Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing? If I write it in this ...
8
votes
3answers
877 views

SQL Server: filegroup for system tables only?

One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements. So it looks like this fileid ...
14
votes
5answers
947 views

Why do primary keys have names of their own?

From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...
1
vote
0answers
271 views

Which are the Oracle basics required for advanced Oracle.DataAccess usage? [closed]

Successful use of [Oracle.DataAccess.Client] is based on proper understanding of three separate parts of Oracle: SQL PL/SQL sql*plus Here on dba.exchange the following questions target this topic: ...
33
votes
8answers
36k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
5
votes
2answers
1k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
7
votes
2answers
180 views

A database for digital assets

I work at a print shop that has a decent sized archive of old print jobs. Currently, to find a job we have to search the SMB share (on win2000 server), which looks through a few hundred thousand ...
2
votes
2answers
3k views

How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

In my oracle Database if have the following table: Create table Test_call_count ( count number(10) ); insert into Test_call_count values (0); Now I can run the following PowerShell script if ...
0
votes
1answer
206 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
5
votes
3answers
392 views

SELECT * WHERE VarCharColumn IN (…) Optimization

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution. The column is ...
7
votes
1answer
649 views

Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word?

In SQL 2008 Server Management Studio: is there a way to keep intellisense from always picking the longest word to autocomplete? For example, I could be trying to type the word client and it'll ...
5
votes
4answers
566 views

Group data by non-unique keys by distinct time range

I have data in my table like following... PROJ_NBR STATUS START_DT AL20 AC 1/14/2010 4:31 AL20 AC 1/14/2010 4:32 AL20 AC ...
5
votes
2answers
4k views

The SELECT permission was denied on the (View) object after explicit grant select on view to user

I have a database with a View Named '1098Statement' We are doing testing for an application with user name 'appuser'. I have tried the following solutions to granting select on the view and keep ...

15 30 50 per page