2
votes
2answers
885 views

sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

I have a SQL 2005 stored proc that sends an email with some job history: msdb.dbo.sp_send_dbmail @recipients = 'me@here.com', @Subject = 'job history', @Body = 'check this out...', ...
2
votes
5answers
193 views

How can I store function names to be executed on a subset of rows?

I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations ...
5
votes
2answers
9k views

Oracle - Automate Export/Unload of Data

Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML). Is there any way to automate it? If not, what free tools are available ...
5
votes
3answers
3k views

Postgresql row-level encryption

Looking over the docs, I didn't see any mention of row-level encryption. Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
2
votes
1answer
140 views

InnoDB foreign key and index duplication?

I'm have a part table like this: part_id INT manufacturer_id INT supplier_id INT part_number VARCHAR(50) part_description VARCHAR(120) with foreign ...
2
votes
4answers
117 views

Backup Scenarios

In My Computer, on C drive I have a Database of 50GB and I want to take backup of that 50 GB data to other drives D,E,F but D,E,F Drives contain only 20 GB for each. In this scenario how can i take ...
4
votes
2answers
2k views

Query problem: there can be only one auto column

Can someone please tell me what's wrong with this table definition. mysql version is 5.1.52-log root@localhost spoolrdb> create table spoolqueue ( queue int, ...
1
vote
2answers
238 views

dm_db_missing_index_details - does the order of equality_columns matter

I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view. MSDN says that this ...
4
votes
1answer
1k views

Storing hex values as strings or integers?

I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints. I'm ...
4
votes
1answer
386 views

How can a raised alert fail to notify an operator via e-mail?

SQL Server 2008: A message with @msgnum = 75000 and @severity = 16 was added to the SQL instance. An alert was added that is raised when error number 75000 is raised. The alert is enabled and an ...
4
votes
1answer
9k views

CONTAINS function in MS SQL 2008 R2

I try the following SQL query : SELECT FirstName, SecondName FROM Persons WHERE CONTAINS(FirstName, 'Mat') and I got this error : Cannot use a CONTAINS or FREETEXT predicate on table or ...
3
votes
2answers
106 views

Rankings based on “versus” style matchups? [closed]

Let's say I have an application that, from a pool of items, presents a random two to the user. The user selects one as the "winner" and the result would be recorded like this: INSERT INTO results ...
6
votes
3answers
3k views

query regarding combining an update and an insert query into a single query in mysql

i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data. I can use a select to get the old ...
2
votes
3answers
745 views

EXCEL sheets batch input to Access

I have a lot of Excel datasheet (in one file) I want to batch input to Access but I can't find a sample code to do it the name of datasheet in access is same as in excel is there any code of ...
5
votes
3answers
11k views

How do I output MySQL logs to syslog?

I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS). I found information that output error log to syslog. [mysqld_safe] syslog But I want to use syslog for logging general logs and ...
6
votes
1answer
5k views

Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server

I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server. I checked this once and found that after this is run, ...
7
votes
2answers
412 views

Can I losslessly decompose this table?

I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills. In essence, I have a table with the following primary key: child_id integer ...
3
votes
2answers
8k views

Oracle 10g dump file push into Oracle 11g database

Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser The following errors occur: IMP-00058: ORACLE error 12560 ...
6
votes
5answers
7k views

SQL Select taking too much time to execute

It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table. In code: SELECT TempTable.Col1, ...
4
votes
2answers
5k views

What are some common and useful fields for a users table in a database?

I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, ...
5
votes
3answers
575 views

What are the top 5 design decisions which need to be made when implementing a database?

I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript. I am new to Database ...
3
votes
1answer
503 views

Is using CRUD stored procedures against a view with NOLOCK bad?

(I am crossposting this from SO on the suggestion of a commenter) Our DBAs have created a pattern where our database layer is exposed to EF via views and CRUD stored procedures. The CRUD works ...
3
votes
2answers
461 views

Unique time range or database schema for a scheduling system

I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap. In my SheduledSlot table I have: id integer PRIMARY KEY begin ...
2
votes
1answer
2k views

