Microsoft SQL Server

instance / database / schema / object

login / user / schema (dbo)

sequence

 

Collation

PSM: Both Instance and DB need case insensitive

Windchill: Both Instance and DB as SQL_Latin1_General_CP1_CS_AS (Since 2012, it is Latin1_General_100_CS_AS_SC)

Cognos: DBs need case insensitive, can share same instance as Windchill

Upgrade:

• To  10.2 on SQL Server 2012 or 2014
  • Upgrade firstly with SQL Server 2008 R2
  •  and then convert - https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS115367
  • To 10.1 on SQL Server 2012
    • Still use SQL_Latin1_General_CP1_CS_AS as before.

About Microsoft SQL Server Collation settings in Windchill

• Instance/Server Collation

• Database Collation

Instance/database

• Instance

1. “Empty” means default Instance Name of MSSQLSERVER

1. Can get from Service

1. Or run query below –again NULL means default of MSSQLSERVER

SELECT SERVERPROPERTY('InstanceName')

Login/user/schema

 

• Here Instance > Security > login show the Instance Login

1. Login user can access Instance/Server, but cannot access Database/Tables

• Database User who map to a Instance Login and define a Default Schema

• Why SQL select * from wind.wtuser does not work (small case of wtuser)?

wtuser needs to be WTUser

• Why SQL select * from  WTUser does not work (without wind.)?

The user’s default schema is not wind

• What is the default schema?
  dbo

Transaction log

• Windchill down due to database full

1. Option #1:

• Forget backup log
  • https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS41551

1. Reason #2:

• Windchill code problem leads to log full
  • https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS41551
    
    
  • https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS147735

Sequence

 

• Sample  create_QueueEntry_Table.sql

1. Create table for sequence

CREATE TABLE entryNumber_seq (dummy CHAR(1), value BIGINT      IDENTITY(1,1))

go

1. Create the procedure to get/generate the sequence

CREATE PROCEDURE wt_get_next_sequence_entryNumber

AS

INSERT entryNumber_seq (dummy) VALUES ('x')

RETURN SCOPE_IDENTITY()

go

Note: the procedure changes to as below since 10.1, and then leads to CS128703

CREATE PROCEDURE wt_get_next_sequence_entryNumber

    @returnValue BIGINT OUTPUT

AS

INSERT entryNumber_seq (dummy) VALUES ('x')

SELECT @returnValue = SCOPE_IDENTITY()

go

1. Retrieve the sequence

DECLARE  @return_value int

EXEC     @return_value = [wind].[wt_get_next_sequence_entryNumber]

SELECT   'Return Value' = @return_value

GO

• How to break the IDENTITY temporarily for some reason, e.g. configure to a BIG value to avoid ID confliction?

1. Two methods

SET IDENTITY_INSERT wind.entryNumber_seq ON

Insert into wind.entryNumber_seq(dummy, value) values ('x', 99999)

SET IDENTITY_INSERT wind.entryNumber_seq OFF

GO

OR

DBCC CHECKIDENT('entryNumber_seq', RESEED, 100014)

GO

• How to verify sequences, like “select * from user_sequences” in Oracle?

SELECT TABLE_NAME,IDENT_INCR(TABLE_NAME) AS IDENT_INCR, IDENT_CURRENT(TABLE_NAME) as IDENT_CURRENT

FROM INFORMATION_SCHEMA.TABLES

where IDENT_CURRENT(TABLE_NAME)is not null

order by TABLE_NAME;