Questions tagged [sql]
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. [mysql], [postgres], [mssql], or [oracle].
1,288
questions
163
votes
10
answers
418k
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;
- 13.4k
147
votes
9
answers
246k
views
How to dump a Microsoft SQL Server database to a SQL script?
Is there any way to export a Microsoft SQL Server database to a SQL script?
I'm looking for something which behaves similarly to mysqldump, taking a database name, and producing a single script which ...
- 1,579
138
votes
10
answers
167k
views
Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?
Is there an equivalent of MySQL's 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 ...
- 1,483
130
votes
4
answers
360k
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 ...
- 1,403
36
votes
2
answers
15k
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 ...
- 973
33
votes
4
answers
64k
views
How to dump a part of a table from PostgreSQL?
I have a PostgreSQL database with a multi-Gb table (which contains a log of certain events). I need to pass the latest events to an analyst - let's say he only needs events from the last month.
How ...
- 561
33
votes
1
answer
47k
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 ...
- 2,182
31
votes
9
answers
20k
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 RAID&...
- 1,345
30
votes
5
answers
74k
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 ...
- 656
27
votes
14
answers
5k
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 we ...
27
votes
2
answers
31k
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...
Alexander
26
votes
26
answers
3k
views
Most popular misconceptions about networking [closed]
00000001 + 00000001 = 00000011 alt text http://locobox.googlepages.com/red_x_round.png
Misconceptions about Networking*
Time to fess-up!... 'at some point' you thought you knew something, and it ...
26
votes
3
answers
76k
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 ...
- 973
22
votes
6
answers
42k
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 ...
20
votes
8
answers
68k
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 ...
- 375
16
votes
1
answer
54k
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 ...
- 275
16
votes
3
answers
79k
views
Postgres INSERT ERROR: permission denied for schema public
In Postgres I created the following table inside a db called testing:
CREATE TABLE category_google_taxonomy (
category_id integer references category ON UPDATE CASCADE ON DELETE CASCADE,
...
- 1,305
16
votes
2
answers
80k
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 ...
Majd
16
votes
6
answers
44k
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 ...
- 3,686
14
votes
9
answers
54k
views
Where are credentials for SQL Management Studio saved?
When we logged into SQL Management Studio(using Server Name, Login and Password) with checked "Remember Password". I need to know, where it save in PC.
I need to format my PC. And when we install SQL ...
- 183
14
votes
4
answers
39k
views
What do I do when pg_cancel_backend doesn't work?
If I have a long-running Postgres query, and regular "kill [pid]" doesn't work, and pg_cancel_backend doesn't work, what should I do?
mike
14
votes
6
answers
25k
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 (http://...
- 1,343
13
votes
2
answers
2k
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 ...
- 3,935
12
votes
2
answers
40k
views
Alias a linked Server in SQL server management studio?
Hoping someone can help - is there a way in SQL server management studio 2008 R2 that I can alias a linked SQL server?
I have a server, added by IP address, to which I do not have the login ...
absentmindeduk
12
votes
5
answers
17k
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 (...
- 123
11
votes
2
answers
6k
views
disable maximum password length on Windows Server [closed]
When trying to create a password longer than 16 chars on my Windows 2012 server, it is refused due to the password being to long.
I have tried looking for a GPO called "Maximum Password Length" - ...
- 121
11
votes
3
answers
30k
views
mysqldump ignore table with wildcard
I need to take a dump of a database containing 50 odd tables of which I want to exclude about 15 odd that have prefix exam_
I tried mysqldump --ignore-table=dbname.exam_* and even tried --ignore-...
- 727
11
votes
3
answers
26k
views
Creating a sql dump of Microsoft Access tables
Is it possible to generate a sql script that contains CREATE and INSERT statements for some tables within an Access database?
- 253
10
votes
3
answers
14k
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 ...
Sleepless
10
votes
6
answers
15k
views
Cannot set SQL_MODE to Mysql 8
I have to set a persistent custom SQL MODE persistent to my server.
I added this line in /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
...
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,...
- 1,312
10
votes
2
answers
7k
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; ...
- 121
9
votes
2
answers
19k
views
How to count unique values?
I am trying get the number of unique ip_addresses (in this case '3'). The table looks like this:
Structure:
CREATE TABLE bandits (
key text NOT NULL,
ip_address inet,
offence text,
count ...
- 751
9
votes
1
answer
24k
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): ...
- 1,725
9
votes
5
answers
22k
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 ...
- 1,094
9
votes
2
answers
19k
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 ...
- 275
8
votes
7
answers
20k
views
DHCP server with database backend [closed]
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 ...
- 1,588
8
votes
2
answers
2k
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 ...
- 1,345
8
votes
5
answers
106k
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 ...
- 20.5k
8
votes
7
answers
4k
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
3
answers
4k
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 ...
- 305
8
votes
1
answer
3k
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 ...
- 257
8
votes
3
answers
19k
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, ...
- 189
8
votes
2
answers
22k
views
Converting Filemaker to SQL or another database system
Currently at work we have a Filemaker database running on Filemaker Server 7 on a Windows Small Business Server 2003. I would like to change from using Filemaker to a different database, one with a ...
- 123
7
votes
3
answers
3k
views
Is a MySQL database a viable alternative to LDAP?
(Apologies if I'm asking the wrong kind of question or in the wrong place.)
We run IT for our association (all volunteers). We have got a server with member database in OpenLDAP, mail server, ftp and ...
- 193
7
votes
8
answers
6k
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.
...
user274
7
votes
2
answers
35k
views
dbcc checkdb('XYZ', REPAIR_ALLOW_DATA_LOSS) indicating that XYZ is already open and can only have one user at a time
I have a DB, XYZ, that had a corrupt log file and now since the log file is totally unusable this is rendering this specific DB unusable and I need to be able to rebuild it.
I've already done a lot ...
7
votes
4
answers
34k
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 ...
- 352
6
votes
7
answers
35k
views
How to rollback a deleted table data?
I have deleted one table accidentally how can I rollback that.
Please suggest that.
- 183
6
votes
2
answers
20k
views
What is a typical Compression Ratio for an SQL Database backup file? [closed]
One of my clients want us to give a safe estimate of how much disk space does a compressed SQL backup file take. They need to size their server hard disks accordingly.
We can rouhgly estimate the ...
- 1,345
6
votes
6
answers
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 ...
- 1,288