Result Processing Database Schema

[hlim]

Hello everyone,

I have a question on logging the teststep results into the database. I am using the Generic Recordset as my database for data logging.

In my database (SQL Server), there exists the following schemas: dbo, dbtest1, dbtest2. I created all the result tables (UUT_Result, Step_Result etc...) of the Generic Recordset in all the schemas. Therefore, to see the results in Step_Result table, I specify the schema along with the table name, e.g. SELECT * FROM dbtest1.Step_Result.

Is there a way to tell Teststand to log the result to different schema (dbo, dbtest1, dbtest2) programmatically? 

Yours sincerely,

hlim

[drakhri]

You can accomplish this by using a different user for each db schema.

You will need to setup your database and teststand in order to do what you want:

Database setup:

You will need to create a user for each schema you want to use. After they are created you can use the following query to set the default schema of that user:

ALTER USER <user> WITH DEFAULT_SCHEMA = <schema name>;

Teststand setup:

I suggest using the tool that comes with Teststand to generate your connection string:

Configure -> Results processing -> Database Options button -> build

Configure your connection to your database. Make sure Allow saving password is checked. Click OK

Copy the connection string expression without the double quotes.

Use DatabaseOptions callback to modify the ConnectionString:

Parameters.DatabaseOptions.ConnectionString = "\"<connection string here>\""

You will want to change the User ID and Password programmatically inside the connection string to match the schema you're going to use.

Database -> Set default schema for each user so that the user name determines the schema used.

Teststand -> Set db user name depending on which schema you want to log to.

[hlim]

Hello drakhri,

thanks for your information.

From what you said, I gather that when a database user is created, it is automatically associated with a schema. If a schema is not specified, it will be associated with a default schema, which in SQL server is: dbo. Is this right?

And from your implementation idea, it is programmatically able to use different schema by logging to the database with different user (through the connection string in DatabaseOption callback).

That's a great suggestion!

Yours sincerely,

hlim

[drakhri]

I believe dbo is the default if you don't specify. 

Something else you can do is have a different schema used for each database with a single user by setting up User Mapping on your login names. 

If you have SSMS it's located at:

Server -> Security -> Logins -> right click properties -> User Mapping

[hlim]

Hello drakhri,

Thanks for sharing your knowledge.

You mean using the user mapping to 'map' a user to a schema via SSMS, rather than a SQL statement right?

Is it possible to allow set up a DB user to access more than one schema in a database? I tried to use the user mapping method that you mentioned to add 2 schemas to a user but it won't work.

Yours sincerely,

Huck Lim

[drakhri · Accepted solution]

Yeah, using the tool is easier to visualize what SQL is doing. A user can access as many schemas as they have permission for. The problem is that in order to specify the schema you need to set the notation on your SQL commands. I don't know of an easy way to do that in Teststand.

If you're manually creating SQL commands the notation is:

[Database].[Schema].[Table]

It's easier to set up multiple user names and make use of the default schema in order to set it. 

You could also have a different database for each of your logging categories (and not have to use schemas at all). 

Lots of good infomation here:

https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

[hlim]

Thanks for sharing your great knowledge with me. I accept your answer as solution.

Best Regards,

hlim