MSDE Administrative Tools 3

[bobbarker]

Questions from a MSDE/SQL newby
Can anyone recommend some good 'cheap' tools for MSDE database administration (creating tables/queries, viewing data etc).
Yours in hope,

 

[Corran007]

Funny you should mention that. Quest software just released a toad for sql 1.0. Im looking through it and it looks pretty good. And its free. its from

http://www.toadsoft.com/downld.html#toadss

take a look and see. it should connect to msde, it handles named instances.

 

[bobbarker]

Many thanks for your response Corran007. I will have a look at this.
Would also be interested to hear from anyone with a suggestion that they have successfully tried and tested.

 

[SQLBill]

Another option, if you have a SQL Server install disk, is to load the SQL Server Client Tools. Enterprise Manager and Query Analyzer work with MSDE also.


-SQLBill

 

[mrdenny]

Failing that there is a web based version of Enterprise Manager that is open sourced.

https://sourceforge.net/projects/asp-ent-man/

It hasn't been updated in a while, but it supports most of the basics. We have our hosting customers use it to connect to there database for security reasons.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[iceqube]

I've been using TOADSS for the past two weeks on test and production data (on a staging server). I've been looking for a replacement for MS Access for a database course I teach. Students complained about having no access to Access at home, so I started looking into freeware frontends for MSDE 2000 and found TOADSS. You cannot connect to a SQL Server database with this tool using Windows authentication - you MUST use SQL Server authentication. The tool's developer did an excellent job - it handles most of the functions of Enterprise Manager and Query Analyzer.

There is a user group at this URL:

http://groups.yahoo.com/group/toadss

Anyone or any organization that wants to migrate to SQL Server and can live with the access and size limits of MSDE 2000 would do well to look at TOADSS as the frontend tool, if one wants to avoid having to purchase the Standard or Enterprise version just to get a GUI database management frontend.

 

[Corran007]

Actually you can connect useing windows authincation, its just not obvious. Use the data connection string and set it for nt auth instead of sqlserver. Dont forget the Test connection. When it asks for username/password, just click ok, soemtimes it asks for username, just put your nt username if it does.

I am now connected with nt auth. Now these are on servers with mixed, i have none with stright nt auth so i am unsure if that works.

It seems to work for me, individual results may vary

 

[bobbarker]

iceqube and corran007 - many thanks for your comments.
I am looking at TOADSS but get an error when trying to create a database "Could not obtain exclusive lock on database 'MODEL'. Retry the operation later." - I can not find any obvious connections. Any idea?

 

[Corran007]

i connected to a msdb database instead of one of my sqlserver just to try, but i was able to create it fine. only thing i have found is that i am unsure how to get the new database to show up in the dropdown list. Only way around it is to close toad and open it again. they really need a refresh db button/ability.

Only thing i needed to do to create it was enter the name. If you can, i would restart your msdb service. It sounds like sometthing else is using it, but i didnt not know excluisve access to model was needed to create a db, but it does make sense. I dont see a way to kill users. if you looked in the model database, it proably still has you in there. So i would first close toad, then reopen and try to create a new one. If that fails, restart the servcie. That should work.

 

[iceqube]

Here's another FREE administrative frontend GUI for MSDE that I've added to my toolkit - DBAMGR2K. You'll find it at:

http://www.asql.biz/DbaMgr.shtm

It's got all the functionality of Enterprise Manager and Query Analyzer, including BCP and Backup/Restore.