Extract number from a string

Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.
Can anybody tell me how to extract a number from a string?...
TIA!!!
Hello, snigs
If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable
If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable
Razvan
PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest ;)
|||On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
>PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
>-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

>PS. With this occasion, I would like to submit my two entries for the
>"most unreadable query of the month" contest ;)
Hi Razvann,
Month, year, century - you win them all! ;-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

extract just the date from a datetime field using T-SQL

I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)

This works, but I thought there must be an easier way.

There are many ways. Easiest is to do below:

convert(varchar, sampletimestamp, 112) = @.selcteddate

|||

Something like this:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

And best to use a form like this for your where:

WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))

So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

Extract and compare hour

Hi all:
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
Thanks.:confused:Yes. Look at DatePart in BOL.

Extract a character string from a text field

I need to extract a character string from a text field. The string I'm
looking for will always start with the first four characters of "ABC-" and
then will end with three numbers (0-9) in varying combinations, ex.
"ABC-508". The problem is that the position of the string in the text field
is different in each record and the last three characters of the string will
vary as described above. Any help is greatly appreciated
--
Finn GirlOne method:
SELECT
CASE
WHEN PATINDEX('%ABC-[0-9][0-9][0-9]%', MyTextCol) = 0 THEN
NULL
ELSE
SUBSTRING(MyTextCol, PATINDEX('%ABC-[0-9][0-9][0-9]%',
MyTextCol), 7)
END AS ExtractedValue
FROM dbo.MyTable
You can encapsulate the code in a proc or function for reusability.
Hope this helps.
Dan Guzman
SQL Server MVP
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl|||create table #foo (SomeColumn varchar(50))
insert into #foo values ('test ABC-508')
insert into #foo values ('testing ABC-509')
insert into #foo values ('ABC-123')
insert into #foo values ('FinnGirlABC-524')
insert into #foo values ('abcABC-456')
select * from #foo
--this shows you the charindex
SELECT CHARINDEX('ABC-', SomeColumn) FROM #foo
--this gets the desired information:
SELECT SUBSTRING(SomeColumn, (CHARINDEX('ABC-', SomeColumn)), 7) FROM #foo
drop table #foo
Keith Kratochvil
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl

extra space at bottom

Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>

Extra "blanks" in the cloumn field

When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column.

this happens in a web form also in Management Studi?o,

Whan am I doing wrong ?

does Collation have anything with it ?

SQL Server 2005 / Developer Edition

It sounds like you are using the char data type. SQL Server will automatically pad this with spaces as it is a fixed number characters. Use varchar instead.|||

thanks for your help,

now it is not padding.

Extensive Use pf Case Statement

Hi,

I want to generate a table output as per table a based on table b. In this case Range field os populated based on Shape and Process field values. You can see in both processes range is different. Now this is varies from shapes & processes which is stored in seperate master table which contains range ex. For Process_1 it is 0.5 & for Process_2 it is 0.10.

I have tried used case statement but I am unable to generate this dynamically in a single query. Can anyone Help me How can I achive this task?

Table A

Barcode

Shape

Process

Weight

Range

1

Shap_1

Proc_1

0.12

0.11-0.15

2

Shap_1

Proc_1

0.16

0.16-0.20

3

Shap_1

Proc_1

0.06

0.06-0.10

4

Shap_1

Proc_1

0.21

0.21-0.25

5

Shap_1

Proc_1

0.13

0.11-0.15

6

Shap_1

Proc_2

0.18

0.11-0.20

7

Shap_1

Proc_2

0.13

0.11-0.20

8

Shap_1

Proce_2

0.24

0.21-0.30

9

Shap_1

Proce_2

0.07

0.00-0.10

10

Shap_1

Proce_2

0.33

0.31-0.40

Table B

Barcode

Shape

Process

Weight

1

Shap_1

Proc_1

0.12

2

Shap_1

Proc_1

0.16

3

Shap_1

Proc_1

0.06

4

Shap_1

Proc_1

0.21

5

Shap_1

Proc_1

