Hi,
I´m using Toad for Sybase 1.2 Freeware.
When i go to database explorer to show the colums of table it shows the following error:
Sybase Databasse Error
Invalid column name ‘encrkeyid’.
My database version is:
Adaptive Server Enterprise/12.5.3/EBF 13055 ESD#5/P/Sun_svr4/OS 5.8/ase1253/1939/64-bit/FBO/Tue Dec 6 09:13:20 2005
How can i fix this error?
I think is related to system table sysencrypt but this version doesnt have this table.
Thanks
Jose Luis
Hello Jose,
Could I ask which version of Sybase Freeware you are using?
It seems this problem has been fixed in the latest release. Please try the latest one and you can download from here:
http://toadworld.com/Freeware/ToadforSybaseFreeware/tabid/814/Default.aspx
Thanks,
Giraffe.
Hello Giraffe
thanks for your answer.
The vcersion of Toad is: ToadSybase 1.2.0.637
and the Sybase DataBase is:
Adaptive Server Enterprise/12.5.3/EBF 13055 ESD#5/P/Sun_svr4/OS 5.8/ase1253/1939/64-bit/FBO/Tue Dec 6 09:13:20 2005
I found this in a PDF from Sybase:
“encrkeyid in sysencryptkeys matches the id column in sysobjects.”
Because my version of database doesn´t have the table sysencryptkeys Toad can´t do the query to show the colums of a table in Database Explorer
Hello Jose,
I am sorry I cannot reproduce this because we have no 12.5.3 installed here but I try to do it in 12.5.4 instead. There is also no system table sysencryptkeys existed in 12.5.4, but I can view column details of one table and get no error like you did.
I think this is because the difference in system table syscolumns between 12.5.3 and 12.5.4. I notice column encrykeyid exists in syscolumns in 12.5.4 and I assume it does not exist in 12.5.3, which I think maybe the reason why you get that error.
Of course we would investigate further then try to give you solution later.
Thanks,
Giraffe.
Hello again Giraffe
You´re right.
There is no encrykeyid column in table syscolumns in my database (12.5.3). It seems version 12.5.3.A and beyong have this column but I can´t update de database server.
I fountd in TOAD Menu Tools->Options->Database->SQL Dictionary->Db.CommonSql an option to change DescribeTableColumns entry from version IQ:12.7,0 to Sybase:12.5.3 or Sybase:11.5.0 but I press Apply Button and OK button and it doesn´t work. When I back to the entry it remains with IQ:12.7,0 selection.
Hello,
finally I found the solution for my version of database (Adaptive Server Enterprise/12.5.3/EBF):
In TOAD You have to go to
Tools Menu ->Options->
Database->SQL Dictionary->
Db.CommonSql -> DescribeTableColumns
Select Sybase:12.5.3 in selection combo and modify the query with the following sql:
select
sc.name as Column_Name,
sc.colid as Column_ID,
convert(bit, sc.status & 8) as Nullable,
convert(bit, sc.status &128) as Is_Identity,
case when sc.usertype = -1 then sxt.xtname else st.name end as Datatype,
case
when sc.usertype = 34 then sc.length/2
when sc.usertype = 35 then sc.length/2
else sc.length
end
AS Datatype_Length,
sc.prec as ‘Precision’,
sc.scale as Scale,
convert(bit, case when st.name in (‘char’, ‘nchar’, ‘varchar’, ‘nvarchar’, ‘binary’, ‘varbinary’, ‘unichar’, ‘univarchar’) then 1 else 0 end) as variable,
convert(bit,
case
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,1) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,2) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,3) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,4) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,5) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,6) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,7) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,8) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,9) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,10) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,11) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,12) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,13) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,14) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,15) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,16) = sc.name then 1
else 0
end) onPrimaryKey,
convert (bit, isnull(sc.status2, 0) & 128) as encrypted,
convert (bit, case when st.usertype > 100 then 1 else 0 end) usertype,
scmt.text as DefaultValue,
scmt2.text as CheckConstraint,
0 as DefaultValueType,
‘’ as EKUser, --su.name as EKUser,
‘’ as EncryptionKey, --seo.name as EncryptionKey,
‘’ as BindValueName
from
syscolumns sc,
sysobjects so,
systypes st,
sysxtypes sxt,
sysindexes si,
syscomments scmt,
syscomments scmt2–,
–sysobjects seo,
–sysusers su
where
sc.id = so.id
and sc.usertype = st.usertype
and so.name = {{StringQuote(?)}}
and so.uid = user_id({{StringQuote(?)}})
and so.id *= si.id
and sc.xtype *= sxt.xtid
and si.indid > 0
and convert(bit, si.status & 2048) = 1
and sc.cdefault *= scmt.id
and sc.domain *= scmt2.id
and charindex(‘check’, lower(scmt2.text)) = 1
–and sc.encrkeyid *= seo.id
–and seo.uid *= su.uid
order by sc.colid
Press Apply and Ok buttons and the error is gone.
Now database explorer and intellisense in editor works fine.
Thanks for the support
Jose Luis
Hello JLRM,
Future versions of the application will no longer reference the encryption key fields when listing tables or columns. So this error should no longer occur in future betas or releases.
Thanks.