Connect to Synapse SQL with sqlcmd

You can use the sqlcmd command-line utility to connect to and query serverless SQL pool and dedicated SQL pool within Synapse SQL.

1. Verbinding maken

To get started with sqlcmd, open the command prompt and enter sqlcmd followed by the connection string for your Synapse SQL database. Voor de verbindingsreeks zijn de volgende parameters vereist:

• Server (-S): Server in the form <Server Name>-ondemand.sql.azuresynapse.net(Serverless SQL pool) or <Server Name>.sql.azuresynapse.net(Dedicated SQL pool)
• Database (-d): Database name
• Enable Quoted Identifiers (-I): Quoted identifiers must be enabled to connect to a Synapse SQL instance

To use SQL Server Authentication, you need to add the username and password parameters:

• Gebruiker (-U): Servergebruiker in de vorm <Gebruiker>
• Password (-P): Password associated with the user

Your connection string might look like the following example:

Serverloze SQL-pool

C:\>sqlcmd -S partyeunrt-ondemand.sql.azuresynapse.net -d demo -U Enter_Your_Username_Here -P Enter_Your_Password_Here -I

Toegewezen SQL-pool

C:\>sqlcmd -S MySqlDw.sql.azuresynapse.net -d Adventure_Works -U myuser -P myP@ssword -I

Als u geïntegreerde Microsoft Entra-verificatie wilt gebruiken, moet u de Microsoft Entra-parameters toevoegen:

• Microsoft Entra-verificatie (-G): Microsoft Entra-id gebruiken voor verificatie

Your connection string might look like on of the following examples:

Serverloze SQL-pool

C:\>sqlcmd -S partyeunrt-ondemand.sql.azuresynapse.net -d demo -G -I

Toegewezen SQL-pool

C:\>sqlcmd -S MySqlDw.sql.azuresynapse.net -d Adventure_Works -G -I

2. Zoekopdracht

Use dedicated SQL pool

After connection, you can issue any supported Transact-SQL (T-SQL) statements against the instance. In this example, queries are submitted in interactive mode:

C:\>sqlcmd -S MySqlDw.sql.azuresynapse.net -d Adventure_Works -U myuser -P myP@ssword -I
1> SELECT name FROM sys.tables;
2> GO
3> QUIT

For dedicated SQL pool, the following examples show you how to run queries in batch mode using the -Q option or piping your SQL to sqlcmd:

sqlcmd -S MySqlDw.sql.azuresynapse.net -d Adventure_Works -U myuser -P myP@ssword -I -Q "SELECT name FROM sys.tables;"

"SELECT name FROM sys.tables;" | sqlcmd -S MySqlDw.sql.azuresynapse.net -d Adventure_Works -U myuser -P myP@ssword -I > .\tables.out

Use serverless SQL pool

After connecting, you can issue any supported Transact-SQL (T-SQL) statements against the instance. In the following example, queries are submitted in interactive mode:

C:\>sqlcmd -S partyeunrt-ondemand.sql.azuresynapse.net -d demo -U Enter_Your_Username_Here -P Enter_Your_Password_Here -I
1> SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')
2> GO
3> QUIT

For serverless SQL pool, the examples that follow show you how to run queries in batch mode using the -Q option or piping your SQL to sqlcmd:

sqlcmd -S partyeunrt-ondemand.sql.azuresynapse.net -d demo -U Enter_Your_Username_Here -P 'Enter_Your_Password_Here' -I -Q "SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')"

"SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')" | sqlcmd -S partyeunrt-ondemand.sql.azuresynapse.net -d demo -U Enter_Your_Username_Here -P 'Enter_Your_Password_Here' -I > ./tables.out

Volgende stappen

For more information about sqlcmd options, see the sqlcmd documentation.