Exporting data from SQL Server to Excel from a Stored Procedure

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

ThanksI'd use the Data | Get External Data | New Database Query menuitem in Excel. When you get to MS-Query, don't select any tables. Use the SQL button and type in the EXECUTE command for the stored procedure. Save this query and then the users can simply execute it!

-PatP

exporting data excel

Hi,
I have a problem concerning to exporting data to an excel sheet.
I use 2 stored procedures (spStockReport1, spStockReport2): one to
pasta date to sheet 1, and other to paste dat to sheet 2.
The problem is that the first sp gets the result with no problem but
the second one gets the same results. I've already checked both in
query analyser and i don=B4't see any problems.
This is the code i'm using:
Protected Overrides Function generateData(ByVal args As Hashtable,
ByVal serverSession As ServerSession) As ArrayList
Dim totalItems As Integer =3D 0
Dim sucess As Boolean =3D True
Dim results As ArrayList =3D New ArrayList
Dim hashRes As Hashtable =3D New Hashtable
Dim status As String =3D ServiceResponse.STATUS_OK
Dim description As String =3D ""
Dim searchValue As String =3D System.DBNull.Value.ToString
Try
Dim cache As GenericCache =3D GenericCache.getInstance()
Dim entity As String =3D ""
If (Convert.ToString(args("entity")) <> "") Then
entity =3D args("entity")
End If
Dim database =3D
System.Configuration.ConfigurationSettings.AppSettings("database")
Dim rsResult As RecordSet
Dim iQueryB As New mysql.MySqlQueryBuilder
Dim iConn As IConnection =3D
ConnectionManager.GetInstance.GetConnection
Dim tmpData As New ArrayList
Dim tmpData2 As New ArrayList
Dim iCommtype As New
Command.CommandType(CommandType.StoredProcedure, "spStockReport1")
Dim strQuery As String
strQuery =3D "EXECUTE " & database & ".dbo.spStockReport1
'" + entity + "'"
Dim iComm As New Command(iCommtype, strQuery)
rsResult =3D iConn.Select(iComm)
tmpData.Add(rsResult.GetResults)
strQuery =3D "EXECUTE " & database & ".dbo.spStockReport2
'" + entity + "'"
rsResult =3D iConn.Select(iComm)
tmpData2.Add(rsResult.GetResults)
'transform to connector format
results =3D dataToSpreadSheet(tmpData, tmpData2,
serverSession)
'Dim headerLine As New ArrayList
'headerLine.Add("Cliente:" & entity & "")
'results.Insert(0, headerLine)
Catch ex As Exception
Log.error("UmGenStockReport: Erro a gerar excel.", ex)
Throw New Exception("Ocorreu um erro a gerar o mapa.
Por favor tente de novo")
End Try
generateData =3D results
End Function
Private Function dataToSpreadSheet(ByVal data As ArrayList,
ByVal data2 As ArrayList, ByVal serverSession As ServerSession) As
ArrayList
Dim result As ArrayList =3D New ArrayList
'construct invoice synopsis
Dim invoiceSynopsis As ArrayList =3D New ArrayList
Dim invoiceMapHeader As ArrayList =3D New ArrayList
invoiceMapHeader.Add("COD ARTIGO")
invoiceMapHeader.Add("ARTIGO")
invoiceMapHeader.Add("DESCRI=C7=C3O")
invoiceMapHeader.Add("DATA DE VALIDADE")
invoiceMapHeader.Add("ESTADO")
invoiceMapHeader.Add("MARCA")
invoiceMapHeader.Add("LOTE")
invoiceMapHeader.Add("ARMAZEM")
invoiceMapHeader.Add("qt disp")
invoiceMapHeader.Add("qt arm")
invoiceMapHeader.Add("qtOk")
invoiceMapHeader.Add("qt Danif")
invoiceMapHeader.Add("qt disp tot")
invoiceMapHeader.Add("qt arm tot")
invoiceMapHeader.Add("qt Ok Total")
invoiceMapHeader.Add("qt Danif Total")
invoiceMapHeader.Add("Total de Lotes :")
invoiceMapHeader.Add("a")
invoiceMapHeader.Add("b")
invoiceMapHeader.Add("Cliente")
invoiceMapHeader.Add("Marca")
invoiceMapHeader.Add("Armazem")
invoiceMapHeader.Add("LotesPorArmazem")
result.Add(invoiceMapHeader)
For Each record As Hashtable In data
Dim invoiceMapLine As ArrayList =3D New ArrayList
If (record.ContainsKey("itemcode")) Then
invoiceMapLine.Add(record("itemcode"))
Else : invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("description")) Then
invoiceMapLine.Add(record("description"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("expirationdate")) Then
invoiceMapLine.Add(record("expirationdate"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("localcode")) Then
invoiceMapLine.Add(record("localcode"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("availableamount")) Then
invoiceMapLine.Add(record("availableamount"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("storderamount")) Then
invoiceMapLine.Add(record("storedamount"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("itemOk")) Then
invoiceMapLine.Add(record("itemOk"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("itemDanif")) Then
invoiceMapLine.Add(record("itemDanif"))
Else
invoiceMapLine.Add(" ")
End If
result.Add(invoiceMapLine)
Next
'add blank line
result.Add(New ArrayList)
Return result
End FunctionSee if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanansql

Exporting Data

I would like to export data to a excel file using a stored procedure. I'm not sure how to go about this or if it is even possible. Can someone point me to a link to show me how to do this?I think that DTS (http://msdn.microsoft.com/data/technologyinfo/sqlserver/SQLKeyTechs/default.aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp) would be a better choice.

-PatP

Exporting .sdf from pda into a text file on desktop

Dear All,

I have an application running on my mobile device and the data is stored in the mobile database. So now i am trying to build any application on my pc which upon click should download the data from my mobile database into a local text file. Is there any idea or reference where I can stary working on this ?

DataSet ds = new DataSet();

...

DataAdapter da = new DataAdapter(...);

da.Fill(ds, "table1");

...

da.Fill(ds, "tableN");

ds.WriteXml("LocalTextFile.xml");

If XML is not what you want then write data row by row in the format you want using System.IO namespace.

|||

Dear Ilya,

I would prefer it to be in text file format. The problem what project shall I develop I mean in the visual studio 2005. Should it be windows form or device application ? Because if it is device application it cant run in my desktop rite ? So it should be windows form rite ? Thanks.

|||

Dear Ilya,

I have now build a new console application. So then I add a reference to

System.Data.SqlServerCe; to enable me to connect to the mobile database found in the pda. So when I try to run I get an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". The problem is that on the same machine I can compile my device application which is also using the same reference'System.Data.SqlServerCe. Please correct me if I am doing something wrong here. Another thing is that how must I reference the path to my mobile database in the pda ? I am trying like this I dont know if this is right

Mobile Device/MiniSD Card/MiniSD Card/test1/db1.sdf. Thanks.

|||

Dear All,

I have a problem here. What i exactly want to do it that build a console application where when I connect my pda to my desktop and run the exe file it should be able to transfer the .sdf data into a text file of my local desktop ? Is there any code or reference where can guide me ?

|||Some of the components from www.primeworks-mobile.com should be able to help you.|||

Dear ErikEJ,

I would prefer to write on my own as I cant afford third party software. Hope you can show me some light into it. Thanks.

|||What you are going to want to probably do is copy the database from the device to your desktop, then use the sql ce for the desktop libs (remember you can use sql ce on devices and full blown windows) to read through the database and output . You can accomplish copying the database over through code using the Windows CE RAPI. The full framework as far as I know does not have built-in support for it. So what you would need to do is P/Invoke some functions in the rapi.dll. Luckily somebody has essentially done this, look for the opennetcf's (www.opennetcf.org) desktop communication library. So once you use this to get a copy of the database over to your desktop you can use the sql ce for windows (not wm or ce) libs to connect to your now local copy of the database. Then using a SqlCeDataReader you can just iterate through every record in the database, while reading a record in you can format it however you like then push that into a file using a StreamWriter object.
|||

Dear Steve,

Thank you very much for your kind information. So now I have downloaded the OpenNETCF.Desktop.Communication Library . Now how shall I start ? Shall I start by building a console application ? So must I first manually download the database or the system can do it for me ? I am really at lost can your pls guide me further on this. Thanks once again.

|||If you want to do it as a console application then start there. Then my next step would be to use the Desktop Communication library to programmatically get the database from your device to local computer. The communication library should have came with an example application that you can probably look at to find out how they copy files back and forth. You can look their and do the same thing.
|||

Dear Steve,

Ok I have followed and done accordingly and sucessfully download the sdf file into my desktop. My next problem is that i am not able to connect to downloaded .sdf in the desktop. I am using the same code as I build the windows device application. Can your please tell me wat can be my mistake ? For example I use this

SqlCeConnection conn1 = new SqlCeConnection("Data Source = c:/test1.sdf");

First I got an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". Then I manage to solve "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)". So wat is the possible error on my side ?

So what can be possible error on my side ? Thanks.

|||

Dear Steve,

I am ok with Desktop Communication library. I have already managed to download the database from the device to my local computer. My next step is that how can I from my console application link to the database which I have already downloaded using the said library. Thanks once again.

|||Once you have the sdf file over to you desktop you can use the desktop version of the sqlce data object. I think you need to download the Microsoft SQL Server Mobile 2005 Mobile Edition SDK. Once you have that downloaded and installed it you should be able to add the proper references to the desktop x86 version of the libraries that you need. Then you should be able to connect to the database and use it just like you did on the handheld with the .NET CF. You should be able to just use a SqlCeDataReader object to query the database then read through the returned records and output them using a StreamWriter object to put them into a text file which you can then control the format of.
|||

Dear Steve,

I have already downloaded the microsoft sql server mobile 2005 mobile edition sdk. So I tried to use the SqlCeDataReader and below is the errors I got. Can you please guide me on this ?

SqlCeConnection conn1 = new SqlCeConnection("Data Source = c:/test1.sdf");

First I got an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". Then I manage to solve that problem but got another one "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)". So wat is the possible error on my side ?Thanks.

|||F**K YOU AND YOUR STUPID QUESTIONS. GOOGLE THE ANSWER AND FIND SAMPLE CODE YOU CHEAP BASTARD.

Exported Stored procedures

Hello and thanks to all of the helpers!
Especially for Linchi Shea for the Perl script.
When I compare the sql script created by the perl script (after little
modification on my side) with the one created by the MS GUI tool for
creating SQL scripts, I get the stored procedues on different order. When
running the script from SQL Query Analyzer, it displays errors (warnings?):
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'GUIRemoveEntity'. The stored procedure will
still be created."
I understand that the order of them DOES matter, but, if at the end of the
process, all of the SPs are loaded, will everything work?
Thanks!
RoeeIf the script is atomic (e.g. drops and re-creates the stored procedures),
running the script a second time should solve the problem.
sysdepends is generally unreliable anyway, and I typically ignore this
message unless I heavily rely on the feature and religiously make sure it is
correct.
A
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bo5mfh$182orj$1@.ID-200860.news.uni-berlin.de...
> Hello and thanks to all of the helpers!
> Especially for Linchi Shea for the Perl script.
> When I compare the sql script created by the perl script (after little
> modification on my side) with the one created by the MS GUI tool for
> creating SQL scripts, I get the stored procedues on different order. When
> running the script from SQL Query Analyzer, it displays errors
(warnings?):
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'GUIRemoveEntity'. The stored procedure will
> still be created."
> I understand that the order of them DOES matter, but, if at the end of the
> process, all of the SPs are loaded, will everything work?
> Thanks!
> Roee
>|||Just a word of caution. if you run the script twice with
DROP PROC placed immediately before its respective CREATE
PROC, you won't see the error message. But it doesn't
solve the problem of dependency.
The best you can do with sysdepends is not to use it.
The only way to be sure of the dependency is to scan the
TSQL code and sort the depenency yourself. If you see
anybody claiming that his tool correctly handles the
procedure dependency, ask how he determines the
dependency. If it doesn't parse the code, don't believe it.
In addition, if someone claims that he has a pure TSQL
script that can sort the SP dependency correctly, don't
believe it either.
Rosee, I do have a Perl script that scan the SP code and
sort them by their dependency. Drop me an email if you are
interested.
Linchi
>--Original Message--
>If the script is atomic (e.g. drops and re-creates the
stored procedures),
>running the script a second time should solve the problem.
>sysdepends is generally unreliable anyway, and I
typically ignore this
>message unless I heavily rely on the feature and
religiously make sure it is
>correct.
>A
>
>"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in
message
>news:bo5mfh$182orj$1@.ID-200860.news.uni-berlin.de...
>> Hello and thanks to all of the helpers!
>> Especially for Linchi Shea for the Perl script.
>> When I compare the sql script created by the perl
script (after little
>> modification on my side) with the one created by the MS
GUI tool for
>> creating SQL scripts, I get the stored procedues on
different order. When
>> running the script from SQL Query Analyzer, it displays
errors
>(warnings?):
>> "Cannot add rows to sysdepends for the current stored
procedure because it
>> depends on the missing object 'GUIRemoveEntity'. The
stored procedure will
>> still be created."
>> I understand that the order of them DOES matter, but,
if at the end of the
>> process, all of the SPs are loaded, will everything
work?
>> Thanks!
>> Roee
>>
>
>.
>|||> The only way to be sure of the dependency is to scan the
> TSQL code and sort the depenency yourself.
Agreed.
> Rosee, I do have a Perl script that scan the SP code and
> sort them by their dependency.
I'm sure this could be done in T-SQL as well... maybe an interesting
exercise if I get bored. :-)|||Aaron;
Do you want to bet two beers at PASS2003 (if you are
going) that if a robust SP dependency sort program is done
in TSQL, it would be so ugly that it's practically
undoable? Ugliness is of course a matter of opinion and
lies the eyes of the the beholder :-)
I have no doubt you can do it in TSQL or any other
language that supports varibles, some string functions,
and the basic set of control flow constructs.
One of the major problems of doing this in TSQL is that
you need true recursion, which you can simulate with TSQL,
but rather unsightly. By 'robust', I mean it has to deal
with all the nuances.
Linchi
>--Original Message--
>> The only way to be sure of the dependency is to scan the
>> TSQL code and sort the depenency yourself.
>Agreed.
>> Rosee, I do have a Perl script that scan the SP code and
>> sort them by their dependency.
>I'm sure this could be done in T-SQL as well... maybe an
interesting
>exercise if I get bored. :-)
>
>.
>sql

export/import a non system stored procedures from one DB to another

Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?

Thanks in advance.

Maaloul wrote:

Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?

Thanks in advance.

have you considered using the Transfer SQL Server Objects Task? http://msdn2.microsoft.com/en-us/library/ms142159.aspx

Export XML data - stored procedure

I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...
Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...

e.g Sample XML file required...
<Person>
<Name>Raymond</Name>
<NickName>The Legend</NickName>
</Person>
<Person>
<Name>Peter</Name>
<NickName>The King</NickName>
</Person>

sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'Web Assistant Procedures', 1;
GO
RECONFIGURE
GO
sp_makewebtask @.outputfile='C:\MyExportFile.xml',
@.query='SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS',
@.templatefile='C:\Template.tpl'

Try these two links for code samples including generating the XML file from a database table, this is assuming you are using SQL Server 2000. If you are using SQL Server 2005 all the code you need is in the BOL (books online) because XML is native to SQL Server 2005. Hope this helps.

http://forums.asp.net/1026295/ShowPost.aspx

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to Excel using DTS

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)

