The Old Joel on Software Forum: Part 3 (of 5)

dumb sql server question

i'm used to dealing with oracle and java. and just developing all my queries using SQL Plus, then once the query was right, incorporating it into my code. Now I'm sitting in front of visual studio .NET and trying to figure out how to run ad-hoc queries from within visual studio. is there a command-line SQL interface within visual studio? or should i use "ISQL" . thanks! aslo are there any "sql server for oracle dorks" books?

ms noob
Thursday, October 2, 2003

In VS.Net, you get at SQL Server through the "Server Explorer"

But a better way - you need to get your hands on a SQL Server CD and install the "Server tools". Then you'll have access to Enterprise Manager (like TOAD - a GUI for server management) and Query Analyzer (text editor for queries, SP's, any TSQL you want to write)

Hope that helps?

Philo

Philo
Thursday, October 2, 2003

...and I completely failed to answer your question. :-)
AFAIK, there's no SQL command line from VS.Net, just the wizards and designers.

(though I'd be happy to hear differently from anyone else)

Query Analyzer's a better tool than ISQL.

Philo

Philo
Thursday, October 2, 2003

yes, very helpful. thanks!

ms noob
Thursday, October 2, 2003

VS.NET does allow you to run SQL Queries. You need to create a database project first. It is not as easy to use as Query Analyzer, but it really helps to keep you organized since you should be integrating the database project into source control anyway.

http://www.informit.com/isapi/product_id~%7B0436AEA7-21CF-41E1-916B-0DC0F1F175E3%7D/content/index.asp

Sorry for the long link

Jason Watts
Thursday, October 2, 2003

ms noob,

Yes, there is a SQL command line for VS .NET.

First, either create a database project or use Server Explorer to open a stored procedure. Either of these will give you a new VS menu called Database.

This menu contains options that allow you to create a new database entity such as a stored procedure or table. But the most interesting Database menu option is the one titled "Run Selection". This window allows you to write an ad-hoc SQL script. Then you can highlight the part of your script you want to run and execute it.

HTH,

Mark
----
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128

Mark Pearce
Thursday, October 2, 2003

Sorry...

>> This window <<

should instead read "this option"...

Mark

Mark Pearce
Thursday, October 2, 2003

set up a database project, figured out the SQL window. everything is working great. Thanks!

ms noob
Thursday, October 2, 2003

I guess you guys are using the full-blown enterprise edition? I have the professional edition, and all I have on the database menu is 'run stored procedure'.

All of the books I have looked at assume you have the enterprise edition, and don't even bother to point out the differences. Do I have the only copy of professional in the world?

Scott Stonehouse
Friday, October 3, 2003

I think you're in the wrong spot. If you create a new project, one of your choices is a database project (dig through everything; it's there). Having created a dbproject, your solution explorer will show Change Scripts, Queries, and a shortcut to Database preferences. On the main menu (Project) (or right-click the project in the solution explorer) choose Add Item and you'll see a whole bunch of goodies.

I'm running the Pro version and just went to check it out. You had me scared there for a minute.

Ron Porter
Friday, October 3, 2003

If you look at the link Jason posted, There is a section (section 4) on creating scripts from database objects. I don't see that option in my version.

Section 5 is change scripts, which also are not available. Yes, there is a folder in the solution for change scripts, but the scripts are not generated from any GUI in the version I have. I can create a script by hand and put it in the folder if I like. I do that quite often.

I see no evidence of the "Run Selection" menu item that Mark pointed out.

I could go on, but basically, there are no GUI database tools in my version. I have 2003 professional.

I'm not complaining, since I already have the SQL Server tools that let me do all these things. But I wish the books would point out the differences. They will write whole chapters on these tools without telling you they aren't available in the professional version.

Maybe I'm still missing something...

Scott Stonehouse
Monday, October 6, 2003

Scott,

This could be a limitation with running VS Pro against the full edition of SQL Server.

I know that debugging with VS Pro is limited to MSDE - and this *is* explored in some depth in my book :)

Mark
----
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128

Mark Pearce
Monday, October 6, 2003

It's true, I'm running against the full SQL Server. But I also have MSDE installed locally, and it looks exactly the same when I run against it.

Is it because I have the SQL Server client software installed? Can you give me any more detail?

Your book is not one of the ones I've read, but I'm thinking I should add it to my wish list!

Scott Stonehouse
Tuesday, October 7, 2003

Scott,

Can you see your MSDE when using Visual Studio's Server Explorer? Does that give you a Database menu?

Mark

Mark Pearce
Tuesday, October 7, 2003

Yes, I can see MSDE. If I click on a table in Server Explorer, I get a database menu. The only option on the menu is "Retreive Data From Table". Nothing about generating scripts...

From the website above: "You can also display the Generate Create Scripts dialog box by dragging and dropping from the Server Explorer to a folder in the Solution Explorer."

This doesn't work either. I'm not allowed to drop into those folders - the mouse shows the 'no smoking' symbol.

You're telling me I should be able to do all these things with VS.Net 2003 Professional? So there must be something wrong with my configuration?

Scott Stonehouse
Wednesday, October 8, 2003

I apologize. Apparently I installed MS SQL Server Personal Edition. I didn't realize they weren't the same thing. But yesterday I copied a lot of data into my 'MSDE' and the database ended up being 5GB. I have read that MSDE could only handle 2GB, so that's when I knew something was odd.

So is there a fix for the issues with SQL Server? It doesn't make sense that there are more VS features when you are working with the more limited database!

Scott Stonehouse
Thursday, October 9, 2003