Detach database in SQL Server - Database Discussion Boards

Richard MacCutchan1-Mar-22 22:45

1-Mar-22 22:45

You should not be trying to create data files inside C:\Program Files, it is a protected location. Please see Where Should I Store My Data?[^].

Re: [MySQL] Failed to set datadir

chipp_zanuff2-Mar-22 0:35

chipp_zanuff

2-Mar-22 0:35

i wasn't

have you read the link that i posted?

i run this command:

mysqld --init-file=C:\\mysql-init.txt

and the error occurred

Re: [MySQL] Failed to set datadir

Richard MacCutchan2-Mar-22 0:47

Richard MacCutchan

2-Mar-22 0:47

chipp_zanuff wrote:
have you read the link that i posted?

I have now. And Googling the message leads to Can't create test file lower test start server mysql - Stack Overflow[^].

Re: [MySQL] Failed to set datadir

chipp_zanuff2-Mar-22 23:37

chipp_zanuff

2-Mar-22 23:37

i've created that "data" folder, and run init command.. there was no problem...
but when testing connection, the problem still the same, i.e. below pic..
error connection[^]

know how to fix it?

Re: [MySQL] Failed to set datadir

Richard MacCutchan2-Mar-22 23:44

Richard MacCutchan

2-Mar-22 23:44

The message is self-explanatory: one or more of the IP address, port, userid or password are not valid. Check all your settings.

Re: [MySQL] Failed to set datadir

chipp_zanuff3-Mar-22 0:30

chipp_zanuff

3-Mar-22 0:30

one or more of the IP address, port, userid or password are not valid.

??

where did you see this message..? it's not in anything in the picture i shared...

the error msg is:
Failed connect to MySQL bla bla bla...

Re: [MySQL] Failed to set datadir

Richard MacCutchan3-Mar-22 0:46

Richard MacCutchan

3-Mar-22 0:46

The message lists all the values it is trying to use to connect. So it is reasonably safe to assume that one or more of them is not correct. That is the only information you have to work with, so you need to investigate further to discover which one it is.

Detach database in SQL Server

Meysam Toluie17-Feb-22 4:44

Meysam Toluie

17-Feb-22 4:44

Hello database experts,
In SQL Server is an option to update statistics before detaching a database, both using Transact-SQL and management studio wizard.
The purpose and advantage of updating statistics is clear.

My question is that, why should we update statistics before detaching a database? Is there any performance issue in recovery of database while attaching? or something else?

SignatureNotFoundException

Works in MYSQL 5.7, Syntax Error in MYSQL 8.0

Douglas Kirk 202216-Feb-22 2:36

Douglas Kirk 2022

16-Feb-22 2:36

Hello
My query to get a ranked leaderboard works on MYSQL 5.7 but gives me errors now that I upgraded to MYSQL 8.0

mysql> SELECT username,score,level, FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score ASC)FROM highscore WHERE game = 0 AND level = 4 AND user = 10 )) AS rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMIT 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

to use near 'rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMI' at line 1

Any ideas?
Thank you Team!

Re: Works in MYSQL 5.7, Syntax Error in MYSQL 8.0

jschell17-Feb-22 11:37

jschell

17-Feb-22 11:37

According to the following 'rank' was added as a keyword in MySQL 8

MySQL :: MySQL 8.0 Reference Manual :: 9.3 Keywords and Reserved Words[^]

Re: Works in MYSQL 5.7, Syntax Error in MYSQL 8.0

Douglas Kirk 202217-Feb-22 15:03

Douglas Kirk 2022

17-Feb-22 15:03

Thank you so much.
Simply changed "rank" to "ranklist" and it has resolved the issue.

Re: Works in MYSQL 5.7, Syntax Error in MYSQL 8.0

Richard Deeming17-Feb-22 21:35

Richard Deeming

17-Feb-22 21:35