what is the best way to do this? DTS, BCP

thanks
FOCELBrett will be along shortly to offer up a much cleaner solution using BCP and two lines of code (one of which will call a stored procedure to mix up a margarita). In the meantime, may I humbly offer up:

You can create a package with a global variable indicating which SP to run. Have three transformations defined in the package and determine which transformation to execute in a VBScript (using the global variable).

I'm not sure what will happen to the other transformations (the two that are not executed). That may require more testing...

You can look for more hints at http://www.sqldts.com.

Regards,

hmscott

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

Export to Excel

Hi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.

Somebody suggested using the following:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from table-name
When I tried executing the above lines of codes I got the following error message:
"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."
If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know ...
Thanks in advance.Hi - I use vbscript client side for this:
<BUTTON id="exportbutton" onclick="BLOCKED SCRIPTexport_onclick()" name="exportbutton" type="button" value="Button">Export</BUTTON>
<script language="vbscript">
Sub export_onclick()
Dim sHTML, oExcel, oBook
sHTML = document.all.items("mydatagrid").outerhtml
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument
oExcel.Visible = true
oExcel.UserControl = true
End Sub
</script>

Hope that helps!
Cheers, Mark|||Hi Matait
Thanks for your reply.
But wont there be a problem if scripting is disabled on the browser ?
|||

