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)

25
votes
3answers
13k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
1
vote
1answer
637 views

When MySQL table gets too large

I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the ...
3
votes
2answers
364 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
2
votes
1answer
413 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
4
votes
3answers
170 views

Expanding a dataset based on a weight field

I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the ...
2
votes
1answer
2k views

Sybase SQL - Truncation error occurred. Command has been aborted and find CACHE QUALITY

Need help with below code, as it fails with truncation error Truncation error occurred. Command has been aborted. create table monCacheQuality ( ServerName sysname ...
30
votes
3answers
2k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
5
votes
3answers
7k views

Return multiple rows from matching on multiple CASE matches?

I want to add a column to my query which will specify one or more categories a row matches. I want to take this: +--------------+---------------+ Row | Product | Quantity_Sold | ...
1
vote
1answer
119 views

Number of times a table is read, written to and updated

Is there a way I can find out the number of times a table is read, updated, added to or has had rows deleted during a trace? I am creating the trace in SQL using sp_trace_setstatus, not using the Sql ...
7
votes
5answers
17k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
3
votes
3answers
266 views

why would command text not execute at all?

in my report i have:: <CommandText>SELECT column1, column2, 'poop' from mytable </CommandText> i am using sql server profiler to see exactly what statement is ...
1
vote
0answers
59 views

Rows do not match Subscriber and Publisher

I have an transactional replication setup with updateable subscription that is utilized by an Application. When I insert a project into the application, the msrepl_tran_version is different on the ...
0
votes
1answer
180 views

Suitable database software for small business [closed]

First of all, I apologise if this is in the wrong area. I'm looking for a suitable database program for a small engineering business, which needs to log various jobs. Currently, we're just using an ...
7
votes
2answers
1k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
-2
votes
1answer
303 views

Query sql-server db and insert results into PostgreSQL

I have a query that returns data from sql-server and I need to be able to put the results into PostgreSQL. I'm using C# for the task.
0
votes
3answers
207 views

Do database engines update when the updated value is the same? [closed]

Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the ...
2
votes
1answer
2k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
0
votes
0answers
146 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
1
vote
1answer
55 views

Altering strings while selecting them

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but ...
1
vote
0answers
188 views

Producing an outstanding fees report in MySQL

This question is about a MySQL stored procedure to select the records from two tables by joining them and also do some arithmetic operations in that. I have 3 tables for fees one is to store fee ...
1
vote
1answer
30 views

Plan for storing transactions in an easily retrievable manner, indefinitely

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the records as financial transactions. Clients need to be able to view these ...
0
votes
1answer
2k views

Query taking too long to execute

My query takes a lot of time, 22 seconds and above. After 22 seconds, we get more than 900 000 records. We have created indexes on the table which are used in the query: select * from ContactMaster ...
3
votes
2answers
1k views

Pulling autogrowth events by file name from default trace

I use below query to get auto-growth event occurred to databases in a sql server. DECLARE @trcfilename VARCHAR(1000); SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1 SELECT COUNT(*)as ...
7
votes
1answer
16k views

CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

Just came across a weird issue, whereby depending on how I create an index, an index name is required. http://dev.mysql.com/doc/refman/5.5/en/create-index.html ...
3
votes
2answers
6k views

Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have the following table: Key Value RecordDate 1 1 ...
-1
votes
1answer
103 views

Disconnect many persistent queries [closed]

By accident, I issued in the region of 500,000 individual queries against my database. It's been running for 6 hours now. When I restart the server, they continue to connect and run. I need them to ...
-2
votes
1answer
383 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 ...
-1
votes
1answer
2k views

'ambiguous column name' error refers to database, table, and column [closed]

sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as ...
0
votes
4answers
2k views

Space at the end of string

I have a SQL code like shown below: declare @str nvarchar(max),@i int set @i=0 set @str='abc ' declare @tbl Table(a nvarchar(max)) insert @tbl select @str while (select a from @tbl)<>'' begin ...
2
votes
2answers
13k views

Could not allocate a new page for database because of insufficient disk space

Using the SQL Server Import and Export Wizard, I get this error: Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. ...
3
votes
2answers
3k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
0
votes
2answers
531 views

Aggregate query on multiple values of same field

I am using SQL-Server 2012, I have a table of credit card transactions as follows: CardScheme RespCode ========================= Visa Declined MasterCard Approved MasterCard Approved ...
1
vote
3answers
163 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 ...
4
votes
1answer
2k views

Calculate months between two dates

For a University course I've been given the following task Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every ...
8
votes
2answers
599 views

function hangs with null case operation

I created a function that accepts a start and end date, with the end date being optional. I then wrote a CASE in the filter to use the start date if no end date is passed. CASE WHEN @dateEnd IS NULL ...
4
votes
2answers
1k views

1 <-> 0..1 relationship

I have "1 <-> 0..1 relationship". For example: There is a set of users. Some users are customers. Thus we have two tables: the table users and the table customers. What is the best way to ...
-4
votes
1answer
3k views

Can I create a view against a table on another instance? [closed]

I need to query a table from another SQL Server on a different server but same domain, but I am not sure how I will be able to do it. I tried the solution in this answer but it doesn't work for me as ...
3
votes
1answer
440 views

Limiting database security

A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
3
votes
1answer
156 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 ...
1
vote
2answers
503 views

Stored Procedure in MySQL

I am new to MySQL Stored Procedure, in my first attempt I got the following issues, Stored Procedure: Delimiter $$ create procedure accountstatus ( IN inmode varchar(27), IN ...
1
vote
2answers
134 views

Finding rows for a specified date range

I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate: CREATE TABLE `pupilgroupteacher` ( `id` int(10) unsigned NOT NULL ...
0
votes
0answers
26 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities? [duplicate]

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
2
votes
1answer
989 views

Does existing msrepl_tran_version column need to be removed when re-creating replication?

What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it ...
2
votes
3answers
739 views

SQL Server data extract with limited performance impact

Background: I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively. Problem: I need to do a data ...
2
votes
2answers
126 views

How to monitor a single db of an instance in SQL Server

I am trying to program against a vendor supplied database, and would like to understand what SQL statements or procedures they are calling. Is there a way I can monitor a specific database? The ...
5
votes
2answers
2k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
1
vote
1answer
3k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
1
vote
1answer
368 views

Column of generic/variable type?

In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is: CREATE TABLE TagValue ( tag VARCHAR(64), ...
4
votes
2answers
3k views

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 ...
0
votes
1answer
568 views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?