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)

2
votes
1answer
105 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 ...
3
votes
2answers
2k views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
1
vote
1answer
483 views

Multiple like conditions for same column?

How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")? My example data for table "address": name like "John" name like ...
1
vote
1answer
327 views

Visually create a join using PHPMyAdmin

I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join. Is this possible on PHPMyAdmin?
0
votes
0answers
75 views

Linked server performace issue?

I have created a linked server in sql server 2008 R2 Enterprise Edition for mysql i.e. MySQL database is accessible under sql server environment. I have a created a trigger in sql server such that ...
0
votes
1answer
423 views

Permission Denied Copy [closed]

I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS. copy tname from '/Users/Shared/folder/file.cxv' ...
2
votes
1answer
37 views

Which MySQL directories to back up using FTP

My server has been hacked and I only have FTP access to recover my data before reinstalling the OS. Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its ...
3
votes
2answers
2k views

How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question): tbl_users: ID | email (all subscribers to our email list) ...
2
votes
1answer
60 views

Show all results for past N “insert batches”

Every once in a while I have several users check in with some value: +-------+-------------+------+---------------------+ | id | user | RT | time | ...
2
votes
0answers
511 views

Cannot connect to SQL Server 2008 after updating the service pack

After updating, whenever I start the service of the server in configuration manager, it always stops then. And I found the error in the log 2013-04-13 19:08:19.63 spid7s Error: 15174, ...
7
votes
1answer
793 views

selecting more fields causes catastrophic failure

I have a connection to a linked server from sql server 2012 to pervasive sql. When I do select field1, field2, field3 from mytable everything works! However, when I do select field1, field2, field3, ...
3
votes
4answers
1k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
6
votes
1answer
1k views

How do I optimize large table so queries that target only recent data perform optimaly?

So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved. I am open to ...
2
votes
2answers
13k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
1
vote
1answer
292 views

One call to `decryptbypassphrase` for decrypting all columns

If I use ENCRYPTBYPASSPHRASE to encrypt the full table then how can I decrypt full table easily by using DECRYPTBYPASSPHRASE? Actually I have to use DECRYPTBYPASSPHRASE ('passphrase', column_name) ...
7
votes
2answers
2k views

Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?

I have a table that stores available appointments for teachers, allowing two kinds of insertions: Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't ...
0
votes
0answers
97 views

Insert performance deterioration over network [closed]

i have a strange issue with a windows application (in delphi) running on a win7 client. The application has a loops and inserts rows on a SQL Server table. It's simply an insert statement, repeated on ...
2
votes
2answers
1k views

How to insert into junction table using triggers

Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables. Artist and Song are base tables and SongArtist is the ...
0
votes
1answer
161 views

What index to add in case of join by two optional fields

I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional. ...
2
votes
1answer
381 views

SQL Application Role access-control

I have been researching the use of Application Roles within Microsoft SQL Server and was wondering if there is a way to limit a user to only be able to set specific application roles. As I understand, ...
1
vote
1answer
442 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
1answer
139 views

how to show only changed rows

if want querying the following table : EventDateTime (DateTime), Host (guid), Flag ( bit) 2013-04-01 05:00, {id-x}, 0 2013-04-01 05:01, {id-x}, 0 2013-04-01 05:02, {id-x}, 0 2013-04-01 05:03, {id-x}, ...
0
votes
2answers
5k views

How to Drop Tables using a variable in SQL Server?

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table. WHY?: In my Access Database I am running a ...
0
votes
2answers
2k views

How do I open local files using heidiSQL?

I'm using HeidiSQL to access my remote MySQL db. Recently I was sent an .sql file. Is there a way to open this file using HeidiSQL? Does anyone know of a better way to graphically view and ...
1
vote
0answers
285 views

Database Timeout Error from VB Application

Recently we are receiving so many database timeout error from different servers we are using vb application -- which will use ODBC connection to connect database - here no problem when we run the ...
1
vote
0answers
114 views

Why is this query creating 300+ temporary tables in Mysql?

SHOW global STATUS LIKE '%tmp%'; set @UID = 132; set @param2 = 'cde'; set @param3 = 20130331; set @param4 = 'C'; select p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, ...
11
votes
5answers
10k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
0
votes
0answers
86 views

Huge sizes Replication Tables in Sql Server db, how to deal with? [closed]

I've a sql server database that is configured to work with old software, i've no details about the software, but i know that DB uses replication. the DB size dramatically increases. When i ...
0
votes
1answer
57 views

SQL Server 2000 trace file extension

Are SQL Server 2000 trace files generated in a .tdf format, the same way as SQL Server 2008 R2? Thanks
-4
votes
2answers
5k views

Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]

In the following Fiddle, sqlfiddle I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table Please refer to this question. Since ...
1
vote
1answer
454 views

Moving model database

I have moved the model database by using following method For each file to be moved, run the following statement. ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = ...
0
votes
1answer
333 views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
1
vote
2answers
1k views

Get most recent 100 records from the database

I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever ...
-1
votes
1answer
50 views

what kind of owner to defined when creating Sql server DB for web site? [closed]

what kind of owner to defined when creating Sql server DB for web site ? I am trying to create db and i need to select owner. my db will serve aspnet web site and I wonder witch owner will be safe to ...
4
votes
1answer
3k views

Moving MSDB database

When I am trying to move msdb database from it's default location (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:). After moving the database I am getting ...
-1
votes
2answers
2k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
5
votes
2answers
528 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 ...
6
votes
3answers
1k views

Query best possible matches and order them

I'm trying to write a query along these lines: select * from tbl where col1 = 1 and col2 = 2 and col3 = 3 order by ... ; I want first all the results where all 3 WHERE ...
0
votes
0answers
360 views

Oracle Alternative to EXISTS EXCEPT

I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here: Comparing Columns that can contain NULLS - is there a more elegant way? I can't get this ...
-1
votes
2answers
97 views

Combine two SQL statements to a single statement [closed]

My question is related to SQL. I need to pull values from two different tables. I am not successful in doing that. I am able to join the two tables with only a single condition but not many values ...
0
votes
2answers
5k views

Multiple Aliases in an SQL Query

Would it be possible to give a table in MySQL query multiple aliases? If so how would I do this?
3
votes
1answer
581 views

Convert SQL Server UPDATE Statement for Use in Oracle

I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server. I am looking for some guidance on how to convert it. UPDATE SOB SET COA = CASE ...
1
vote
2answers
534 views

Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Imagine you have inherited some project with extensive usage of SQL queries. Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point. A question is: could ...
4
votes
2answers
3k views

SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose ...
0
votes
1answer
125 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
2answers
239 views

SQL Server database backup on Remote site

We have 4 different sites and each site has a SQL Server. our requirement is to have a SQL Server backup or database(Mdf files) of site 1,2,3 in site 4. In case of disaster on any of the site we ...
0
votes
1answer
891 views

Set field values to newly imported rows in PostgreSQL table with existing data

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import. Question: For the newly imported ...
5
votes
1answer
456 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
0
votes
2answers
2k views

Estimated Execution Plan SQL Server Sort?

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything. Anyone got ...