You cannot use OPENROWSET because Excel is not a RDBMS(relational database management system) and yes you can run queries in Excel version XP/2003 but the queries are Access SQL not ANSI SQL because SQL Server rejects most. That said try the thread below for code sample and links for Asp.net 1.1 convert it for Asp.net 2.0. Hope this helps.

http://forums.asp.net/921410/ShowPost.aspx

|||

Hi Caddre

Thanks for your reply. That should work for me :)
Cheers

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.

export Strored procedures in MS sql server Management studio

Hi,

Can anyone tell me how to export a batch of stored procedures in MS sql server Management studio?

Thanks.

Right click the database

Tasks > generate Script > Choose the appropiate object types in the wizard.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Export Stored Procedures

Hi,
Anyone know how I can export my stored procedures from one sql server to
another over the internet? I tried using the DTS, but kept getting an
"Objects cannot be transferred" error.
Thanks
Stored procedures will be stored in the sysobjects table .
Goto the query analyzer and select your source-database.
Open a new query-window and run:
Select * from sysobjects where xtype='P'
Query-analyzer will show all stored procedures related to your database.
Within Query-analyzer you can choose save as and save the data in different
ways. When saved it should be simple to send the file to another
administrator, DTS will make it possible to import the file into sysobjects.
The only bug when importing could be the ID's as they might already exist in
the other database!!
Another option is replication to be found in the books online
Greetings
Johan
"sympatico" <feedback@.a1vbcode.com> schreef in bericht
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com ...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>
|||don't forget these tables also
syscomments (stores the query)
syscolumns
sysdepends (relations to dependent objects like underlying tables in the
query)
If the target database has the same dependent objects and these objects have
a different ID from the source a copy>paste would cause meta-data to lose
its integrity. "Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:es6r$QDxEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Stored procedures will be stored in the sysobjects table .
> Goto the query analyzer and select your source-database.
> Open a new query-window and run:
> Select * from sysobjects where xtype='P'
> Query-analyzer will show all stored procedures related to your database.
> Within Query-analyzer you can choose save as and save the data in
different
> ways. When saved it should be simple to send the file to another
> administrator, DTS will make it possible to import the file into
sysobjects.
> The only bug when importing could be the ID's as they might already exist
in
> the other database!!
> Another option is replication to be found in the books online
> Greetings
> Johan
>
> "sympatico" <feedback@.a1vbcode.com> schreef in bericht
> news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com ...
>
|||Probably easiest to use Enterprise Manager to script them, and then just
apply the script to the remote server.
Right-click on your database of choice, select All Tasks, then Generate SQL
Script, check the All Stored Procedures box, then click the Preview
button...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sympatico" <feedback@.a1vbcode.com> wrote in message
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com ...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>

Export Stored Procedures

Hi,
Anyone know how I can export my stored procedures from one sql server to
another over the internet? I tried using the DTS, but kept getting an
"Objects cannot be transferred" error.
ThanksStored procedures will be stored in the sysobjects table .
Goto the query analyzer and select your source-database.
Open a new query-window and run:
Select * from sysobjects where xtype='P'
Query-analyzer will show all stored procedures related to your database.
Within Query-analyzer you can choose save as and save the data in different
ways. When saved it should be simple to send the file to another
administrator, DTS will make it possible to import the file into sysobjects.
The only bug when importing could be the ID's as they might already exist in
the other database!!
Another option is replication to be found in the books online
Greetings
Johan
"sympatico" <feedback@.a1vbcode.com> schreef in bericht
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>|||don't forget these tables also
syscomments (stores the query)
syscolumns
sysdepends (relations to dependent objects like underlying tables in the
query)
If the target database has the same dependent objects and these objects have
a different ID from the source a copy>paste would cause meta-data to lose
its integrity. "Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:es6r$QDxEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Stored procedures will be stored in the sysobjects table .
> Goto the query analyzer and select your source-database.
> Open a new query-window and run:
> Select * from sysobjects where xtype='P'
> Query-analyzer will show all stored procedures related to your database.
> Within Query-analyzer you can choose save as and save the data in
different
> ways. When saved it should be simple to send the file to another
> administrator, DTS will make it possible to import the file into
sysobjects.
> The only bug when importing could be the ID's as they might already exist
in
> the other database!!
> Another option is replication to be found in the books online
> Greetings
> Johan
>
> "sympatico" <feedback@.a1vbcode.com> schreef in bericht
> news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
> > Hi,
> >
> > Anyone know how I can export my stored procedures from one sql server to
> > another over the internet? I tried using the DTS, but kept getting an
> > "Objects cannot be transferred" error.
> >
> > Thanks
> >
> >
>|||Probably easiest to use Enterprise Manager to script them, and then just
apply the script to the remote server.
Right-click on your database of choice, select All Tasks, then Generate SQL
Script, check the All Stored Procedures box, then click the Preview
button...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sympatico" <feedback@.a1vbcode.com> wrote in message
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>

