Database language for managing data in relational database management systems (RDBMS). Note that each database implements SQL with slight variations. Please remember to tag with your specific database as well, e.g. [tag:mysql], [tag:postgres], [tag:mssql], [tag:oracle]

learn more… | top users | synonyms

74
votes
8answers
83k views

Dump Microsoft SQL Server database to an SQL script

Is there any way to export a Microsoft SQL Server database to an sql script? I'm looking for something which behaves similarly to mysqldump, taking a database name, and producing a single script ...
29
votes
2answers
45k views

Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?

I'm trying to grant all privileges on all tables of a given database to a new postgres user (not the owner). It seems that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; does not do that. After ...
28
votes
9answers
7k views

To improve SQL performance, why not just put lots of RAM rather than having faster hard disks?

People keep telling me that in order to improve an SQL server's performance, buy the fastest hard disks possible with RAID 5, etc. So I was thinking, instead of spending all the money for ...
28
votes
6answers
50k views

GRANT SELECT to all tables in postgresql

Is there a one-liner that grants the SELECT permissions to a new user postgresql? Something that would implement the following pseudo-code: GRANT SELECT ON TABLE * TO my_new_user;
24
votes
3answers
15k views

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres?

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres? Is this possible? If not what is the next best solution? I need the statement because I use it to create the table on an remote server ...
22
votes
15answers
3k views

HELP! Production DB was SQL INJECTED! [duplicate]

Possible Duplicate: My server's been hacked EMERGENCY Geeze, I'm desperate! A few hours ago our production DB was sql-injected. I know we have some big holes in the system... because ...
20
votes
8answers
59k views

SQL Server 2008 Express - “Best” backup solution?

What backup solutions would you recommend when using SQL Server 2008 Express? I'm pretty new to SQL Server, but as I'm coming from an MySQL background I thought of setting up replication on another ...
13
votes
6answers
21k views

How can I diff two Oracle 10g Schemas?

I have the feeling that there are some difference between two large complicated Oracle schemas which should be identical, so decided to dump and diff them to investigate. I found an article ...
13
votes
2answers
15k views

Third-party SSD solutions in ProLiant Gen8 servers

I was wondering if anyone had any specific experience using Intel DC3700 SSDs (or similar) in the HP (DL380p) Gen8 servers? I'm upgrading a set of database servers that use direct-attached storage. ...
13
votes
6answers
36k views

How do I change the collation of a SQL Server Database?

I'm trying to standardise all databases on a single collation - Latin1_General_CI_AS (the standard collation). I have some databases that are in SQL_Latin1_General_CP1_CI_AS. I know I can use ALTER ...
13
votes
4answers
13k views

Alter charset and collation in all columns in all tables in MySQL

I need to execute these statements in all tables for all columns. alter table table_name charset=utf8; alter table table_name alter column column_name charset=utf8; Is it possible to automate this ...
11
votes
3answers
18k views

Postgres: How can I see all SQL statements being executed by the database server?

I'm in the process of reviewing every SQL statement that an application makes against the database, for performance reasons. Is there an easy way to log all statements that are executed by the ...
11
votes
5answers
14k views

How to load-balance SQL Server 2008 for high-usage ASP.NET applications?

Imagine that you have a successful web application that uses ASP.NET and IIS 7. It generates many calls to a SQL Server 2008 database, and is expected to be available to the public with 99.9% uptime ...
10
votes
2answers
3k views

Postgres: Non zero exit code when executing a sql file?

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One ...
10
votes
2answers
9k views

How to change in postgresql password of the user using SQL

How to change in postgresql password of the user using SQL. I have user (alex) and his password (e.g. pass) i need to change using sql statement his password to NULL...
10
votes
2answers
918 views

Is there a MySQL performance benchmark to measure the impact of utf8_unicode_ci versus utf8_general_ci?

