Re: ODBC access to TSM from VBScript

[Ung Yi]

Thanks,

this looks good.

Yi


>>> [EMAIL PROTECTED] 09/06/02 11:35AM >>>
Actually now that  think about it, I don't think my response below really
answered the question about created a "linked server". The procedure I
outlined shows how to import TSM tables into MS SQL. This just goes to
show how little I know about MS SQL! (sorry, I just installed it this
morning, and have never really used it before).

I was able to create a "linked server" with the following steps:

1) Start the SQL Server Enterprise Manager.

2) Navigate to "Microsoft SQL Servers" --> "SQL Server Group" --> "STORMAN
(Windows NT)" --> "Security". Note that "STORMAN" is my Windows machine
name. Open up the "Security" tree.

3) Right-click on "Linked Servers" and select "New Linked Server...".

4) In the "Linked server" text box, put "TSM". Select the "Other data
source" radio button, then from the "Provider name" drop-down list, select
"Microsoft OLE DB Provider for ODBC Drivers". In the "Provider string"
text box, enter the following:

   DSN=yourdsn;UID=youradmin;PWD=yourpassword

Where "yourdsn" is the name of your TSM ODBC driver DSN, "youradmin" is
your TSM Admin ID, and "yourpassword" is your TSM Admin password. For
example, if my DSN is "amr_odbc", my Admin ID is "storman", and my
password is "x", then I would enter:

   DSN=amr_odbc;UID=storman;PWD=x

Leave the other text box fields blank.

In the "Server option" section, "Data access" is checked, but the other
checkboxes are unchecked. Click "OK".

5) "TSM" should now appear in the "Linked Servers" tree. Open up "TSM" and
click on "Tables". In a few moments, you should see the TSM table names
appear in the right-hand pane.

I imagine that you know what to do with a linked server once you have
one... I have not yet figured out what to do with it.   ;-)

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Andrew Raibeck/Tucson/IBM@IBMUS
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/06/2002 07:45
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:Re: ODBC access to TSM from VBScript



Yes, I've been able to get this to work with MS SQL 7.0 Desktop Edition
(should work with Enterprise Edition as well, I just haven't tried it).

Here are the steps I followed:

1) Start the SQL Server Enterprise Manager.

2) Navigate from "Microsoft SQL Servers" --> "SQL Server Group" -->
"STORMAN (Windows NT)" --> "Databases". Note: STORMAN is my Windows
machine name.

3) Right click on "Databases" and select "New Database..." from the pop-up
menu. This brings up the "Database Properties" dialog.

4) Select the "General" tab (should be the default). In the "Name" text
box, I put "TestTSM". Then click "OK".

5) Right click on the "TestTSM" database, and select "All Tasks/Import
Data..." from the pop-up menu. This brings up the "DTS Import Wizard"
dialog. Click "Next" to continue.

6) In the "Source" drop-down list, select "TSM ODBC Driver". Select the
"User/System DSN" radio button (normally the default), then select your
TSM ODBC driver DSN from the drop-down list. Enter your TSM Admin ID in
the "Username" text box, and your TSM Admin password in the "Password"
text box. Click "Next".

7) In the "Destination" drop-down list, select "Microsoft OLE DB Provider
for SQL Server". This is probably already selected for you by default. In
the "Server" drop-down list, select your SQL server (mine shows STORMAN).
Select the "Use Windows NT authentication" radio button. In the "Database"
drop-down list, select "TestTSM" (should already be selected). Click
"Next".

8) Select the "Copy table(s) from the source database" radio button, then
click "Next".

9) You should now see a list of the tables from the TSM server. Check the
tables you wish to copy (I picked the ADSM.ADMINS table), then click
"Next".

10) You should now be in the "Save, Schedule and Replicate Package"
dialog. Check "Run immediately" (should be the default) and leave
everything

Re: ODBC access to TSM from VBScript

[Andrew Raibeck]

Actually now that  think about it, I don't think my response below really
answered the question about created a "linked server". The procedure I
outlined shows how to import TSM tables into MS SQL. This just goes to
show how little I know about MS SQL! (sorry, I just installed it this
morning, and have never really used it before).

