Back to Community Threads
1
Convert DB back to SQL
Question asked by Chris - 10/12/2015 at 7:12 PM
Answered
Could you please let me know how I could convert the db back to SQL (MS SQL Server) form the Vista DB?
 

5 Replies

Reply to Thread
1
Cem Alacayir Replied
10/13/2015 at 10:30 AM
Employee Post Marked As Answer
Hi Chris,
This needs to be done manually.
 
1. First, you need to create an empty database in SQL Server and then you need to create the schema of the database (tables vs.). For creating the schema, I recommend letting FileVista do this for you. I am not pasting the full sql script here as the script can change from version to version so it's better to let FileVista create it automatically. Open App_Data\FileVista.config file and empty the value of this setting:
 
  <add key="ConnectionString" value="Data Source=|DataDirectory|Database/FileVista.vdb5;  Open Mode=NonexclusiveReadWrite" />
like this:
  <add key="ConnectionString" value="" />
For newer versions:
  <DbConnectionString>Data Source=|DataDirectory|FileVista.vdb5;  Open Mode=NonExclusiveReadWrite</DbConnectionString>
like this:
  <DbConnectionString></DbConnectionString>

and save the file. When this value is empty, it will force FileVista to run Configuration Wizard (configuration.aspx). You may also need to touch (open/save) Web.config file so the change is recognized. Now when the configuration wizard is opened, in step 2 choose your SQL Server database and enter correct connection details, then click Next. It will tell you if the connection is successful and create the database structure automatically behind the scenes. Now do not complete the last step where it asks for admin user name etc. We need the database to be empty so leave it as it is for now.
 
2. You will now need to import data from your existing VistaDB database (.vdb5 or .vdb4 file). You will use SQL Server Management Studio for this but first you need VistaDB provider installed on your machine. Download a trial version of VistaDB and then install it on the machine that you have the SQL Server Management Studio. This will install and register the necessary provider for you. For .vdb5 file download and install VistaDB-5.8.7.exe and for .vdb4 file download and install  VistaDB-4.3.3.34.exe. After you complete the import, uninstall VistaDB trial version from your machine.
 
3. In SQL Server Management Studio, you will need to repeat this step several times:
 
  • Right click the empty FileVista database that you have created in first step and then click Tasks -> Import Data in the opened menu.
  • Now in the opened wizard, choose VistaDB 5 ADO.NET Provider for .Net 4 as the data source (provider) and then below choose the VistaDB database file (.vdb5 or .vdb4 file) to be imported (in Misc -> Data Source field) and click Next
  • Choose the destination as your empty FileVista SQL Server database and click next.
  • Choose Write a query to specify the data to transfer option and click Next
  • Now paste the below sql statement: 
    select * from [User]
    We can not enter all the sql statements at once as this wizard does not allow it. So we have to do it one by one. Click Next after you enter the statement.
  • Now choose the corresponding table in Destination column. For example, for the above query, you should select [dbo].[User]. Then click Edit Mappings button check Enable identity insert option.
  • Now after you click Next and then Finish, this wizard will import the table (in this case User table). However you need to repeat this step (step 3) for each table, below are all the remaining sql statements that you need to enter each time (in this exact order): 
    select * from [Group]

select * from [RootFolder]

select * from [Membership]

select * from [AccessControlList]

select * from [AccessControlEntry]

select * from [Setting]

select * from [PublicLink]

select * from [Event]
     
0
Chris Replied
10/13/2015 at 7:00 PM
Thanks, the detail in your replies always amazes me.  Looks like the DB provider doesn't support a VistaDBv4 database so I was attempting an upgrade to FileVista 6.8 (from 4.x) first to get to a v5 database.  Below is the error I get during the upgrade, corrupt database?
 
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.
   at GleamTech.FileVista.Updates.Update53.Execute()
   at lambda_method(Closure , Object , Object[] )
   at GleamTech.Reflection.MethodInvoker.‬‎‏‎‬‫‌‪‍‌​‌‎‌‬‌‮‪‪‌‏‍​‌‮.​‭‍‪‍‎‫‍‏‫‫‭‫‎​‍‌‌‏‮‍‭‮‮(Object , Object[] )
   at GleamTech.Reflection.MethodInvoker.Invoke(Object instance, Object[] parameters)
   at GleamTech.Reflection.MethodInvoker.GleamTech.Reflection.IMethodInvoker.Invoke(Object instance, Object[] parameters)
   at GleamTech.FileVista.Web.UpdatePage.ButtonNext_Click(Object sender, EventArgs e)
0
Cem Alacayir Replied
10/13/2015 at 7:25 PM
Employee Post
No, the database does not seem corrupted but it seems some user has a "null" password when not expected (maybe you hand edited the database in the past?) Anyway, I prepared a new build for you to skip this problem, please download 6.8.0.1 build and try. It's sufficient to replace only bin\GleamTech.FileVista.dll as it's the only difference since 6.8.0.0.
 
It's possible that you can continue the update where it left (at v5.3 update step). If you receive errors, then do the update from the start by first restoring the original FileVista.vdb4 file and FileVista.config file to their locations.
 
 
0
Chris Replied
10/14/2015 at 6:34 PM
Thanks for the guidance, I'm updated to the current version and moved to SQL server now.
0
Cem Alacayir Replied
10/14/2015 at 10:31 PM
Employee Post
You are welcome, I am glad it went smooth.
Back to Community Threads

Reply to Thread

Tags

FileVista