Static Data File Causes 'Non-Schema Statement Was Ignored'

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Static Data File Causes 'Non-Schema Statement Was Ignored'

Postby isme » Tue Jun 12, 2012 2:27 pm

I have a project to compare SVN with my local development database, in order to synchronize my development environment with the last changes in SVN.

I clicked 'Compare Now' to compare the two schemas. SQL Compare presented me with the 'Error Parsing Scripts' dialog, filled with 'Non-schema statement was ignored' warnings.

I can click 'Continue Without Resolving Errors' to safely ignore these errors, but they appear every time I make a comparison using the project. I would to solve the cause of the warnings instead of ignoring them.

SQL Compare raises one warning for every insert statement in a file stored in the Data directory of my database's repository. The Data directory was added by SQL Source Control when the repository was initialized.

The file represents static data held by a table whose definition is stored in a file in the Tables directory of the database repository. It consists of a sequence of insert statements generated by a regular expression from an external data source. The file was added to the Data directory using TortoiseSVN.

I don't understand why SQL Compare is reading from the Data directory at all, since it only compares database schemas, and not the data they contain, static or otherwise.

In any case, how can I stop these warnings from appearing? Do I have to commit the static data using SQL Source Control?

My SQL Compare version is 10.1.0.102.
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby peter.peart » Thu Jun 14, 2012 7:08 pm

Thanks for your post and sorry you're hitting this. What happens if you re-create the project file, do you find that the warnings still appear at all?

Pete
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby jeromedayton » Mon Jul 02, 2012 1:50 am

I used Sql Compare to create a scripts folder. I then used Sql Data Compare to cause a "Data" folder to be added to that scripts folder. Now when I do a Sql Compare, it tries to compare the contents of that Data folder leading to the following warnings:

Non-schema statement was ignored.

Is there a way to get SQL Compare to ignore the Data directory?
jeromedayton
 
Posts: 5
Joined: Thu Sep 17, 2009 10:39 pm

Postby isme » Fri Jul 06, 2012 12:29 pm

Thanks for your reply, peter.peart. I would have got back to you sooner, but I wasn't receiving alerts for replies to my post.

The warnings appear when I re-create the project file. I follow these steps reproduce the issue:

    Start SQL Compare.

    Create a new project.

    In the Data Sources tab, configure Source like this:


    And configure Target like this:

      Server: (local)
      Windows Authentication
      Database: ExampleDatabase

    In the Options tab, uncheck all options except 'Whitespace'.

    Run the comparison.


During the 'Registering data sources' step, the 'Error Parsing Scripts' dialog appears:

Image
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Tue Aug 14, 2012 10:21 am

You can use Red Gate's SQL Source Control plugin for Management Studio to fix this:

1) Remove (svn delete) the static data file from version control.

2) In Object Explorer, right-click on the table containing the static data, and choose 'Other SQL Source Control Tasks', and then 'Link/Unlink Static Data'.

Image

3) A list of tables should appear with a check next to your static data table. Click 'Save and Close'.

Image

If no check mark appears, you may have to add a primary key to the table first.

4) Go to the main SQL Source Control tab and view the Commit Changes tab. Commit the new 'Data Link' change type to check in the static data.

Image

After the commit succeeds, your data will be under version control as before. Additionally, changes to the data will be automatically detected by SQL Source Control and it will prompt you to commit these data changes.

And, of course, SQL Source control will no longer issue 'Non-schema statement was ignored' warnings!

At the SQL In The City conference in London last month, I had the opportunity to discuss this directly with one of the developers. He explained that the data file set is defined not by the content of the Data directory alone but also by the <DataFileSet> section of the file RedGateDatabaseInfo.xml. Any file in the Data directory not also listed in this XML file will be parsed as a schema file instead of a data file. Thanks for your help, Red Gate!
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests