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

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