Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>

Extending ReportingServicesFileShareDeliveryProvider

Hello all,
I need to somehow extend the functionality of the
ReportingServicesFileShareDeliveryProvider so I can ship html formatted
reports along with their chart images to a file share. I've deployed some
reports using the mhtml file format, but alot of our users have problems
viewing reports in this format and our IT dept cannot figure out why...some
computers cannot view the reports unless windows system font is set to small.
Others cannot see the reports at all...and yet other have no problems.
The best thing will be to have the html format and the chart images
exported. The Printer Delivery example does not help 100% because of two
areas:
1) I'm wanting to extend an existing extension if possible...as opposed to
what the Printer Delivery example does...creating an extension from scratch
2) I want to stream the chart images out to the file share as well
Any assistence with this would be awesome. I'll hobble along in the
meantime...
-thanks, brian parkerHi Brian:
I don't see the extensions being too extensible themselves. The
parameters and delivery mechanisms are usually so disparate that it
wouldn't make any sense to inherit from an existing delivery class.
I have another sample that might be useful to you (although I haven't
taken care of delivering images, I plan to try this in the future).
The Blog Delivery Extension
http://odetocode.com/Blogs/scott/archive/2004/08/16/393.aspx
Deliver Reports To A Blog
http://odetocode.com/Blogs/scott/archive/2004/08/16/390.aspx
--
Scott
http://www.OdeToCode.com/
On Fri, 1 Oct 2004 20:03:02 -0700, Brian Parker
<BrianParker@.discussions.microsoft.com> wrote:
>Hello all,
> I need to somehow extend the functionality of the
>ReportingServicesFileShareDeliveryProvider so I can ship html formatted
>reports along with their chart images to a file share. I've deployed some
>reports using the mhtml file format, but alot of our users have problems
>viewing reports in this format and our IT dept cannot figure out why...some
>computers cannot view the reports unless windows system font is set to small.
> Others cannot see the reports at all...and yet other have no problems.
>The best thing will be to have the html format and the chart images
>exported. The Printer Delivery example does not help 100% because of two
>areas:
>1) I'm wanting to extend an existing extension if possible...as opposed to
>what the Printer Delivery example does...creating an extension from scratch
>2) I want to stream the chart images out to the file share as well
>Any assistence with this would be awesome. I'll hobble along in the
>meantime...
>-thanks, brian parker|||Scott, This worked beautifully for me. Thanks for your help...
"Scott Allen" wrote:
> Hi Brian:
> I don't see the extensions being too extensible themselves. The
> parameters and delivery mechanisms are usually so disparate that it
> wouldn't make any sense to inherit from an existing delivery class.
> I have another sample that might be useful to you (although I haven't
> taken care of delivering images, I plan to try this in the future).
> The Blog Delivery Extension
> http://odetocode.com/Blogs/scott/archive/2004/08/16/393.aspx
> Deliver Reports To A Blog
> http://odetocode.com/Blogs/scott/archive/2004/08/16/390.aspx
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 1 Oct 2004 20:03:02 -0700, Brian Parker
> <BrianParker@.discussions.microsoft.com> wrote:
> >Hello all,
> >
> > I need to somehow extend the functionality of the
> >ReportingServicesFileShareDeliveryProvider so I can ship html formatted
> >reports along with their chart images to a file share. I've deployed some
> >reports using the mhtml file format, but alot of our users have problems
> >viewing reports in this format and our IT dept cannot figure out why...some
> >computers cannot view the reports unless windows system font is set to small.
> > Others cannot see the reports at all...and yet other have no problems.
> >
> >The best thing will be to have the html format and the chart images
> >exported. The Printer Delivery example does not help 100% because of two
> >areas:
> >
> >1) I'm wanting to extend an existing extension if possible...as opposed to
> >what the Printer Delivery example does...creating an extension from scratch
> >2) I want to stream the chart images out to the file share as well
> >
> >Any assistence with this would be awesome. I'll hobble along in the
> >meantime...
> >
> >-thanks, brian parker
>sql

Exporting SQL Server 2005 data into formatted XML file

Hello,

I currently have a stored procedure in my SQL Server 2005 database that contains a simple SELECT FOR XML statement. I would like to call this stored procedure in C# and use C# to write an XML file such that when I open it in Visual Studio for editing it looks properly nested (rather than all on one line). Is there a quick way to do this?

I would like the file to look something like the following when opened:

Code Snippet

<Element1>

<Element2>

<Element3>Text</Element3>

<Element4>Text</Element4>

</Element2>

</Element1>

Thanks.

Create a DataSet put your table in it and call the DataSet.ReadXML method, if you want formatting you can use a Repeater for Webform. Try the link below to get started.

http://msdn2.microsoft.com/en-us/library/360dye2a.aspx

|||Thanks for your reply! I will look into that.

Exporting sql data to specific cells

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Quote:

Originally Posted by Edgar

Tools: SQL Server 2K, Excel 2000

Hi,

I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?

I appreciate any suggestions.

Edgar J.

