extract data from sqlserver

hi all
how can I go about extracting data from a sql database and piping to a file,
lets say .csv format.... any ideas appreciated
thnx
tmWhile this is possible with Reporting Services (using the file share
delivery extension), you might look at using a tool like DTS.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"tmmm" <a@.a.com> wrote in message
news:eSzJTPFVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> hi all
> how can I go about extracting data from a sql database and piping to a
file,
> lets say .csv format.... any ideas appreciated
> thnx
> tm
>

Extending the CSV Export to add Text Padding etc.

Hi All,
I am looking at using are report to generate an export file that is similar
to the CSV export...
I need to be able to the text length of the columns and remove the ","
between columns eg:
CSV
Angus,Logan,Data#3
REQUIRED EXPORT
Angus Logan Data#3
text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to 10
(add 4 spaces)
I am thinking about programatically downloading the CSV (or Xml) and parsing
them into the right format but thought there may be an easier solution...
Any Ideas?
Regards
Angus Logan
MCDBA / MCADYou can use the PadRight() method to add the spaces and then use the CSV
renderering extension and set the deviceInfo named FieldDelimiter to
emptystring. You will have to use URL Access or the SOAP API.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angus Logan" <angus_logan@.data3.com.au> wrote in message
news:ePRIhZnYEHA.556@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking at using are report to generate an export file that is
> similar
> to the CSV export...
> I need to be able to the text length of the columns and remove the ","
> between columns eg:
> CSV
> Angus,Logan,Data#3
> REQUIRED EXPORT
> Angus Logan Data#3
> text+pad to 10 (add 5 spaces) text+pad to 10 (add 5 spaces) text+pad to
> 10
> (add 4 spaces)
> I am thinking about programatically downloading the CSV (or Xml) and
> parsing
> them into the right format but thought there may be an easier solution...
> Any Ideas?
> Regards
> Angus Logan
> MCDBA / MCAD
>

Extend CSV Rendering

