Cool SQL Server and SQL Express Command-Line Utility

Brad on my team recently pointed me at a cool command-line SQL/SQL Express utility that might be useful for people to keep in the tool chest.  It is free and you can download it here.

 

It includes a useful doc-file that walks-through the various commands it provides.  I also found this link useful in walking through how to use common SQL commands for creating new databases.

 

Here is a simple set of steps that demonstrate how to use it to create a SQL Express database file, attach to it, create a database in it, then add a table, then add values into the table, then do a select query against it (note: all commands I typed are in bold below):

 

C:\SQLUtility>sseutil -c

 

Console mode. Type 'help' for more information.

 

1> !create c:\sqlutility\testing123.mdf

Command completed successfully.

 

1> !attach c:\sqlutility\testing123.mdf

Command completed successfully.

 

1> use "c:\sqlutility\testing123.mdf"

2> go

 

Command completed successfully.

 

1> create database people

2> go

 

Command completed successfully.

 

1> use people

2> go

 

Command completed successfully.

 

1> create table names

2> (id INTEGER NOT NULL,

3> name VARCHAR(50) NOT NULL)

4> go

 

Command completed successfully.

 

1> insert into names values(1, 'bill gates')

2> go

 

1 row(s) affected.

 

1> select * from names

2> go

 

id   name

------------------

1    bill gates

 

1 row(s) affected.

 

1> quit

 

Of course, an easier way to-do this from scratch would be to use Visual Web Developer or VS 2005 and select Add New Item->Database file, name it, and then use the server explorer to create and add the table.  But the nice thing about the above approach is that you can use it execute a SQL script file to quickly create/re-create your database without needing a tool on your system (or a user who knows how to use it).

 

Hope this helps,

 

Scott

 

P.S. One other cool thing about this utility is that it allows you to list all databases you have installed on your system.  Just type sseutil –l to list all of the databases and .mdf files being used.  This is very helpful if you have multiple database instances on your system (for example: I have some SQL 2000 databases and some SQL 2005 Express ones).

 

Published Thursday, December 15, 2005 7:44 PM by ScottGu

Comments

# re: Cool SQL Server and SQL Express Command-Line Utility

Friday, December 16, 2005 2:41 AM by Parag Kantharia
Thanx,

Nice utility. By the way is this the same utility, you were talking about, which was under development for the hosting providers.

In your blog, you had mentioned that there is a tool under development for hosting companies, which will allow them to convert SQL EXPRESS database to a full SQL SERVER.

Pl. clarify this.

Thanks for this effort by your team.

# re: Cool SQL Server and SQL Express Command-Line Utility

Friday, December 16, 2005 10:53 AM by John Mark Howell
Great utility! Thanks for sharing this!
JMH.

# re: Cool SQL Server and SQL Express Command-Line Utility

Friday, December 16, 2005 12:09 PM by Firoz Ansari
Cool Utility!!!
Thanks,
Firoz Ansari

# re: Cool SQL Server and SQL Express Command-Line Utility

Friday, December 16, 2005 5:42 PM by cowgaR
I use this utility JUST for purpose of starting SQL USER INSTANCE under RESTRICTED USER account(I would be happy if text stated clearly then it is starting user instances when user invokes this and this commands automatically thought, like -list), as I didn't find any other way to start SQL Database 2005 EE engin when working as limited user. And clearly you can't use ADMIN process as you have no right to change any Master attached databases nor you have any rights to view SQL folder.

The problem I have is THE NAMED PIPE which is created for user instance and to which I need to connect when I want to work with SQLManagement Studio. VWD recognize running user instance just fine and it would put userinstance true attribute to connection string, but 'classic' connection to this running engine via other tools like mentioned SSMSEE needs to know this:
\\.\pipe\57C99C8D-4D03-45\tsql\query

so you could connect np:......

sseutil -childlist would list this running userinstance ID, but ONLY when run as Administrator, so we are loosing whole point here as help for limited users.

Any other help would be welcome. I might throw a longer post about my 'restricted user ASP.NET development' at forums once I get some time.

# re: Cool SQL Server and SQL Express Command-Line Utility

Saturday, December 17, 2005 3:29 AM by scottgu
Hi Parag,

Actually this is a different utility. The SQL Express->SQL Server Web Upload utility is in beta testing now and will hopefully start appearing places soon.

Hope this helps,

Scott