Run SQL script file from Dos command prompt

Note: The solution is at the bottom of the post, you can jump there directly if you don’t to read my silly story 😉

I have MS SQL Server 2008 R2 RTM – Express with Advanced Services. At work I got SQL script file from a client contains a table creating and record insertion scripts.

I tried to run SQL script file from SQL Management studio  but the file size was huge. So I got this error:

SQL2008RunScript

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot execute script.
——————————
ADDITIONAL INFORMATION:
Insufficient memory to continue the execution of the program. (mscorlib)
——————————
BUTTONS:
OK
——————————

Then I decided to run the SQL script file from DOS command prompt as SQL server is supporting it.

I used this command:

osql -S <SQL Server IP/Name> -d <DB Name> -E -i <sql script file path>

where:

-S put your SQL server IP or Name
-d type your database name
-E the means trusted connect .. when you use this you don’t need to use -U & -P

in my case the command was:

osql -S dbserver -d SMSPortal -E -i c:\tmp\GroupContact.sql

Unfortunately, when I run the command I got another error that is:

[ODBC Driver Manager] Data source name not found and no default driver specified

after searching and digging I found that there are two files with name “osql.exe”. One in this folder:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE

and the second is in this folder:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE

But how to know which one is used when I run the command. While I am able to run the command from any place in command prompt that means the folder of one of two above files is added the PATH system variable. To check that, from DOS I run:

c:\>path

and I got this:

PATH=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;

of course I got lengthy result but I truncated and removed unwanted stuff. Any way, as you can see both folders are added to the path. So I dont know which one is used when I run the command “osql …”. the best way is the navigate to the path of first folder and the command from there and navigate to the second folder and also run the file from there.

The Solution:

the file:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE

giving that same error:

[ODBC Driver Manager] Data source name not found and no default driver specified

While file:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE

Working fine and solved the problem.

So the command was like this:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>osql.exe -E -d SMSPortal -i c:\tmp\GroupContact.sql

with ruining this command I was able to run the script huge file successfully 🙂

Advertisements

12 Responses to Run SQL script file from Dos command prompt

  1. Nauman says:

    Thanks a lot buddy! Really struggling hard to find the solution.

    It really helped me a lot.

    Cheers

  2. Assaf says:

    Man!
    You are a super genius!
    You sure helped us alot and saved us huge time 🙂

    Thanks 🙂

    Assaf.

  3. After I originally left a comment I seem to have clicked the -Notify me when new comments are added-
    checkbox and from now on every time a comment is added I recieve four emails
    with the exact same comment. Perhaps there is a means you are able to
    remove me from that service? Appreciate it!

    • Sir .. I didn’t find a place in admin to see and remove subscribed people.

      But I think if you checked the notification email sent to you, you wil find a link at the bottom of the email to manage/unsubscribe from this notification.

      Mostly it will be similar to “Unsubscribe or change your email settings at Manage Subscriptions”

      Sorry for that ..

      Best Regards
      aMeeN

  4. Josefina says:

    Just what honestly motivated u to create “Run SQL script file from Dos command prompt | en aboFaisal”?
    I personallycertainly loved the post! Thanks ,Octavia

  5. In order to work 50 weeks a year, you’re going to have to
    work 30 weeks; in order to work 30 weeks, you’ll likely
    have to work 10. It is an industry norm that you offer a hotel room for the comedian at the same time.
    These surgeries are a matter of personal preference, peer
    pressure from fellow entertainers and the need to maintain an unrealistic image that is associated with them.

  6. Pingback: Execute Script Error Resolved In SQL Server Management Studio

  7. Emelia says:

    Seeing a visual of how your house might
    be modified to suit their vision of these dream home could be the impetus that moves individuals
    from lookers to buyers. But at the moment, an uncluttered look may
    well be more attractive to prospective buyers. In case, your answer is yes,
    it’s advocated to attempt visiting various online portals.

  8. This tree sprouts flowers along with the give an impression of these flowers attracts bats.
    After all of the planning, we ended up with an array of easy
    to implement, use and monitor activities to enrich all from
    the species (over 25. Ever question what cats are
    usually thinking, why guide keep schedules, or rest throughout the day
    long.

  9. I really like what you guys are up too. Such clever work and coverage!
    Keep up the terrific works guys I’ve incorporated you guys to blogroll.

  10. I couldn’t resist commenting. Very well written!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: