Attaching and Normalising Problems in NVivo12

[gdzqzzx]

Hi Johnathan @jschultz ,

First of all, thank you so much for developing this fantastic tool to free data from NVivo.

I am currently trying to extract data from NVivo12 (Windows) for my team. Though nvivotools hasn't supported NVivo12 yet, it seems that current mssqlAttach.bat still works in NVivo12 based on #9 . It's not bothering for me to query MS SQL Server directly by python or any other db management tools.

So here is my dummy question: How to attach npv file to SQL Server manually?

My feeble programming skills can only make me identify the key points are in mssqkTools.py and mssqlAttach.bat, but I'm not sure how exactly nvp file is attached to MS SQL Server. I have tried to attach nvp file directly to MS SQL Server, and got following error:

Msg 5120, Level 16, State 101, Server XXXXXXX\SQLEXPRESS, Line 1 Unable to open the physical file "XXXX.nvp". Operating system error 5: "5(Access is denied.)". Msg 1802, Level 16, State 7, Server XXXXXXX\SQLEXPRESS, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Do I need to convert the nvp file into some other format before attaching it to MS SQL Server?

[jschultz]

Glad you like it @gdzqzzx !

In answer to your questions, attaching the NVivo file to MS SQL Server shouldn't be difficult and you don't need to convert it into another format since .nvp files are in fact just renamed .mdf files. Although you could access the file directly through SQL Server, you would have to deal with NVivo's opaque data structures and miss out on all the hard work I did reverse engineering them!

Based on the error message it seems the SQL Server is unable to access the NVivo file. If you look at mssqlAttach.bat you'll see that it doesn't attach the file directly but makes a temporary copy of it into the directory %TMP%\SQL and (in theory) enables full access for all users to that temporary copy. Are you able to locate the file there and check the relevant permissions as discussed on this page: https://stackoverflow.com/questions/18286765/sql-server-operating-system-error-5-5access-is-denied?

Let me know how it goes and I'll try to work out a fix to the scripts.

[gdzqzzx]

Thanks for your quick response! I will try it and get back to you soon.

[gdzqzzx]

After a whole day testing, I finally attached my nvp file to MS SQL Server 2017.

For those who also want to attach by themselves, here are my experience:

1. Install MS SQL Server 2017 (Basic) + Management Studio (18);
2. Copy nvp file to a temporary folder that all users can access.
   Create a new folder >> Right click the folder >> Click properties >> Click security >> just give FULL CONTROL to all the users/groups you can see!! (Dangerous but convenient)
3. Run management studio as administrator, then you should be able to ATTACH the nvp file to SQL Server.

Note1: If you still can't attach your nvp, it might be still because of authentication problem, add a new group including all users under current account, and give this group full control permission.

Note2: If you can attach your nvp, but the data is shown not found, try to open this file in your NVivo once. Then you can find that the detected database name changes and data can be found.

Note3: I am not sure why I can't start my SQL Server Service after I changed TCP port to 1433. So I have to keep using the dynamic port 2851, which can be connected successfully to my DBeaver.

[gdzqzzx]

Another annoying issue is that I got is grabled columns in dbo.Item and dbo.Project table.
It seems that attributes in dbo.Item are all important, it might be something relate to encoding because I am using a Chinese system, and I still have no idea to fix it.

[jschultz]

Those issues with file and directory permissions should be handled by the helper scripts in NVivotools. It would be good to work out why they aren't.

As for the mangled Name and Description fields, this is fairly simple. NVivo just offsets each character but some fixed value. I forget what it was but if you look in the NVivotools code you'll find it.

But what you probably want to do now is to use NVivo tools to process your NVivo file using the DenormaliseNVP.pyNormaliseNVP.py script.

[gdzqzzx]

I have tried to use NVivotools directly. But first failed in SQL Server setting and then not sure what parameter I should set. I will try it again and see how it goes.

[jschultz]

@gdzqzzx Did you mean to close the issue?

[gdzqzzx]

Yes, because the problem has been solved and actually it's not that relevant to NVivo improvement.

I think it might be good to open another issue if I have any other problems when using NVivotools -- of course, feel free to keep it opened if you want.

[jschultz]

Did you work out why the helper scripts didn't fix the file access problems?

[gdzqzzx]

Yes, but not all of it, to some extent.

What I fixed 2 days ago is attahing nvp file to SQL Server manually without using NVivotools. Before that, I tried mssqlAttach.py as python mssqlAttach.py -P 2851 -i EXPRESS "xxx.nvp" ntool_test and got following error:

Traceback (most recent call last):
  File "mssqlAttach.py", line 54, in <module>
    mssqlAttach(None)
  File "mssqlAttach.py", line 49, in mssqlAttach
    verbosity = args.verbosity)
  File "...\mssqlTools.py", line 74, in __init__
    raise RuntimeError('No suitable SQL self.server self.instance found')
RuntimeError: No suitable SQL self.server self.instance found

And I just tried running mssqlAttach.bat directly as mssqlAttach.bat "xxx.nvp" ntool_test SQLEXPRESS. It works fine this time.

But some import error occured when I ran DenormaliseDB.py, I will open a new issue for that.

[gdzqzzx]

I have tested mount function in NVivo.py. It seems that the instance variable somehow wasn't passed correctly into mssqlTools initialization. (Of course, the SQL Server version has
been fixed as 'MSSQL14')

I got RuntimeError when I used NVivo.mount(), while mssqlAPI.attach() worked fine separately.

@jschultz Please let me know if you want to do more testing on this issue.

(NormaliseDB.py hasn't worked for me yet, but it seems that it's because I haven't set up the output DB properly. I will test it later.)