6
votes
1answer
282 views

selecting where two columns are in a set

This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following: SELECT whatever WHERE col1,col2 IN ...
3
votes
1answer
562 views

Different Results From sys.databases As Different Users

We have a SQL Agent job that is configured to run as user "dbo". This job gets a list of databases defined in sys.databases that match a naming scheme. The job then performs some dynamic SQL to ...
6
votes
1answer
498 views

Getting Identity values to use as FK in an INSTEAD OF trigger

I have a series of updateable views we are exposing to end users as the interface for a back end process. One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and ...
12
votes
1answer
13k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
1
vote
4answers
3k views

Restore backup fails - not enough disk space [duplicate]

I have a backup that's about 6Gb. It's a "light" backup of the original (with the log tables purged), which is about 14Gb. I try to restore the backup on my SQL Express local server. It fails with a ...
3
votes
2answers
916 views

Update row based on match to previous row

I have the following table: Declare @m_TestTable table ( RowID int, OrderNo nvarchar(10), DetailID int, FirstUniqueDetailEntry int ) Insert into ...
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
434 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
200 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
161 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
387 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
142 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
314 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
182 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
353 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
993 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
93 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
417 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
381 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
576 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. ...

15 30 50 per page