NI TestStand

cancel
Showing results for 
Search instead for 
Did you mean: 

SQL database schema issues: Generic vs. SQL Stored Proc

Solved!
Go to solution

Hello,

 

  I am currently using a Microsoft SQL Server 2014 Express server to host a database that I am using to store TestStand results. When I use the "Generic Recordset" schema to setup the database, everything seems to work fine....no issues and the TestStand "Validate" button reports no errors or warnings. 

 

However, when I use the "SQL Server Stored Proc" schema to setup the database I run into the following issues:

1) When I run the .sql file generated for the schema I get the following error:

Msg 1767, Level 16, State 0, Line 237
Foreign key 'PROP_MULTINUMERICLIMIT1_PROP_RESULT_FK' references invalid table 'PROP_RESULT'.
Msg 1750, Level 16, State 0, Line 237
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 279
Foreign key 'PROP_MULTINUMERICLIMIT2_PROP_MULTINUMERICLIMIT1_FK' references invalid table 'PROP_MULTINUMERICLIMIT1'.
Msg 1750, Level 16, State 0, Line 279
Could not create constraint or index. See previous errors.

2) When I run the Scheme validation tool in TestStand:

Validate_StoredProcSchema.jpg

 

I have tried seting up the database using 2008 and 2012 compatibility and used the SQL native client 10 and 11 in TestStand. These settings do not seem to change the issues I am seeing.

 

SOOOO...my question really is, what does using the stored proc schema gain me over using the generic recordset schema? Are the gains worth figuring out this issue? Also, are there any database experts that might be able to help me identify how to modify the TestStand generated .sql to correctly setup the database for using the stored proc schema? Ok, so that's three questions.

 

Any help would be appreciated,


Corey Rotunno

0 Kudos
Message 1 of 13
(5,519 Views)

Corey -

There are known limitations in the feature for generating SQL files from a schema. If you want SQL files that match the default "SQL Server Stored Proc" schema, use the following file that is installed by TestStand:

<Program Files>\National Instruments\<TestStand>\Components\Models\TestStandModels\Database\SQL Server Create Stored Proc Result Tables.sql

 

Other SQL files exist in that same directory for other schemas.

 

The main downside to using the 'Generic Recordset' schema, is that it can slow down some when used in a single execution that runs 24/7 because the recordset that the connection to the database is managing increases in size.

 

The main downside to not using 'Generic Recordset' schema is that the other schemas use GUIDs for primary key values, where the 'Generic Recordset' schema uses an identify key which uses less memory.

Scott Richardson
Message 2 of 13
(5,443 Views)

Scott,

 

  Thank you for reply. The strengths and weaknesses you identified were helpful in guiding me to more reading on the subject. Being that I do not have any executions that will run for more than a few hours I think I can get away with the "Generic Recordset" if I had to. However, the SQL Server Create Stored Result Tables.sql you referenced does not seem to work either. Actually, it resulted in more errors than using the .sql file generator tool in TestStand. The only table that .sql was able to generate seems to be "UUT_RESULT". None of the other tables populated. The error is pasted below:

 

Msg 102, Level 15, State 1, Line 51
Incorrect syntax near '/'.
Msg 1767, Level 16, State 0, Line 55
Foreign key 'STEP_SEQCALL_FK' references invalid table 'STEP_RESULT'.
Msg 1750, Level 16, State 0, Line 55
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 66
Foreign key 'PROP_RESULT_FK' references invalid table 'STEP_RESULT'.
Msg 1750, Level 16, State 0, Line 66
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 82
Foreign key 'PROP_BINARY_FK' references invalid table 'PROP_RESULT'.
Msg 1750, Level 16, State 0, Line 82
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 93
Foreign key 'PROP_NUMERICLIMIT_FK' references invalid table 'PROP_RESULT'.
Msg 1750, Level 16, State 0, Line 93
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 109
Foreign key 'PROP_ANALOGWAVEFORM_FK' references invalid table 'PROP_RESULT'.
Msg 1750, Level 16, State 0, Line 109
Could not create constraint or index. See previous errors.
Msg 1767, Level 16, State 0, Line 125
Foreign key 'PROP_DIGITALWAVEFORM_FK' references invalid table 'PROP_RESULT'.
Msg 1750, Level 16, State 0, Line 125
Could not create constraint or index. See previous errors.

 

Am I running into limitations of the "Express" version of SQL server? If so I can switch to the "Standard" version, I was just using Express to learn with. Once the database becomes network hosted it will be the Standard version.

 

EDIT: I was able to make a small edit (comment syntax was wrong on two lines) to the .sql file and it looks like my tables were created....or at least some of them. When I run the Validation tool I now get the following warnings:

 

SQL_StoredProcWarnings.JPG

 

 

Thanks,


Corey Rotunno

0 Kudos
Message 3 of 13
(5,426 Views)

Corey -

Line 51 contains a comment and syntactical the tool you were using to execute did not understand it. Since the STEP_RESULT table failed to create, all subsequent references will fail, and this will cascade. Where did you execute the .SQL file?

 