Is there anyway to extend the existing CSV rending to change deviceinfo
settings that would be used when export is selected from the standard
ReportServer interface.
Example would be to have TXT rendering capability that used the CSV
rendering components, but changed the deviceinfo settings so that
Extension=txt, FieldDelimiter=|. I want to use the parameter selection
capability, and then allow the user to export to this TXT format instead of
CSV.
Logically I would think that you could added some DeviceInfo settings to the
config file in the Report Service directory where Render is defined. But I
am unable to find any sort of documentation.
Also the BOL talks about extending existing rendering formats, as opposed to
create custom rendering extensions. I have yet to find out how it is
possible to extend the CSV rendering extension to accomplish this. I really
want to be able to leverage the existing CSV rendering extension, to modify
a couple of settings.
I know that this can be done via URL and Hidden values in the calling web
page that shells the Report Services window. But when you click the Export
functionality and pick CSV none of the CSV deviceinfo settings are carried
forward to the Export window that is opened before prompting to Save or Open
the file.
Thanks,
MikeUnfortunately, we do not support setting the device info through report
manager.
Regarding the BOL reference to extending rather than writing your own: I
think this might just be a case of terminology confusion. You cannot extend
a particular format. You can extend the report server by adding your own
formats. I'll see about getting the wording changed.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael McCallum" <mmccallum@.honovi.net> wrote in message
news:%23D35dY2FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to extend the existing CSV rending to change deviceinfo
> settings that would be used when export is selected from the standard
> ReportServer interface.
> Example would be to have TXT rendering capability that used the CSV
> rendering components, but changed the deviceinfo settings so that
> Extension=txt, FieldDelimiter=|. I want to use the parameter selection
> capability, and then allow the user to export to this TXT format instead
> of CSV.
> Logically I would think that you could added some DeviceInfo settings to
> the config file in the Report Service directory where Render is defined.
> But I am unable to find any sort of documentation.
> Also the BOL talks about extending existing rendering formats, as opposed
> to create custom rendering extensions. I have yet to find out how it is
> possible to extend the CSV rendering extension to accomplish this. I
> really want to be able to leverage the existing CSV rendering extension,
> to modify a couple of settings.
> I know that this can be done via URL and Hidden values in the calling web
> page that shells the Report Services window. But when you click the
> Export functionality and pick CSV none of the CSV deviceinfo settings are
> carried forward to the Export window that is opened before prompting to
> Save or Open the file.
> Thanks,
> Mike
>|||Thanks for the information. Maybe this could be a wishlist type item?
Mike
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eg8QrjHGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, we do not support setting the device info through report
> manager.
> Regarding the BOL reference to extending rather than writing your own: I
> think this might just be a case of terminology confusion. You cannot
> extend a particular format. You can extend the report server by adding
> your own formats. I'll see about getting the wording changed.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Michael McCallum" <mmccallum@.honovi.net> wrote in message
> news:%23D35dY2FFHA.3648@.TK2MSFTNGP09.phx.gbl...
>> Is there anyway to extend the existing CSV rending to change deviceinfo
>> settings that would be used when export is selected from the standard
>> ReportServer interface.
>> Example would be to have TXT rendering capability that used the CSV
>> rendering components, but changed the deviceinfo settings so that
>> Extension=txt, FieldDelimiter=|. I want to use the parameter selection
>> capability, and then allow the user to export to this TXT format instead
>> of CSV.
>> Logically I would think that you could added some DeviceInfo settings to
>> the config file in the Report Service directory where Render is defined.
>> But I am unable to find any sort of documentation.
>> Also the BOL talks about extending existing rendering formats, as opposed
>> to create custom rendering extensions. I have yet to find out how it is
>> possible to extend the CSV rendering extension to accomplish this. I
>> really want to be able to leverage the existing CSV rendering extension,
>> to modify a couple of settings.
>> I know that this can be done via URL and Hidden values in the calling web
>> page that shells the Report Services window. But when you click the
>> Export functionality and pick CSV none of the CSV deviceinfo settings are
>> carried forward to the Export window that is opened before prompting to
>> Save or Open the file.
>> Thanks,
>> Mike
>|||Yes, we plan (at some point :-)) to expose UI around device info settings.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael McCallum" <mmccallum@.honovi.com> wrote in message
news:%23oBG%234OGFHA.1836@.tk2msftngp13.phx.gbl...
> Thanks for the information. Maybe this could be a wishlist type item?
> Mike
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:eg8QrjHGFHA.2156@.TK2MSFTNGP09.phx.gbl...
>> Unfortunately, we do not support setting the device info through report
>> manager.
>> Regarding the BOL reference to extending rather than writing your own: I
>> think this might just be a case of terminology confusion. You cannot
>> extend a particular format. You can extend the report server by adding
>> your own formats. I'll see about getting the wording changed.
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Michael McCallum" <mmccallum@.honovi.net> wrote in message
>> news:%23D35dY2FFHA.3648@.TK2MSFTNGP09.phx.gbl...
>> Is there anyway to extend the existing CSV rending to change deviceinfo
>> settings that would be used when export is selected from the standard
>> ReportServer interface.
>> Example would be to have TXT rendering capability that used the CSV
>> rendering components, but changed the deviceinfo settings so that
>> Extension=txt, FieldDelimiter=|. I want to use the parameter selection
>> capability, and then allow the user to export to this TXT format instead
>> of CSV.
>> Logically I would think that you could added some DeviceInfo settings to
>> the config file in the Report Service directory where Render is defined.
>> But I am unable to find any sort of documentation.
>> Also the BOL talks about extending existing rendering formats, as
>> opposed to create custom rendering extensions. I have yet to find out
>> how it is possible to extend the CSV rendering extension to accomplish
>> this. I really want to be able to leverage the existing CSV rendering
>> extension, to modify a couple of settings.
>> I know that this can be done via URL and Hidden values in the calling
>> web page that shells the Report Services window. But when you click the
>> Export functionality and pick CSV none of the CSV deviceinfo settings
>> are carried forward to the Export window that is opened before prompting
>> to Save or Open the file.
>> Thanks,
>> Mike
>>
>|||Lukasz, Is there any complexity in rendering dynamically generated matrix
total columns in CSV format? I have been having this problem that when I
export my reports to CSV, if a matrix in the report has column groupings as
well as dynamic total columns, these total columns don't generate in CSV
version. I was thinking of writing my own CSV renderer but then I thought
there's probably a complexity into that, otherwise the Microsoft CSV renderer
would do it. Is that right? Or do you think CSV rendere just wasn't intended
to render dynamic columns in a matrix?
"Lukasz Pawlowski [MSFT]" wrote:
> Yes, we plan (at some point :-)) to expose UI around device info settings.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Michael McCallum" <mmccallum@.honovi.com> wrote in message
> news:%23oBG%234OGFHA.1836@.tk2msftngp13.phx.gbl...
> > Thanks for the information. Maybe this could be a wishlist type item?
> > Mike
> >
> > "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> > news:eg8QrjHGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> >> Unfortunately, we do not support setting the device info through report
> >> manager.
> >>
> >> Regarding the BOL reference to extending rather than writing your own: I
> >> think this might just be a case of terminology confusion. You cannot
> >> extend a particular format. You can extend the report server by adding
> >> your own formats. I'll see about getting the wording changed.
> >>
> >> -Lukasz
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >> "Michael McCallum" <mmccallum@.honovi.net> wrote in message
> >> news:%23D35dY2FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> >> Is there anyway to extend the existing CSV rending to change deviceinfo
> >> settings that would be used when export is selected from the standard
> >> ReportServer interface.
> >>
> >> Example would be to have TXT rendering capability that used the CSV
> >> rendering components, but changed the deviceinfo settings so that
> >> Extension=txt, FieldDelimiter=|. I want to use the parameter selection
> >> capability, and then allow the user to export to this TXT format instead
> >> of CSV.
> >>
> >> Logically I would think that you could added some DeviceInfo settings to
> >> the config file in the Report Service directory where Render is defined.
> >> But I am unable to find any sort of documentation.
> >>
> >> Also the BOL talks about extending existing rendering formats, as
> >> opposed to create custom rendering extensions. I have yet to find out
> >> how it is possible to extend the CSV rendering extension to accomplish
> >> this. I really want to be able to leverage the existing CSV rendering
> >> extension, to modify a couple of settings.
> >>
> >> I know that this can be done via URL and Hidden values in the calling
> >> web page that shells the Report Services window. But when you click the
> >> Export functionality and pick CSV none of the CSV deviceinfo settings
> >> are carried forward to the Export window that is opened before prompting
> >> to Save or Open the file.
> >>
> >> Thanks,
> >> Mike
> >>
> >>
> >>
> >
> >
>
>

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>

