6
votes
3answers
1k views

Clustered index in SQL Server vs index organized tables in Oracle

I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
2
votes
3answers
158 views

Looking for industry standard and/or tool to represent and export RDBMS metadata

Do you know of an industry standard that is designed to describe the metadata contents of a relational database (or part of it, such as tables, columns, etc.)? Do you know if a tool exists that does ...
4
votes
1answer
310 views

Deciding between an IsDeleted/DateDeleted column or a separate History table?

We have a table that stores nothing but an AccountId, TagId, and DateCreated. Users can update the Tags on accounts, which adds or remove records from this table. No tag can exist more than once on an ...
2
votes
1answer
436 views

Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included

I am currently experiencing the error (logged via traceID 1222 ): keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S ...
0
votes
0answers
313 views

MySQL Left Join Select all rows

Hi guys I have a query which is pulling data from database : SELECT sandwich_table.id, `sandwich_name`, salad, COUNT(salad), salad_type.type, ...
0
votes
0answers
1k views

MS Access trigger

I got a trigger to update a table in SQL. My actual need is in MS Access but I could not make it. So please help me to write the same trigger in MS Access. Query is as follows: Create trigger ...
1
vote
2answers
1k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
1
vote
1answer
71 views

Reference the database from a stored proc?

I'm using this T-SQL to get index stats (edited for brevity): SET @db_id = DB_ID(N'TestDB'); SET @object_id = OBJECT_ID(N'TestDB.dbo.Users'); SELECT * FROM sys.dm_db_index_physical_stats(@db_id, ...
0
votes
0answers
201 views

Try to find a match using SUBSTRING(SHA1(value)), 0, 6)

I have a tbl where all entries for companies that participate in a online project are saved. This table is named "business". Those companies can use their employess to access the online platform i 've ...
2
votes
1answer
3k views

unable to login oracle as sysdba

I just got 11gR2 running and was able to conn as sysdba. I shutdown and started up the database to mount a new pfile. Now, I cannot login as sysdba. My parameter for password file is: ...
2
votes
0answers
1k views

How to find when last reindexing happened in sql server? [closed]

I have set 'job' for re-indexing, I want to know when the last re-indexing happened in datetime.
0
votes
2answers
41 views

SQLQueryTableData-Important

In SQL Server Management Studio, when I run the query select * from tablename it shows me the data. But, that table is not physically present in that database. Is it possible?
7
votes
1answer
2k views

SQL Server Deadlock on two updates due to index lock order

I have two UPDATEs - one locks the CI first and then the NCI (on status) because the status column is also being updated. The other already owns a U lock on the NCI because it knows it is changing ...
0
votes
1answer
163 views

Server default collation change or not change

The default collation of our new server is SQL_Latin1_General_CP1_CI_AS. However the collation of the other databases which are used as the source to migrate data into the target databases on this ...
4
votes
1answer
1k views

Continuing failovers on production server

At least a few times a day, my main production server has recently begun failing over. Only, the quorum doesn't move to the other node. I ran the validation report, and got a lot of info I don't ...
8
votes
1answer
388 views

Should I be alarmed by this NO JOIN PREDICATE warning?

I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning select method = case ...
2
votes
1answer
1k views

How do you avoid a foreign key constraint creating deadlocks?

So I have two tables that create a deadlock. The application literally do nothing but update two different tables in two different transactions. There is a foreign key constraint (not cascading, just ...
0
votes
1answer
87 views

sql job cancel triggers failure notification

When I cancel a sql agent job running an ssis package, I get a failure email because I set up the notifications to email when the Job Fails. Does anyone know why this is considered a 'failure'?
1
vote
1answer
7k views

How and when to use sys_refcursor in oracle

Can some one give me a small explanation about how and when should someone use sys_refcursor ?
5
votes
1answer
143 views

Unexpected Unique Constraint Violation

Given the following CREATE TABLE statement: CREATE TABLE [dbo].[Unit] ( [id] SMALLINT NOT NULL IDENTITY(1,1), [name] VARCHAR(100) NOT NULL, CONSTRAINT ...
5
votes
3answers
9k views

Can SQL Server Management Studio Express be installed standalone?

I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
4
votes
2answers
316 views

Open source and commercial tools to build dynamic queries of fact tables in star schema

What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z ...
5
votes
1answer
378 views

TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
3
votes
1answer
1k views

Identify File Growth Events

I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to ...
7
votes
1answer
111 views

Trouble with SQL and aggregates

With this query: SELECT SUM(qty) AS sumQty, cnetprodid FROM quoteitem WHERE ordered = 1 AND sageSOPOrderReturnLineID IS NOT NULL AND LEN(LTRIM(RTRIM(cnetprodid))) > 0 ...
3
votes
1answer
184 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
2
votes
1answer
100 views

Oracle Table Design, Storing Small Values

In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
9
votes
2answers
427 views

Best way to defrag/compact a database for archival purposes

We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
7
votes
3answers
3k views

How to have a one-to-many relationship with a privileged child?

I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
1
vote
1answer
360 views

Automatic MySQL Sync from master, where Slave was offline for long period of time

Does anyone here know if there are any tools, not using mysql's built in replication that will sync up two databases when the slave has been inaccessible from the internet for extended periods of ...
0
votes
0answers
1k views

12154 error when using PL/SQL Developer

I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit. Using the following guide I removed all versions of ...
1
vote
2answers
1k views

Using MySQL EXPLAIN to analyze the performance of a function

You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
4
votes
1answer
94 views

comma delimited column fix

At my current place of employment someone made a decision to put IDs into a comma delimited column. I'm looking to break these out into a proper many to many relationship. However; it is not in the ...
9
votes
5answers
385 views

Efficiently Filter Large Set With Disjunctions

Let's say I have a single table CREATE TABLE Ticket ( TicketId int NOT NULL, InsertDateTime datetime NOT NULL, SiteId int NOT NULL, StatusId tinyint NOT NULL, AssignedId int NULL, ...
1
vote
1answer
120 views

Change SQL Server 2008 name

I want to change my SQL Server's name. Currently I am connecting to SERVER1\SQLSERVER. SERVER1 is the machine name. I want to change it so I could connect to SERVER2\SQLSERVER and keep SERVER1 as the ...
4
votes
2answers
1k views

Struggling with pgBouncer

I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine. My application can happily ...
2
votes
1answer
49 views

Changing subscription expire days

When I go to publication properties and General Tab in that. I see an option Subscription expire and may be dropped if not synchronized in the following number of days. Replication meta data is kept ...
7
votes
2answers
8k views

How export a sql server 2008 diagram to PDF filetype?

I want to have a export from my database diagram to PDF or image types.How can I do this? I worked with sql server 2008 R2.
2
votes
1answer
422 views

Monitor user to query processlist

We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to ...
3
votes
1answer
383 views

SSD for production database

I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides ...
3
votes
3answers
581 views

Identify applications with locks

In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2. Many users access this database via some applications, that sometimes "forget" to ...
7
votes
3answers
185 views

Choice- or ids-list- field representation

I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to ...
3
votes
2answers
3k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
0
votes
1answer
142 views

Database suitable for data mining

What would be an appropriate / suitable database sans Oracle that would be suitable for the following conditions? data mining includes but does not limit to the following: a. clicking on links b. ...
0
votes
0answers
33 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
0
votes
1answer
90 views

Assign most elevated permission to Sql Server DB

I am using SQL server 2008 R2, on my development machine. I had to delete the old windows user account, with which I had installed SQL Server. I have created a new admin account. When doing any DB ...
1
vote
1answer
227 views

MySQL selects/updates only using 1 core

I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads. When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at ...
1
vote
2answers
395 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
3
votes
1answer
196 views

Use a Context namespace in a multi-tenant database?

I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works ...
1
vote
2answers
2k views

MySQL case sensitivity for table names

Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this. Yes, windows ...

15 30 50 per page