OK, so with a little offline help from LoTN, I managed to connect to the Media Manager database. I installed Microsoft SQL Server Management Studio Express. When I ran it, I managed to connect to the database with the following settings:



I had to have Vegas and the Media Manager running before I could connect. Once connected I can snoop around in the tables etc.. I think my previous error in creating the OBDC DSN was probably that I didn't include my computer's name before SONY_MEDIAMGR2.

I have absolutely no idea what to do next, since I have almost no experience with databases, but I'm quite encouraged that we seem to be able to get at the data.

A couple of questions to any database-savvy folk out there:

1. Could this Microsoft SQL Server Management Studio Express be used to get all the Media Manager data out and into another type of database?

2. Could it be used to get data into Media Manager via spreadsheets, tables etc., faster (in some cases) than manually inputting in MM?

Hi Nick:

Every data base I've worked with (Access, FoxPro, Pick) has data tables that look rather like an Excel spreadsheet. This may not be what you see in the user-interface, but that's what exists behind the curtain.

I wasn't able to open the sample file that you posted. Now that you have entry in the Media Manager system, maybe you can actually get at the tables! Reading this post has made me realize I need to know more about Media Manager.

Kim

This is the sort of thing you see once you're in. The tree on the left is enormous. Many levels deep. I haven't yet found anything that contains any intelligible reference to the files and tags that I've entered in MM.

Try expanding the System Tables folder. Maybe there will be some database looking objects in there. Your Open Office Base program should open most data base objects. Microsoft Access has a lot more horsepower but Base is okay for starters.

Is the Media Manager an Internet based tool, or does it reside on your computer after you have installed everything? I have clicked on Media Manager when I have Vegas open (10d) but then I get a message saying it's not installed/available/whatever.

Kimberly, Media Manager is an application intended to be used with Vegas and Vegas only, it seems - no internet involved (see my post 2nd from the top). You will find the Media Manager as a separate download last available with VP 9 but still working fine with VP10 and Window 7 (64bit in my case). When installed it installs the required SQL.

I do Access programming and linked to the Media Manager Tables using Access but could not make any head or tails of it. Maybe I did not spend enough time with it?

Perhaps you will have more success.

Willem.