Tool to Convert MySQL DB to MS SQL 2005 (Especially stored procedures) [closed]

Is there any tool to convert MySql db to MS SQL 2005. I am mostly interested in converting Stored Procedures. There are 100s of procedures and writing them again in T-SQL is a big job. Thanks.
8
votes
3answers
3k views

Is there a standard implementation of a Users Database?

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
3
votes
1answer
989 views

No “Using locked pages for buffer pool” in SQL Server log

As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to ...
7
votes
2answers
2k views

Fact table foreign keys null?

I am new to data mart design and need to clear a few concepts. I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables. Now suppose ...
2
votes
2answers
6k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
4
votes
2answers
7k views

Multiple copies (cp) with Oracle ASM asmcmd tool?

In order to import a transportable tablespace into an Oracle 11 database with ASM, we use the cp command of the asmcmd tool. How can we copy more than one file at a time? Wildcards are not ...
2
votes
3answers
697 views

Best database choice for a highly scalable web ticketing system?

Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will ...
4
votes
4answers
4k views

Storing arrays of integers in database (for efficient select)

I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of ...
3
votes
1answer
2k views

What does “slot 80 for LOB data type node does not exist” error in sql server mean?

When I modify a table in SQL Server 2008 I get the following error: The Database ID 29, Page (1:235225), slot 80 for LOB data type node does not exist. This is usually caused by transactions ...
2
votes
3answers
12k views

Clone / Copy Oracle 10g & 11g

Hi I am absolute begginer in Oracle domain. I would like to learn way how to clone copy oracle database to som backup, use this backup on another server. Oracle is installed on Windows server 2003. ...
4
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
7
votes
1answer
2k views

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The ...
16
votes
6answers
53k views

Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on? I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
1
vote
1answer
2k views

SQL 2008 R2 Repair: Setup failed to install Windows PowerShell. Error code is -2145124329

When I try to Repair my SQL 2008 R2, I get the following error. How can I solve this? TITLE: Microsoft SQL Server 2008 R2 Setup The following error has occurred: Setup failed to install Windows ...
1
vote
1answer
158 views

Duplicate record problem

create table #Personel ( Ad varchar(100) ) create table #Masa ( MasaAdi varchar(100) ) insert into #Personel values('deneme1') insert into #Personel values('deneme2') insert into #Personel ...
8
votes
2answers
1k views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
7
votes
2answers
11k views

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated...) ...
1
vote
1answer
8k views

store result set in temporary table, variable or separate variables within a trigger

I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I ...
0
votes
1answer
485 views

Performance problem caused by joining with the result of a scalar function?

In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join. When I store the result of the left side into a temporary table and ...
1
vote
1answer
2k views

Recovering from: A transport-level error has occurred when sending the request to the server in SSMS

Every now and than I loss the connection in SSMS and get the message Msg 121, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP ...
4
votes
2answers
3k views

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
5
votes
4answers
11k views

MySQL: Why is auto_increment limited to just primary keys?

I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be ...
3
votes
2answers
832 views

A query submitted from different applications has differing DOP

I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
1
vote
1answer
615 views

CLR TVF that calls web service is getting 401error

I am calling a web service from a CLR TVF in SQLServer 2008R2. On the IIS server this service has open permissions. When tested using GET or POST requests on a browser the web service works as ...
1
vote
1answer
772 views

Cartesian Product SQL / Inserts with multiple Values Clauses

I want to create cartesian product sql query.But,I'm getting the error.Where I wrong? alter table #Personel ( Ad varchar(100) ) alter table #Masa ( MasaAdi varchar(100) ) insert into #Personel ...
7
votes
1answer
19k views

How can I Estimate Table Sizes within Schema (Oracle)

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far: SELECT table_name, owner, last_analyzed FROM all_tables I'm fairly new to SQL so I have no idea how I ...
0
votes
1answer
336 views

Date Format in Sql Server

I am importing data from another sources, which has multiple columns but I am facing problem in date column like I have multiple format in this column like: dd/mm/yyyy mm/dd/yyyy mmm-yyyy Can you ...

15 30 50 per page