All Questions
57 questions
0
votes
0
answers
67
views
Using SQLCMD Mode within SSMS and a SQL script file path, how can I store the result in a temp table?
Using SQLCMD Mode in SSMS (not the SQLCMD Utility) and a file path to a SQL query script, I would like to store the results into a temp table.
I am aware the following command executes the query from ...
- 1
1
vote
1
answer
783
views
Azure SQL authentication - Service Principal not working in SSMS/SqlCmd
We have a requirement to connect to Azure SQL Database using Service Principal Details. We are using SSMS(v19) and sqlcmd to connect.
What we done so far:
Create service principal and secret
Assign ...
- 21
0
votes
1
answer
324
views
SQL Server don't run in sqlcmd but run in ssms
I have a problem with a script running under sqlcmd vs SSMS.
The first time that I had the problem, I cut the large file (a lot of inserts) into small pieces, cutting it in blocks of 2000 inserts.
In ...
- 11
6
votes
4
answers
5k
views
How to react to message from Microsoft regarding updating API's
I got a message from Microsoft in the last few days
Azure SQL Database 2014-04-01 APIs will be retired on 31 October 2025.
You're receiving this email because you use Azure SQL Database APIs.
To ...
- 2,922
0
votes
0
answers
825
views
How to connect to a remote database with Windows authentication using sqlcmd?
I'm trying to use sqlcmd to execute querys on a remote database but I can´t login
When I connect using SSMS i have to use this runas command
runas /user:domain\username /netonly "Ssms.exe"
...
0
votes
0
answers
715
views
SSMS path error: file specified for :r command was not found
While navigating to the given path on cmd it was working fine but I am not sure why its not working here.
:setvar path "C:\Users\hamid\Documents"
IF NOT EXISTS (SELECT * FROM ...
- 49
0
votes
1
answer
192
views
What is wrong with MS SQL Server (*.sql) script backup restore operation with SQLCMD?
I have a 6GB *.sql script backup file, which when I'm loading into database:
sqlcmd -S .\SQLEXPRESS -U SA -d testdbb -i whole_DB_backup.sql -o result.log
It taking days to finish the script. On the ...
- 2,172
1
vote
1
answer
389
views
Run .txt file within ssms SQLCMD
I want to a .txt file that contains a sql script via ssms SQLCMD query window.
Could someone give me an example.
I can run via pc CMD successfully using the following.
sqlcmd.exe -S pcName -E -i "...
- 59
0
votes
2
answers
1k
views
Connect to multiple servers and databases and run the same SQL statements
I have a fixed list of SQL Server 2019 (v15) Express databases, distributed across the country. I am hoping to script a way of querying them in a T-SQL batch. Have gotten as far as enabling SQLCMD and ...
- 2,778
0
votes
1
answer
111
views
How to skip invalid servers in SQLCMD mode in SSMS
I am running sqlcmode mode in ssms agains multiple servers:
:CONNECT SERVER1
script
GO
:CONNECT SERVER2
script
GO
:CONNECT SERVER3
script
GO
etc, however when server2 is not accessible it stops there ...
- 3
0
votes
1
answer
199
views
SQL Query fails while running in SQLCMD Mode
I need help, I am writing the SQL code shown below for extracting data from multiple servers from the SSMS window of one SQL Server. I have CMC on SERVER1 and have registered SERVER2 as well and all ...
0
votes
0
answers
290
views
sqlcmd syntax issue
I am trying to run some sqlcmd to create new database on a new server but everytime I run my script I get the incorrect syntax near 'S' and do not know why exactly. the command line is to create a new ...
- 3
4
votes
0
answers
6k
views
Do sqlcmd and SSMS not validate the server certificate if the server enforces the encryption?
I am hosting a few linux SQL Server 2019 instances. All connections to these instances should be encrypted, and therefore I enforce the encryption on the server side like this (following the official ...
- 418
25
votes
11
answers
31k
views
Cannot connect with SSMS to SQL Server on Docker
I have followed the official Microsoft documentation and I have installed SQL Server Docker image
As result I have a SQL Server image running on Docker at the IP address 172.17.0.2
I also can ...
- 12.1k
1
vote
2
answers
2k
views
Backup SQL Server 2019 in a Docker Linux container fails
Attempted multiple ways to do the backup.
Tried adding SA as a root user in the container
Azure Data studio
BACKUP DATABASE [PrestigeCars] TO DISK = N'/var/opt/mssql/backup//PrestigeCars-202044-8-...
- 109
0
votes
0
answers
30
views
Trying to change columns in one table from varchar to int, and do a type lookup in another table in SQL CMD Mode on SSMS
The problem I'm having involves 3 tables. The first one has a primary key and some other stuff in it. We'll call it Stuff, with a StuffID as its primary key column. Let's call the second table Things, ...
- 253
4
votes
3
answers
24k
views
Can't find sqlcmd.exe (SQL SERVER 2017)
I've run into a problem about sqlcmd,
In the computer there is SSMS 2017 and I can use SQLCMD MODE in SSMS 2017, but I can't call sqlcmd in command prompt,
"'sqlcmd.exe' is not Recognized as an ...
- 67
1
vote
1
answer
656
views
TSQL syntax not the same for SSMS and sqlcmd
Here is a simple tsql file :
# file foo.sql :
DROP TABLE IF EXISTS "Testing";
CREATE TABLE "Testing"
(
"Id" BIGINT NOT NULL,
"FilePath" NVARCHAR (442) NULL,
CONSTRAINT "PK_Testing" ...
- 51.6k
0
votes
0
answers
263
views
SQLCMD: WHERE NOT LIKE
I'm creating a batch file to export data using SQLCMD, the command look like this:
sqlcmd -S servername -d db1 -o c:\Temp\export.txt -Q "SELECT xColumn, yColumn FROM xTable1 WHERE xColumn NOT LIKE '...
0
votes
1
answer
271
views
I want to write a sqlcmd script that pulls data from a database and then manipulates that data
I'm trying to find examples of sqlcmd script files that will run a select statement, and return those values internal to the script and place them in a variable. I then want to iterate over those ...
- 253
0
votes
1
answer
801
views
SQLCMD from SSMS is not working; connecting to Azure SQL Database
Running this from my SQL Server Management Studuio (SSMS) connecting to Azure sql database. I am using SQLCMD mode of SSMS.
:Connect -S taiobdemo1.database.windows.net -d taiobcustomer -U sqlusername ...
- 369
3
votes
0
answers
263
views
SQL Server different types of variables, $ and @
I am unsure of the difference between types of variables when using SSMS and SQL server.
:SETVAR A 7
DECLARE @B INT
SET @B = 11;
SELECT ($(A) * @B) AS PRODUCT
When should I be using SET and when ...
- 4,733
-1
votes
1
answer
546
views
sqlcmd for SQL Servers
Faced the problem of using sqlcmd in sqlserver, but I do not know for which server versions it fits, I could not find it. this console does not depend on the version of sql server?
So, if I want to ...
- 1
0
votes
1
answer
440
views
Run several scripts in sequence and output each one to a csv file
I have several queries that need to be run on a weekly basis in Microsoft SQL Server Management Studio, each one one is just a relatively simple select query, and the results need to be saved into csv ...
- 137
4
votes
1
answer
913
views
SQLCMD Mode in SSMS
I've started getting into the habit of switching to SQLCMD mode in SQL Server Management Studio when I'm deploying batches of SQL. I'm doing this so I can preface the SQL with
:on error exit
I've ...
- 12.8k
0
votes
1
answer
4k
views
A fatal scripting error occurred. Variable is not defined
When I run this query in SQLCMD mode in SSMS 2017 and SQL Server DB. It works fine.
--SQLCMD mode
--DECLARE Global variables
:SETVAR daysBack -1
SELECT $(daysBack)
Result: -1
If I run only 1 line
...
- 973
2
votes
3
answers
2k
views
SQL SERVER backup database into .sql file using script or cmd
I am trying to migrate from SQL SERVER 2012 to 2014 and found out I cannot use .bak files because of different versions. I know how to create a .sql back up file using the SSMS but I can't find a ...
- 561
4
votes
3
answers
5k
views
SET NOCOUNT OFF disregarded in SQLCMD
I want to see how many rows are affected for each DDL statement that is run by a query, so I set SET NOCOUNT OFF at the start of each query that is run.
Sample query:
SET NOCOUNT OFF;
GO
BEGIN TRY
...
- 41
0
votes
1
answer
109
views
sqlcmd SQL returning string value as #name? - value is displayed correctly in SSMS
I have a simple SQL query that is pulling the value from a varchar2 column. It returns the correct value in SSMS, however in sqlcmd it returns: "#NAME?" (without the double-quotes).
The actual value ...
- 61
0
votes
1
answer
188
views
Conditionally trigger :CONNECT statement SQL
Note 1: From what I have seen in my research so far, there is no way to accomplish what I'm looking to do, but I hope I'm wrong ...
Note 2: The goal of this is to eliminate the need to change ...
- 81
0
votes
0
answers
88
views
Does SSMS provide any default variables when running in SQLCMD mode?
I would like to run a parameterized script meant for SQLCMD that contains variable expansions mainly for a database name. It appears that others are using hard coded setvar: statements at the top of ...
- 17.3k
1
vote
2
answers
1k
views
Write query to export database to csv file via Microsoft SQL Server Management Studio
I am trying to write a query that will export the data from a table into a csv file. I cannot use the manual methods of right clicking the results and selecting save as. It has to be via query. I ...
- 666
1
vote
1
answer
413
views
Connect to multiple DB's from different servers
I have multiple DB's on different servers and I want to take some information from a table (for example User- this table is on all DB's). So I add all servers and DB's on a cursor and then I run this ...
- 11
0
votes
1
answer
96
views
Do scripts executed remotely via SQLCMD get processed by the host or target machine?
When running a command like the following:
SQLCMD -S MYREMOTECOMPUTER\DB -E -i CreateDBCompany.sql
Does the CPU of the host or target machine get used to execute the bits within CreateDbCompany.sql?...
- 12.9k
0
votes
1
answer
522
views
SQL script works fine when run from SSMS, but fails syntax check when run with sqlcmd
So I've got a script being used to update users on developer box SQL nodes. It works fine in SSMS, but when I run it from sql cmd, I get an "Incorrect syntax near..." error on this line:
EXEC('Drop ...
- 673
3
votes
1
answer
5k
views
SQLCMD MODE - Incorrect syntax near 'S'
In SSMS, when I execute
sqlcmd -S "flynetSrv"
I get :
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'S'.
I am already connected to the server through SSMS
I also tried
sqlcmd -d "...
- 159
1
vote
1
answer
1k
views
setvar - Incorrect syntax near 'dbname'
I've searched for the answer for this but all I can see is answers to "incorrect syntax near ':'" which is easily solved by turning on SQLCMD mode.
But once I've turned that on I get another error:
...
- 3,586
4
votes
1
answer
9k
views
SQLCMD Passing in Variables
I have a batch file which runs a number of *.sql files. Currently all the variables have been hard coded within the sql files. I wanted to know how to pass in variables values from the SQLCMD Batch ...
- 45
-1
votes
1
answer
5k
views
How can I use SQLCMD with Windows Authentication (-E,) but without elevated permissions
I'm going to create a script to execute numerous SQL files. I've chose to use SQLCMD instead of ADODB because it provides logging information quickly, and we're executing T-SQL. I'm testing out SQLCMD....
- 49
0
votes
2
answers
2k
views
SQLCMD to import large sql file gives "Invalid object name" error
I am trying to import a large .SQL file (downloaded here) (210mb) containing postcodes in a table called postcodelatlng. This is the setup:
I tried executing the query directly in SSMS 2012 but it's ...
- 18k
0
votes
0
answers
395
views
SQL variables and the IF statement
I am using T-SQL in SQLCMD mode in SSMS to run various subfile from one file, and the principle file (princ.sql) is where I need to declared and set a variable @MyInteger and run a subfile:
DECLARE @...
- 1
0
votes
1
answer
74
views
Executing all the lines before :CONNECT SQLCMD
My SQL query looks something like this and am executing it in SQLCMD mode in SSMS:
DECLARE @tempTable TABLE(acct nvarchar(50))
INSERT INTO @tempTable (Acct)
(select acct from tUsers)
:CONNECT ...
0
votes
1
answer
753
views
No Login for SQL Server but I am Local Admin
I am a local Admin on my machine. Same old thing, my account wasnt added when SQL Server was installed, and I don't want to have to reinstall SQL Server. I went through this blog and it worked until I ...
- 3,470
-2
votes
2
answers
2k
views
Multiple queries on same connection using same T-SQL variables
I'm trying to create something similar to SQL Server Management Studio.
I want the ability to run several queries on the same connection like one does in the same tab in SSMS, however from c# each ...
- 69
0
votes
2
answers
1k
views
How to display the error message when using ":on error exit" in SQLCMD mode?
I have a query with T-SQL statements in SQL Server 2012 with SQLCMD Mode turned On running interactively in SSMS. There's a :on error exit statement. When I run the query, the query window shows "...
- 44k
6
votes
1
answer
8k
views
The file specified for :r command was not found
In MS SQL Server Management Studio in SQLCMD Mode I use this script to run another script:
:setvar path "C:\workspace\scripts\scripts\"
:r $(path)'VERSIONS.sql'
But it gets this error:
A fatal ...
- 5,864
0
votes
0
answers
685
views
SQLCMD vs SSMS performance
I wrote a script, that takes long enough to run.
It looks like this:
set nocount on
select 1
GO
select 2
GO
select 1
GO
select 2
...
- 179
3
votes
2
answers
10k
views
SQLCMD mode: Environment variable syntax
I assume the best way to set my SSMS output file path during runtime of T-SQL, is to use SQLCMD mode.
How do I use environment variables (e.g. %systemroot%) in the code? The following bombs:
:set ...
- 1,012
1
vote
0
answers
1k
views
Permissions Different in SQLCMD and SSMS?
In both cases, I'm logged in using Windows Authentication. My account is a Domain account with the sysadmin role on the SQL Server (2005). I've even verified my sid is the same, by querying SUSER_SID()...
- 1,229
0
votes
1
answer
2k
views
How to run sqlcmd remotely on a machine which does not have SSMS
Is there a way in which we can run sqlcmd on a machine without having SSMS.
- 63