Exporting to Excel 2000

IT support won't consider giving Excel XP or 2003 to users at this stage.
Other than letting them choose CSV as export format for the report and loading the CSV into Excel 2000, does anybody have an idea how I could easily deliver Reports to Excel 2000?SP1 was released recently. Try installing that as it allows Excel 97/2000
exporting.
Regards,
Benjamin Pierce
"James" <news@.att.com> wrote in message
news:F7701A3F-297C-44D2-8CFB-1BD2A40FEE91@.microsoft.com...
> IT support won't consider giving Excel XP or 2003 to users at this stage.
> Other than letting them choose CSV as export format for the report and
loading the CSV into Excel 2000, does anybody have an idea how I could
easily deliver Reports to Excel 2000?

Exporting to CSV format : possible bug ?

Hi,

I have a report, I can export it to Excel, XML and other format ..but I can not export it to .CSV format ...only thing I see in CSV file is some garbage character in first row,first column.

I have RS 2005.

How can I make this thing work ? How can I export report to CSV format ?

thanks,

prashant

fixed..

please see this post ..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335761&SiteID=1

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 to CSV (comma delimited)

Hi,
I've done a report based on several fields and i want to export my report in
a CSV file. Unfortunatly my fields have commas in it. So, is there a
opportunity to change
the deliminator to e.g. ; or #?
Thanks for answering
Tom.Sufix the report URL with &rs:format=CSV&rc:FieldDelimiter=%23 for #
delimiter and &rs:format=CSV&rc:FieldDelimiter=";" for semi-colon delimiter.
Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
for an explanation on FieldDelimiter CSV device info setting.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" <tom.suhr@.bbtsoftware.ch> wrote in message
news:uoNyKwlaEHA.996@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've done a report based on several fields and i want to export my report
in
> a CSV file. Unfortunatly my fields have commas in it. So, is there a
> opportunity to change
> the deliminator to e.g. ; or #?
> Thanks for answering
> Tom.
>
>|||Hello,
thanks for your help. Unfortunatly I still have the problem.
because I do not wan't to programm enything. Is it possible
to define the deliminator in the report.rdl or whithin the report-manager?
My solution should be independent from any program, and I want,
that the administrator only has to define the time of the job. My locig
is all in a stored procedure.
Thanks for helping
Tom.
"Ravi Mumulla (Microsoft)" wrote:
> Sufix the report URL with &rs:format=CSV&rc:FieldDelimiter=%23 for #
> delimiter and &rs:format=CSV&rc:FieldDelimiter=";" for semi-colon delimiter.
> Refer to
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> for an explanation on FieldDelimiter CSV device info setting.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tom" <tom.suhr@.bbtsoftware.ch> wrote in message
> news:uoNyKwlaEHA.996@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I've done a report based on several fields and i want to export my report
> in
> > a CSV file. Unfortunatly my fields have commas in it. So, is there a
> > opportunity to change
> > the deliminator to e.g. ; or #?
> >
> > Thanks for answering
> >
> > Tom.
> >
> >
> >
>
>|||Hello,
thanks for your help. Unfortunatly I still have the problem.
because I do not wan't to programm enything. Is it possible
to define the deliminator in the report.rdl or whithin the report-manager?
My solution should be independent from any program, and I want,
that the administrator only has to define the time of the job. My locig
is all in a stored procedure.
Thanks for helping
Tom.
"Ravi Mumulla (Microsoft)" wrote:
> Sufix the report URL with &rs:format=CSV&rc:FieldDelimiter=%23 for #
> delimiter and &rs:format=CSV&rc:FieldDelimiter=";" for semi-colon delimiter.
> Refer to
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> for an explanation on FieldDelimiter CSV device info setting.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tom" <tom.suhr@.bbtsoftware.ch> wrote in message
> news:uoNyKwlaEHA.996@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I've done a report based on several fields and i want to export my report
> in
> > a CSV file. Unfortunatly my fields have commas in it. So, is there a
> > opportunity to change
> > the deliminator to e.g. ; or #?
> >
> > Thanks for answering
> >
> > Tom.
> >
> >
> >
>
>|||No. You'd have to specify the device info settings while accessing the
report via URL or rendering a report using SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"tomline.se" <tomline.se@.discussions.microsoft.com> wrote in message
news:5298DD2A-AD06-4EE0-90B6-549746B063C4@.microsoft.com...
> Hello,
> thanks for your help. Unfortunatly I still have the problem.
> because I do not wan't to programm enything. Is it possible
> to define the deliminator in the report.rdl or whithin the report-manager?
> My solution should be independent from any program, and I want,
> that the administrator only has to define the time of the job. My locig
> is all in a stored procedure.
> Thanks for helping
> Tom.
>
> "Ravi Mumulla (Microsoft)" wrote:
> > Sufix the report URL with &rs:format=CSV&rc:FieldDelimiter=%23 for #
> > delimiter and &rs:format=CSV&rc:FieldDelimiter=";" for semi-colon
delimiter.
> > Refer to
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> > for an explanation on FieldDelimiter CSV device info setting.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "Tom" <tom.suhr@.bbtsoftware.ch> wrote in message
> > news:uoNyKwlaEHA.996@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > I've done a report based on several fields and i want to export my
report
> > in
> > > a CSV file. Unfortunatly my fields have commas in it. So, is there a
> > > opportunity to change
> > > the deliminator to e.g. ; or #?
> > >
> > > Thanks for answering
> > >
> > > Tom.
> > >
> > >
> > >
> >
> >
> >

Exporting to CSV

I have a report which is just a simple table with only the detail row (which then as about a dozen columns)

I have uploaded this to report manger. The report generates correctly and I export to CSV.

But when I open the CSV file there are Headings at the top. I want to know is there any way of getting rid of there headers so it is jus the data which shows.

(The headers seem to be the name of the textbox column in reporting services)

See this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1487851&SiteID=1

cheers,

Andrew

exporting to CSV

I have a need to run an export of fields from a table into a CSV text file located on my e:\exports. Is there a simple way to do this?

Hi jim,

Why cant you use DTS service to transfer the tables datas into CSV file ?

|||

use the following query...

master..xp_cmdshell 'bcp "select * from master..sysobjects" queryout e:\exports\exported.csv -SMYSERVER -UMyUserName -pMyPassword -c -t "," -r "\n"'

|||thanks I will do!

Exporting to CSV

I'm not sure if this is possible or not, still new to RS. I need to export a file in CSV format but a vendor needs the very first character to be a '~'. I've tried adding that to the first column name without sucess and RS won't allow me to name it with the ~ in the properties box. Any ideas? Thanks

Can you have the ~ be the only thing on the first row? If so you can write it into the "Header" section. If not, you can write a script task to go in and add it to the first line (among many other options I'm sure).

exporting to CSV

Hi,

I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms

Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal

1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)

So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.

I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.

Thanks for your time and help
:)use DTS export wizard...then schedule it...
Katie wrote:

Quote:

Originally Posted by

Hi,
>
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
>
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
>
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
>
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
>
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
>
Thanks for your time and help
:)

|||How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.

Cimode wrote:

Quote:

Originally Posted by

use DTS export wizard...then schedule it...
Katie wrote:

Quote:

Originally Posted by

Hi,

I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms

Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal

1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)

So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.

I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.

Thanks for your time and help
:)

|||Katie wrote:

Quote:

Originally Posted by

How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.

You can write a custom DTS package that is called from a stored proc.
Parameters to that stored proc will be the parameters you want to query
on. The stored proc will set global DTS variables based on these
parameters and DTS will use these variables to filter out the unwanted
records. You'll probably want a good book on DTS as this sort of thing
can be a bear for the uninitiated. SQL Server 2000 DTS Step By Step by
Carl Rabeler worked for me!

Cheers,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||What you can do is generate your DTS package and schedule it. As a
result you will get an identifier for that package. You may then call
the package from some user event.

Katie wrote:

Quote:

Originally Posted by

How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.
>
Cimode wrote:

Quote:

Originally Posted by

use DTS export wizard...then schedule it...
Katie wrote:

Quote:

Originally Posted by

Hi,
>
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
>
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
>
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
>
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
>
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
>
Thanks for your time and help
:)

exporting to csv

