I need to excute a .SQL file from CMD line but i got this:

Ramon Quintana

7y8.8k

C:\BPC\Procesos_Almacenados>echo off

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'AD005\z003aedu'..

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "1RQ_BPCSQL1" requested by the login. The login failed..

How can i fix this?

Thanks..

16 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi,
You can refer this link to execute sql script from command prompt and save out put as text file.

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-ver15
Laxmidhar Sahoo6y ago
1- open command prompt by typing cmd from run

2- syntax
sqlcmd -S –U -P -i

3- example
sqlcmd -S . –U sa -P serverpassword -i D:\SQLQuery2.sql
Bhavin Patel6y ago
First i would suggest that do not Run sql file because it might be not secure outside in your drive. Instead of this just create a stored procedure and simple rum using CMD or .bat file whatever you want.

i would like to help you for Execute SQL stored procedure using .bat file or SQL Cmd. follow this steps.

1-Go to CMD.

write following lines.

C:\>sqlcmd -S (your database server IP),(Database Port) -U (username) -P (password) -d (databaseName) -Q "SELECT * FROM [TestDB].[dbo].[Demodata];"

Generally database default port is 1433. you can check your code in sql server configuration manager-property-IP section.

C:\>sqlcmd -S 192.168.18.188,1433 -U sa -P zxy@123 -d TestDB -Q "SELECT * FROM [TestDB].[dbo].[Demodata];"

Instead of "SELECT * FROM [TestDB].[dbo].[Demodata];" you can write your stored procedure execution line too like

C:\>sqlcmd -S 192.168.18.188,1433 -U sa -P zxy@123 -d TestDB -Q "exec [dbo].[executebat];"

This will execute your procedure.

Also, you can Execute SQL stored procedure using Linkserver.

Add linkserver name before your database name.

"SELECT * FROM [LNKSRV].[TestDB].[dbo].[Demodata];"

The same line you can write in .bat file and execute.

.bat file code will be like this

sqlcmd -S 192.168.18.188,1433 -U sa -P zxy@123 -d TestDB -Q "INSERT INTO [LNKSRV].[TestCRM].[dbo].[TempPAN]([User_ID],[PanNo])VALUES(10100,'AAAAAAA')"

This code is tested and performed successfully on my server and client both PC.

Thanks a lot.
Nitin Sontakke7y ago
First, unfortunately, unless I try it personally, may be I will be reluctant to make any further comments.

Second, the 'administrator' aspect has nothing to do with this. So please keep that aside.
Ramon Quintana7y ago
Mr. Nitin:

C:\WINDOWS\system32>sqlcmd.exe -S "." -E -d 1RQ_BPCSQL1 -Q "C:\BPC\Procesos_Almacenados\SQLQuery10.sql¨
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'AD005\z003aedu'..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "1RQ_BPCSQL1" requested by the login. The login failed..
C:\WINDOWS\system32>

:/
Ramon Quintana7y ago
Hi good morning Nitin,

What i mean is that the user logged in sql server is Administrator..in other words i run sql as administrator just like Command Promt CMD to run the .BAT file

imagen sql:

but i logging in windows with "Z0042hjv" and it don't have Administrator perfil.
Nitin Sontakke7y ago
Hello,

The -E parameter should be as demonstrated below:
sqlcmd.exe -S "." -E -d 1RQ_BPCSQL1 -Q "C:\BPC\Procesos_Almacenados\SQLQuery10.sql¨
However, having said that, for some reason, the sqlcmd worked on my machine without any parameters and connected to sql server on the box. Surprised! Anyway.
For some reason I am completely confused by your statement: "The user that i run sqlcmd is the same who logging in sql server (AD005\z003aedu), but not the same who log in Windows (z0042hjv)". What does this means?

Just to clarify, the only user who can use the windows authentication on the box is the one who is logged on to that box. There is NO way that I KNOW OF which would allow you to supply user id and password of another domain user on a command prompt. It doesn't work that way. That defeats the whole purpose of Windows Authentication in the first place. I am sure you understand this already.
Ramon Quintana7y ago
Hi Nitin,

Where in the comand do i have to put the -E parameter ??
Jignesh Kumar7y ago
Hi
Please find below link,

I have created my bat file unig below script and its running fine. Please change all required details as per your database and server name
@ECHO OFF

SET SQLCMD="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
SET PATH=D:\Scripts\
SET SERVER="ServerName"
SET DB="DatabaseName"
SET OUTPUT="D\Log.txt"

CD %PATH%

ECHO %date% %time% > %OUTPUT%



FOR /R "%PATH%" %%G IN (*.sql) DO (
%SQLCMD% -S %SERVER% -d %DB% -i "%%G"  >> %OUTPUT%
ECHO Executing: "%%G"
IF %ERRORLEVEL% == 1 (
ECHO THERE WAS AN ERROR - to file >> %OUTPUT%
ECHO THERE WAS AN ERROR - on screen)
)
Note : Change below path as per your system,
SET SQLCMD="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" //This your sql exe file
SET PATH=D:\Scripts\ // This your script folder path
SET SERVER="ServerName" // Server name
SET DB="DatabaseName" // databasename
SET OUTPUT="D\Log.txt" // Log file folder path
Nitin Sontakke7y ago
I believe there is a missing -E parameter in your command. Can you please try adding that.
Ramon Quintana7y ago
Hi NITIN,

Here the complete sentence from .bat file:

********************************
echo off
sqlcmd.exe -S "." -d 1RQ_BPCSQL1 -Q "C:\BPC\Procesos_Almacenados\SQLQuery10.sql¨
set /p delExit=Presione una tecla para salir...:

********************************

The user that i run sqlcmd is the same who logging in sql server (AD005\z003aedu), but not the same who log in Windows (z0042hjv), so i changed in sql server to allows logging in both, sql server and Windows NT, but once a run sql2.bat as administrator (AD005\z003aedu) in CMD line i got the error, i don´t know why..

error:

C:\BPC\Procesos_Almacenados>sql2.bat
C:\BPC\Procesos_Almacenados>echo off
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'AD005\z003aedu'..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "1RQ_BPCSQL1" requested by the login. The login failed..

Thank for your support ;)
Nitin Sontakke7y ago
If possible, please share details from the .bat file showing how exactly you are connecting to SQL Server. Please be generally aware that the parameters are case sensitive.

As you are using windows authentication, there is no password involved, as such, I believe it is quite safe to share the details of the exact command issued to connect.
Ramon Quintana7y ago
Hi Amit, The user that i run sqlcmd is the same who logging in sql server (AD005\z003aedu), but not the same who log in Windows (z0042hjv), so i changed in sql server to allows logging in both, sql server and Windows NT, but once a run sql2.bat as administrator (AD005\z003aedu) in CMD line i got the error, i don´t know why..

error:

C:\BPC\Procesos_Almacenados>sql2.bat

C:\BPC\Procesos_Almacenados>echo off

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'AD005\z003aedu'..

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "1RQ_BPCSQL1" requested by the login. The login failed..
Amit Gupta7y ago
If this part is okay then you can check with the user id and password.

Also be sure that you are able to login in SSMS with the same user and password that you used in SqlCMD. The rest part seems good.
Ramon Quintana7y ago
I got only that message, and the service instance its running by the way.

what more do i have to check in both sql server and windows?? it´s seem to be user´s perfil..i think
Amit Gupta7y ago
Hey

Can you share full error details. According to your issue, make sure the service of Sql Server instance is runnng.