[The documentation](https://docs.dbatools.io/Invoke-DbaQuery.html) for Invoke-DbaQuery mentions many other similar functions > This function is a wrapper command around Invoke-DbaAsync, which in turn is based on Invoke-SqlCmd2. It was designed to be more convenient to use in a pipeline and to behave in a way consistent with the rest of our functions. There are of course other ways to query SQL from PowerShell, e.g. Invoke-Sqlcmd. What are the benefits of using Invoke-DbaQuery instead of Invoke-Sqlcmd, Invoke-Sqlcmd2, and Invoke-DbaAsync?

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE 'key01_en.html' By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default. Now, I want the file to be saved in my user let say /home/userone/dump_html folder If I say this (I launch the mysql command from the shell, and logged as "userone": SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/home/userone/dumphtml/key01_en.html' It makes an error that the process does not have enough rights to write there. But now still from the mysql> prompt, if I make: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/tmp/key01_en.html' ; -- and then system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html ; It works without any issue. It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why? (The discussion started on this point from [this answer](https://stackoverflow.com/a/23390257/628006) on SO)

I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation Install SQL Server 2016 from the Command Prompt regarding this issue. The problem is that there is no option written there to upgrade all server instances like there is in installing updates: Installing Updates from the Command Prompt using /allinstances. Is there any way to auto upgrade all instances on the server using one command line?

In windows mysql(5.7 version), I want to run mysql commmand using command line for .sql file as input . Also i was to pass some variable whose value should get used in .sql file. somehow I am not able to achieve it. mysql -u root -p xyz -e "set @tmp='abc'; source d:/v_test1.sql" Here I want to file v_test1.sql having mysql code and also variable @tmp being used in v_test1.sql. But getting error: > MySQL Error: Unknown column '@tmp' in 'field list' I think issue might be because of version. Please suggest some solution.

After asking you how to turn on the expanded mode in MySQL and how to turn on the expanded mode in Oracle SQL*Plus , I come to you once again and my question is **how to turn on the expanded mode in SQLite 3**?

In MySQL 8 server community to export a SQL query's output is possible execute in the MySQL Shell the following command (as most basic):

SELECT * FROM cientifico INTO OUTFILE '/var/lib/mysql-files/cientifico-data.txt';

And it works as expected But for a non SQL query as follows:

SHOW PROCESSLIST INTO OUTFILE '/var/lib/mysql-files/processlist.txt';

Throws the following error > 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 'INTO OUTFILE '/var/lib/mysql-files/processlist.txt'' at line 1 Same as:

SHOW DATABASES INTO OUTFILE '/var/lib/mysql-files/databases.txt';

Giving > 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 'INTO OUTFILE '/var/lib/mysql-files/databases.txt'' at line 1 Therefore if is possible **Question** * How to export the output for a non SQL query sentence? I am assuming that the INTO OUTFILE syntax is only for SQL sentences, but for non SQL sentences?

In MySQL 8 server community edition, here are examples of a _SQL query_ and _command_ in the MySQL Shell:

SELECT * FROM planet;
DESC planet;
SHOW DATABASES;
SHOW tables;

As you can see is **mandatory** to declare the ; character (or either \g or \G according the case) at the end of the sentence. But I did realize by mistake that it is possible to execute the use command to change the database **without** ;. Therefore, the two following commands work in peace:

use 
use ;

Just being curious: **Question** * Why doesn't the use command require ; to be executed? To be honest I am not sure if it is the unique command with this situation but why it is different against others?

In MySQL 8 server community the two following commands show the same structure:

SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;

**Question** * When use the SHOW PROCESSLIST vs SELECT * FROM information_schema.PROCESSLIST command? Each command exists for a specific reason, right? About their outputs I only found two differences for the State and Info columns for each command respectively:

State     | Info
----------------------------
init      | SHOW PROCESSLIST
executing | SELECT * FROM information_schema.PROCESSLIST

The difference of the Info column is clear because it represents the command itself that was executed **but**: * Why is different the State column? Is clear the executing value but: * What does the init value mean?

I've been using the "mysql" command from the Unix console for a while now. It's more convenient for scheduling tasks or launching them unattended. The outline of a .sh script for this is: #!/bin/bash #DB Config USR_BD=myUsr PWD_BD=myPass IP_BD=x.x.x.x f_exec () { echo date ' -> Executing sql file ' $1 mysql -h $IP_BD -u $USR_BD -p$PWD_BD my_schema Executed sql file ' $1 } f_exec my_sentences.sql The thing is, I've passed a security check, and they tell me that I can't save the clear password in a variable, or in plain text file, inside a .sh script My question is: how can I solve this problem? - If you can save something like a token, the token presents the same problem as the password. - If you give permission to connect without a password to the host from which I run the script, if you gain access to that host, you also, consequently, gain access to the database. Of course, it is obvious that I am not going to connect at 3am to enter the password to run the script. Or waiting online for a 10 minutes long heavy query. I don't know how to solve the situation. Thanks.

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux (Ubuntu) based solution. I have seen a online version but nothing as a installable. Eclipse Based IDE's are a plus as well Example: select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc to something like SELECT f1, f2, fname, lName FROM tblName WHERE f1 = true AND fname IS NOT NULL ORDER BY lName ASC Here is a online example: - http://www.dpriver.com/pp/sqlformat.htm But I would rather this be in a local environment Related: - https://stackoverflow.com/questions/3310188/free-sql-formatting-tool UPDATE: Looking at this: - https://github.com/darold/pgFormatter FINAL UPDATE: While this might be an overkill JetBrains has a database IDE, DataGrip , which has some great re-formatting options.

I am working on SQL server task. I need a sqlcmd result file with complete records with headers. Here is the command and error message below sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -W -y0 -Q "select test_desc from Test0 " 1>>Result.txt **Error:** Sqlcmd: The -W and the -y/-Y options are mutually exclusive. Has anyone had this problem before? Thoughts on how to solve it? **Actual Data:** test_desc dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx  dcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217hbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscde2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns s fqwhvchedcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i91= **-W command:** **-W** command is not useful for large data. the columns are getting truncated at 256 bytes. sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -W -Q "select test_desc from Test0 " >> Result.txt Result.txt: test_desc --------- dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx ÿdcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv234821`13142414v42vhv4jh1b2j1bjhb412489ywq8e8772 **-y0 command:** -y0 command returns complete records of a column, but column header is missing. sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -y0 -Q "select test_desc from Test0 " >> Result.txt Result.txt: dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx ÿdcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217hbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscde2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns s fqwhvchedcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i91= (1 rows affected)

Trying to figure out why mysql's ORDER BY clause can return order differently, e.g.: The two files are both generated by using mysql on command line with a single command: mysql mydb -e 'SELECT DISTINCT name FROM my_table ORDER BY name;' > name.lst but on two different Linux systems, querying the same DB with apparently same mysql/MariaDB:

# Machine 1
$ mysql -V
mysql  Ver 15.1 Distrib 10.5.25-MariaDB, for Linux (x86_64) using  EditLine wrapper


# Machine 2
$ mysql -V
mysql  Ver 15.1 Distrib 10.5.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Where should I look next for what's causing the problem please? (Like my LANG? would it matter?) **UPDATE:** Comparing character set / collation variables completely as suggested by Akina revealed where the problem is. Here is the comparison between two systems https://www.diffchecker.com/DhsN55Wq/ Comparing with the two and consulting with https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql , I'm thinking that settings on the right make more sense; what'd you say? How to correct it please? thx.

I had a local table with arabic values.when exported i got the following query. DROP TABLE IF EXISTS type; CREATE TABLE IF NOT EXISTS type ( bs_id int(11) NOT NULL AUTO_INCREMENT, types varchar(250) NOT NULL, PRIMARY KEY (bs_id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- -- Data for table business_type -- INSERT INTO type (bs_id, types) VALUES ('1', 'صناعة'), ('2', 'معارض'), ('3', 'أسواق'), ('4', 'إستيراد وتوزيع'), ('5', 'خدمات'), ('6', 'إستشارات'), ('7', 'أخرى'); I want to create this table on server so i just copied the above query and run it on server terminal.The query works fine but the values in table is showing like ".....". tried this SET NAMES utf8; before inserting the values,but failed.I don't have a cpanel installed on server.Command prompt is the only option. Can anyone help!! Thanks

There is a manual for configuring PostgreSQL on Windows with these commands:

C:\"Program Files"\PostgreSQL\12\bin\createuser.exe --username=postgres --superuser --pwprompt saleor

C:\"Program Files"\PostgreSQL\12\bin\createdb.exe --username=postgres --owner=saleor saleor

I'm going to do the same on Linux, but I'm not sure how. I know that I have to do:

> sudo su postgres
> psql 

psql (13.3)
Type "help" for help.

postgres=#

But I'm not sure what to do next. I appreciate any help.

I'm attempting to upgrade a Postgres instance from version 12 to version 13, following the steps outlined at https://www.postgresql.org/docs/13/pgupgrade.html . From an elevated command prompt, I'm running (under Windows 10): > SET PATH=%PATH%;C:\Program Files\PostgreSQL\13\bin; > > pg_upgrade.exe --old-datadir "E:\Programs\PostgreSQL\12\data" > --new-datadir "E:\Programs\PostgreSQL\13\data" --old-bindir "C:\Program Files\PostgreSQL\12\bin" --new-bindir "C:\Program > Files\PostgreSQL\13\bin" --old-port=5431 --new-port=5432 --jobs=5 The directories appear to be all correct, however the command returns: >

> Performing Consistency Checks  
> -----------------------------  
> Checking cluster versions                                   ok  
>   
> *failure*  
> Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for  
> the probable cause of the failure.  
>   
> connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)  
>         Is the server running on host "localhost" (::1) and accepting  
>         TCP/IP connections on port 5431?  
> could not connect to server: Connection refused (0x0000274D/10061)  
>         Is the server running on host "localhost" (127.0.0.1) and accepting  
>         TCP/IP connections on port 5431?  
>   
> could not connect to source postmaster started with the command:  
> "C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start  
> Failure, exiting  
>

pg_upgrade_server_start.log says: > command: "C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start >> "pg_upgrade_server_start.log" 2>&1 > The program "postgres" was found by "C:/Program Files/PostgreSQL/12/bin/pg_ctl" > but was not the same version as pg_ctl. > Check your installation. I'm at a loss; what might be wrong, and how can I successfully perform this procedure?

I started using [mycli](https://github.com/dbcli/mycli) and want $HOME/.mycli-history to be generated and updated from $HOME/.cache/mycli/.mycli-history 1. Moved $HOME/.myclirc -> ~/.config/mycli/.myclirc 1. Created alias mycli='sudo mycli --myclirc ~/.config/mycli/.myclirc' 1. Updated log_file = ~/.cache/mycli/.mycli.log in .myclirc 1. Moved $HOME/.mycli.log to ~/.cache/mycli/.mycli.log These steps were successful for the rc and log files. I did not get much from searching online or their Documentation, but saw [this answer](https://github.com/dbcli/mycli/issues/647#issue-357617425) and added export MYCLI_HISTFILE="~/.cache/mycli/.mycli-history" to my .zshrc. After restarting my shell, my history file is still being populated in $HOME. Is there any command line flag, environment variable, or configuration that can change this? If not, would my only option be to symlink it? Using mycli v1.27.0 in zsh on MacOS Sonoma 14.2.1

I'm creating an installer for my program and I'd like to check if the database already exists in the computer. I do this by calling psql -lqt | findstr dbname. If someone knows a better way I`ll be glad to learn it. Well, now I know if the database exists, but I would also like to know where its data directory is. Is there a programmatic way to find this out?

1) The pgpass.conf (of PostgreSQL) is: localhost:5432:*:postgres:mypassword I have no experience with batch and I have spent the day searching in the net. I'm using Windows - if that matters. 2) I tried with a file.bat as: cd C: start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\1Giswater impNode.sql" pause start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\2Giswater impArc.sql" pause start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\3assegnazione enet.sql" pause After typing my password the result is: 3) I've written a simple file.bat, which is: cd C:\Program Files\PostgreSQL\9.4\bin\ pause rem psql --help pause psql -e -a -f="C:\Users\utente\Desktop\1Giswater impNode.sql" -d=postgres -U=postgres The result is: What am I missing?

I've been fiddling with these for 3 hours now and I can't figure out why my PostgreSQL server on a Debian server accepts connections from my Ubuntu server but not from my Windows 10 or 11 machines. When I run psql -h dbserver -U dbmanage postgres the log indicates: **/var/log/postgresql/postgresql-15-main.log**

2024-01-01 09:02:00.358 UTC  dbmanage@postgres FATAL:  no pg_hba.conf entry for host "2001:5a8:4453:7900:b851:cdf3:6995:1d7e", user "dbmanage", database "postgres", SSL encryption
2024-01-01 09:02:00.358 UTC  dbmanage@postgres DETAIL:  Could not resolve client IP address to a host name: Name or service not known.
2024-01-01 09:02:00.382 UTC  dbmanage@postgres FATAL:  no pg_hba.conf entry for host "2001:5a8:4453:7900:b851:cdf3:6995:1d7e", user "dbmanage", database "postgres", no encryption
2024-01-01 09:02:00.382 UTC  dbmanage@postgres DETAIL:  Could not resolve client IP address to a host name: Name or service not known.

The PostgreSQL config is set properly. I created the user with PASSWORD (MD5) **pg_hba.conf**

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# Configurations added after installation
#host   all             dbmanage         192.168.4.0/24          scram-sha-256
host    all             dbmanage         192.168.4.0/24          md5
host    all             all             localhost               scram-sha-256

This is really weird. The whole point was to use pgAdmin on my Windows machine, and it is refusing to connect, so I check to see if psql will connect, and discover that there is a problem with Windoze connecting to my postgres? **Update** Found a solution here

When I log in with psql --username=postgres, how do I list all databases and tables? I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.