How do you execute large SQL Server script file?

Question

I am trying to execute a large SQL Server script file from the command prompt as its not loading in Management Studio. I am using this command

sqlcmd -S .\SQLEXPRESS -U ttandel -P '' 
       -d [Zen.Databases.Suite] 
       -i D:\NewMachine\COM.B_Address.Table.sql

Note: password is blank. I have tried all the options for keeping password as blank such as ("",'', ) but nothing is working.

Can anyone please suggest how to do this?

From comment:

I tried with this

sqlcmd -S .\SQLEXPRESS -E 
       -d [Zen.Databases.Suite] 
       -i D:\NewMachine\COM.B_Address.Table.sql

This is throwing an error

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'NAPG\ttandel'..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "[Zen.Databases.Suite]" requested by the login. The login failed..

My Server Security information as below.

I mean i have tried with password " ",' ' but its not working. — tt0206, Dec 01 '16 at 19:13
Can you try to use *integrated security* by using `-E` and dropping the `-U / -P` switches? — marc_s, Dec 01 '16 at 19:13
tried with this 'sqlcmd -S .\SQLEXPRESS -E -d [Zen.Databases.Suite] -i D:\NewMachine\COM.B_Address.Table.sql' its throwing error 'Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'NAPG\ttandel'.. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "[Zen.Databases.Suite]" requested by the login. The login failed..' — tt0206, Dec 01 '16 at 19:17
An error message, now we are getting somewhere. In the future please update your question with this sort of information instead of including it in the comments. That way the relevant parts can be found in 1 place, I did it for you this time. Also there is no way anyone could have guessed what your error was or even if there was an error, `nothing is working` is not descriptive and we are not mind readers or omnipotent and can not "just know" what you are looking at on your PC screen. — Igor, Dec 01 '16 at 19:21
You need to troubleshoot your connection information, see this guide to help you do that. https://blogs.msdn.microsoft.com/chaitanya_medikonduri/2011/03/09/sql-server-connectivity-issuestroubleshooting-tips/ — Igor, Dec 01 '16 at 19:28
Possible duplicate of [Unable to login to SQL Server + SQL Server Authentication + Error: 18456](http://stackoverflow.com/questions/2474839/unable-to-login-to-sql-server-sql-server-authentication-error-18456) — Igor, Dec 01 '16 at 19:30

Krishneil · Answer 1 · 2016-12-01T23:19:48.067

1

The following SQL command will do the work.I have used it multiple times for installing 100 million to 200 million records in DB.

sqlcmd -S Krishneil-PC -E -i C:\Users\Krishneil\Desktop\Script.sql

change Krishneil to your suitability.

edited Dec 01 '16 at 23:19

answered Dec 01 '16 at 23:13

Krishneil

1,432
1
18
26

@Krishneli i have tried as you have suggested but still its throwing Error. **C:\Windows\system32>sqlcmd -i C:\Users\TTandel\Desktop\try.sql -S .\SQLEXPRESS -d [Zen.Databases.Suite] -U NAPG\ttandel -P "" Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'NAPG\ttandel'..** – tt0206 Dec 02 '16 at 13:58
@krishnell also tried with **sqlcmd -S .\SQLEXPRESS -PC -E -i C:\Users\TTandel\Desktop\try.sql** but getting error as **Sqlcmd: The -E and the -U/-P options are mutually exclusive.** – tt0206 Dec 02 '16 at 14:58

score 0 · Answer 2 · answered Dec 02 '16 at 14:03

0

Change -d [Zen.Databases.Suite] to -d "Zen.Databases.Suite"

answered Dec 02 '16 at 14:03

Gareth Lyons

1,942
12
14

I have already tried that options but same result **Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'NAPG\ttandel'..** – tt0206 Dec 02 '16 at 14:59
Have you checked you have suitable permissions to that database? – Gareth Lyons Dec 02 '16 at 16:10
You do need to replace the [] in db name with "" regardless, or remove them outright. Sqlcmd is interpreting them as part of the name. – Gareth Lyons Dec 02 '16 at 16:13
i don't see that's an issue, because i have tried other options with removing [], providing "". but still the same issue. and yes i have access to the database. i am able to login from the Sql server management studio. – tt0206 Dec 02 '16 at 18:09
You still need permissions on the database itself (or membership of a suitable server role), permission to login alone may not be enough by itself. Can you show or tell us any server roles that are ticked for your user, and the user mapping tab for the login? Thanks – Gareth Lyons Dec 02 '16 at 18:14

How do you execute large SQL Server script file?

2 Answers2