[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

SQL Server SysColumns.Name from Deplhi query

I am trying to get the name of a the identity colum in a table on SQL Server 2005, using Delphi 7

I issue this query

select * from SysColumns where ID = 1109578991 and Status = 128

I ge the right record from SysColumns, but it does not include the Name field, or any other field of type sysname(nvarchar(128))

How do I get around this problem ?
Is there another way to find the name of the Identity column?
0
dcl
Asked:
dcl
  • 6
  • 5
1 Solution
 
sas13Commented:
select object_name(id), name from syscolumns (nolock) where COLUMNPROPERTY(id, name, 'IsIdentity') = 1
0
 
dclAuthor Commented:
Thanks for the suggestion

I tried it and it worked in the SQL Server Management Studio, but not in my Delphi queries.
In Delphi I got a whole lot of records with no fields.

I forgot to mention that I am using normal TQuery objects via an ODBC connection - could that be a problem?
0
 
sas13Commented:
Can you show me your code from Delphi?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dclAuthor Commented:
Hi again

this is my code

select Name from SysColumns where ID = 1109578991 and Status = 128

(I already have the logic to get the table ID)
I have just tried it using ADO components instead of TQuery & it works perfectly
However if I could get the BDE/ODBC combination working it would save me a lot of trouble
0
 
sas13Commented:
i'm run your query from sql explorer. i create MSSQL BDE connection and its work fine.
which of ODBC drivers are you use? And which type of OS?
0
 
sas13Commented:
try to use:
select Name from master.dbo.SysColumns where ID = 1109578991 and Status = 128
0
 
sas13Commented:
maybe your Server 2005 installed with collation which sensitive to register of keyboard?

select name from syscolumns where id = 1109578991 and status = 128
0
 
dclAuthor Commented:
I have used SQL Server & SQL Native Client ODBC drivers - neither work.
I am runninq Windows XP Pro, Delphi7 Professional

I don't have the choice of MSSQL as a BDE connection type.
My choices are STANDARD, INTRBASE, MSACCESS, & then all the ODBC drivers
0
 
dclAuthor Commented:
Do you mean using lower case etc?

I know all of the query is working, except it is not return the name field
If I "select * ..." I get all of the fields except for "Name", & a few others, all of the same datatype as Name
0
 
sas13Commented:
try this:
select cast(Name AS varchar(2000)) as name from SysColumns where ID = 1109578991 and Status = 128

i think that your driver ODBC is not support NChar datatype
0
 
dclAuthor Commented:
Thanks for your help
That is just the solution I needed

FYI I changed the cast from 2000 to 50 chars.
2000 produces a memo field, and 50 takes it bak to a text field

Thanks again
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now