Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

2
votes
1answer
179 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
1
vote
1answer
9k views

How can we get table name from inside trigger?

I need to pass the table name from trigger to procedure. ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects] AFTER INSERT AS BEGIN SET NOCOUNT ON; -- procedure call ...
2
votes
1answer
996 views

Restoring SQL Server 2012 database onto 2008

I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: ...
3
votes
1answer
1k views

Create SQL Script for Statistics

I wanna create script my whole table, including all statistics and indexes. In SSM, When I right click table and create script, it gives me script for Indexes for not for statistics. If I right ...
1
vote
1answer
1k views

Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my ...
3
votes
3answers
18k views

how to check which background process are running in my oracle database?

To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ? Thanks
0
votes
2answers
228 views

Database search with multi joins

I have a MySQL database and I want to perform a little bigger search. I have about 10k records in one of the tables and It's expected to grow, but slowly. The biggest problem is that to perform the ...
5
votes
1answer
2k views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
-1
votes
3answers
118 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
1
vote
1answer
261 views

Separating tables vs having one table

At the moment I have a table setup that looks somewhat like this: create table tbl_locationcollections ( id int(11) PRIMARY KEY IDENTITY, --(Primary Key), name varchar(100) not null, ...
1
vote
1answer
77 views

Summation query with complex grouping criteria

I have a database of coded actions with this structure. {User, Date, ActionCode} I need to generate reports based on dates and action codes, and the action codes are not clean because we have to ...
1
vote
2answers
330 views

How to create trigger on database that 'triggers' when mirroring role changes from mirror to principal

I want to perform some sql when my database mirroring state changes
7
votes
2answers
4k views

tempdb logs on same drive as tempdb data or with other logs? [duplicate]

For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files: D: Data E: Logs F: Tempdb Should the tempdb log file go on F: with the data file(s) or on E:? ...
0
votes
4answers
289 views

Selecting the first purchase item of each customer

I have a table like this: CREATE TABLE SALES ( CID NUMBER(4), PDATE DATE, ITEMCODE NUMBER(4), ITEMNAME VARCHAR2(20) ); INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX'); INSERT INTO SALES ...
0
votes
1answer
133 views

Efficient way to perform approximated search?

I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record. As it happens ...
5
votes
2answers
13k views

Extract part of string based on nth instance of character

I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character. \\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS ...
0
votes
1answer
153 views

Combine multiple queries into single query

I am trying to run the following query: SELECT Freight.Freight_No, Freight.Available, Locomotive.Loco_No, Locomotive.Available, Driver.Driver_No, Driver.Name, ...
4
votes
2answers
5k views

Using T-SQL to dynamically create Extended Properties

I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values. It would be very laborious to add these ...
3
votes
2answers
7k views

Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)? Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
1answer
773 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
2answers
338 views

“Rewinding” a Postgresql database

I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
4
votes
2answers
4k views

Error while taking backup using SSMS

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for ...
2
votes
1answer
616 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...
1
vote
1answer
481 views

GROUP BY with OR/AND clause?

Lets say my database looks like this: Table: messages +----+------+----+----------+----------+ | id | from | to | double_1 | double_2 | +----+------+----+----------+----------+ | 1 | 1 ...
2
votes
1answer
143 views

How can I identify the number of times a View is called in SQL Server?

How can I identify the number of times a View was called in SQL Server? I am looking for an answer that is similar to this below which shows how it can be done for stored procedures. How can I ...
1
vote
3answers
166 views

T-SQL Issues With Defining 'AS'

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently ...
3
votes
2answers
101 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
2
votes
3answers
1k views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
1
vote
1answer
38 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
2
votes
1answer
615 views

How to enhance the SQL query performance for thousands and millions of huge data

Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
1
vote
3answers
10k views

Oracle SUM - ORA-00937: not a single-group group function

For every journey want to sum them to work out the distance between. SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between) FROM Journey, Journey_Stages, Stages WHERE ...
2
votes
2answers
1k views

SQL3550W: … target column has been defined as GENERATED ALWAYS

I am trying to export + import data from a table in a db2 database to another. But while running the export command I am getting the following error. SQL3550W The field value in row "26" and column ...
0
votes
2answers
343 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
0
votes
2answers
74 views

Accessing Database without using SQL

This might be some ambiguous but I have not much info about this so I need some start point; As a general idea; in conventional RDBMS', there are a DB engine and storage of data. We use the language ...
3
votes
3answers
2k views

Relational vs Non-Relational Database for Events Database

I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either ...
7
votes
1answer
1k views

LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
2
votes
1answer
681 views

Replacing a PHP loop with a MySQL Query

I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site. The ...
3
votes
2answers
4k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
4
votes
1answer
2k views

Update “NULL” string to Actual NULL value

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like where date is null it will not ...
0
votes
2answers
134 views

How to write a script to push the server time to out?

Background: I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data ...
0
votes
2answers
2k views

Only allow one checked row in a Column in SQL Server [duplicate]

Background: I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL. Question: Is is possible to have a ...
1
vote
4answers
1k views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
1
vote
2answers
7k views

Oracle SQL, how to filter on a set by a rank?

I am struggling with the following SQL query right now: http://sqlfiddle.com/#!4/736a7/12 select uc.* from usercredential uc inner join users u on u.userid = uc.userid inner join credential c on ...
0
votes
1answer
58 views

How should I set up these tables in my relational database?

I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm ...
0
votes
1answer
456 views

exec xp_cmdshell 'sc \\<servername/serverip> start <service_name>'

When I execute this on sql server on another machine (in same sub net) I am getting "Access is denied". But when I did it from command prompt (sc \ start ) it is working. Please advice why I am ...
20
votes
2answers
44k views

How to optimize a query that's running slow on Nested Loops (Inner Join)

TL;DR Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history: JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell ...
2
votes
4answers
2k views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...
2
votes
3answers
83 views

How to take SQL Server database under this scenario?

I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
0
votes
2answers
12k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
2
votes
1answer
115 views

Find application password expiration date

I changed password of my application on one sql server? when I am using Login property command I am getting last login date. I want to know when my application password is going to expired in SQL ...