Database logning stored procedures problems

[Bojer]

Hi All.

I have a problem with the schema for MSSQL server and stored procedures, it is working when the database is set for GUID (uniqueidentifier), but I dont want to use that data type because the index's is not working then. Then I try to change the GUID to a bigint and turn on identity on the filed (autogenerate number), and it is still the primary key.

But I can't get it to work, I have changed the scheme so the direction to "return value", and changed the primary key type to "Auto Generated/Counter".... that the setting I believe should work.... I have tried to drop the database and make one again from the schema..... but as I can see it the it still try to write ID to the database...(I can see that in the stored procedure)

I have tried to change the stored procedure, so I removed the write of the ID and made a "select " so it also retrieve the new ID.... but nothing seems to help...

So is the anyone having a schema that stills work with stored procedures and a number as primary key... then please upload the stored procedure and the schema.....

Kind regards Thomas

[TerryS]

Hi Thomas,

How exactly is it not working?  Is TestStand throwing an error when it tries to log to the database or is it not logging as you are wanting it to?  Are you doing On-the-Fly Database logging?

Lastly, can you send two screenshots, one of the Statements Tab under Database Options and another of the Database Viewer?

[Bojer]

Hi Terry

I have tried many things.... I think I have tried all combinations in the schema, and changing in the stored procedure.... 🙂

I have attached my settings as I think they should work.

What I cant figure out is that when you have ID... then you have to set a direction.. but what to set it for... It's not a "Input" because it is generated by the database... then you can set it to "Output" or "Return Value".. this should properly be used when you change the stored procedure.... But when you set the ID to Primary key and choose "Auto Generated/Counter"... then as I understand then it automatic receive the new ID from the column.....

The best solution that I found is to still set the ID as input, but I have removed the value in the stored procedure, so it is not written in the "insert into... "

I also tried to change the primary key to "Get Value From Return Value" and then change the stored procedure so it return @@identity..... but that is also trowing an error...

Is there more information about logging features.. I think there is a lack of information in TS help, and also in TS reference manual..

/Thomas

[Bojer]

Hi Terry...

Please comment my last post....

/Thomas

[TerryS]

Hi Thomas,

If you change ID to be a return value, then you also need to reflect this in the Stored Procedure.  It is currently still an input in the Stored Procedure. 

Are you receiving error messages when trying to log to the database?  If so, can you post the error message.

[Joely5]

Hi

I have a problem with TestStand Logging to Database SQL SERVER. I changed type of ID for UUT_RESULT as a BigInt auto genereated value. When i tried to log to datbase with schema stored procedure i have nex error:

An error occurred calling 'LogResults' in 'ITSDBLog' of 'zNI TestStand Database Logging'
An error occurred executing a statement.
Schema: Copy of SQL Server Stored Proc
Statement: UUT_RESULT.
Description: Procedure or function 'InsertUUTRESULT' expects parameter '@pID', which was not supplied.
Number: -2147217904
NativeError: 201
SQLState: 42000
Reported by: Microsoft OLE DB Provider for SQL Server

Source: TSDBLog