Export Stored Procedures

Hi,
Anyone know how I can export my stored procedures from one sql server to
another over the internet? I tried using the DTS, but kept getting an
"Objects cannot be transferred" error.
ThanksStored procedures will be stored in the sysobjects table .
Goto the query analyzer and select your source-database.
Open a new query-window and run:
Select * from sysobjects where xtype='P'
Query-analyzer will show all stored procedures related to your database.
Within Query-analyzer you can choose save as and save the data in different
ways. When saved it should be simple to send the file to another
administrator, DTS will make it possible to import the file into sysobjects.
The only bug when importing could be the ID's as they might already exist in
the other database!!
Another option is replication to be found in the books online
Greetings
Johan
"sympatico" <feedback@.a1vbcode.com> schreef in bericht
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>|||don't forget these tables also
syscomments (stores the query)
syscolumns
sysdepends (relations to dependent objects like underlying tables in the
query)
If the target database has the same dependent objects and these objects have
a different ID from the source a copy>paste would cause meta-data to lose
its integrity. "Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:es6r$QDxEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Stored procedures will be stored in the sysobjects table .
> Goto the query analyzer and select your source-database.
> Open a new query-window and run:
> Select * from sysobjects where xtype='P'
> Query-analyzer will show all stored procedures related to your database.
> Within Query-analyzer you can choose save as and save the data in
different
> ways. When saved it should be simple to send the file to another
> administrator, DTS will make it possible to import the file into
sysobjects.
> The only bug when importing could be the ID's as they might already exist
in
> the other database!!
> Another option is replication to be found in the books online
> Greetings
> Johan
>
> "sympatico" <feedback@.a1vbcode.com> schreef in bericht
> news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
>|||Probably easiest to use Enterprise Manager to script them, and then just
apply the script to the remote server.
Right-click on your database of choice, select All Tasks, then Generate SQL
Script, check the All Stored Procedures box, then click the Preview
button...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sympatico" <feedback@.a1vbcode.com> wrote in message
news:5k8jd.28997$dj2.1974131@.news20.bellglobal.com...
> Hi,
> Anyone know how I can export my stored procedures from one sql server to
> another over the internet? I tried using the DTS, but kept getting an
> "Objects cannot be transferred" error.
> Thanks
>