I read here and there that using the utf8_unicode_ci collation ensures a better treatment of unicode text (for example, it knowns how to expand characters such as 'œ' into 'oe' for searching and ...
9
votes
1answer
12k views

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently ...
8
votes
2answers
1k views

Is it a good idea to back up SQL database to an External USB Hard Drive?

I will be installing a Database System on an Offshore Oil Platform (rack Servers). They have very limited hardware and space therefore sending up a backup infrastructure is not possible. Sending ...
8
votes
7answers
2k views

Is MySQL better than PostgreSQL in something?

I know the question sounds provocative, but it really isn't. I'm lately finding MySQL limiting in a lot of areas and liking PostgreSQL more and more. It scales a lot better and it respects the SQL ...
8
votes
1answer
1k views

Are vCPU the same as 1 Socket, or a single Core?

Currently I have a Hyper-v VPS with 2 (vCPU) processor. I would like to install SQL Server Express 2012 that has a limitation of 1 Socket or 4 Cores, whichever the lesser. My Question: Are vCPU's ...
7
votes
8answers
2k views

Should I ever DELETE (SQL and DB) anything?

I am curious, should I ever delete anything? Right now I am building a site (for myself) which allows you to subscribe to users which you then receive a message every time the user has upload content. ...
7
votes
3answers
2k views

Is there an easier way of resolving SQL Server/database collation mismatches than changing every column?

DISCLAIMER: I know that this question has been asked a hundred times before, but I just wanted to check that there wasn't an easier solution I might have missed before I went ahead and wrote/sourced ...
6
votes
6answers
1k views

How should I set up protection for the database against sql injection when all the php scripts are flawed?

I've inherited a php web app that is very insecure, with a history of sql injection. I can't fix the scripts immediately, I rather need them to be running to have the website running, and there are ...
6
votes
3answers
11k views

mdf file cannot be overwritten when restoring a database in SQL Server

I got a databse A. It has some data in it. I created a backup for A as A.bak file. Then I create a new empty database B. And then I try to restore B from A.bak. But the SQL Serve tell me the following ...
6
votes
1answer
8k views

Change collation of a MySQL table to utf8_general_cs

I tried to change collation MySQL table to utf8_general_cs but got following error: mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_cs'; ERROR 1273 (HY000): ...
6
votes
7answers
6k views

DHCP server with database backend

I have been looking around for something to replace my (ancient) ISC-DHCPd server. A DHCP server with a database backend sounds like a great idea to me, as I could then have a nice, friendly web ...
6
votes
2answers
6k views

How to attach mdf files from a different SQL Server Install?

Hi I have mdf and ldf files from a different SQL Server install from a machine i dont have access to now, and would like attach them to my current install to get at the data. When I try this, I get an ...
6
votes
5answers
15k views

Calling sp_start_job from a stored procedure

Our developers need to be able to start a SQL Server Agent job from their .Net code. I know I can call msdb..sp_start_job to do just that, but I don't want to give general user accounts direct access ...
6
votes
2answers
8k views

How to monitor activity on a single table using SQL Server profiler

I have a table which uses and identity seed for its unique identity column. We have noticed some massive jumps in the identity value which we are not able to explain. The table has 20,000 odd rows, ...
6
votes
2answers
3k views

DB auto failover in c# does not work when the principal server physically goes offline

I'm setting up DB auto failover in C# with SQL Server 2008 and I have a 'high safety with automatic failover mirror' using a witness setup and my connection string looks like "Server=tcp:DC01; ...
6
votes
4answers
4k views

SQL Server 2008 R2 Error 15401 when trying to add a domain user

I am trying to add a domain user. I am doing the following. Expand Security Right click on Logins Select New Login... Login name select search Click on location and select entire directory Type ...
5
votes
6answers
17k views

How to rollback a deleted table data?

I have deleted one table accidentally how can I rollback that. Please suggest that.
5
votes
1answer
6k views

How can I verify that a SQLite db3 file is valid/consistent

I have some sqlite version3 db3 files I copied off a live running production system (I know bad sysadmin bad sysadmin) for various reasons. Is there some sqlite command I can run that will verify that ...
5
votes
2answers
569 views

My table half-exists. Can't drop it

I have a table that is taking up quite a bit of space even though it has no rows. I also dropped it a while ago. When I try to drop it now, it says the table doesn't exist. It shows up in ...
5
votes
3answers
3k views

SQL Server Collation settings for System Center Service Manager 2012

There is a lot of conflicting or confusing information surrounding the required collation settings for SQL Server, as it pertains to hosting the SCSM 2012 database and associated data warehouse ...
5
votes
2answers
13k views

Difference between “NT AUTHORITY\NETWORK SERVICE” and “NT AUTHORITY\SYSTEM”

I am installing MS SQL Server 2008 R2 and I got to the window where I have to set the service accounts. I want to know the difference between NT AUTHORITY\SYSTEM" and "NT AUTHORITY\NETWORK SERVICE". I ...
5
votes
4answers
36k views

How can I tell if an Oracle database is mounted and activated?

IANADBA, but I'm writing a script that will take action as long as the oracle standby database hasn't been activated. What I'm looking for is two queries (or sets of queries, if necessary). 1 - Is ...
5
votes
1answer
1k views

On Amazon Web Services what is the difference between SQL Server Standard and SQL Server Web?

I noticed that SQL Server Web is vastly cheaper than SQL Server Standard on Amazon Web Services. Why is that? What am I missing if I use SQL Server Web instead of Standard?
5
votes
3answers
2k views

Do I need to split a database to filegroups? It's 30gb now

I'm in the process of changing a database server at my company from Windows 2000/Sql Server 2000 to a Windows 2003 R2/Sql Server 2005 now. It holds 30 databases, each of the databases is about 7gb in ...
5
votes
2answers
156 views

Impact of keeping 100+ Linked Server Objects open in MS SQL Server

I manage a large scale cloud based product that uses SQL Server as the database engine. The model is designed such that each customer using the product has its own instance of MS SQL Server. I'm ...
5
votes
3answers
883 views

Does resizing a column lock the table?

What happens if I change a column from nvarchar(2) to nvarchar(10) or a column from char(2) to nvarchar(10). Does this lock, reorganize the table or does this work without any impact or downtime? I ...
5
votes
2answers
3k views

SQL Server: Statements vs. Batches vs. Transactions vs. Connections

My question is simple: What are the differences / similarities / cardinalities between Transactions Batches Connections and Statements in SQL Server? As far as I understand a connection is a ...
4
votes
4answers
3k views

How to shape size of SQL server resorces (RAM, CPU, and others)

It is standard story, there is fight between developers and administrators. One denunciation that database design and queries are bad while others says It is lack of hardware and amount of data. So ...
4
votes
1answer
350 views

Ran out of disk space, how to tar without creating copy

I am at 98% of an 8GB SSD, and I have a 3 GB mysql data that I need to gzip and or tar and then download it, so then I can delete it. How can I tar or gzip the sql (or the mysql tables) so that that ...
4
votes
2answers
2k views

SQL Clustering or Hyper V Clustering

We will soon be implementing our Dynamics AX installation. Our aim is to have high availability and we are working closely with our implementers. We raised the question to them about SQL and Hyper-V ...
4
votes
3answers
5k views

Importing a large SQL script into SQL Server?

I'm trying to move a database from our production server to our test server. I've used Microsoft's Database Publishing Wizard to create a script containing the schema and data for the production ...
4
votes
3answers
4k views

How does SQL Server handle simulataneous read/write requests?

Background: We have an application which uses an SQL Server 2008 database heavily. We are trying to optimize this application. It was single threaded, and we noticed through logging/ profiling of the ...
4
votes
1answer
292 views

Backup database at certain intervals?

My client would like to have the database backed up several times per day, as well as the ability to backup the database manually. I've created a page to backup the database manually, it just calls a ...
4
votes
4answers
8k views

Moving a table to a different schema in Oracle

How do I move a table from one schema to another? It's a fairly large table, so the following query would take ages to complete and need huge disk space: CREATE TABLE newschema.mytable AS SELECT * ...
4
votes
4answers
14k views

How to find the next generated value for a auto-increment column?

I face some trouble with IBM DB2's auto-increment columns. At first, all my columns were defined as GENERATED ALWAYS, but since I had trouble with this when using the "db2 import ..." command, I ...