Hi
have had a look this morning through previous postings but cant quite find
what Im looking for, as a complete beginner to reporting services Im a bit
behind in how to do things and where configurations are set
what I have is a report that needs to be approved by human eye before being
exported(csv) for import to financial package
what I want to do is have a lot of information in the report to aid the
manual check(name,address etc), but then only export the information required
for the import(account number,amount) when the user selects the export option
if someone could point me in the general direction I would be grateful
many thanks
STuartHere is how I do this. Create your report as normal. Then copy it and remove
all the extraneous stuff so all you have is. For CSV you really want to only
have a table, don't have any extra fields on the report. Then at the top of
the page of the first report have a textbox. Have the text say something
like, Export Data, underline it and make it blue. Right mouse click
properties, advanced properties, navigation, jump to URL. Call the second
report (I hide this report in list view so the user doesn't normally see
it). In my case I wanted it to come up in another window automatically
(which pulls it up into Excel if you click open, in your case they would
click save and save to whatever filename they want).
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
A few things to note, I specify ASCII because the default is Unicode. I
assume you want ASCII. If you want unicode then just leave off that part.
Also be sure to be careful with the parameters, the parameter names are case
sensitive.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:FF0C86A2-D53C-4EE1-B952-3C7DE218B0E3@.microsoft.com...
> Hi
> have had a look this morning through previous postings but cant quite find
> what Im looking for, as a complete beginner to reporting services Im a bit
> behind in how to do things and where configurations are set
> what I have is a report that needs to be approved by human eye before
> being
> exported(csv) for import to financial package
> what I want to do is have a lot of information in the report to aid the
> manual check(name,address etc), but then only export the information
> required
> for the import(account number,amount) when the user selects the export
> option
> if someone could point me in the general direction I would be grateful
> many thanks
> STuart

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,
Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,

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

exporting SQL tables

Hello:
I am familiar with SQL 2000's Export wizard in terms of exporting data to a
csv or a text file.
Is there any way to export more than one table at a time, though, other than
through something like BCP?
Let's say that you were to use BCP, to export more than one table at a time.
In SQL 2000, when you use BCP to export one or more tables, how would you
get the column headers to show in the exported file?
childofthe1980sHi
If you just want the data then you would not need column headers!
See if http://www.sqldts.com/299.aspx does what you want!
John
"childofthe1980s" wrote:
> Hello:
> I am familiar with SQL 2000's Export wizard in terms of exporting data to a
> csv or a text file.
> Is there any way to export more than one table at a time, though, other than
> through something like BCP?
> Let's say that you were to use BCP, to export more than one table at a time.
> In SQL 2000, when you use BCP to export one or more tables, how would you
> get the column headers to show in the exported file?
> childofthe1980s

Exporting sql table data to csv format

Hi
I am trying to export an table data to csv format. The problem here is the table columns are dynamic. The DTS exports only the columns available during the DTS design time and it ignores if any new columns are added after the design. I need solution for this asap.
Thanks
SqlJerinUse BCP to do that,or you could achieve this using the command line utilities like isql.exe and osql.exe.
I think that will solve your problem...

From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "Put your query" -o "E:\output.csv"

From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "put your query" -o "E:\output.csv"'|||Hi
I am trying to export an table data to csv format. The problem here is the table columns are dynamic. The DTS exports only the columns available during the DTS design time and it ignores if any new columns are added after the design. I need solution for this asap.
Thanks
SqlJerin
A better question is: Why is your schema changing after the design phase?|||While there might be a good reason for this, it makes me nervous. What kind of changes are you making to the schema on an ongoing basis, and what is the underlying (business) reason for making those changes? This sounds like a problem in search of a victim to me.

-PatP|||Hello Rudra
Thanks for your immediate reply. The BCPs are not working in this context as it generates ununiform data in the csv file and it doesn't provide column headers. I prefer dynamic DTS export solutions.
Thanks
SqlJeba|||Hello Rudra
The BCPs are not working in this context as it generates ununiform data in the csv file and it doesn't provide column headers. Thanks
SqlJeba
yea,that will not help you,dynamic columns right..ok this can help you a bit
link
(http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/thread/db64c75d96945d07/fadd21a55a86d51d?lnk=st&q=dynamic+column+in+dts+export+sql+server+2000&rnum=1&hl=en#fadd21a55a86d51d)

it doesn't provide column headers.

You haven't mentioned column header in your first post,anyway