Sign in to follow this  
Followers 0
Shawn W

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

10 posts in this topic

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!

Share this post


Link to post
Share on other sites



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


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

run osql.exe via run().

Cheers

Kurt

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by ChrisL

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by Shawn W

Share this post


Link to post
Share on other sites

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.


Auto3Lib: A library of over 1200 functions for AutoIt

Share this post


Link to post
Share on other sites

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. :whistle:

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

Share this post


Link to post
Share on other sites

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. :whistle:

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.


Auto3Lib: A library of over 1200 functions for AutoIt

Share this post


Link to post
Share on other sites

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

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!

Share this post


Link to post
Share on other sites

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

Auto3Lib: A library of over 1200 functions for AutoIt

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0