DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
0answers
2k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
1
vote
1answer
1k views

Error creating scalar function “Invalid Object Name dbo.fnMyFunctionName'”

I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions. When I try to modify function, function name is ...
-2
votes
1answer
367 views

Second time query execution using different constants makes faster? [closed]

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
180 views

Database is read-only

I got this error. How can I resolve it? Failed to update database "msdb" because the database is read-only. Could not insert a backup or restore history/detail record in the msdb database. This ...
6
votes
3answers
215 views

Primary Key efficiency

If I need a table to hold Point of Sales transactions, and am told I need to store: Country ID Store Number POS Terminal Number Transaction Date Item Code Teller ID Another Field More Fields Now, ...
3
votes
2answers
1k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
3
votes
2answers
2k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
2answers
158 views

Find the average number of n:m connections of two tables?

I want to know to how many lists a user is assigned in average. User ==== id name Lists_Users =========== user_id list_id List ==== id title Is there a good way of receiving this number via ...
3
votes
2answers
593 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
1
vote
1answer
4k views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
1
vote
6answers
99 views

Performance about this simplified where-condition query

I have a query in SQL Server 2008 like this: SELECT a.type, a.name, a.startDate, a.endDate, b.key AS key, b.is_locked AS is_locked, ...
3
votes
1answer
236 views

Avoiding repetition without creating a view

Suppose that I have a query Q1 and I need to run a query like the following: Q1 union select * from (some query that uses Q1 outcome) I would like to do that: Without creating any view Without ...
2
votes
2answers
870 views

Mysql notifier Error

i'm getting error when opening Mysql notifier High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2. My mysql server is running. why it's showing this error.
1
vote
3answers
529 views

help with simple sql queries [closed]

Can I use some help please with the following query Consider the following schema: Suppliers(sid,sname,saddress) Parts(pid,pname,color) Catalog(sid->Suppliers,pid->Parts,cost)sid and pid ...
0
votes
1answer
760 views

SQL Server 2008 SP2 randomly slow down query

I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query. It is taking 4 sec to 4 min in execution. Please suggest the possibilities. There is no scope of query optimization. ...
0
votes
1answer
129 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
2
votes
2answers
108 views

How do I write backup procedure

I've read that a decent backup strategy would be: 1) Full backup weekly 2) Differential backups daily 3) Log backups every 5 minutes. Now question is how do I write this stored procedure and create ...
2
votes
1answer
292 views

How to Model / Quickly test for set membership in SQL?

Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below Desired Table Structure pkey | data | roles ...
1
vote
1answer
25 views

Reserving dates with quantities, table schema wrong?

Okay I have a table like so. ID Productname Quantity Image QuantityOut LastDateReserved 1 productOne 5 'image.jpg' 3 2013-03-15 2 ...
3
votes
1answer
153 views

Custom sp_who/sp_whoUsers

I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
8
votes
6answers
5k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
1
vote
3answers
968 views

How to generate TPC-DS query for sql-server from templates

I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale ...
2
votes
3answers
1k views

Database Design Relationship for Patient Visit in MS Access

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have. There are a total of 6 ...
5
votes
2answers
541 views

Defining constraints in `CREATE TABLE` statements

Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
2
votes
1answer
2k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
1answer
166 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
0
votes
2answers
144 views

Changing passphrase for EncryptByPassPhrase

I am using EncryptByPassPhrase to encrypting some columns. If I want to change the passphrase in the future, how can I change it?
0
votes
1answer
29 views

content types of insertion

I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
1
vote
1answer
446 views

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
2
votes
0answers
56 views

Create Scheme that allows for fluidity between entities [closed]

We are a small rapidly evolving company and recently investigated switching databases from SQL Server to a graph database. We like the graph approach because of the fluidity and openness that it ...
1
vote
0answers
541 views

Collect CPU, reads and writes when replaying SQL Profiler Trace

I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
2
votes
1answer
20k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
2
votes
4answers
4k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...
-1
votes
1answer
2k views

Conditionally INSERTING columns into Table

using SQL Azure and like to insert a data to a table using dynamic Query. No of columns in select statment is dynamic. It will be based on some conditions. Ex: Salary, Code. How can I create a ...
3
votes
1answer
98 views

Adding column from SSMS wizard in SQL Server

Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process: Create a New ...
2
votes
0answers
237 views

How to speed up SQL Server RIGHT JOIN on 100MM+ Records - use TVF?

I use a table insert to UNION a CTE of converting transactions to visits (converting or not) and then summarize the visit data (no details, just pages visited & txns completed) to get funnel ...
1
vote
1answer
135 views

Join Calender Table

I have a Datatable and a Calendartable. I do a join on this tables to get sequential dates if there is no data for this day. My problem now is that there a several id's and i need a count for each ...
4
votes
1answer
77 views

Under what circumstances can a division expression reduce the number of rows in a query's result?

I'm experiencing a problem with a query that I've written. Given a table with the schema: create table somevals(division varchar(100), subdivision varchar(100), ...
1
vote
1answer
300 views

Database Relational Design for Multiple Categories

I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
2
votes
1answer
2k views

SQL Server cluster questions please

We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment. We did a test on one of the instances, and failover test. Manual failover from node c ...
4
votes
2answers
148 views

SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct. I inherited this database and it came to ...
-4
votes
0answers
62 views

Can I move up rows in the memory itself? [closed]

Lets say i have the following table : id col2 col3 1 1 1 2 2 2 3 3 3 4 4 4 And I'm trying to move rows to different ...
10
votes
1answer
381 views

how do I insert a default row?

If I create a table with an identity column as primary key, and all the other columns have default values, for example create table rr (id int identity(1,1) primary key, dt datetime default ...
3
votes
2answers
5k views

How do I remove duplicate records in a join table in PostgreSQL?

I have a table that has a schema like this: create_table "questions_tags", :id => false, :force => true do |t| t.integer "question_id" t.integer "tag_id" end ...
3
votes
0answers
1k views

Is there a Postgres admin GUI that can execute a master file containing multiple sql files

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file. example: ...
3
votes
3answers
360 views

Sum Up values in a query based on other information

I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this: I get these results by running this statement: select distinct a.eventnum, ...
0
votes
1answer
2k views

SQL update set column = @ parameter

doeas anyone knows how to update a table where column = @parameter WHERE SELECT Basically I have a table with the following values. Table name is UserAttribute ID|Name |Properties| ...
1
vote
1answer
172 views

Query to determine a count by rolling interval

I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of ...
1
vote
1answer
59 views

Script to get duration

I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
330 views

How to Reorganize Microsoft Access Table

I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...