Perhaps you should get together with your classmate / colleague:
I need to generate rank in mysql v5.7.31[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

SQL Server Activity Monitor

Meysam Toluie30-Jan-22 19:56

Meysam Toluie

30-Jan-22 19:56

I am familiar with SQL Server Activity Monitor. What all I know about it, have been learned by my own experinced. I decided to learn all details about it, and googled many keywords but most of results were a brief. I did not find a comprehensive article or document in detail.
Can anyone send me a well documented link?

For example the task states vary in different articles; Or what head blocker value is (the concept is clear but not the value; I thouth it is SPID but is not)?

SignatureNotFoundException

Re: SQL Server Activity Monitor

Meysam Toluie30-Jan-22 22:04

Meysam Toluie

30-Jan-22 22:04

After some extra research I found a complete list of state from different sources:

Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).
Background - The session is running a background task, such as deadlock detection.
Rollback - The session has a transaction rollback in the process.
Pending - The session is waiting for a worker thread to become available.
Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
Spinloop - The session's task is waiting for a spinlock to become free.
Suspended - The session is waiting for an event, such as I/O, to complete.
Sleeping - A session in the sleeping state means a client connection without an active query.
Dormant - SQL Server is resetting the session. Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. (Replication SPIDs show "DORMANT" when waiting.)

For more information about other columns see:

SQL Server Activity Monitor

I hope it helps others.

SignatureNotFoundException

Get all tables under a database in Postgre

_Flaviu12-Jan-22 23:57

_Flaviu

12-Jan-22 23:57

I need to get all database names, using SQL script only. I have tried:

SQL

SELECT datname FROM pg_database  WHERE datistemplate = false

and seem to work. I got:

SQL

mydb1
mydb2
postgres

mybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?

Re: Get all tables under a database in Postgre

Victor Nijegorodov13-Jan-22 6:05

Victor Nijegorodov

13-Jan-22 6:05

Could it help you: sql - List all tables in postgresql information_schema - Stack Overflow ?

Re: Get all tables under a database in Postgre

_Flaviu13-Jan-22 6:45

_Flaviu

13-Jan-22 6:45

Thank you Victor. Obviosly, I don't understand something: if I do:

SQL

create database mydb3

and then I want to create a table under that db, how can I do that ?

Because any (dummy) table I create, seem to be any no database inside ...

P.S. I saw (and tried) that link ... thanks anyway.

Re: Get all tables under a database in Postgre

Victor Nijegorodov13-Jan-22 6:57

Victor Nijegorodov

13-Jan-22 6:57

I never used Postgre...
In MS SQL Server you usually use something like:

SQL

create database mydb3
go
use mydb3
CREATE TABLE <new_table_name>

In postgresql you should use something similar... Example: postgresql - How to switch databases in psql? - Stack Overflow

Re: Get all tables under a database in Postgre

_Flaviu13-Jan-22 21:24

_Flaviu

13-Jan-22 21:24

Hello Victor. As far as I understood, "use ..." is not valid command in Postgre SQL ... I have tried that.

Re: Get all tables under a database in Postgre

Victor Nijegorodov13-Jan-22 21:28

Victor Nijegorodov

13-Jan-22 21:28

_Flaviu wrote:
As far as I understood, "use ..." is not valid command in Postgre SQL

I know.
But did you try the

SQL

\connect

Re: Get all tables under a database in Postgre

_Flaviu14-Jan-22 0:01

_Flaviu

14-Jan-22 0:01

That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that.

Thank you.

Re: Get all tables under a database in Postgre

CHill6014-Jan-22 1:16

CHill60

14-Jan-22 1:16

If you are not using psql then how are you running your SQL?

Re: Get all tables under a database in Postgre

_Flaviu14-Jan-22 1:47

_Flaviu

14-Jan-22 1:47

Is about a C++ ODBC class which can run SQL scripts only.

Re: Get all tables under a database in Postgre

Victor Nijegorodov14-Jan-22 2:05

Victor Nijegorodov

14-Jan-22 2:05

Could you try to set the desired database in the connection string (PostgreSQL ODBC Driver (psqlODBC) Connection Strings - ConnectionStrings.com)?

Re: Get all tables under a database in Postgre

Tomas@PawSQL30-Aug-22 1:15

Tomas@PawSQL

30-Aug-22 1:15

SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T   WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.