How to write this script to open SQL Server, Open Table, Query

[Shawn W]

hi, guys

I use SQL Server a lot and one of the frequent jobs I do in SQL Server is using the Open Table, Query.

Here is what I usually do manually:

1. Open SQL Server Enterprise Manager

2. Expand SQL Server Group

3. Expand a registered SQL Server Alias

4. Expand the Databases branch and choose the database I want to access

5. click Tables

6. Right Click the table I want to Query on the right panel

7. Open Table

8. Query

As you can see, there are so many steps that I wonder if I can write a script to do it automatically!

Any one can help me out?

Thanks a lot!

[/dev/null]

As you can see, there are so many steps that I wonder if I can write a script to do it automatically!

run osql.exe via run().

Cheers

Kurt

[Shawn W]

run osql.exe via run().

Cheers

Kurt

What is osql.exe? It doesn't work anyway.

[ChrisL]

What is osql.exe? It doesn't work anyway.

You would do Run ("osql -U username -P password -Q SELECT * FROM table -o d:\textfile.txt") I think or whatever sql command your trying to do

Edited October 27, 2006 by ChrisL

[Shawn W]

You would do Run ("osql -U username -P password -Q SELECT * FROM table -o d:\textfile.txt") I think or whatever sql command your trying to do

I see what you mean.

But I don't need just running a specific SQL command.

All I want is just open the "open table Query" window in the SQL enterprise managermert. So, I can do the query I want. The beauty of this tools is, I can even modify the value from the query result, which is impossible in Query Analyzer or OSQL.exe.

Edited October 27, 2006 by Shawn W

[PaulIA]

You can do what you're talking about, but you going to have to manipulate Enterprise Manager to do it and there is quite a bit involved.

When you start up EM, the left hand pane is a TreeView control and the right hand pane is a ListView. You'll need to read the TreeView to find the DB that you want, expand the DB and select the Tables entry. The right hand pane then becomes another TreeView control. You'd scan down this TreeView, looking for the table that you want to query on. When found, issue a right click on the table name, which will popup the first of the two menus that you need to manipulate in order to open the Query window.

And then you have to manipulate the Query window itself....

If you're serious about doing this, grab a copy of Auto3Lib as you'll need it to manipulate the TreeView, ListView and popup Menu controls in EM and the Query window.

[Shawn W]

You can do what you're talking about, but you going to have to manipulate Enterprise Manager to do it and there is quite a bit involved.

When you start up EM, the left hand pane is a TreeView control and the right hand pane is a ListView. You'll need to read the TreeView to find the DB that you want, expand the DB and select the Tables entry. The right hand pane then becomes another TreeView control. You'd scan down this TreeView, looking for the table that you want to query on. When found, issue a right click on the table name, which will popup the first of the two menus that you need to manipulate in order to open the Query window.

And then you have to manipulate the Query window itself....

If you're serious about doing this, grab a copy of Auto3Lib as you'll need it to manipulate the TreeView, ListView and popup Menu controls in EM and the Query window.

Thank you PaulIA!

That's the way I wanted it to be done!

I am still a rookie about AutoIit and i am not sure how to work on TreeView and ListView etc.

It looks really hard to do, doesn't it? I am thinking maybe using mouse control will be simpler.

[PaulIA]

I am still a rookie about AutoIit and i am not sure how to work on TreeView and ListView etc.

It looks really hard to do, doesn't it? I am thinking maybe using mouse control will be simpler.

Well, let's just say it's not what I would use as a starting AutoIt project.

I think you'll quickly find that just using mouse control is not going work. If you're going to manipulate EM, you're going to have to identify where things are and then use MouseClick to select the items of interest. You're also going to have to be able to capture the popup menus when you right click, read the menu items and click on the correct one. It's not at all impossible, in fact it's really pretty straight forward.

Take a look at the TreeView demo script in Auto3Lib and START SMALL! Start off just by writing a function that opens EM. Then write another one that reads the right had pane and selects the correct DB branch. Then write another one that selects the "Tables" branch. etc., etc., etc.

You're not going to sit down and do this in an evening, so if you don't have the time/will to spend on this, then you might want to look at other alternatives.

[Shawn W]

Well, let's just say it's not what I would use as a starting AutoIt project.

I think you'll quickly find that just using mouse control is not going work. If you're going to manipulate EM, you're going to have to identify where things are and then use MouseClick to select the items of interest. You're also going to have to be able to capture the popup menus when you right click, read the menu items and click on the correct one. It's not at all impossible, in fact it's really pretty straight forward.

Take a look at the TreeView demo script in Auto3Lib and START SMALL! Start off just by writing a function that opens EM. Then write another one that reads the right had pane and selects the correct DB branch. Then write another one that selects the "Tables" branch. etc., etc., etc.

You're not going to sit down and do this in an evening, so if you don't have the time/will to spend on this, then you might want to look at other alternatives.

You are right.

Since it is not such a simple project, I will not spend too much time on it for now.

thanks anyway for you guys' help!

[PaulIA]

You are right.

Since it is not such a simple project, I will not spend too much time on it for now.

thanks anyway for you guys' help!

You're welcome