Enterprise Manager-vs-Query Analyzer Newbie here-some guidance pls

[markajem]

The company I am working for had allowed me to start working with VIEWs in Enterprise Manager. Just recently it was suggested that I use Query Analyzer instead to create my VIEWs to help prevent accidental data changing etc. I fully understand the MIS director's concern in this area. He states it is safer for me to use Query Analyzer instead of Enterprise Manager because there is far less of a chance that I may change something in an active data table. I have no problem with this and would feel more comfortable using the Query Analyzer for this reason.

My question is though how much of a difference is there in using the Query Analyzer than Enterprise Manager. I was just getting used to working with Enterprise Manager and now have to start in a new environment again. I am new to creating VIEWs but understand their concepts and use.

I have been told I am using T-SQL. I have been using Crystal to create custom reports and as I learned more realized creating VIEWs will be advantageous in some areas. I do have some crystal reports pending while I create some VIEWs to finish them. What I am asking for I guess is what is the main difference in creating my VIEWs in Query Analyzer compared to Enterprise Manager. The MIS head said that when he has some time within the next few days he will set me up on my system so I can begin to use Query Analyzer.

So this is basically a general question in all. Some pointing to reference would be helpful. It is difficult for me to have any idea of what I will be facing without first being in the environment but have to wait til he has the time to sit and work with me. I just wanted to get some preliminary work/study done first if possible.

Thank you so much
Mark







Mark
Email: markanas333@hotmail.com

 

[AWithers]

The main difference between enterpise manager (EM) and query analyzer (QA) is that EM presents you with a more user-friendly interface. As you are probably aware it is simple to add tables, define joins, select fields to return, add criteria(very similar to the Query by example grid available in MSA Access), in QA you will have to write to SQL from scratch, which is not necesarily a bad thing but may present a relativbely steep learning curve.

