Use Invoke-Sqlcmd to run SQL script to create table in Synapse Workspace SQL Pool-CodePudding

I am running into issues where I am trying to run an Invoke-Sqlcmd command to run create table SQL queries. I used different ServerInstances names such as the Synapse workspace name, the Dedicated SQL, Serverless SQL and Development endpoint. I have been testing these scripts in a PowerShell CLI on the Azure Portal before moving them into an ADO pipeline.

The command that is run is as below:

Invoke-sqlcmd -Query 'CREATE TABLE #DUMMYTABLE(USERNAME varchar(50));'
-SeverInstance {endpoints or Synapse Workspace resource name} `
-Database {dedicated SQL Pool name (not master db)}
-Username {Synapse Workspace username}
-Password {Synapse Workspace password}
-QueryTimeout 36000
-Verbose

I got an error code 25 or 35 or even just a general SQL Connection String error. Any support on this would be greatly appreciated. Using the Dedicated endpoint would result in a 'cannot log in username error'.

Cheers

CodePudding user response：

Getting Error because of wrong credentials.

You can get connection string from here:

from this connection string use Servername as ServerInstance and Initial Catalog as Database

Invoke-sqlcmd -Query 'CREATE TABLE DUMMY (USERNAME varchar(50));' -ServerInstance <synapseworkspacename>.sql.azuresynapse.net -Database <Inital catlog name from connection string or dedicated sql pool name> -Username <username> -Password <password> -QueryTimeout 36000 -Verbose

Output

DUMMY Database created successfully.