Retrieval Of The Description Of A Field In A SQL Server 2000 Based Table

Jun 1, 2006

I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.

I have an example table with the following structure:

Table Name: Autioneer's (translated from Portuguese)

'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code

'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name

'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number

I need a way to programatically extract the Fields Description Property from the table

example sintaxe:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'

Thanks for any help that can be offered here
Neil Ramkeerat.

Replies can be sent to neilramkeerat@hotmail.com

View 3 Replies


ADVERTISEMENT

Description Field In Subscriptions Table

Apr 10, 2007

The Description field in SQL Server 2005 Reporting Services just defaults to "Send e-mail to email@domain.com" (obviously a dummy e-mail address) when you create an e-mail subscription. You cannot edit the Description when you select "Edit" beside a subscription.



The Description may be edited directly in the Subscriptions table under the ReportServer database but this is not end-user friendly.



I thought of using an add/update trigger to set the Description to the "Subject" out of the Extension Settings field. The Extension Settings field is XML so you would have to parse the Subject out of there first. The "Subject" is the e-mail Subject for the e-mail that is sent to the subscriber and it much more adequately describes the report.



My boss understood what I wanted to do but wanted to make sure in the industry literature that the product was not that lacking. Hard to believe that you cannot directly edit the Subscription Description through the web browser interface - would love for someone to prove me wrong.

View 5 Replies View Related

Get Field Description From SQL Server 2005

Jan 12, 2007

 
I have SQL Server 2005 Epress and I want to get the field's dscription..
 
 

View 1 Replies View Related

How Can I Get The SQL Server 2000 Column Description

Feb 9, 2007

How can I get the SQL Server 2000 Column Description within ADO.NET 2.0?

BR / Chris

View 3 Replies View Related

Retrieving The Error Description In SQL SERVER 2000

Feb 25, 2008

Hello All,

I am stuck at a place in SQL SERVER 2000.

I have created stored procedures and I am checking whether any error has occured on execution of the statements in that procedure.

If the @@Error <>0 then I need to log this error into my error logging table.
For this, I need to retrieve the error description given by SQL SERVER 2000.

I tried this using the master.dbo.sysmessages table. But I get a text from master.dbo.sysmessages which has the placeholders like %l, %s, etc.


I dont want this type of error. I want the exact error description which has the actual objects names and not the placeholders.

I found some help at this link : http://www.sommarskog.se/error-handling-I.html#textretrieve

But i want to know whether there is any other way of doing this or not.

Thanks,
Kapadia Shalin P.

View 3 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related

Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria

Jan 22, 2008

Hello Friends,

I have two tables, And also I have Sample data in them.

create table X
(y int,
m int,
v int)

insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1

select * from X

Create table Y
(fy int,
fm int,
v int)

insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0

select * from X
select * from Y

I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m

Using temporary table cannot be done.

Thanks in Advance,
Babz

View 1 Replies View Related

Transact SQL :: Update A Field In Table Based On Another Table

Nov 17, 2015

I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)

I want to update table1 and set field2Update as somevalue where table2.Flag <> 0

View 4 Replies View Related

Description Field

Sep 30, 2004

Hi

Getting an error on the following SQL. The field in question is the Description field which has * in the description. Not sure how I should have the SQL to solve this

UPDATE Taylors
SET Area=::Area::,Avail=::Avail::,Address=::Address::,Type=::Type::,Price=::Price::,Bedrooms=::Bedrooms::,HotProps=::HotProps::,Office=::Office::,Description=::Description::
WHERE RefNo =::RefNo::


Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '* modern retirement flat * double bedroom * south lounge * fitted kitchen * good size bathroom * communal gardens * residence parking *'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

View 1 Replies View Related

Retrieve A Recordset From One Table Based On The CSV Values In The Field Of Another

Feb 6, 2007

Hello!

I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:

Tab1

SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102

Tab2

DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102

I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:

SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')

The subquery will return: 'S100','S101' and I expect the final recordset to be:

SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101

However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.

SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')

it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..

Cheers
Greg

Complete DDL, Sample Data, and Query below:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO

CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO

INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO

SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')

View 1 Replies View Related

Accessing Description Field

Mar 20, 2001

On the Design Table screen for SQL 2000 there is a Description field along with the Default, Identity, etc. options. Once I put a description in there, is there a way to retrieve it through T-SQL?

Thank you!

View 2 Replies View Related

Accessing Description Field

Mar 20, 2001

On the Design Table screen for SQL 2000 there is a Description field along with the Default, Identity, etc. options. Once I put a description in there, is there a way to retrieve it through T-SQL?

Thank you!

View 1 Replies View Related

Setting Field Description Property...

May 19, 2008

I'm selectively migrating a load of data from one database to another and would like to be able to get & set a table fields Description property (as seen on the Design window under Column Properties) programmatically. I'm having a spot of bother in actually finding how to do it - anyone know?

Ta,

Rob


P.S. Just thought I'd mention - I do know about sp_addextendedproperty, and while I will use that if I have to, it would be nice to be able to use the existing column property Description so that when editing, the user can see the value.

View 2 Replies View Related

How To Get Table Record's Position In Comparison To Other Records Based On Numeric Field?

Apr 2, 2007

Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?

View 6 Replies View Related

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

Automatically Populate Application User Defined Field Based On Other Fields In Table

Nov 22, 2015

I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.

Writing a script to accomplish the following:

The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
 
- First_Name
 - Last_Name
 - Email
 - User_Field_2
 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)

Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?

Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?

View 5 Replies View Related

Row Retrieval Problem In Small Table

Jan 18, 2005

Dear Participant,

I face following problem by last few day, please help me for the same

My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.

I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.

Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.

I don’t understand what is the problem.

Thanks

