Export Report to File: columns

Showing posts with label columns. Show all posts

Tuesday, March 27, 2012

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob Boles
This isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:
> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in
a
> long text field (defined as ntext (16)). When I run the query and export t
o
> Excel the text is truncated at 255 characters. How do I get all the text t
o
> export?
> Thanks,
> Bob Boles

Sunday, March 25, 2012

Exporting Data from SDF Database to CSV file

Hi guys,

Would any of you be able to provide some guide on how am I going to export the selected data (multiple rows and columns) into Excel or CSV file?

Or at least export into Text file, which I can later on save the file name as .CSV, so it become a CSV file after saving.

Thanks.

Regards,

Jenson

In which context is the data "selected". You can always enumerate your data and use the System.IO namespace to create the CSV file in code?|||

Hi Erik,

Yes Erik, that's what I intend to do, do you have any guide for me to follow, or any sample codes to refer to? I need to refer to them and write my own one, as I don't really think what I wanted to do is the same, I just need to structure and check how they do it, and what are the various ways to achieve the same thing.

Thanks.

Regards,

Jenson

|||

Hope this sample is what you are looking for:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlServerCe;

namespace ExportSDF

{

class Program

{

static void Main(string[] args)

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

try

{

// Based on this sample: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx

// Open the connection and create a SQL command

conn = new SqlCeConnection(@."Data Source = C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf;max database size=256");

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM Customers", conn);

rdr = cmd.ExecuteReader();

System.IO.TextWriter stm = new System.IO.StreamWriter(new System.IO.FileStream(@."C:\customers.csv", System.IO.FileMode.Create), Encoding.Default);

// Iterate through the results

while (rdr.Read())

{

// Write all fields except the last one...

for (int i = 0; i < rdr.FieldCount-2; i++)

{

if (rdr[i] != null)

{

stm.Write(rdr[i].ToString());

stm.Write(";");

}

else

{

stm.Write(";");

}

if (rdr[rdr.FieldCount-1] != null)

{

stm.Write(rdr[0].ToString());

}

stm.Write(System.Environment.NewLine);

}

// Always dispose data readers and commands as soon as practicable

stm.Close();

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

conn.Close();

}

Happy coding!

|||Hi Erik,

Thanks for the great reply and sorry for the late reply. The reason behind is I have finished the workaround for this requirements and it's working just fine. By looking at your code, I find that this code is even easier to understand!

I will try it out later as I'm currently busy with other stuff. Thanks for the great codes, Erik!

Have marked your answer as answer =)

Monday, March 19, 2012

Export wizard trouble exporting columns names to ragged file.

I’m using the Import\Export wizard to export the top 5 lines from a MS Sql table into a fixed format (“ragged”) file.But I want the first record to contain the column names of the exported fields so I selected the “Column names in the first data row” option of the “Choose a Destination” box. When I run the Package I get:

>>>

· Information 0x402090dc: Data Flow Task: The processing of file "C:\barkingdog\ExportWithheader.txt" has started (SQL Server Import and Export Wizard)

· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)

>>>

When I de-select the “Column names” option, the package works fine.Other than manually, how can I et the column names in output file?

TIA,

Barkingdog

That's very strange. I'm racking my brains as to what might be the problem but I can't think of anything!

Is there any other clue as to what is the problem?

-Jamie

|||

The problem here is that when you include column names in the first data row, then you might need to modify the column width (for a ragged right file). For example, if you have a column CustomerID which is of type DT_STR(5), it will fail trying to write "CustomerID" in that space, which takes 10 characters. Use the Edit Transform button on configure Flat File Destination page of the Import Export Wizard to adjust the length specified for each fixed width column.

hope that helps.

|||

Ranjetta,

I thought that that could be the case but I haven't had a chance to test it.

I also have to test if the "Import" wizard, with "column names in first row", has the same issue. My impression is no and perhaps the Export wizard is not as forgiving as the Import one. I will check.

Barkingdog

Sunday, March 11, 2012

Export to sqlserver

when i export tables to another database then that table which has columns identity(yes),identity seed=1,increment=1 properties removed on destination table.Why?Identity columns and key constraints are not preserved when using the standard replication service in SQL Server, you must use Transactional Shipping Logs.

Friday, March 9, 2012

Export to PDF Is a Mess

I have several reports on a report server that export to Excel just fine. When I export them to PDF, the columns don't fit, the font gets much larger and the columns that got cut off are put on extra pages. It is absolutely unusable. Any suggestions out there?

Hi, I had lots of problems with this when I was starting out.

I found that more often than not it was caused by the page property settings of the report found in report designer.

There are page size properties. check that these match the output you want.

e.g I want an A4 portrait output,so set it page witdh to 21cm; page length to 29.7cm

if it is A4 landscape I want the width = 29.7 length is 21cm

Also important in page properties are margins, most important are the left and right margins.

They are important because if in the designer the body of your report plus these page margins is greater than an A4 output then PDFs will spill onto other pages.

e.g Page property is a4 landscape 29.7width. Page margins are 2cm left and 2cm right. The width of the body of my report is 26cm.

I have a problem because :26(body) + 2(L margin) +2(R margin) = 30 which is greater the the page output property (29.7).

Solution: reduce the margins to 1cm each .

|||Yes, you are right. I found the margin settings and reduced all of them. The columns all appear on the same page now but there is a blank page between each page of the report. The blank page has only the header on it. I have the page size set to 8.5 x 11. Any ideas on that one?|||Width of your report content + left margin + right margin <=8.5 in your case. Defatult margin is 1in, so your report width should be 6.5in or less. If this is your case, you can either change margin size or reduce your content width.|||

I've got the margins working just fine now. It's the blank pages I can't figure out. I get a PDF like this:

Page 1: Header with data
Page 2: Header and no data
Page 3: Header with next data
Page 4: Header and no data

|||What is the width of your rdlc report and your margins from the report properties' Layout tab?|||

It's an .rdl report with these parameters:

Interactive size: 8.5in, 11in
Margins: 0.5in, 0.25in, 1in, 0.25in
PageSize: 8.5in, 11in

|||Thank you for giving me this. But what I want to know most is the ruler width (the top ruler of the report in the design view). This is the Width of the report.|||Sorry about that. The grid goes out to the 8" mark. Should I pull it in some?|||

Hello:

This is the problem. You page width now is 8+your margins. You need to reduce this 8 to make room for your margins. You will be fine after that.

|||Yes! I pulled it in to 7.75 and no more blank pages. I understand what you mean now. The report width cannot exceed the page width minus the sum of the margins. Iwas .25 off.

Wednesday, March 7, 2012

Export to Excel yields extra columns

I have a table in a report and the report also includes a page header area.
When I export to Excel, I get a lot of extraneous additional columns in
between my columns of data -why is this and how do I get rid of these?
thanks!
MarthaYou have to make your headings the same width as all of columns below it.
Martha wrote:
>I have a table in a report and the report also includes a page header area.
>When I export to Excel, I get a lot of extraneous additional columns in
>between my columns of data -why is this and how do I get rid of these?
>thanks!
>Martha
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1

Export to Excel Problem..! URGENT

Hi All,
I'm facing one problem when I'm exporting my report to excel. I have a
report with 200 pages and 120 columns. When I'm exporting that report, After
waiting for some time (I think its around 3 min) its asking windows challenge
box for my user credentials, even after giving the correct credentials also
its saying that "You are not authorized to view this page". Some times its
giving a different error like "ASP.Net" yellow page error. But for the small
reports its working fine.
Can any one help me out on this issue. Early replies will highly appreciated.
Thanks,
VeereshMy guess it that your timing out, there are several spots that timeouts can
be set, manually time how long it takes to get the log on prompt. Find this
number in the settings, then raise it. IIS timeout, web.config?,
machine.config? , report server setting, sql timeout, web service timeout, on
so on......
Good luck.
"Veeresh" wrote:
> Hi All,
> I'm facing one problem when I'm exporting my report to excel. I have a
> report with 200 pages and 120 columns. When I'm exporting that report, After
> waiting for some time (I think its around 3 min) its asking windows challenge
> box for my user credentials, even after giving the correct credentials also
> its saying that "You are not authorized to view this page". Some times its
> giving a different error like "ASP.Net" yellow page error. But for the small
> reports its working fine.
> Can any one help me out on this issue. Early replies will highly appreciated.
> Thanks,
> Veeresh
>|||KENWOOD is dead on with his suggestion. Had the same thing happen. IIS is
set to timeout at 120 seconds or something, and so these requests run too
long, and it craps out. Up your IIS timeout and you are good to go.
"KENWOOD" wrote:
> My guess it that your timing out, there are several spots that timeouts can
> be set, manually time how long it takes to get the log on prompt. Find this
> number in the settings, then raise it. IIS timeout, web.config?,
> machine.config? , report server setting, sql timeout, web service timeout, on
> so on......
> Good luck.
> "Veeresh" wrote:
> > Hi All,
> > I'm facing one problem when I'm exporting my report to excel. I have a
> > report with 200 pages and 120 columns. When I'm exporting that report, After
> > waiting for some time (I think its around 3 min) its asking windows challenge
> > box for my user credentials, even after giving the correct credentials also
> > its saying that "You are not authorized to view this page". Some times its
> > giving a different error like "ASP.Net" yellow page error. But for the small
> > reports its working fine.
> >
> > Can any one help me out on this issue. Early replies will highly appreciated.
> >
> > Thanks,
> > Veeresh
> >

Sunday, February 26, 2012

export to excel issues

can anyone explain, why when exporting a really simple spread to excel, one ends up with a ton of blank rows?

I have a report with 8 columns but when exporting to excel i end up with around 50 columns. All most are blank?

That is because Excel uses a whole lot of column merging to get the alignment as close as possible to the original report. It sounds like you have unmerged the columns/cells or have tried to copy and paste to another worksheet.

A simple way to fix this is to unmerge the cells, then copy just the columns you are interested in, or delete the blank columns altogether.

|||this may because of formating issues .try to format the fields properly or try to make some changes(like reduce or increase text size etc) in each field.|||

I'm not sure if you're seeing extra columns or rows or both, but it could be due to the size of your background (the white grid space in the body of the report). Make sure it is not bigger than your report items. I always get extra columns and rows in a dark grey in Excel if I don't size it correctly.

-Marianne

Friday, February 24, 2012

Export to Excel - Multiple Columns

I have a pretty simple report that when exported to excel some columns span multiple excel columns?

Anybody know how to correct this?

The renderer tries to mimic the layout you created in the report designer. That may be the reason that some columns expand multiple columns.

Try the CSV renderer if you want very basic output.

|||

The renderer will merge columns in order to preserve the layout you defined in the RDL file.

Most often, merged cells are caused when there are report items either below or above the table that has borders that do not align precisely with the table columns.

A classic example of this is a single text box above the table acting as a header. If that textbox's left and right edges begin and end exactly at one of the below table's columns then merging should not occur.

-Chris

Export to Excel - merge and centre issue

hi
I am exporting a report from SQL 2000 reporting services into excel and by
default all columns have merge and centre clicked. Can i change this so this
property is not checked which makes it easier for users to do things like
sorting etc.
Any help would be appreciated.
Thanks heaps
RidhimaIs this Excel export or CSV export? With CSV the default is Unicode which
Excel then merges (or doesn't split into different columns). In 2000 the
only thing you can do if this is the case is have a link the user clicks
that then uses jump to URL and you have it render in CSV ASCII. In RS 2005
you can configure it to export CSV in ASCII format as the default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ridhima Sood" <RidhimaSood@.discussions.microsoft.com> wrote in message
news:511E9718-8B0F-4A84-AF8D-71DA3598E104@.microsoft.com...
> hi
> I am exporting a report from SQL 2000 reporting services into excel and by
> default all columns have merge and centre clicked. Can i change this so
> this
> property is not checked which makes it easier for users to do things like
> sorting etc.
> Any help would be appreciated.
> Thanks heaps
> Ridhima

Export to Excel

Hi,

When our report exported to Excel, In the Excel file it is giving some extra columns between the data columns.How to avoid those extra columns.This extra columns causes problems when we want to sort one column it is throwing sort can't be applied on merged columns.

How to avoid the Extra columns.

Thanks in advance

Hello Mahima,

You need to make sure that everything in your report lines up, this includes objects in your report header and footer. When objects do not line up, it creates a column so that the excel export looks like the actual report (or as close as it can).

Hope this helps.

Jarret

|||

Hi Jarret,

Lineup means what we need to check, In my report I have one table in which table header is formed by merging two cells.

|||

Try to merge all the cells in the header. If it was a list you had to make sure that all fields (header and data) are vertically aligned i.e. same Y-Axis location.

Shyam

|||

Hi,

In my report header i have the text boxes those are in the following format:

Date: Parameters!Date.value

Name: parameters!Name.Value

and one image

How to merge these to line up to avoid excel extra columns

Thanks

|||

Hello,

Here's what I would do...

Turn 'Snap to grid' and 'Draw grid' options on. Set the position of the first textbox to have the same left position as one of the columns dividers in the table, then increase/decrease the width of the textbox so that the right edge falls on the same Y coordinate as another one of your column dividers. Do the same for your second textbox and your image.

Jarret

Sunday, February 19, 2012

Export to CSV and oepn in Excel

Is there a way to export to CSV and open the file directly in Excel without
saving it first? And, to have Excel parse the columns correctly when opening?
I can save the file and then import it in another step into Excel without
problems. But, when I Open in Excel instead of saving it first it has all the
exported csv columns in a single Excel column, i.e. Excel did not parse it
into a spreadsheet format.
Thanks,
SteveOn Jan 15, 4:46 pm, Steve <MyNoS...@.NoSpam.org> wrote:
> Is there a way to export to CSV and open the file directly in Excel without
> saving it first? And, to have Excel parse the columns correctly when opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
You might try setting the delimiter programmatically and use Microsoft
Automation to open the file using Excel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The problem here is that the default for CSV export is Unicode which Excel
puts all into one column (I don't know about the latest version of excel but
Excel 2003 and earlier does).
RS 2005 you can set this in config file to default to ASCII instead of
Unicode. Then it will do exactly as you want.
RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
CSV format.
RS 2005 solution:
RSReportServer.Config file make a backup before changing.

<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Note how I commented out the original and then added the new configuration.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> Is there a way to export to CSV and open the file directly in Excel
> without
> saving it first? And, to have Excel parse the columns correctly when
> opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all
> the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
>|||Bruce,
We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
suggestion and it works great.
I also tried modifying the config as suggested and restarting the report
server, but that didn't work for us. So, I guess unless I typed something
incorrectly that is strictly a RS 2005 solution?
Thanks much!
Regards,
Steve
"Bruce L-C [MVP]" wrote:
> The problem here is that the default for CSV export is Unicode which Excel
> puts all into one column (I don't know about the latest version of excel but
> Excel 2003 and earlier does).
> RS 2005 you can set this in config file to default to ASCII instead of
> Unicode. Then it will do exactly as you want.
> RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
> CSV format.
> RS 2005 solution:
> RSReportServer.Config file make a backup before changing.
> 
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> Note how I commented out the original and then added the new configuration.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> > Is there a way to export to CSV and open the file directly in Excel
> > without
> > saving it first? And, to have Excel parse the columns correctly when
> > opening?
> >
> > I can save the file and then import it in another step into Excel without
> > problems. But, when I Open in Excel instead of saving it first it has all
> > the
> > exported csv columns in a single Excel column, i.e. Excel did not parse it
> > into a spreadsheet format.
> >
> > Thanks,
> > Steve
> >
>
>|||Yes, it is a RS 2005 only solution.
As you have seen in RS 2000, exporting to CSV to get data into Exel is much
faster. Excel rendering is a good bit better in RS 2005 and I expect it will
be even better in RS 2008. Although I have CSV export configured as ASCII in
RS 2005 my users very seldom need to do that anymore. Pretty much they can
export to Excel with good enough performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:F9F9DFB8-52CF-49C0-8E88-023B45C7CBDD@.microsoft.com...
> Bruce,
> We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
> suggestion and it works great.
> I also tried modifying the config as suggested and restarting the report
> server, but that didn't work for us. So, I guess unless I typed something
> incorrectly that is strictly a RS 2005 solution?
> Thanks much!
> Regards,
> Steve
> "Bruce L-C [MVP]" wrote:
>> The problem here is that the default for CSV export is Unicode which
>> Excel
>> puts all into one column (I don't know about the latest version of excel
>> but
>> Excel 2003 and earlier does).
>> RS 2005 you can set this in config file to default to ASCII instead of
>> Unicode. Then it will do exactly as you want.
>> RS 2000 I added a link to my report and used Jump To URL to open up in
>> ASCII
>> CSV format.
>> RS 2005 solution:
>> RSReportServer.Config file make a backup before changing.
>> 
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> Note how I commented out the original and then added the new
>> configuration.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
>> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
>> > Is there a way to export to CSV and open the file directly in Excel
>> > without
>> > saving it first? And, to have Excel parse the columns correctly when
>> > opening?
>> >
>> > I can save the file and then import it in another step into Excel
>> > without
>> > problems. But, when I Open in Excel instead of saving it first it has
>> > all
>> > the
>> > exported csv columns in a single Excel column, i.e. Excel did not parse
>> > it
>> > into a spreadsheet format.
>> >
>> > Thanks,
>> > Steve
>> >
>>

Export the data as an XML file?

I have two tables in my database like this.

AppsTable with columns INSTANCE_NAME, VALUE.

SecondTable with columns UID,SID,XID

I need the xml file like below.

- -

where commonID is the Value of UID from the second table,

appInstance name is the instance_name from the appstable.

If i have values like this in second table:

SID UID XID

XAB ABC AB2

BAX 23D BCK

I need the xml file like this.

</appInstance>

- <appInstance name="sap">

<appID commonID="ABC">AB2appID>

</appInstance>

please help me.

From your explanations it is not clear to me what you are trying to do.

Can you provide a query that gives the following result:

Siebel ABC XAB 23D BAX

sap ABC AB2 23D BCK

Then formatting it as XML will be a FOR XML application.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hai Eugene,

Here i am giving the code what i have done for my requirement.

SELECT name,(SELECT * FROM TempTable_XREF

FOR XML AUTO,TYPE).query(' for $p in /TempTable_XREF

return

{data($p/@.UCM_UID)} --here the modification needed.

</APPID>

FROM AppsTable as APPINSTANCE

FOR XML AUTO

In the above query in place of @.ucm_uid i need one different value for each loop

In other words, I have 4 columns in my database table UCM_UID,S_ID, IX_ID,COMMON_ID

For the first time i need UCM_UID

For the second time i need S_ID

For the third time i need IX_ID

|||

What I didn’t understand was the relationship between the two tables you use. Without that I can’t interpret “the first time”, “the second time”, and “the third time”.

Do you really want to get a cross product of the two tables and format it as XML? Did you intend to add into your sub-query a correlation condition to the outer query?

As for the formatting part, did you mean something like

SELECT

name,

(SELECT * FROM TempTable_XREF FOR XML AUTO,TYPE)

.query('for $p in /TempTable_XREF

return

{data($p/@.UCM_UID)}

</APPID>

{data($p/@.S_ID)}

</APPID>

{data($p/@.IX_ID)}

</APPID>

FROM AppsTable as APPINSTANCE

FOR XML AUTO

Or the equivalent using purely FOR XML functionality:

SELECT

name,

(SELECT

COMMON_ID AS "APPID/@.CommonID",

UCM_UID AS "APPID/text()",

NULL AS dummy1,

COMMON_ID AS "APPID/@.CommonID",

S_ID AS "APPID/text()",

NULL AS dummy2,

COMMON_ID AS "APPID/@.CommonID",

IX_ID AS "APPID/text()"

FROM TempTable_XREF FOR XML PATH(''),TYPE)

FROM AppsTable FOR XML RAW('APPINSTANCE')

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

- <appInstance name="Siebel_80">

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

</appInstance>

- <appInstance name="SiebelSIA75_01">

</appInstance>

- <appInstance name="SiebelSIA75_02">

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

- <appInstance name="Siebel_80">

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

</appInstance>

- <appInstance name="SiebelSIA75_01">

</appInstance>

- <appInstance name="SiebelSIA75_02">

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene..

Here i am specifying the entire requirement.

I have two tables
one with appinstance name,appid.
secondwithucmid,siebelid,ixid.(there are many tables i have with these cols)

note:I have many tables with these 3 id columns.

In my xml file

The element <idxRef name=" CUSTOMER">
The customer here is my table name
Each tag of this type specifies different tables.

The element <appInstance name="siebel_80">
-this is the value from the first table.

As i have 3 ids in my table , here the common id would be
some appended text with any id from those 3 values.

HOPE YOU UNDERSTOOD MY REQUIREMENT ,
IF CAN'T PLEASE REPLY.

|||

First you should write a SQL query that gives you the following result.

BUS UNIT Siebel_80 Cust_1-UCM-123001 1-SIEB-123001

BUS UNIT Siebel_80 Cust_1-UCM-123002 1-SIEB-123002

BUS UNIT Siebel_80 Cust_1-UCM-123003 1-SIEB-123003

BUS UNIT Siebel_UCM Cust_1-UCM-123001 1-UCM-123001

BUS UNIT Siebel_UCM Cust_1-UCM-123002 1-UCM-123002

BUS UNIT Siebel_UCM Cust_1-UCM-123003 1-UCM-123003

BUS UNIT IXMAL_01 Cust_1-UCM-123001 1-IX-123001

BUS UNIT IXMAL_01 Cust_1-UCM-123002 1-IX-123002

BUS UNIT IXMAL_01 Cust_1-UCM-123003 1-IX-123003

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_3EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_3EY

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX3EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_Y

PAYMENT SAP46C_01 Pay-1-3EY 0001

PAYMENT SiebelSIA75_01 Pay-1-3EY 1-3EY

PAYMENT SiebelSIA75_02 Pay-1-3EY 1-3EY

Then such query can be transformed into a FOR XML query that formats the result as XML in the desired shape. You’ll have to use new FOR XML features of SQL Server 2005. See my post above for examples and also look at FOR XML articles in BOL. Use WITH XMLNAMESPACES to add XML namespace into XML results. Let me know if you need any help with FOR XML query.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Fine Eugene, I got your point.

But i am not able to get my result in a single sql query

I have written the following queries to get the format what you have specified in the above reply.

SELECT AppsTable.name,TempTable_XREF.UCM_UID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_UCM'

SELECT AppsTable.name,TempTable_XREF.SIEBEL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_80'

SELECT AppsTable.name,TempTable_XREF.IXMAL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='IXMAL_01'

I have referred SQLSERVER DOC but i am not able to write the single query by using ' FOR XML ' clause. Please Reply how to get the same result in XML for the above queries using the FOR XML clause in one sql query.

Thanks,

|||

You could have used UNION ALL between the three queries ;-)

Here’s a draft FOR XML query for the three queries you provided using SQL Server 2005 sub-query FOR XML and PATH mode:

WITH XMLNAMESPACES (DEFAULT 'http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon')

SELECT

'BUS UNIT' AS "idXRef/@.name",

'Siebel_UCM' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.UCM_UID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_UCM'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator1",

'Siebel_80' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.SIEBEL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_80'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator2",

'IXMAL_01' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.IXMAL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='IXMAL_01'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*"

FOR XML PATH('listOfIDXRefData')

I can’t verify the syntax so the query may contain parse errors.

You’ll need to add your ordering if necessary.

Note that the result will be in UTF-16 encoding and there will be no XML declaration. If you need XML declaration you’ll have to add it separately.

If you need this kind of XML formatting in SQL Server 2000 environment you’ll need to use FOR XML EXPLICIT.

You may want to take a look at your data modeling side of story – I don’t see much sense in AppsTable in the FROM clause of your queries.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thank u verymuch Eugene,

Atlast i got the solution from your side.

Eugene, After running the above query , it is showing the namespace for each and every tag ( eg: <appid xmlns="http://..........")

How can i resolve this problem. Except this everything is ok.

Thanks,

Rao.

|||

Rao,

Unfortunately, there’s no way to remove the extraneous XML namespace declarations in SQL Server 2005. They add verbosity but do not change XML content in most XML application data models. You could work it around with string concatenations but it would lead to highly unmaintainable code.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Got your point thanks Eugene.

Regards,

Rao.

|||

Hai Eugene,

To generate the XML file,

Currently we are working with sample database which contains less number of records, as the process going on we will have lakhs of records with in the database.

Will the same query be ok for that also? or it leads to any performance issues.

export table to excel

how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3.

I'm fairly new to using SQL server, and not experienced at all with Transact-SQL.

using enterprise manager from client manager... there is export task wizard... you can easily achieve this.....

mandip

|||while i can do this from my pc, there are some clients that will need to be doing the same thing who will not have enerprise manager on their computer...|||

If these other clients have MS Access, you can link the SQL Server table to Access, and they can use the Export feature within Access. (Right-click on the table name, choose "Export...", etc.) You could also write an Access query if you wish to control the columns that will be exported, as well as the column titles and sort order.

Dan

|||they may or may not have access, this would also require me show them how to do this (show users who are not reliably familiar with access or mysql). is it possibe to create an application or script that could do this?|||

I'm sure that you could write a .NET web application. Such web applications written by my coworkers typically call stored procedures that contain the desired SELECT statement. As such, the stored procedure returns a table. If multiple SELECT statements are in the stored procedure, it returns multiple tables.

You may need additional software to convert the SELECT output to an Excel spreadsheet. Perhaps you can do that with SSIS. You might consider asking that in the SSIS forum.

What software can you expect users to have on their PCs?

|||they will be using xp machines with .netframework and the basic microsoft office supplies (word, excel, outlook, maybe access), generally office 2003|||

You may wish to explore using BCP.exe (perhaps installing the BCP utility on the users computers. Then you could create a batch file for the users to run.

With BCP running locally, you can easily output to a local file.

Otherwise, you could create a Stored Procedure that uses SLQCmd.exe, BCP, and XCopy to create file on the server, and then move that file to the local computer. However, this option will be more problematic due to network security concerns.

|||

Arnie,

Is BCP a "free" utility, or are there licensing considerations concerning placing BCP on all the users' computers?

Dan

|||

BCP can be used on any properly licensed SQL Server, and it can be used by anyone having access to that server and either a CAL, or the server accessed is covered with a processor license.

As far as I am aware, it is freely distributable to licensed users in your organization. However, for the definitive answer related to licensing questions, call the licensing folks:

Licensing –Microsoft, Contact
(800) 426-9400

From: http://www.microsoft.com/sql/howtobuy/faq.mspx

Do I need a separate license to run the SQL Server tools and analysis services? A.

No, a separate license is not required. However, any device that has SQL Server tools or technologies installed must have a valid SQL Server license.

|||

Wraithzshadow,

1) Create an ODBC on the local PC pointing to the SQL server.

2) Open Excel spread sheet

3) Select Data > import external data > new database query

4) Select the ODBC created in step one, Click OK

5) Select Table or view from the list, Click Add then Click Close

6) Select the columns needed from the table

7) Click the return data icon (door with arrow)

The data is displayed in the spread sheet. The good thing about this is you can update the data by right clicking into any cell containing data and selecting refresh data or by setting the query to refresh on open.

Tuesday, March 27, 2012

Exporting long text fields to Excel

Exporting long text fields to Excel

Exporting long text fields to Excel

Sunday, March 25, 2012

Exporting Data from SDF Database to CSV file

Monday, March 19, 2012

Export wizard trouble exporting columns names to ragged file.

Sunday, March 11, 2012

Export to sqlserver

Friday, March 9, 2012

Export to PDF Is a Mess

Wednesday, March 7, 2012

Export to Excel yields extra columns

Export to Excel Problem..! URGENT

Sunday, February 26, 2012

export to excel issues

Friday, February 24, 2012

Export to Excel - Multiple Columns

Export to Excel - merge and centre issue

Export to Excel

Sunday, February 19, 2012

Export to CSV and oepn in Excel

Export the data as an XML file?

export table to excel

Export Report to File

Blog Archive

About Me