I have to say that maybe your MIS director is being a little over the top...it is not possible to change data using a view...and really there is no difference in this respect to what damage you could do by using QA. They key thing is ensuring that your permissions are correct and really that you should probably be creating views etc. in a copy of the database (a development or uat type environment

Hope this helps a little

Andy

 

[sguslan]

With Query Analyzer you get more chance to really learn the power of SQL Server. EM is very limited to some GUI type design environment. With EM you can not do everything you do in Quer Analyzer. More, if for instance you need to write a stored procedure or trigger in EM, you'd be presented with a small delibitating window to write code in it. Very limiting! In QA you get the power to cennect to a DB or many DBs each connection with it's own windows that is re-sizable and comfortable to work within, a typical winodws stuff. Go for QA! You'll learn more ....

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[mrees]

I agree with the others, QA and EM are just two different interfaces to acomplish the same thing. Although some tasks are easier than others, such as I tend to create new tables in EM,as I find the graphical interface quicker to build them (and i'm lazy).

But I do all my queries/views/stored procs in QA, as EM tries to be helpful and "re-writes" your code to fit in the graphical interface. (Especially as Enterprise Manager does not support CASE statements...and tends to do massive duplication when it comes to using an OR in a where clause, when a bit of careful braketing in QA sorts that out.)

I think you have found the best source of Info you are going to get right here.

One thing I would consider though (this depends on your DB setup) is how many users are going to view your crystal reports, and how often. It may not be advisable to have 100's of people hitting your DB all day, when it could possibly interefere with other DB processes.

As a lot of our reports are presented as at a month-end posistion, we put this data into a separate table each month, and build the report from this. This speeds up the reports considerably, and means the report viewers don't hit the "base" tables.

Hope I haven't confused you too much, if you need any more help, just post!

 

[markajem]

Thank you all for your input. Really appreciate it.

Well I guess for a beginner like me the graphical interface of EM may be easier for me to deal with but won't force me to learn as much as with QA from what I can tell. Once I can learn to write T-SQL then I can learn to be lazy I guess .

As far as security for the database and how the MIS director feels I will not question him. Whatever he feels more comfortable with me doing is good for me. I do know you can access the datatables and the SP's from EM and it does present me with some chance of error and to tell you the truth I would feel better working in QA just knowing I can't do any damage while LEARNING, that's the key.

Although the graphical interface in EM was quite handy it helped me get a start on some of my VIEWs whereas now I really have to know the code to create even the most basic VIEW.

Andy I know where you are coming from but on the other hand I must respect his wishes and not question them at this time, for whatever reason. I know you can't change data creating a VIEW it is what else I can access while in the EM.

Thanks all for your help and if anyone else has anything else to add that would be appreciated.

Mark






Mark
Email: markanas333@hotmail.com

 

[SQLSister]

I hate to tell you but you can just as easily do damage in query analyzer as in Enterprise Manager. Maybe more so because you can highlight just part of a statement (Say missing the where clause in a delete statment, oops deleted the whole table's data.) and run it.

Plus since you don't see things graphically creating table joins especially the comlex ones used in a join can be much more prone to error and thus to mistake. But if you are creating views to base reports on, then you probably are only using select statements not any insert, update or delete staments. Myabe I'll be charitable and believe it is because you can directly type in the tables in Enterprise Manager that he is worried. However, if you don't need the rights to insert to update or delete data, you shouldn't have them and then it makes no difference which tool you use. Of course if you are working in a development databse, even a mistake which changes or erases data is no big deal. It isn't live data. I can't stress enough that you should NEVER do development work in a live production database. If you company doesn't have a development database, you need to push hard to get one. And until you do, make sure that backups of the datbase and the transaction logs are being made frequently. No faster way to lose your job than to muck up a production database with a development mistake and then have no backup newer than last month!

Query analyzer is the preferred tool for most of us and it has to do with the fact that Enterprise manager doesn't support all the functionality that Query analyzer does and that it is much more intensive in how it uses resources than the same commands in Query Anlayzer are.

I also suggest you get yourself some big fat books on T-SLQ. You are gonna need them. See the following FAQ for my recommendations and those of other memebers of this forum.

Useful Reference Books for SQL Server Professionals
faq183-3324

Books Online is also a valuable resource for learning about the various options you have onthe t-SQL commands and this board can be invaluable.

One useful Query Analuzer tip that I didn't learn for months was that you can drag and drop table names, column names and even the names of all the columns in a table onto the notepad where you write you code. Doing this ensures no spelling/typing errors and can save a lot of development time. You might also look at using the query templates. If you are going to write a lot of views which use the same three or four tables in joins, then you might want to create a view template that includes the join already so you don't have to keep typing it.

Good Luc.k

 

[Corran007]

On a semi related note. Why do you not have some sort of dev/test environment that you can work in? Unless your database is so large as it would be prohibitily expensive like a datawarehouse that you cannot have a test/dev environment, you really should. Even with a datawarehouse you could have the data structure with a sample of the data to develop and test against.

You should never ever do development on a production database. Even if its just reports. I would strongly reccomend that you at least set up a test database. If you can, put a copy of production database on another box,even if its just a desktop machine to test with.

Most companies do development on thier local machine. they may or may not have a development enviorment apart from thier local machines. They then have a test enviroment to test everthing in. this is either Qa people or the developers testing it themeselves. it is important to have it on a differnt box than thier local machine so that any settings they forgot about can be rememberd before they go to production. It can be just a desktop with boosted memory. you may or may not have a uat enviornment. depends on your companys needs. and then production enviornment of course.


As for the topic at hand...
I would say that learning how to code it in tsql is the best way to really learn it. and if you could change data using em for view creation, wouldnt it make sense that you could do the exact same thing using tsql to write views? you will get the same output after all. but if his concern is about you changing data in a table with em accidently, then there is much less of a chance since you would have to write it all out in QA.

 

[markajem]

Thanks for your VIEWs on this topic, very helpful.

As far as a test environment I do know there is one. I was creating the VIEWs in the test company database however you can access the live database area in EM the way it is set up. I can not suggest to him how he should be or comment on how he is doing what he is doing or going against his wishes - not in a position to do that and I must respect his wishes in all areas.

I do know he is making backups daily; how, don't know.

I am just creating VIEWs and not going to be doing updates, deleting etc with the TSql code. I am basically using my VIEWs in lue of some subreports in Crystal because it helps streamline the running of Crystal reports. Subreports slows them down so much. Also I see it gives me greater flexibility in creating some of my reports. I do have some existing Crystal reports that I do need to alter because the person that wrote them did not understand the business as well as I do and with doing Crystal before it is helpful that I make the changes.

Thanks also for the drag n drop info on the table names etc. That is helpful.

Mark






Mark
Email: markanas333@hotmail.com