0.13

6

Shap_1

Proc_2

0.18

7

Shap_1

Proc_2

0.13

8

Shap_1

Proce_2

0.24

9

Shap_1

Proce_2

0.07

10

Shap_1

Proce_2

0.33

Nilkanth Desai

create table TableB(Barcode int, Shape char(6), Process varchar(8), Weight decimal(5,2))
insert into TableB(Barcode ,Shape , Process ,Weight)
select 1 , 'Shap_1', 'Proc_1' , 0.12 union all
select 2 , 'Shap_1', 'Proc_1' , 0.16 union all
select 3 , 'Shap_1', 'Proc_1' , 0.06 union all
select 4 , 'Shap_1', 'Proc_1' , 0.21 union all
select 5 , 'Shap_1', 'Proc_1' , 0.13 union all
select 6 , 'Shap_1', 'Proc_2' , 0.18 union all
select 7 , 'Shap_1', 'Proc_2' , 0.13 union all
select 8 , 'Shap_1', 'Proc_2', 0.24 union all
select 9 , 'Shap_1', 'Proc_2', 0.07 union all
select 10 , 'Shap_1', 'Proc_2', 0.33

create table Master(Process varchar(8), Range decimal(5,2))
insert into Master(Process, Range) values('Proc_1',0.05)
insert into Master(Process, Range) values('Proc_2',0.10)

select t.Barcode,
t.Shape,
t.Process,
t.Weight,
floor(t.Weight/m.Range)*m.Range+0.01 as RangeFrom,
floor(t.Weight/m.Range)*m.Range+m.Range as RangeTo
from TableB t
inner join Master m on m.Process=t.Process
order by t.Barcode

Expressions as a field value in a table

Is there a way to store an expression inside a table, and have the expression
being pulled from the table to execute during run time? I want to dynamically
create an expression based on the value of a column of the table. Using the
IIF statement will work but it's going to be a very long nested IIF.... and
that brings to the second question -> is there a limit for the length of an
expression?
Thanks in advance.There is no limit on the length of an expression (other than limits imposed
by the VB.NET compiler).
In your case, you may want to look at alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
In addition, you may want to consider writing a function in custom code or
as custom assembly that contains your logic. You can then reuse the function
from RDL expressions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
> Is there a way to store an expression inside a table, and have the
> expression
> being pulled from the table to execute during run time? I want to
> dynamically
> create an expression based on the value of a column of the table. Using
> the
> IIF statement will work but it's going to be a very long nested IIF....
> and
> that brings to the second question -> is there a limit for the length of
> an
> expression?
> Thanks in advance.|||Or a User Defined Function that the Stored Procedure calls.
GeoSynch
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uO3ZYBwvFHA.252@.TK2MSFTNGP09.phx.gbl...
> There is no limit on the length of an expression (other than limits imposed by
> the VB.NET compiler).
> In your case, you may want to look at alternatives to the IIF function:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> In addition, you may want to consider writing a function in custom code or as
> custom assembly that contains your logic. You can then reuse the function from
> RDL expressions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
> news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
>> Is there a way to store an expression inside a table, and have the expression
>> being pulled from the table to execute during run time? I want to dynamically
>> create an expression based on the value of a column of the table. Using the
>> IIF statement will work but it's going to be a very long nested IIF.... and
>> that brings to the second question -> is there a limit for the length of an
>> expression?
>> Thanks in advance.
>

Expression to get the last word of the fields

Hi,

How do i get the last word of a field in an expression?

Thanks

MosheDeutsch wrote:

Hi,

How do i get the last word of a field in an expression?

Thanks

Its a string manipulation problem right? There are lots of string functions available to you in the expression language. Check them out in the top right hand corner of the expression editor.

-Jamie

|||

I am aware of all the string functions available in the expression editor, how would I last word of a field using the functions available?

Thanks

|||Well, with Perl this would be easy!

But for your issue, what defines a "word"? How many words are there in the field? Is the number of words in a field consistent across all rows? What if the field only has one "word"? Etc... Show us some data.|||