I was able to create a "linked server" with the following steps:

1) Start the SQL Server Enterprise Manager.

2) Navigate to "Microsoft SQL Servers" --> "SQL Server Group" --> "STORMAN
(Windows NT)" --> "Security". Note that "STORMAN" is my Windows machine
name. Open up the "Security" tree.

3) Right-click on "Linked Servers" and select "New Linked Server...".

4) In the "Linked server" text box, put "TSM". Select the "Other data
source" radio button, then from the "Provider name" drop-down list, select
"Microsoft OLE DB Provider for ODBC Drivers". In the "Provider string"
text box, enter the following:

   DSN=yourdsn;UID=youradmin;PWD=yourpassword

Where "yourdsn" is the name of your TSM ODBC driver DSN, "youradmin" is
your TSM Admin ID, and "yourpassword" is your TSM Admin password. For
example, if my DSN is "amr_odbc", my Admin ID is "storman", and my
password is "x", then I would enter:

   DSN=amr_odbc;UID=storman;PWD=x

Leave the other text box fields blank.

In the "Server option" section, "Data access" is checked, but the other
checkboxes are unchecked. Click "OK".

5) "TSM" should now appear in the "Linked Servers" tree. Open up "TSM" and
click on "Tables". In a few moments, you should see the TSM table names
appear in the right-hand pane.

I imagine that you know what to do with a linked server once you have
one... I have not yet figured out what to do with it.   ;-)

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Andrew Raibeck/Tucson/IBM@IBMUS
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/06/2002 07:45
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:Re: ODBC access to TSM from VBScript



Yes, I've been able to get this to work with MS SQL 7.0 Desktop Edition
(should work with Enterprise Edition as well, I just haven't tried it).

Here are the steps I followed:

1) Start the SQL Server Enterprise Manager.

2) Navigate from "Microsoft SQL Servers" --> "SQL Server Group" -->
"STORMAN (Windows NT)" --> "Databases". Note: STORMAN is my Windows
machine name.

3) Right click on "Databases" and select "New Database..." from the pop-up
menu. This brings up the "Database Properties" dialog.

4) Select the "General" tab (should be the default). In the "Name" text
box, I put "TestTSM". Then click "OK".

5) Right click on the "TestTSM" database, and select "All Tasks/Import
Data..." from the pop-up menu. This brings up the "DTS Import Wizard"
dialog. Click "Next" to continue.

6) In the "Source" drop-down list, select "TSM ODBC Driver". Select the
"User/System DSN" radio button (normally the default), then select your
TSM ODBC driver DSN from the drop-down list. Enter your TSM Admin ID in
the "Username" text box, and your TSM Admin password in the "Password"
text box. Click "Next".

7) In the "Destination" drop-down list, select "Microsoft OLE DB Provider
for SQL Server". This is probably already selected for you by default. In
the "Server" drop-down list, select your SQL server (mine shows STORMAN).
Select the "Use Windows NT authentication" radio button. In the "Database"
drop-down list, select "TestTSM" (should already be selected). Click
"Next".

8) Select the "Copy table(s) from the source database" radio button, then
click "Next".

9) You should now see a list of the tables from the TSM server. Check the
tables you wish to copy (I picked the ADSM.ADMINS table), then click
"Next".

10) You should now be in the "Save, Schedule and Replicate Package"
dialog. Check "Run immediately" (should be the default) and leave
everything else unchecked. Click "Next".

11) Click "Finish". The table(s) you selec

Re: ODBC access to TSM from VBScript

[Andrew Raibeck]

Yes, I've been able to get this to work with MS SQL 7.0 Desktop Edition
(should work with Enterprise Edition as well, I just haven't tried it).

Here are the steps I followed:

1) Start the SQL Server Enterprise Manager.

2) Navigate from "Microsoft SQL Servers" --> "SQL Server Group" -->
"STORMAN (Windows NT)" --> "Databases". Note: STORMAN is my Windows
machine name.

3) Right click on "Databases" and select "New Database..." from the pop-up
menu. This brings up the "Database Properties" dialog.

4) Select the "General" tab (should be the default). In the "Name" text
box, I put "TestTSM". Then click "OK".

5) Right click on the "TestTSM" database, and select "All Tasks/Import
Data..." from the pop-up menu. This brings up the "DTS Import Wizard"
dialog. Click "Next" to continue.

6) In the "Source" drop-down list, select "TSM ODBC Driver". Select the
"User/System DSN" radio button (normally the default), then select your
TSM ODBC driver DSN from the drop-down list. Enter your TSM Admin ID in
the "Username" text box, and your TSM Admin password in the "Password"
text box. Click "Next".

7) In the "Destination" drop-down list, select "Microsoft OLE DB Provider
for SQL Server". This is probably already selected for you by default. In
the "Server" drop-down list, select your SQL server (mine shows STORMAN).
Select the "Use Windows NT authentication" radio button. In the "Database"
drop-down list, select "TestTSM" (should already be selected). Click
"Next".

8) Select the "Copy table(s) from the source database" radio button, then
click "Next".

9) You should now see a list of the tables from the TSM server. Check the
tables you wish to copy (I picked the ADSM.ADMINS table), then click
"Next".

10) You should now be in the "Save, Schedule and Replicate Package"
dialog. Check "Run immediately" (should be the default) and leave
everything else unchecked. Click "Next".

11) Click "Finish". The table(s) you selected will be copied. Click "Done"
when the copy is complete.

12) Now in the Enterprise Manager, navigate to the "TestTSM" database, and
click on the "+" sign to open the tree. Click on "Tables".

13) The right-hand pane of the the Enterprise Manager should show a list
of tables in the TestTSM database. Among those tables will be the ones you
chose to copy from the TSM server (in my example, I see the ADMINS table).
Right-click on the table, then select "Open Table/Return all rows" from
the pop-up menu. The rows from the ADMINS table should appear.

And that's it!

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Ung Yi <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/04/2002 13:58
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:Re: ODBC access to TSM from VBScript



Wow.

Can you create a linked server from SQL Server to TSM database(in a
supported way)?

Thanks,
yi

Re: ODBC access to TSM from VBScript

[Ung Yi]

Wow.

Can you create a linked server from SQL Server to TSM database(in a supported way)?

Thanks,
yi


>>> [EMAIL PROTECTED] 09/04/02 10:03AM >>>
First, I recommend using the 5.1.1.1 or 4.2.2.0 ODBC driver.

A simple example of using Excel with the TSM ODBC driver:

0) Install the TSM ODBC driver and define a data source. If you are
unfamiliar with installing and configuring the ODBC driver, you should
review the README file that accompanies the ODBC driver, as it contains
information on installation and configuration. See the "Installing the
ODBC Driver" and "Defining Data Sources" sections.

1) Start Excel and create a new sheet.

2) Select the menu item "Data/Get External Data/New Database Query...".
This will bring up the "Choose Data Source" dialog.

3) From the "Choose Data Source" dialog, select the "Databases" tab
(should be the default selection) and then highlight the name of your TSM
ODBC driver data source (from step 0 above). Click "OK".

4) You should now see the "Connect to TSM Server" dialog. Enter your TSM
Admin ID (if it is not already there) and your password, then click
"Connect".

5) You should now see the "Query Wizard - Choose Columns" dialog. On the
left-hand side, select ADMINS, then click the " > " button. The columns in
the ADMINS table will now appear in the right-hand side of the dialog.
Click "Next".

6) The dialog title now reads "Query Wizard - Filter Data". You do not
need to do anything here. Just click "Next".

7) The dialog title now reads "Query Wizard - Sort Order". Again, you do
not need to do anything here. Just click "Next".

8) The dialog title now reads "Query Wizard - Finish". The "Return Data to
Microsoft Excel" radio button should be selected. Click "Finish".

9) You will now see the "Returning External Data to Microsoft Excel"
dialog. The "Existing worksheet" radio button should be selected, and the
text box should contain "=$A$1". Click "OK".

10) If all went well, then the work sheet should now contain the
information from the ADMINS table.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Jin Bae Chi <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/04/2002 06:06
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:Re: ODBC access to TSM from VBScript



