Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.sqlsql

Combine text columns

I have 2 text data type columns that I would like to combine into a new column. I'd also like to add a newline character between each column value when I combine them.

I've tried columnA + columnB but that didn't work.

How could I do that?

Hi,

you can do it like this

select columnA + ' ' + columnB as columnAB from tableX

Grz, Kris.

|||

Here's the error that it produces:

Msg 402, Level 16, State 1, Line 1

The data types text and varchar are incompatible in the add operator.

|||

In that case you need to cast the varchar to type text. You can do that by using the Transact-SQL functionCAST.

Grz, Kris.

Combine tables from previous db to new db (uhh...?)

I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.

My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?

Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6

New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?you can use a cursor...and loop through each record...get the values into variables and insert into the 2 tables...

your products_attributes table can be somthing like

ID,Attr1, Attr2, Attr3, Attr4, Attr5, Attr6.... you can have the product name too if you need to..

HTH|||could you post a link or some example code for doing this? Thanks!|||sorry i misread your post...did u mean move data from one table in a DB to two tables in ANOTHER db ? or is this all within the same db ? if you are moving from one DB to another,then it wud be easier to move the entire table into the new db first and then separate the data into two tables inside the new db...

HTH|||from one source db to a different target db, then split into two tables at the target db.|||there are prbly many ways to do this...you can do the export/import wizard to transfer the table to the target db and then use tht table to split it..thts when you can use cursors to go through each row and insert the values into diff tables...

HTH|||Any links for cursor samples you're referring to? I haven't used them before.

Column, parameter, or variable #1: Cannot find data type SqlDatareader

Hello Everyone,

A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:

public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index)
{
if(dr.GetSqlValue(index) == null)
return SqlInt32.Null;
else
return dr.GetSqlInt32(index)
}

I than allways get the following ErrorMessage:

Column, parameter, or variable #1: Cannot find data type SqlDatareader.

Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.

My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.

Would appreciate any kind of help! Thanks

You may need to refer to the SqlDataReader with full qualified name if you haven't using the proper namespace:

System.Data.SqlClient.SqlDataReader

Column width

Hi Every1, I'm getting this error.
'Cannot specify a column width on data type smalldatetime.'
I'm using SQL Server & I've check the data type in the db & it's same as smalldatetime.
Any suggestions would be highly appreciated.
Thanks

<Error
Column or parameter #8: Cannot specify a column width on data type smalldatetime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.

Source Error:

Line 344: Try
Line 345: myConnection.Open()
Line 346: dgdSearch.DataSource = myCommand.ExecuteReader()
Line 347: dgdSearch.DataBind()
Line 348: Finally

Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb Line: 346

Stack Trace:

[SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader() +41
Demo_Equip_Log.Search_Equipment.BindGrid() in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:346
Demo_Equip_Log.Search_Equipment.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:195
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750Show the code you are using to set up the parameter. I presume the message means EXACTLY what it says. Are you trying to set the length of the smalldatetime parameter? You cannot.|||Hi Doug, thanks for your response & Yep!. I've released that after I posted on the forum.
This is the first time I'm using Stored Procedure.
I've removed it & now I'm not getting that error.
However, I'm getting a different error ... I highly appreciate your suggestion.

Thanks

<Error>
Cannot compute Count for a data source that does not implement ICollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Cannot compute Count for a data source that does not implement ICollection.

Source Error:

Line 345: myConnection.Open()
Line 346: dgdSearch.DataSource = myCommand.ExecuteReader()
Line 347: dgdSearch.DataBind()
Line 348: Finally
Line 349: myConnection.Close()

Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb Line: 347

Stack Trace:

[HttpException (0x80004005): Cannot compute Count for a data source that does not implement ICollection.]
System.Web.UI.WebControls.PagedDataSource.get_DataSourceCount() +152
System.Web.UI.WebControls.PagedDataSource.get_Count() +187
System.Web.UI.WebControls.PagedDataSource.GetEnumerator() +268
System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) +712
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49
System.Web.UI.WebControls.BaseDataList.DataBind() +23
Demo_Equip_Log.Search_Equipment.BindGrid() in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:347
Demo_Equip_Log.Search_Equipment.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:195
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.
The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much mo
re
> efficient than converting a character column to integers which you're maki
ng
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn'
t
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||U¿ytkownik Roger Wolter[MSFT] napisa³:
> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

column type

I am putting together a SQL table which will hold a latitude and
longitude for each record. An example of a latitude is: 47 05 36.5
Which column type would best represent this and retain the spaces
between degrees, minutes, seconds? Text, varchar, char?

Thanks
Jeff

jeff-godfrey@.wa.nacdnet.orgDon't use Text. Text is only useful for large texts and needs special
treatment.

Since the variability in value length will be small I would opt for the
char datatype. Other than that varchar is a fine choice as well. If you
choose char, make sure you don't overdimension it (you would waste
space).

Gert-Jan

Jeff Godfrey wrote:
> I am putting together a SQL table which will hold a latitude and
> longitude for each record. An example of a latitude is: 47 05 36.5
> Which column type would best represent this and retain the spaces
> between degrees, minutes, seconds? Text, varchar, char?
> Thanks
> Jeff
> jeff-godfrey@.wa.nacdnet.org|||Unless you are doing any computations on the values in this column, you can
use CHAR datatype. If each portion of this data has operational significance
or need any computations, you may be better off storing them as separate
numeric columns.

--
- Anith
( Please reply to newsgroups only )|||>> a latitude and longitude ... Which column type would best
represent this and retain the spaces between degrees, minutes,
seconds? Text, varchar, char? <<

