Combining established columns into one

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?Could you please post your syntax?|||It is best to do the formatting for display purposes on the client. What if you want to change the formatting later? You hav e to make schema changes even if you use computed columns or views or queries in SPs.|||

you may wish to use a calculated column

use northwind
select * from employees
go
alter table employees
add
fullname as rtrim(lastname)+','+rtrim(firstname)
go

select fullname,lastname,firstname from employees

|||I realize that it would be best to do all the formatting on the client. The problem is that I have about 50 stored procedures that were developed on another database that was "supposed to" have the same table schemas. Unfortunately, the developer decided to split apart the names into first name and last name fields. It would be easier to just created a computed column.|||Actually, splitting the name into it's constituent parts and storing it is the correct way. You can use a computed column or view with the computed expression or modify your SP to include the computed expression. With all these methods, you can get the required column for display purposes. But if you want to search on this concatenated string then it is a different deal. Performance depends on lot of factors like index on the computed column, whether optimizer matches the computed column expression and uses the index and so on.

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan Kelly
Have a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Columns on Summary Page of SSMS

Hello all,
The Summary Page In SSMS looking at the tables of a DB in Details view,
there are 3 columns: Name, Schema, & Created.
Is there any way to select other columns to view?
I created a "Notes" property in the Extended Properties of a Table's
Properties with some info on why that table is there and what the data is.
I would like to display this as a column in the Summary Page.
Is this possible?
Thanks for any help anyone can provide,
Conan KellyHave a look at this blog. There is a lot of good info related to SSMS and
specifically custom reports in SP2.
http://blogs.msdn.com/sqlrem/default.aspx
Andrew J. Kelly SQL MVP
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:7H7Wh.62184$VU4.46621@.bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
> The Summary Page In SSMS looking at the tables of a DB in Details view,
> there are 3 columns: Name, Schema, & Created.
> Is there any way to select other columns to view?
> I created a "Notes" property in the Extended Properties of a Table's
> Properties with some info on why that table is there and what the data is.
> I would like to display this as a column in the Summary Page.
> Is this possible?
> Thanks for any help anyone can provide,
> Conan Kelly
>

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~Graham
Graham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>
|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER T
ABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Qu
ery Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error
message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM dat
abase on another
machine, or another database name if you don't want to mess about with your
production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per ba
se
> table. I just hit 256 and stopped cold. I find it odd that it stopped at t
he
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields fo
r
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER
TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using
Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the erro
r message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM d
atabase on another
> machine, or another database name if you don't want to mess about with you
r production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRMn">
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you g
et an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The erro
r
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues
as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a C
RM
> limitation.
> "Tibor Karaszi" wrote:
>

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:
> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> > This could belong in the CRM community but I thought I'd come here first.
> >
> > I am trying to add for custom fields to the CRM Schema manager. It creates
> > the database portion. As I understand it, SQL can have 1024 columns per base
> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> > the most part.
> >
> > Any insight is greatly appreciated.
> >
> > ~Graham
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:
>> In addition to Jerry's post:
>> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
>> creating the table with that many column (whichever applies to you) using Query Analyzer. If you
>> get
>> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
>> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
>> machine, or another database name if you don't want to mess about with your production database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
>> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>> > This could belong in the CRM community but I thought I'd come here first.
>> >
>> > I am trying to add for custom fields to the CRM Schema manager. It creates
>> > the database portion. As I understand it, SQL can have 1024 columns per base
>> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
>> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
>> > the most part.
>> >
>> > Any insight is greatly appreciated.
>> >
>> > ~Graham
>>

column data types?

I know there's a way to query the schema, but don't recall exactly. Does
anyone know how to query the database to determine the field type, length,
precision, scale, etc?By the way, I know there was an undocumented stored procedure to do this too
,
but it returned the datatype string as the 3rd argument, and since I'm hopin
g
to receive this in a recordset (although one row) from an ADODB.Connection i
n
VB, I'm not sure how to take that 3rd argument and get it to the recordset.
"Les Stockton" wrote:

> I know there's a way to query the schema, but don't recall exactly. Does
> anyone know how to query the database to determine the field type, length,
> precision, scale, etc?
>|||www.aspfaq.com/2177
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:7CFEB2D5-4C65-4353-A350-4FF755F0C478@.microsoft.com...
>I know there's a way to query the schema, but don't recall exactly. Does
> anyone know how to query the database to determine the field type, length,
> precision, scale, etc?
>

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG

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 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.
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