Have you experimented with the MS Query option in Excel? Underneath "Data" + "Import External Data" + "New Database Query" you can connect to your SQL Server datasource and query for the values you are looking for. There are some limitations however (i.e. can't run stored procedures that take parameters directly) but seems to cover a lot of the things people want to do in Excel.

|||To export data from SQL Server table to Excel file, create an Excel
file named testing having the headers same as that of table columns and
use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Bala|||Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar|||Does the data need to be fetched each time the spreadsheet is
recalcuated?|||

Quote:

Originally Posted by Edgar

Thanks Bala for your suggestion. What I really need is a direction on
how to update a specific cell, ex: C10. I have a scalar value coming
from the SQL Server not rows of data.

Edgar

If you use the External Data option I described, then you can exactly what you are looking for.

In your example, you want to put a value into c10. You use the "Import External Data" feature to connect / build / write your SQL Query. As part of this process you'll tell Excel what cell you want the data populated into, so select c10 (note: a column header is returned so you either need to put the results into c9 so your data is in c10, or put the results on a different sheet or far removed cell of the same sheet and then make c10 = the cell that the data is actually returned to.

|||I have done this a couple of different ways depending on the situation.
Here are a few ideas that might be helpful:

If there are not very many cells that would get updated then I would
create a custom excel function within the workbook that connects to SQL
Server, retrieves the value and then puts it in the cell. Using this
approach, the cell can move around the spreadsheet and you don't have
to change any of the code. This can also get more flexible as you
could pass in variables to the function. Make sure to set the function
to "volatile" or it will not recalc each time the spreadsheet is
recalc'd. Also note that this is solution can get annoying to the user
as it slows the worksheet calculation since each time it is
recalculated, a connection and query has to be made to SQL Server.
This can be aided by creating a connection to SQL server when the
spreadsheet is opened. If that is the case then I would make sure to
ask the user if they want to connect, store the response, and build it
into an if statement so that the function does time out a call to the
database server each time.

If there are a lot of cells to update, then I would suggest adding a
custom dropdown menu to the standard excel menu that has a button to
recalculate the "special" cells that get data from SQL Server. This is
more efficient in that you don't need to create, query, and then close
a connection for each cell - this requires a little more work to set up
the menu. Another issue is finding the cells that need to be updated.
You could do this with your custom menu too and store the result which
works well. I have also used cell comments to flag cells to receive
data since the comments are stored in a list that is easy to iterate
through.

Perhaps another solution would be to use the msft wizard under the data
menu and create a new database query - this has many limitations but is
very easy to set up.

A few to think about anyway, hopefully one of those is useful...|||The data is updated on a weekly basis. I mentioned I have a scalar
value. But I think I can fetch one row with 3 columns. The cells I
want to update on the Excel are: C4, C7 and C10.
The integer values are used in the excel formulas.

So far, I've tried using a DTS with activex to test just one particular
cell. But the code was updating the cell below C4 which I can't figure
out why. Here is the ActiveX code I'm using:

Function Main()

Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.add
Set oSheet = newBook.Worksheets(1)

'Specify column names.
oSheet.Range("C4").Value = "D"

DTSGlobalVariables ("FileName").Value = "C:\MyExcel.xls"

With newBook
.SaveAs DTSGlobalVariables("FileName").Value
.Save
End With

appExcel.quit

'dynamically specify the destination Excel file

set oPackage = DTSGlobalVariables.parent

' connection 2 is to the Excel file

set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Again, thank you for all your input and/or directions.

Edgar J.|||Hi Edgar,

You haven't mentioned why this approach won't work or isn't appropriate, so I'll elaborate.

First, add a new worksheet to your workbook. Call it whatever you want...for now I'll refer to it as "scaler_sheet". Also, as an example, I'll pretend your current worksheet you are referring to (the one you want the data in) is called "live_sheet". Make sure "scaler_sheet" is the active sheet...

From the "Data" Menu, choose "Import External Data" + "New DB Query". This will open MS Query. Choose "New Data Source" and then "Ok". Give your datasource a name and choose the "SQL Server" driver. Choose "Connect". This should open the SQL Server login box. Enter your server name or IP, login id, password. Click on the "Option" button and set your default db. Click "Ok". You should be back at the "Create New Data Source" dialogue. Leave "Choose Default Table" blank. Choose "Ok" and you can now tell Excel to use your new datasource (highlight it and click "Ok"). This will open MS Query...from here you can choose tables (if you want) or write your SQL directly ("Cancel" out of the table selection box and then click on the "SQL" button in the tool bar).

Depending on how complex your SQL is, MS Query may complain that it can represent your query graphically (no big deal). When your query results are displayed, choose "File" + "Return Data to Microsoft Excel". You'll be prompted to choose the cell where you want the data returned (i.e. scaler_sheet!A1). The data will be displayed with the column headers in A1, B1, and C1 and the data in A2, B2, and C2 respectively. Now, from your description above, you said you need your SQL data to appear in C4, C7, and C10. So, make this sheet the active sheet, and then reference the data from the appropriate cells from scaler_sheet (i.e. live_sheet!C4 = scaler_sheet!A2...live_sheet!C7 = scaler_sheet!B2...and live_sheet!C10 = scaler_sheet!C2).

I believe you will find this will work very well for you, and the data need only be refreshed when you want it to be.

Keith