Errors in TestStand 4.1.1 SQL Schema

[Brian_Esque]

I'm trying to create a new MSDE 2005 SQL Server database using the new TestStand 4.1.1 schema. I have used the SQL Server Stored Procedure to build a .sql file and then run this SQL file. I am getting several errors similar to the following:

> CREATE TABLE STEP_RESULT ( ID uniqueidentifier PRIMARY KEY, UUT_RESULT uniqueidentifier, STEP_PARENT uniqueidentifier, ORDER_NUMBER int, STEP_NAME varchar(255), STEP_TYPE varchar(255), STEP_GROUP varchar(32), STEP_INDEX int, STEP_ID varchar(32), STATUS varchar(255), REPORT_TEXT varchar(255), ERROR_CODE int, ERROR_MESSAGE varchar(255), CAUSED_SEQFAIL bit, MODULE_TIME float, TOTAL_TIME float, NUM_LOOPS int, NUM_PASSED int, NUM_FAILED int, ENDING_LOOP_INDEX int, LOOP_INDEX int, INTERACTIVE_EXENUM int, CONSTRAINT STEP_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES (ID))
Command failed with the following error...:
Line 1: Incorrect syntax near '('. (-2147217900)

I have tried to run this statement in both MSDE and through the TestStand database viewer with similar results. Is anyone else experiencing this problem or have any ideas?

[Gist]

Hello bce,

I believe the issue may be with your last parameter, which neglects to identify the table you are referencing.  If you instead use CONSTRAINT STEP_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES UUT_RESULT(ID) for this parameter, I believe the issue will be resolved.

[Brian_Esque]

Matt,

Thanks for the help. The SQL I am using is the schema generated by TestStand 4.1.1 with no modifications. Your suggestion fixed this one instance, but there are many instances of errors generated by the new SQL file. Is there a list of the fixes available?

[Scott_Richardson · Accepted solution]

The feature to generate the SQL files from a schema does not always generate perfect SQL statements, but get you closer to where you need to be than trying to create it from scratch. Have you considered looking at the default SQL files included with the product, specifically the "SQL Server Create Stored Proc Result Tables.sql" file located in the "<TestStand>\Components\Models\TestStandModels\Database" directory? This is a file that should work out of the box for the default schemas for SQL server and MSDE. There are other SQL files for the other default schemas, and there are older SQL files for previous releases in the "Previous Schemas (2.x-4.0)" subdirectory.

Message Edited by Scott Richardson on 01-10-2009 12:42 AM

[Brian_Esque]

Scott, thanks for the suggestion. I was copying the Stored Procedure schema in teststand, and assumed the Build SQL function would work on an unmodified NI schema. Using your suggestion, the database appears to have been created properly. Thanks, I will use this process moving forward.