R.Mall

View 6 Replies View Related

Default Retrieval From Table Loaded By DTS

Jul 20, 2005

[MS SQL Server 7]We load a table from a text file using Data Transformation Services. Thesource file is already sorted by primary key order.After the DTS load, the default retrieval order on the target table (select* from targettable) appears to be random. I know that theoretically theretrieval order from a SELECT statement isn't guaranteed, but this is thefirst time that I've actually had a default retrieval not follow theprimary key order in the table. I'm thinking that what we're seeing is thephysical storage order of the rows in the table, which have been somewhatscrambled by the way that DTS loads a text file (probably some I/Ooptimization).Is there any way that we can get DTS to load the table in the order thatthe rows appear in the text file (assuming that's what our problem is)?The Project Lead really doesn't like the fact that the default retrievalorder isn't following the primary key.Regards,Lyle H. Gray

View 1 Replies View Related

Data Retrieval For Reports From Summary Table.

Feb 22, 2007

Hi,
I have a summary table like this






 
Field1
Field2
Field3
Field4
Field5


AAA11
value1
value2
value3
value4
value5


AAB23
value6
value7
value8
value9
value10


BCD14
value11
value12
value13
value14
value15


GFD12
value16
value17
value18
value19
value20


SDL25
value21
value22
value23
value24
value25


AUD56
value26
value27
value28
value29
value30


BER11
value31
value32
value33
value34
value35









Columns are obviously fixed, but not rows.
I want to show this data using lables and SqlDataReader for report purpose like;
Label1.text=dr("value16").toString( )
Label2.text=dr("value28").toString( )
Label3.text=dr("value31").toString( )  etc
 
 Do you have any idea how i can do it or am I approaching it in the wrong way????
 
Thanks.
Michelle
 

View 1 Replies View Related

Description Properties In SQL 2000

Jul 15, 2001

A query to get the Table and Column Description fields in SQL Server 2000.


SELECT so.name, sc.name, sc.xtype, sc.length, sc.colid,
tableprop.value as TableDescription,
colprop.value as ColDescription

FROM

sysobjects so

INNER JOIN syscolumns sc ON sc.id = so.id
INNER JOIN sysproperties tableprop ON tableprop.id = so.id and tableprop.type = 3
INNER JOIN sysproperties colprop ON colprop.id = sc.id and colprop.type = 4
and colprop.smallid = sc.colid

WHERE so.type='u' and so.name = 'TableName'

Order by sc.ColID





Damian

View 2 Replies View Related

SQL Server 2012 :: Updating A Field Based On Result Set

Feb 21, 2014

I am trying to update records based on the results of a query with a subquery.

The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.

When I run the update statement all records are updated and not the result set when I run the query by itself.

Below you will find the code I am running:

create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int

[Code] ....

Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.

Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)

[Code] ...

Why is the update changing each record? How can I change the update to choose the correct records?

View 6 Replies View Related

SQL Server 2012 :: Replace A Result Value Based On Value Of Another Field?

May 7, 2015

I would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.

The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.

JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.

Current Select Results:

ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN

Desired Select Results:

ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN

Current SQL Select statement:

SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,

[code]....

View 9 Replies View Related

SQL Server 2008 :: How To Change Columns Based On Another Field Value

Jun 10, 2015

I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.

DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Merging Two Tables Based On Date Field?

Sep 8, 2015

I have two tables like,

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit

[code].....

I want result as the attached image.

Create table query for result is: CREATE TABLE dbo.#Result(
ID varchar(50),
Fee varchar(100),
Bill varchar(50),
A_Date date,
B_Date date,
Status VARCHAR(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime
)

how to achieve this in sql server 2012.

View 6 Replies View Related

SQL 2000 Table Field String Wrap

Dec 27, 2007

A string needs to be stored in a SQL 2000 table varchar(255) fileld in such a way that when emailed or printed out, it will display as below:
Name: John DoePhone: 213-444-5555Email:jdoe@test.comCity: New YorkCountry: USA
How can such a string be constituted?Thanks

View 1 Replies View Related

How To Encrypt A Column(field In A Table) In MS SQL 2000

Jul 3, 2006

Hi,

I want to store user-id and passwords in a table in SQL Server. But as passwords are very secure, I want to encrypt them while storing and may be decrypt them when reqd.

How can I achieve this functionality

Thanks
-Sudhakar

View 1 Replies View Related

How To Encrypt A Column(field In A Table) In MS SQL 2000

Jul 3, 2006

Hi,

I want to store user-id and passwords in a table in SQL Server. But as passwords are very secure, I want to encrypt them while storing and may be decrypt them when reqd.

How can I achieve this functionality

Thanks
-Sudhakar

View 2 Replies View Related

Multiple Foreign Keys On Same Field, Based On Other Field

Jul 23, 2005

I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.

View 26 Replies View Related

Separating One Field Into Two Fields Based On A Character In The Field

Jul 20, 2005

I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?

View 1 Replies View Related

SQL 2012 :: Single Column In Staging Table Deems Retrieval Query Unresponsive

Jul 17, 2015

where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.

View 3 Replies View Related

Transact SQL :: If Not Exists Some ID In One Table Then Insert ID And Description In Another Table

Jul 14, 2015

I need to find out if a Transaction ID exists in Table A that does not exist in Table B.  If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B.  This would work easily if there were only one row.  What is the best way to handle it if there are a bunch of rows?  Should there be some type of looping?

View 12 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

SQL Server Data Retrieval

Jul 12, 2004

Hi,



I wanted to retrieve all the databases present in an SQL Server and put them in an Dropdown list.

Then retrieve all tables of the selected database

and finally retrieve all data from the selected table (This i know)

How do i accomplish the above two.


Regards
Vijay R

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved