Export To PDF Loses VbCRLF Formatting

Hi

I've simplified this so I can test it's not me and so it's easier to explain.

I have one table in SQL server called Table1. Five fields called: One, Two, Three, Four, Five. Values of NULL, 2, 3, 4, NULL.

Reporting services, put a table in a report. Two fields. Header of Field1 and Field2.

Value field data for Field1 ="One: " & vbcrlf & "Two: " & vbcrlf & "Three: " & vbcrlf & "Four: " & vbcrlf & "Five: "

Value field data for Field2 =Fields!One.Value & vbcrlf & Fields!Two.Value & vbcrlf & Fields!Three.Value & vbcrlf & Fields!Four.Value & vbcrlf & Fields!Five.Value

Preview the report, all is well, you get this:

Field1 Field2

One:

Two: 2

Three: 3

Four: 4

Five:

Export it to a PDF, all is not well, I get this:

Field1 Field2

One: 2

Two: 3

Three: 4

Four:

Five:

I can make it work by checking the NULL fields and replacing them with a character. But why does it ignore the vbCRLF when exported to a PDF?

Any ideas...

give minimum space (width and hight) for that field and try|||

Hello merin

Not sure how I give it minimum space?

Also the field is being outputted with enough space (I think), as 234 appear in that field. If I replace the NULLS with a hypen, it works perfectly. But that NULL in the table in field one is causing the VbCRLF to be ignored when it is exporting to a PDF.

With a hypen as a replacement for the NULL I get this preview and it outputs perfectly to a PDF. (This is how I've fixed the problem, but I'm still interested as to why this happens)

Field 1 Field2

One: -

Two: 2

Three: 3

Four: 4

Five: -

|||

Unfortunately, this is not something we support very well at all in SQL Server 2005. You'll see inconsistent behavior - for instance, exporting to TIFF will act as if the tabs are not present so the words will run together and exporting to PDF will act as if the tabs are spaces except for underline which won't cover the complete text.

Your best bet will be to do this via code. Switch to a fixed-width font and simulate tabs with multiple spaces.

Sorry I don't have a better answer.

|||

Thanks for this. The NULL replace with a hypen works for me for now. Hopefully in the next SP it will be a little more consistant with the formatting, here's hoping anyway.

Export sql query to excel vba easier way

Hi,

I have a query ( Using sql) containing data that i need to export into excel.
I am currently exporting it by cut and paste the data, it is
not a very intuitive way of doing things, what i would prefer is if i
could have a button on a form which when it was clicked, it
automatically exported the query to a Excel file. Can anyone help me
with the VBA that would be neccessary, so i can connect excel and ms sql.Have you tried using MS query from Excel? Open a blank worksheet, click on "Data", "Import External Data", then "New Database Query". That will open up MS-Query with the query wizard. You can use the wizard to recreate your sql query from you db tables, or just stop the wizard, click on the SQL button in MS query and you can actually paste the sql code there. Will probably have some syntax issues, but those are pretty easy to solve. Once the query is created in MS-Query and you export the results to Excel, the query will be saved with the worksheet and you can use VB to simply refresh the query by the name you assign. Will need to set up an OBDC SQL driver to your db for this to work.

Note: If you can possibly create the query via the MSquery wizard, that is best, since you can then use fields from the Excel sheet to contain variable parameters to pass through to the query if you need to. If there are no variables to change on the sql query, it doesn't matter then.|||Another possibility is to use ODBC an call with an Access DB. Then use a form to export the data to excel.

We do that a lot with SQL and Oracle databases.|||You Can also use Ms SQL Server DTS.
Open the Export Wizard and set the source and destination. then enter your Query to run. at the Final Step Save the package as Vbscript.
now you can use this Code in your VB program and Run In your desired Time. you can also schedule it that SQL Server exports your data to Excel file in specified Times.
hope to be useful