The standard MS SQL comment prefix is --. In TestStand 2016 we converted most of the "//" comments to "--", but it looks like we missed a few.

 

If you change // to -- on appropriate lines, it should work in the tool you are using.

 

I have written a bug tracking issue to change the // comments to --

Scott Richardson
0 Kudos
Message 4 of 13
(5,402 Views)

Scott,

 

  I did fix the syntax error on line 51, as noted in the "edit" of my last post. This cleared up all of the errors resulting from running the .sql file. However, the screen shot of the schema verification tool is after I made the fix. Maybe there is something I just do not understand. The warnings indicate numerilimit1 and 2 as well as multinumericlimit1 and 2 are missing. However, when I look at the generic recordset schema those tables are part of the schema but aren't actually there in the database viewer. Are these some sort of virtual tables that don't actually exist? Maybe I shouldn't worry about the warnings?

 

And, I am running the .sql file in "Microsoft SQL Server Management Studio".

Thanks,


Corey Rotunno

0 Kudos
Message 5 of 13
(5,398 Views)

So, I ran the .sql in the database viewer that comes with TestStand....same result. At this point I am wondering if there is some legacy stuff left from past versions of TestStand that don't apply anymore. All schemas list the following tables that do not actually show up in the database viewer after the database is created. Reading through the .sql files there also are no sections related to creating said tables:

1. STEP_NUMERICLIMIT1

2. STEP_NUMERICLIMIT2

3. PROP_MULTINUMERICLIMIT1

4. PROP_MULTINUMERICLIMIT2

5. PROP_IVIWAVE

6.PROP_IVEWAVEPAIR

 

So, at this point I might just give up on the stored proc schema, or ignore the warning and hope they won't cause any problems in the future. I have never seen the "missing" tables that the generic recordset schema lists and that has been working fine. Perhaps the stored proc is the same way.

 

Thanks,


Corey Rotunno

0 Kudos
Message 6 of 13
(5,392 Views)

I am not 100% sure if this pertains to your issue or not. The schema for some reason is trying to make two prop result tables and two numeric limit tables.  You only need one of each.  So comment out at least one of them from prop and the numeric limit.  Let me know how that works.  That is what I had to do to get mine to work.

0 Kudos
Message 7 of 13
(5,358 Views)

Sorry. correction.  Get rid of step_numericlimit2 and get rid of prop_multinumericlimit2.  Comment them out

 

 STEP_NUMERICLIMIT1

2. STEP_NUMERICLIMIT2

3. PROP_MULTINUMERICLIMIT1

4. PROP_MULTINUMERICLIMIT2

5. PROP_IVIWAVE

6.PROP_IVEWAVEPAIR

0 Kudos
Message 8 of 13
(5,356 Views)

newBegin,

 

  There is nothing to comment out. The .sql does not contain any statements to create those tables. If you look at the schema tab under "database options" the schema description lists those tables, but I have never seen them in the actual database. My theory is that those tables used to be in the schemas in a prior version of TestStand but aren't anymore and their appearance in the schemas descriptions are in error, as are the procedures that reference them in the stored proc schema. I would love to know if I am right or wrong.

 

SchemaTables.JPG

I have run all of the available test step types at one point or another and the results seem to fit into the database just fine without any of those tables i listed (this is using the genericrecordset, I do not have much experience with the stored proc schema yet).

 

Thanks for the reply,


Corey Rotunno

0 Kudos
Message 9 of 13
(5,354 Views)

Ok.  I see that that you are having a problem with the stored procedure.  I am not going to mislead you in anyway and I am going to tell you right now that I don't have the exact answer for you.  Hopefully someone who has worked with the stored procedure will reply to this and help.  That begin said, I can give you what I think might be issues with the stored procedure.  These are just theories.  But may be worth thinking about.

 

1.  Based on what I have learned with the sql query export tool in teststand, it don't think it runs and creates the SQL statements in "pure SQL".  Lack of a better term..  It creates it in MySQL or transact SQL or something like that.  So there was a case where I exported the SQL statements from teststand and tried to run them in SQL server and there were a whole bunch of errors that I had to correct because the syntax wasn't quite right.  So that could be one of the problems.  Teststands stored procedure could have a bunch of errors that you are not aware of.  If you somehow export the SQL procedure and try to paste the statement directly into SQL server query and try to run it, I bet there will be errors.  That is my first theory.

 

2. Next, I am pretty sure I am correct about the two numeric limits tables and the two prop tables. There can only be one of them because the stored procedure has errors because there are duplicate key issues going on.  This could be another problem.  I had to correct that when I created my teststand database.

 

Anyway, I know I did not give you the answer and I know my theories are very vague, but at least I think I am giving you an idea of why you might be having problems. I hope it helps in some little way.  I ended up steering away from using the stored procedure and I used the MySQL schema instead.  But I had to create the tables in SQL server myself in order for it to work.

0 Kudos
Message 10 of 13
(5,327 Views)