Its not consistent across all rows, a word is delimited by a space, will always have more then one word, I am sure its possible to do it with code but I would like to now how I can do it with the expression builder

Thanks

|||You'd probably be better off trying this in a script component.|||

I was trying to eliminate script, i am not so familiar with script, but it that’s the only way I will need a sample please

Thanks

|||Add a script component to your dataflow... Add an Output Column, LastWord, of type string. Make sure that the field you are working on is selected as an input to the script component.

Then, here's your script... Note the "your-field-here" location, which represents the input field name that you are trying to get the last word of.

Imports System
Imports System.Data
Imports System.Math
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim WordArray As Array
Dim RegExprObj As Regex
Dim ArrayLength As Integer

RegExprObj = New Regex(" ")
WordArray = RegExprObj.Split(row.your-field-here)
ArrayLength = WordArray.Length
Row.LastWord = WordArray.GetValue(ArrayLength - 1).ToString
End Sub

End Class|||As an aside, I didn't know how to do this prior to you asking your question. Using Google was a big help, by the way. Search for "vb.net regular expressions" and go from there.

I don't guarantee that this is the best way to solve your problem, but it works with my test data.

You might also want to be sure that your field is trimmed before coming into this script component.|||

Thanks so much, I will try it

|||

MosheDeutsch wrote:

I am aware of all the string functions available in the expression editor, how would I last word of a field using the functions available?

Thanks

Have you actually attempted this or do you just want someone else to do your work for you?

A combination of FINDSTRING, SUBSTRING and REVERSE should be able to do it for you.

This worked for me (where [Column] is the name of a column with a sentance in it):

REVERSE(SUBSTRING(REVERSE([Column]),1,FINDSTRING(REVERSE([Column])," ",1)))

I'll stick a demo package up on my blog as well. Look in about an hour.

-Jamie

|||

Jamie Thomson wrote:

Have you actually attempted this or do you just want someone else to do your work for you?

A combination of FINDSTRING, SUBSTRING and REVERSE should be able to do it for you.

This worked for me (where [Column] is the name of a column with a sentance in it):

REVERSE(SUBSTRING(REVERSE([Column]),1,FINDSTRING(REVERSE([Column])," ",1)))

I'll stick a demo package up on my blog as well. Look in about an hour.

-Jamie

More than one way to solve the problem... Let your testing be the judge of what's correct for you.|||

Yes I did try different ways to do it with a combination of FINDSTRING, SUBSTRING and LEN I never used the REVERSE now I will for sure know the different ways how to use it and how powerful it is.

Thanks for every one

|||

Phil Brammer wrote:

More than one way to solve the problem... Let your testing be the judge of what's correct for you.

Amen to that.

There's usually more than one way to solve a problem in SSIS.

-Jamie

|||

Here's the demo:

Extract last word from a sentance
(http://blogs.conchango.com/jamiethomson/archive/2006/11/22/SSIS_3A00_-Extract-last-word-from-a-sentance.aspx)

-Jamie

Expression problem in Derived Column Component

I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))

However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.

[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.

I just recreated this scenario using flat file as a source for <first name, last name>. Your formulas work correctly for me in the derived column transform. Where have you exactly defined the "LastColumn"?

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

expression for count on a field

Can anybody point me in the right direction please?

I am printing labels. And have got this problem.

The total quantity for example is 250 all with different labels fields.

What I need is:

1 of 250

2 of 250

Example of label field: IAC-000245

This is my expression:

=CountDistinct(Fields!ASSETNO.Value)

But the result is is giving me 250 for all labels it should be 1 or 2 or 3

The end result will be:

=CountDistinct(Fields!ASSETNO.Value) & " to " & Fields!QUANTITY.Value

Kindest Regards

This may work for you...

=RunningValue(1,Sum,Nothing)

To append a string, you may need to convert the Running Value to a string.

cheers,
Andrew|||

Thank you it worked like a dream

Expression Error: Attempted to divide by zero.

i'm trying to calculate a percent field with the following expression and
getting the error message above:
=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
Fields!valorPrevisto.Value)
what am i doing wrong ?
thanks.Hi levogiro -
Check out this thread. It describes the problem and offers a couple of
solutions.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/481dcece9e84c707/d34656d64a7f39a9?q=divide+by+zero+expression&rnum=4&hl=en#d34656d64a7f39a9
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 13 Sep 2005 07:08:05 -0700, "levogiro"
<levogiro@.discussions.microsoft.com> wrote:
>i'm trying to calculate a percent field with the following expression and
>getting the error message above:
>=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
>Fields!valorPrevisto.Value)
>what am i doing wrong ?
>thanks.

expression

In RS, is it possible to code an expression so that if a field contains the
word "STAT" that whole row is highlighted in another color? If not, how about
a that field?
I can do it with numeric values, not text.
Thanks.On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> In RS, is it possible to code an expression so that if a field contains the
> word "STAT" that whole row is highlighted in another color? If not, how about
> a that field?
> I can do it with numeric values, not text.
> Thanks.
In Layout view, select the field that you want to change the
background color for and select F4 (for the Properties window). To the
right of Background Color, select <Expression...> and enter an
expression similar to the following:
=iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That worked. I ad the syntax wrong. Thanks!
"EMartinez" wrote:
> On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > In RS, is it possible to code an expression so that if a field contains the
> > word "STAT" that whole row is highlighted in another color? If not, how about
> > a that field?
> >
> > I can do it with numeric values, not text.
> >
> > Thanks.
>
> In Layout view, select the field that you want to change the
> background color for and select F4 (for the Properties window). To the
> right of Background Color, select <Expression...> and enter an
> expression similar to the following:
> =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On May 24, 9:12 pm, brian <b...@.discussions.microsoft.com> wrote:
> That worked. I ad the syntax wrong. Thanks!
> "EMartinez" wrote:
> > On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > > In RS, is it possible to code an expression so that if a field contains the
> > > word "STAT" that whole row is highlighted in another color? If not, how about
> > > a that field?
> > > I can do it with numeric values, not text.
> > > Thanks.
> > In Layout view, select the field that you want to change the
> > background color for and select F4 (for the Properties window). To the
> > right of Background Color, select <Expression...> and enter an
> > expression similar to the following:
> > =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Exporting to CSV add "_Value" or "_Value2" to the field name

Hi,

When I'm export a report that has been build in the "Report Builder" to csv to all fields caption added at the end of the field name the word "_Value" or "_Value2"

example:

field name in the report: EmployeeName

field in the csv : EmployeeName_Value

How can I remove this adding?

Thanks

Assaf

Assaf,

There is no easy way to change CSV heading in ReportBuilder, but you can save the report uploaded to the Report Server on a local disk, modify RDL and change names of the column headings, and upload it back to the server.

To do this, you can either search for "_Value" in the textbox names in RDL file and remove it, or add a child XML element node DataElementName in the RDL for each text box with column heading you want to modify:

<Textbox Name="Component_Value">
<DataElementOutput>Output</DataElementOutput>
<DataElementName>Component</DataElementName>
<CanGrow>true</CanGrow>
<Action>
....

The value of <DataElementName> element becomes a column heading in CSV output.

Note though that if you modify the uploaded report in ReportBuilder after you applied your changes, the changes will be lost as ReportBuilder re-generates RDL.

Thanks!

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename

Exporting TEXT Field into CSV file

I have a record set I have to export on a weekly basis into a CSV file for a customer/client. One of the fields being included in the export is a TEXT field. When I export this field, the CSV file is truncating the record significantly.

Is there a way I can get the export to pass all the data in the TEXT field, or is this a limitation on the data transformation, or is it a limitation on the CSV file (because of the size/nature of the TEXT field).After taking a break, I tinkered around some more and got this to work with the DTS. Apparently a different result occurs if I use the DTS to save the TEXT field, as opposed to using the Query Analyzer and saving the results (which I was using for testing).