Peter,
I just read your questions and it's very interesting for me, too. Can
you share with me how you configue ODBC to use with Excel? I'm kind of
new on ODBC. Thanks.



Jin Bae Chi (Gus)
System Admin/Tivoli
Data Center
614-287-2496/5922
614-287-5488 Fax
[EMAIL PROTECTED]


>>> [EMAIL PROTECTED] 09/04/02 08:27AM >>>
Has anybody actually had any success in accessing the TSM database from
a
VBScript piece of code (running as asp in IIS) on a Win2K machine via
the
ODBC connection ?

I have installed the ODBC driver from the TSM 4.2 CD and configured an
ODBC
datasource with a  DSN pointing to the TSM server.

I know the ODBC DSN works because I can use Excel, MSQuery and Lotus
Approach to extract data
from the TSM database without any problems.

But what I will really like is to get it to work from VBScript.
So far I have not been successful in doing that.
All the examples I have found about how to access an ODBC datasource
from
VBScript
have ended in miscelaneous errors.

Has anybody done a similar thing from VBScript and how did you do it ?
A few lines of example code would of course be wonderful.

Regards

Peter

Re: ODBC access to TSM from VBScript

[Andrew Raibeck]

First, I recommend using the 5.1.1.1 or 4.2.2.0 ODBC driver.

A simple example of using Excel with the TSM ODBC driver:

0) Install the TSM ODBC driver and define a data source. If you are
unfamiliar with installing and configuring the ODBC driver, you should
review the README file that accompanies the ODBC driver, as it contains
information on installation and configuration. See the "Installing the
ODBC Driver" and "Defining Data Sources" sections.

1) Start Excel and create a new sheet.

2) Select the menu item "Data/Get External Data/New Database Query...".
This will bring up the "Choose Data Source" dialog.

3) From the "Choose Data Source" dialog, select the "Databases" tab
(should be the default selection) and then highlight the name of your TSM
ODBC driver data source (from step 0 above). Click "OK".

4) You should now see the "Connect to TSM Server" dialog. Enter your TSM
Admin ID (if it is not already there) and your password, then click
"Connect".

5) You should now see the "Query Wizard - Choose Columns" dialog. On the
left-hand side, select ADMINS, then click the " > " button. The columns in
the ADMINS table will now appear in the right-hand side of the dialog.
Click "Next".

6) The dialog title now reads "Query Wizard - Filter Data". You do not
need to do anything here. Just click "Next".

7) The dialog title now reads "Query Wizard - Sort Order". Again, you do
not need to do anything here. Just click "Next".

8) The dialog title now reads "Query Wizard - Finish". The "Return Data to
Microsoft Excel" radio button should be selected. Click "Finish".

9) You will now see the "Returning External Data to Microsoft Excel"
dialog. The "Existing worksheet" radio button should be selected, and the
text box should contain "=$A$1". Click "OK".

10) If all went well, then the work sheet should now contain the
information from the ADMINS table.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Jin Bae Chi <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/04/2002 06:06
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:Re: ODBC access to TSM from VBScript



Peter,
I just read your questions and it's very interesting for me, too. Can
you share with me how you configue ODBC to use with Excel? I'm kind of
new on ODBC. Thanks.



Jin Bae Chi (Gus)
System Admin/Tivoli
Data Center
614-287-2496/5922
614-287-5488 Fax
[EMAIL PROTECTED]


>>> [EMAIL PROTECTED] 09/04/02 08:27AM >>>
Has anybody actually had any success in accessing the TSM database from
a
VBScript piece of code (running as asp in IIS) on a Win2K machine via
the
ODBC connection ?

I have installed the ODBC driver from the TSM 4.2 CD and configured an
ODBC
datasource with a  DSN pointing to the TSM server.

I know the ODBC DSN works because I can use Excel, MSQuery and Lotus
Approach to extract data
from the TSM database without any problems.

But what I will really like is to get it to work from VBScript.
So far I have not been successful in doing that.
All the examples I have found about how to access an ODBC datasource
from
VBScript
have ended in miscelaneous errors.