Nick, My memory is dim (and getting dimmer), but I used to do a lot of this stuff. Maybe the following screen print will help you. I created a database named "myDB" and a table named "states". Wrote an SQL statement and pressed "Execute" and the results pop up in the Management Studio. I'm sure there are more experts than I 'cuz it's been some years since I've done any of this (and it's not like riding a bicycle).

1. Click New Query
2. That opens a tab where you can write your SQL
3. Write your SQL (this can get very complex).
3a. Select (i.e. click & drag) the the SQL you wish to execute. if you don't do this, all SQL on the page will be executed. In my example, there was only one SQL statement so... not a problem.
4. Click "execute"



Good Luck!
...Jerry

PS: Here's a tutorial: http://www.asp.net/sql-server/videos/using-sql-server-management-studio or....you might want to go these: http://www.asp.net/sql-server/videos if that tut is overwhelming.

Thanks Jerry and Kimberley. I'll have a dig into it tomorrow.

Kimberley, the latest version of MM can be downloaded from the Vegas Pro 9 page at [url=http://www.sonycreativesoftware.com/download/updates/vegaspro9].

After it had all installed, Windows Update wanted to update the SQL Server to service pack 4 but it would hang while doing so and I found that many people had the same problem. I suggest you update it to service pack 4 before Windows Update gets its hands on it, as otherwise you might need to go in and delete half-finished update-stuff. I used this updater: [url=http://download.windowsupdate.com/msdownload/update/software/svpk/2011/01/sqlserver2005expresssp4-kb2463332-x86-enu_896d55b16d7d0978618378f6bbbb3b6ab23296cc.exe]

On the strength of 2 days' use of MM, I like it. Busy adding all my tags to my 15k video clips.

One thing to note as you get started with MM. Most people seem to prefer to uncheck "Save media-usage relationships in active media library" in the Vegas Pro preferences to stop everything they bring into Vegas getting put into the library.

Data migration is one of the things I do for a living.
So....
There is a free, powerful program called OpenStudio from
http://Talend.com
It will read/write over 400 formats.
It should read SQL Server Express databases and copy data from it to another database, or even csv files or most anything you can think of. You can use
either an ODBC interface or SQL native to read/write data.
It can be programmed to do actions via a schedule or impromptu.
Has a pretty steep learning curve, but not daunting.
It will also do things like verify (based on rules you create) data, combine or separate columns/data and much more.

If you feel intimidated, check out their online videos before downloading.
Actually performs better than SQL Server integration services in most cases.

MS also has lots of free add ons for SQL Express that can help you view your data, even better than Excel.

In the SQL manager, you should be able to right-click and say Open.

Also, check out the Views. There maybe be a View already there that makes the data intelligible.
But DO NOT try any editing of the tables through this. You're just as like to mess up the whole thing from Vegas' point of view.

Thanks for the replies.

I installed Talend Open Studio and downloaded the manual. It looks promising but it's a big program with apparently quite a learning curve.

With MS SQL Management Studio Express, the furthest I've got can be seen in this screenshot, before realising I was way out of my depth:



Both these routes are rather daunting and potentially very time-consuming for me, so I'm going to stop now and let someone with more experience pursue it if they are so-inclined. At least I've gained a little confidence that MM data could be got into another program at some point in the future, if necessary.

If anyone ever gets to a point where they can usefully access Media Manager entries in a program other than Vegas, please let us know how you did it. Cheers!

Nick,

If it were me, I'd forget the system tables for the present.

Try these queries and see what you get:

SELECT * FROM Item

Your can also do queries from views, like

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
(this will probably give you strange results, but may be more meaningful on non-system views).

Remeber to select (click & drag) the area over the SQL & click "execute"

Good Luck! You'll be able to put MS SQL on your resume!
...Jerry

Edit: The red icon on your MS SQL instance makes me think you need to right click & start it.

It seems to me that what you're seeing (And all I've ever been able to see when I tried this) isn't the data you want. The server management tool is showing you the underlying nuts and bolts of the database itself. In other words, it's showing you the bureaucracy that built the highway when what you really want to see is the cars on the road.

Databases tend to use data tables to run themselves and that's what you're seeing.

Rob Mack

Nick,
You will find most of the data concerning your media in the tables dbo.item (names, paths etc...), dbo.property and dbo.propertyType (tags), just rightclick the table names and select "open table" to browse the data in Microsoft SQL Server Management.

As said it is easy to import the tables into Mysql or an other relational database system but then more difficult to make good use of them through queries...

On a side note, I took some of the things you're finding to heart and tried another route with limited success.

First off, I opened Vegas, Started Media Manager, and then created a new Media Library. I then populated it with data by searching a folder.

Next, I opened Excel (didn't want to install new tools just yet, had tried the manager last night at home and it wouldn't install on Win7-64). In Excel I chose Data/Import External Data/Import Data, and pretty much followed your path to connect to the database (computername/servername, use Windows Authentication).

One of the databases offered was the new one I'd just created. After choosing it I was offered a selection of tables from within the database. I chose "Item" since that seemed likely. After finishing the wizard and getting that table's data onto an Excel sheet I could see that it really did have useful data in it.

This is just a proof of concept. The database is relational, meaning it contains a lot of tables that relate to each other, and also meaning that opening just one table doesn't really get you very far since the meat of any relational database is in how the tables interrelate.

I haven't had a copy of Access in a while but it does seem to me that you could get at this data and do stuff with it through Access. LibreOffice Base might be a different matter-last I looked at Base it seemed flat rather than relational.

Probably, you could use SQL queries to get data from multiple tables into Excel or any other app that can do it. Then you could relate the media to the tags, etc. The holy grail ought to be to do something new with the info, like make a tool that can organize several veg files into a project, or to back up all the media used in those veg files to some external media like a tape drive or hard disk.

Rob Mack

To chime in with what Jacques said, once you get the data tables imported into Microsoft Access or Open Office Base, you will need to look at each table to understand what it contains. There will probably be many tables behind the curtain that drive the Media Manager.

The idea of a relational data base is that you can make queries and reports by linking tables together with common elements and then apply criteria to display only the records meeting the criteria.

Each record in a table may have a field that uniquely identifies it. This is often called the Item ID or Record Key. You will get one to one relationship if you link a Record Key in one table to a matching Record Key in another table (by definition a Record Key should be unique). You will get a one to many relationship if you link a unique field such as the Record Key in one table to a matching non-unique field in another table.

Think of a sales order data base. You will probably have a sales order header table may contain only the sales order and customer number. In this table the sales order is the the Record Key. Then you will have a sales order detail table containing the sales order (which is not the Record Key in this table) as well as a line number for each item on the order. You can link from the sales order table header table to the detail table to via the sales order number. From there you can write queries to tell you things about the data.

Probably more than you want to know, but it's a start for when you look at your tables.

Kimberly

Just to add more confusion to this subject, you can extract the data directly from MS SQL (i.e. the Media Manager database) to Excel.

Just spent 15 minute making this tutorial.



...Jerry

Thanks a lot everyone.

Well, Jacques is right. The dbo.item table contains all the files I have added to MM. The dbo.propertyType table contains the tags I have added.

Unfortunately I still can't find a way in OpenOffice Calc or Base to get at the data. Presumably LibreOffice would be the same. I had another crack at opening a setting up an OBDC/DSN source through Windows Adminstratitive Tools but drew a blank as before. It looks like I would have to buy MS Excel/Access to get further.

Besides wanting to prove that MM data could be opened up to other programs for future portability, I had been hoping to use my MM data as the basis for a spreadsheet of metadata for approx 4000 files that I'm sending to my stock footage rep, instead of duplicating work.

Also would be awesome to be able to get bulk data in to MM via a spreadsheet-style interface, in a much faster way than item-by-item through the MM interface, but I understand the risk of corrupting the whole thing if one doesn't do it on a backup copy of the .medialib file.

It does seem to me that there is a killer, high performance, Vegas media management app here waiting to be written by some bright spark.

Nick:

Can you post one of the tables on MediaFire and I'll see if I can open it? I have Excel and Access

Kimberly

Unfortunately I don't know how to export a table, so I only have a .medialib file as I uploaded before. Do you know how? Maybe you should install MM yourself. If you archive your underwater footage by location and subject etc., it might be very useful for you.

In the meantime I have discovered that I can select a range of cells in the table view in Microsoft SQL Server Management Studio Express, copy them to clipboard, and paste them into OpenOffice Calc. That's a rather crude way of transferring some of the data across. Potentially one might be able to rebuild a database by that method.

Whoops! Sorry, I should read these posts more thoroughly before posting. I missed the fact that you did not have Excel. If you run your query in SQL Server Mgmt Studio, you can right-click anywhere in the results and export these results to a .csv file. That should be open in just about any spreadsheet app.


...Jerry

Nick said:
"In the meantime I have discovered that I can select a range of cells in the table view in Microsoft SQL Server Management Studio Express, copy them to clipboard, and paste them into OpenOffice Calc."

Unfortunately as this is a relational database and one that the user can add tags to you may well find that copying and pasting data from one table into a flat file system will drive you nuts trying to figure it all out.

As a very crude example one table would probably hold clip names and in that table you'll also find a field (column) called something like "ID". The number in"ID" is how other tables link to the clips.
So say you had a tag called "Location". That'd be in a table "Location" and in that table you'd maybe find fields called "ID", "ClipID" and "UserValue".

So to connect the clip name with the location you need to create a join between the "ID" field in the table with the clip names and the "ClipID" field in the Location table. Then from the Location table's UserValue field would be the string with the location text.

Of course this is just a very quick and dirty guess as to how I'd consider designing such a database, many other and more complex schema maybe employed. One clue is to always pay attention to the fields with names that end in "ID". It's only by convention but generally those fields contain the links between tables.

The easiest way I've found to crack someone else's database residing on a SQL Server is to suck everything into Access tables via ODBC and then nut out how it all links together there. One problemo is last time I was paid to do this SQL Server and ODBC did not get along at all well under Win 7/64 forcing me to buy a Win 7/32 license to get started.

Bob.

Okay, I bit the bullet and installed Media Manager. Also, did some fiddling extracting data. Admittedly my recollection of SQL syntax is really rusty, but here's an example of the type of results you can get. Export the data to a .csv and open in your favorite spreadsheet.


...Jerry