Collation problem: description

Showing posts with label description. Show all posts

Monday, March 19, 2012

Combine fields

I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thankscould you provide us some info as outlined in the hint sticky that's on top of the board?|||Select DocNoc, DocDesc from DocTable (take these to fields and insert it into one field in the same table.
Insert into DocNumDesc

How would I go about creating this syntax. Thanks|||SELECT DocNoc+DocDesc

??|||Okay is the correct syntax:

INSERT INTO DocNumDesc
SELECT DocNoc +DocDesc
FROM DocTable

Thanks

Sunday, February 19, 2012

Column Descriptions in Matrix

Is there a way to have a column description/header for each column in a matrix?Try setting the ToolTip property on the textbox in the column header. If it's an expression, make sure it returns a string value.

column description?

Thursday, February 16, 2012

column description?

Hi All
how can i access a normal sql server 2000 tables column description and give it a value from a query or from a stored procedure?
thanks for the help!
Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:

> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:

> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>

column description?

column description in tables design

Using SS2000. When we design a new table in EM, we often put text in the
description field for each column. We tried copying the table as an object
via DTS to a new server/database and everything seems to transfer except the
descriptions.
Is there a way to transfer the descriptions? Where are they stored? I can
run this query to see what the descriptions are but I can't look inside the
function to see where it's pulling the info from.
SELECT objname, value
FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
'tblleads_branch', 'column', null)
Thanks,
Dan D.
I have not tested it but the DTS Import/Export Wizard, when you select Copy
objects and data between SQL Server databases, has the choice to include
extended properties, unchecked by default. Just check that box and that
should work.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dan D." wrote:

> Using SS2000. When we design a new table in EM, we often put text in the
> description field for each column. We tried copying the table as an object
> via DTS to a new server/database and everything seems to transfer except the
> descriptions.
> Is there a way to transfer the descriptions? Where are they stored? I can
> run this query to see what the descriptions are but I can't look inside the
> function to see where it's pulling the info from.
> SELECT objname, value
> FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> 'tblleads_branch', 'column', null)
> Thanks,
>
> --
> Dan D.
|||It did. Thanks.
Dan D.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> I have not tested it but the DTS Import/Export Wizard, when you select Copy
> objects and data between SQL Server databases, has the choice to include
> extended properties, unchecked by default. Just check that box and that
> should work.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Dan D." wrote:

column description in tables design

Using SS2000. When we design a new table in EM, we often put text in the
description field for each column. We tried copying the table as an object
via DTS to a new server/database and everything seems to transfer except the
descriptions.
Is there a way to transfer the descriptions? Where are they stored? I can
run this query to see what the descriptions are but I can't look inside the
function to see where it's pulling the info from.
SELECT objname, value
FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
'tblleads_branch', 'column', null)
Thanks,
Dan D.I have not tested it but the DTS Import/Export Wizard, when you select Copy
objects and data between SQL Server databases, has the choice to include
extended properties, unchecked by default. Just check that box and that
should work.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dan D." wrote:

> Using SS2000. When we design a new table in EM, we often put text in the
> description field for each column. We tried copying the table as an object
> via DTS to a new server/database and everything seems to transfer except t
he
> descriptions.
> Is there a way to transfer the descriptions? Where are they stored? I can
> run this query to see what the descriptions are but I can't look inside th
e
> function to see where it's pulling the info from.
> SELECT objname, value
> FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> 'tblleads_branch', 'column', null)
> Thanks,
>
> --
> Dan D.|||It did. Thanks.
--
Dan D.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> I have not tested it but the DTS Import/Export Wizard, when you select Cop
y
> objects and data between SQL Server databases, has the choice to include
> extended properties, unchecked by default. Just check that box and that
> should work.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Dan D." wrote:
>

column description in tables design

Using SS2000. When we design a new table in EM, we often put text in the
description field for each column. We tried copying the table as an object
via DTS to a new server/database and everything seems to transfer except the
descriptions.
Is there a way to transfer the descriptions? Where are they stored? I can
run this query to see what the descriptions are but I can't look inside the
function to see where it's pulling the info from.
SELECT objname, value
FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
'tblleads_branch', 'column', null)
Thanks,
--
Dan D.I have not tested it but the DTS Import/Export Wizard, when you select Copy
objects and data between SQL Server databases, has the choice to include
extended properties, unchecked by default. Just check that box and that
should work.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dan D." wrote:
> Using SS2000. When we design a new table in EM, we often put text in the
> description field for each column. We tried copying the table as an object
> via DTS to a new server/database and everything seems to transfer except the
> descriptions.
> Is there a way to transfer the descriptions? Where are they stored? I can
> run this query to see what the descriptions are but I can't look inside the
> function to see where it's pulling the info from.
> SELECT objname, value
> FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> 'tblleads_branch', 'column', null)
> Thanks,
>
> --
> Dan D.|||It did. Thanks.
--
Dan D.
"Ben Nevarez" wrote:
> I have not tested it but the DTS Import/Export Wizard, when you select Copy
> objects and data between SQL Server databases, has the choice to include
> extended properties, unchecked by default. Just check that box and that
> should work.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Dan D." wrote:
> > Using SS2000. When we design a new table in EM, we often put text in the
> > description field for each column. We tried copying the table as an object
> > via DTS to a new server/database and everything seems to transfer except the
> > descriptions.
> >
> > Is there a way to transfer the descriptions? Where are they stored? I can
> > run this query to see what the descriptions are but I can't look inside the
> > function to see where it's pulling the info from.
> >
> > SELECT objname, value
> > FROM ::fn_listExtendedProperty(NULL, 'user', 'dbo', 'table',
> > 'tblleads_branch', 'column', null)
> >
> > Thanks,
> >
> >
> > --
> > Dan D.

Column Description

Hello, it seems I forgot how to do it,

I want to create a query to get the column description table, I mean get structure without data.

I remember it was select DESC or something like that, donno if im right!!!

regards

select * from information_schema.columns where table_name='foo'

DESC tells you to sort your results in descending order

--
James

|||

Well, I am not sure about DESC, but I used to work with SQL*Plus in Oracle, and I used to do so, it will give me just the columns and datatypes.

So, thanks a lot

regards

|||There are several ways you can do this.
My preferred method is using one of the INFORMATION_SCHEMA views:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'

Alternately, you could use the sp_columns stored procedure
EXEC sp_columns 'myTable'
You could also directly query the sysobjects, etc. tables but I don't recommend that approach.
|||

Terri, thanks dear a lot.

Regards

|||

Don't you just love vendor extensions Smile [:)]

standard SQL uses this format to give you the result set in descending order

SELECT * FROM TITLES ORDER BY PRICE DESC

SQL*Plus uses

DESC[RIBE] table_name to give you column information.

Who says you can't reuse keywords ?

|||

Thanks my friend, I used that once, but as I mentioned I forgot how, and whether its applicable in SQL Server.

regards

Column description

Does anybody know where stored column description in sql2K database is?
Is it possible to script table with this information.
Thanks in advanceVicko
SELECT s2.id, s1.name,
( SELECT s3.value
FROM sysproperties s3
WHERE s3.id = s1.id
AND s3.smallid = s1.colid ) AS "col desc"
FROM syscolumns s1
INNER JOIN sysobjects s2
ON s1.id = s2.id
WHERE s2.name = @.tablename
"Vicko" <avicentic@.gmail.com> wrote in message
news:1155730450.323233.199170@.i42g2000cwa.googlegroups.com...
> Does anybody know where stored column description in sql2K database is?
> Is it possible to script table with this information.
> Thanks in advance
>|||Great,
What about script?
Anyway, I could insert or update sysproperties table.
Uri Dimant wrote:
> Vicko
> SELECT s2.id, s1.name,
> ( SELECT s3.value
> FROM sysproperties s3
> WHERE s3.id = s1.id
> AND s3.smallid = s1.colid ) AS "col desc"
> FROM syscolumns s1
> INNER JOIN sysobjects s2
> ON s1.id = s2.id
> WHERE s2.name = @.tablename
>
> "Vicko" <avicentic@.gmail.com> wrote in message
> news:1155730450.323233.199170@.i42g2000cwa.googlegroups.com...
> > Does anybody know where stored column description in sql2K database is?
> > Is it possible to script table with this information.
> >
> > Thanks in advance
> >|||Vicko
Here's an example script:
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Vicko" <avicentic@.gmail.com> wrote in message
news:1155732443.407871.64680@.i42g2000cwa.googlegroups.com...
> Great,
> What about script?
> Anyway, I could insert or update sysproperties table.
>
> Uri Dimant wrote:
>> Vicko
>> SELECT s2.id, s1.name,
>> ( SELECT s3.value
>> FROM sysproperties s3
>> WHERE s3.id = s1.id
>> AND s3.smallid = s1.colid ) AS "col desc"
>> FROM syscolumns s1
>> INNER JOIN sysobjects s2
>> ON s1.id = s2.id
>> WHERE s2.name = @.tablename
>>
>> "Vicko" <avicentic@.gmail.com> wrote in message
>> news:1155730450.323233.199170@.i42g2000cwa.googlegroups.com...
>> > Does anybody know where stored column description in sql2K database is?
>> > Is it possible to script table with this information.
>> >
>> > Thanks in advance
>> >
>|||Vicko wrote:
> Anyway, I could insert or update sysproperties table.
For reference, directly modifying the system tables is a big no-no.

Column description

Is it possible to add a column description when creating a table? An example
would be great.
http://aspfaq.com/show.asp?id=2244
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gerko" <Gerko@.discussions.microsoft.com> wrote in message
news:AA6FC82B-FFC7-4510-ACAE-F0B7DE70D461@.microsoft.com...
> Is it possible to add a column description when creating a table? An
> example
> would be great.

Column description

Does anybody know where stored column description in sql2K database is?
Is it possible to script table with this information.
Thanks in advanceVicko
SELECT s2.id, s1.name,
( SELECT s3.value
FROM sysproperties s3
WHERE s3.id = s1.id
AND s3.smallid = s1.colid ) AS "col desc"
FROM syscolumns s1
INNER JOIN sysobjects s2
ON s1.id = s2.id
WHERE s2.name = @.tablename
"Vicko" <avicentic@.gmail.com> wrote in message
news:1155730450.323233.199170@.i42g2000cwa.googlegroups.com...
> Does anybody know where stored column description in sql2K database is?
> Is it possible to script table with this information.
> Thanks in advance
>|||Great,
What about script?
Anyway, I could insert or update sysproperties table.
Uri Dimant wrote:[vbcol=seagreen]
> Vicko
> SELECT s2.id, s1.name,
> ( SELECT s3.value
> FROM sysproperties s3
> WHERE s3.id = s1.id
> AND s3.smallid = s1.colid ) AS "col desc"
> FROM syscolumns s1
> INNER JOIN sysobjects s2
> ON s1.id = s2.id
> WHERE s2.name = @.tablename
>
> "Vicko" <avicentic@.gmail.com> wrote in message
> news:1155730450.323233.199170@.i42g2000cwa.googlegroups.com...|||Vicko
Here's an example script:
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Vicko" <avicentic@.gmail.com> wrote in message
news:1155732443.407871.64680@.i42g2000cwa.googlegroups.com...
> Great,
> What about script?
> Anyway, I could insert or update sysproperties table.
>
> Uri Dimant wrote:
>|||Vicko wrote:

> Anyway, I could insert or update sysproperties table.
For reference, directly modifying the system tables is a big no-no.

Sunday, February 12, 2012

Collect SSRS/SSAS schema (metadata)

Hi all,

I would like to collect metadata from cubes&reports automatically from servers SSAS and SSRS. Metadata include name, description, dimensions, members, permissions, etc. Then I store it into a table to be searchable.

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Do anybody has an idea or some piece of help?

thx.
attila

Both servers expose management APIs. The Report Server has web service management APIs while SSAS comes with AMO library.

Collect SSAS / SSRS schema (metadata)

Hi all,

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Has anybody an idea or some piece of help?

thx.
attila

Hey attila,

This can be done using AMO and a little code to store the metadata in some sort of db.

There is some good information here

(really good code examples, too)

Hope that helps a little,
C

Friday, February 10, 2012