Has anybody done a similar thing from VBScript and how did you do it ?
A few lines of example code would of course be wonderful.

Regards

Peter

Re: ODBC access to TSM from VBScript

[Andrew Raibeck]

Here you go:

==
dim dbc
dim recordSet
dim sql
dim recordCount
dim myArray

call testTsm


sub testTsm
   set dbc   = WScript.CreateObject("ADODB.Connection")
   set recordSet = WScript.CreateObject("ADODB.Recordset")

   WScript.Echo "Connecting to TSM server..."
   ' your_dsn   = your TSM ODBC driver DSN name.
   ' your_admin = your TSM Admin ID
   ' x  = your TSM Admin password
   dbc.Open "your_dsn", "your_admin", "x"
   WScript.Echo "Connected!"

   sql = "select * from admins"
   recordSet.Open sql, dbc
   myArray = recordSet.GetRows()

   for r = 0 to UBound(myArray, 2)
  WScript.Echo "ADMIN_NAME: ", myArray(0, r)
   next

   recordSet.Close()
   dbc.Close()
end sub
==

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Peter Bjoern <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/04/2002 05:27
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:ODBC access to TSM from VBScript



Has anybody actually had any success in accessing the TSM database from a
VBScript piece of code (running as asp in IIS) on a Win2K machine via the
ODBC connection ?

I have installed the ODBC driver from the TSM 4.2 CD and configured an
ODBC
datasource with a  DSN pointing to the TSM server.

I know the ODBC DSN works because I can use Excel, MSQuery and Lotus
Approach to extract data
from the TSM database without any problems.

But what I will really like is to get it to work from VBScript.
So far I have not been successful in doing that.
All the examples I have found about how to access an ODBC datasource from
VBScript
have ended in miscelaneous errors.

Has anybody done a similar thing from VBScript and how did you do it ?
A few lines of example code would of course be wonderful.

Regards

Peter

Re: ODBC access to TSM from VBScript

[Andrew Raibeck]

As an addendum to my prior post on this thread, I would recommend you use
the 5.1.1.1 or 4.2.2.0 ODBC driver.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Peter Bjoern <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/04/2002 05:27
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:ODBC access to TSM from VBScript



Has anybody actually had any success in accessing the TSM database from a
VBScript piece of code (running as asp in IIS) on a Win2K machine via the
ODBC connection ?

I have installed the ODBC driver from the TSM 4.2 CD and configured an
ODBC
datasource with a  DSN pointing to the TSM server.

I know the ODBC DSN works because I can use Excel, MSQuery and Lotus
Approach to extract data
from the TSM database without any problems.

But what I will really like is to get it to work from VBScript.
So far I have not been successful in doing that.
All the examples I have found about how to access an ODBC datasource from
VBScript
have ended in miscelaneous errors.

Has anybody done a similar thing from VBScript and how did you do it ?
A few lines of example code would of course be wonderful.

Regards

Peter

Re: ODBC access to TSM from VBScript

[Jin Bae Chi]

Peter,
I just read your questions and it's very interesting for me, too. Can
you share with me how you configue ODBC to use with Excel? I'm kind of
new on ODBC. Thanks.



Jin Bae Chi (Gus)
System Admin/Tivoli
Data Center
614-287-2496/5922
614-287-5488 Fax
[EMAIL PROTECTED]


>>> [EMAIL PROTECTED] 09/04/02 08:27AM >>>
Has anybody actually had any success in accessing the TSM database from
a
VBScript piece of code (running as asp in IIS) on a Win2K machine via
the
ODBC connection ?

I have installed the ODBC driver from the TSM 4.2 CD and configured an
ODBC
datasource with a  DSN pointing to the TSM server.

I know the ODBC DSN works because I can use Excel, MSQuery and Lotus
Approach to extract data
from the TSM database without any problems.

But what I will really like is to get it to work from VBScript.
So far I have not been successful in doing that.
All the examples I have found about how to access an ODBC datasource
from
VBScript
have ended in miscelaneous errors.

Has anybody done a similar thing from VBScript and how did you do it ?
A few lines of example code would of course be wonderful.

Regards

Peter