If you have to compute with them, you might want to use FLOAT and
convert things to radians, and display them with a UDF.

Column Summary

HI,
am new to sql and trying to write a query to summarise data in a table.The table contains a list of documents, their type and author
DocName DocType Author
Name1 letter tom
Name2 essay rick
Name3 essay rick
Name4 letter arnold
Name5 letter tom
Name6 poem ed
Name7 letter tom
...etc

and i wish to do a breakdown of the form:

DocType Total Docs Tom Arnold Ed Rick
letter 54 21 14 14 5
essay 78 18 36 15 9
poem 18 3 6 6 3

have tried a few select queries but have come up short...any ideas?Look up Cross-Tab Reports in Books Online or search here. This has been covered several times and you can find some good examples in each location.|||Originally posted by Paul Young
Look up Cross-Tab Reports in Books Online or search here. This has been covered several times and you can find some good examples in each location.

Cheers Paul, much appreciated

Column spacing issue

Hi,

I'm developing a report at the moment that uses a sub report twice on the same report (legal legislation type of thing, requires information to be displayed twice.). In the first location the one column is heavily spaced to the right (though not designed this way), causing a new blank page to be created when exporting, but at the other location (on the same report, same data), the spacing is normal. What could be causing this?

Thanks,

It seems the only way to fix this is to delete the specific table, and then to re-create it.

Strange. This occurred now in VS 2003.

Column max size for String

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.

text is for variable-length non-Unicode data.

ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...

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

Column Data Type

Hello, I would like to get the columns in a table, this works fine:
SELECT syscolumns.*
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.name = 'Customers'
ORDER BY syscolumns.colid
However, is there a way to get Column_Data_Type as for example "nchar" or "varchar" instead of having it as numbers.
Can anybody help ?
thanksYou can join to systypes using the xtype column.
But you should probably, instead, use the INFORMATION_SCHEMAviews. Directly accessing the system tables is not recommended,especially as we get closer to 2005 -- those tables are beingdeprecated. Try:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
|||SELECT syscolumns.* , systypes.name
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xusertype = systypes.xusertype
WHERE sysobjects.name = 'Customers'
ORDER BY syscolumns.colid|||

That was a great query in fact.
I am doing something similar, I would like to get all SP, View, triggers, is that possible?
Regards

|||--P=SP, V=Views, TR=Triggers
select *
from sysobjects
where xtype IN ('P', 'V', 'TR')
order by xtype
If you need the information from all databases, you can use the master DB and select from sysdatabases. Run a cursor and put the data into a table so you can access it after the cursor completes.
Nick|||

I will give it a try later. That is great.
Tell me, where do you get such information? Are they present in somewhere? I usually use SP, View, Triggers, but never went into details of such queries, I guess they are system queries right?
regards

|||

Here is a link to all the system tables and what they are for:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

Here is a really helpful tool that shows the links between all the tables.

http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp

Nick

|||Those were extremly great links. Thanks a lot.
Regards

Column Chart Issue in MSRS 2005

Hi,
I have report created with Reporting Service 2000 SP2 designer. This
contains a Column Chart with Simple Column type. I had selected
GraySacle Pallete for my report. I have max 4 columns in my report.
When I deployed the report on MSRS 2000 SP2 Report Server it is showing
correctly. The style of the columns in legend and that appearing in
chart are correct.
When I deploy the same report on MSRS 2005 SP1 Report Server its
showing in-correct. The style of the columns in legend and that
appearing in chart are not matching. For e.g I have a column
representing "Data Unavailable" in legend it shows as "Front
Slash" on reports in the chart this DataUnavailable appears 7 times
(representing week) first time it appears as "Front Slash" 2nd time
"Back Slash" 3rd time "Straight horizontal line", 4th
"Vertical lines" ...and so on all 7 times it appears different.
Is this a bug in 2005. Its working fine in MSRS 2000 SP2
What is a solution for making it consistent on MSRS 2005?
Thanks in advance for any kind of help.
Regards,
ManojHi
Do anyone know the reason for this behaviour?
I tried creating the same report in Reporting Service 2005 SP1
designer. Still same result. GrayScale Pallete shows wrong column graph
other than those shown in legends
Any kind of help in this regards will be very usefull.
Regards,
Manoj
Manoj wrote:
> Hi,
> I have report created with Reporting Service 2000 SP2 designer. This
> contains a Column Chart with Simple Column type. I had selected
> GraySacle Pallete for my report. I have max 4 columns in my report.
> When I deployed the report on MSRS 2000 SP2 Report Server it is showing
> correctly. The style of the columns in legend and that appearing in
> chart are correct.
> When I deploy the same report on MSRS 2005 SP1 Report Server its
> showing in-correct. The style of the columns in legend and that
> appearing in chart are not matching. For e.g I have a column
> representing "Data Unavailable" in legend it shows as "Front
> Slash" on reports in the chart this DataUnavailable appears 7 times
> (representing week) first time it appears as "Front Slash" 2nd time
> "Back Slash" 3rd time "Straight horizontal line", 4th
> "Vertical lines" ...and so on all 7 times it appears different.
> Is this a bug in 2005. Its working fine in MSRS 2000 SP2
> What is a solution for making it consistent on MSRS 2005?
> Thanks in advance for any kind of help.
> Regards,
> Manoj

Colour of the series in legend

I have few chart based reports where I am showing different costs in series. I want to fix the colour of each type of cost so that I can maintain the same colour for each cost in different reports.

Is it possible? If yes, then please let me know.

Thanks in advance!!!

Check out my sample report on Brian Welcker's blog: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert