I am looking (and I have looked everywhere) to run a SP and get the data from it and open an Excel spreadsheet (manual column property editing is fine and preferred) and allow the user to save it wherever they want.
I am using VS 2003
Thanks in advance,
Hi
I am Utarsh Gajjar.
I am working on SQL Server 2005.
I have following Stored Procedure.
----------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ExportInExcel]
@QueryString VarChar(8000) =''
AS
BEGIN TRY
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Customers.xls;IMEX=1','SELECT * FROM [Sheet1$]')
EXEC (@QueryString )
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(8000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
-------------------------------------------------------------------------------
Error is
-------------------------------------------------------------------------------
(0 row(s) affected)
Msg 50000, Level 16, State 2, Procedure ExportInExcel, Line 31
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.
(1 row(s) affected)
how can i solve this problem?
Thanks in advance.
Hi,
I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.
The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.
Can this be done using DTS or do i have to try another method?
thanks
scott
Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.
Thanks
I'm new to 7.0 and to DTS, and I find it all very confusing and need some help. :)
1) I need to automatically import data from a text file on a daily basis. Can someone tell me in short, simple steps how to set this up?
2) I need to export certain data into a new Excel sheet. Can this be triggered from a stored procedure, by calling some function? If yes, is it possible to send parameters to this function?
thanks bunches.
-M
Can you export the results of a stored procedure using BCP?
If so, what is the syntax? If not, I was trying to convert my SP to a view, but it has a CASE statement within it and so I can't save a view with that (at least not as I've been trying).
Thanks,
Bob Larson
Hi,
I know how I can export a singe stored procedure to a file, clipboard etc..
But I have several stored procedures and I can't multi select them all
What's the best practice to export all stored procedures from a database?
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;GORECONFIGURE;GOsp_configure 'Web Assistant Procedures', 1;GORECONFIGUREGOsp_makewebtask @outputfile = 'C:MyExportFile.xml', @query = 'SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS', @templatefile = 'C:Template.tpl'
Reading through the forums, I found some great imformation for importing/exporting an excel spreadsheet via a stored procedure, however, the amount of data I have won't fit in excel. Can someone help me Import a CSV file via a stored procedure? and Export a CSV file via a stored procedure? I don't know if XML is the answer or if there is another way. For many reasons, I don't want to use DTS or Bulk. We are currently using SQL Server 2000.
Thank you,
Stacy
Thanks,
Stacy
Hi,
I have a SSIS package which exports data from a view in SQL database into Excel file. It was created by Export Wizard in SQL 2005 Server.
Now I would like to modify this package and change data source from view to stored procedure.
What component from toolbox should I use? it should be placed in Control Flow or Data Flow? And how connetc it with my Excel Destination?
thanks,
Przemo
I have used BCP to perform this, but I now need an SSIS package. Is this possible to use an SSIS package to automate the task?
View 1 Replies View RelatedAnyone have the code used in DTS to make a connection to Excel? Or perhaps a better idea? I need to pass different file names to a stored procedure which would upload the spreadsheet data into SQL7 on demand.
View 1 Replies View RelatedI'm trying to automate a data entry process. We get annual or semi-anual fee schedules from our clients. Sometimes it's an excel file, sometimes it's csv or other text, sometimes it's from the web. We clean up these files a bit and then import them to a new table in a sql server database. Then someone writes a custom insert to take specific columns from that temp table, do some transformations, and put them in the correct place in the normal database. Then the imported table is deleted or archived. We'll still have to do the clean up phase, but I should be able to automate most everything after the import.
What I want to know is, can I pass a filename to a stored procedure, along with perhaps a few other parameters, and have the procedure import data from that file? If so, how would I go about it?
Hello,
I'm a SSIS beginner.
I need to create users in my database from an Excel file source.
I have an Excel source with user name and email.
I would like to use SSIS to go though the Excel file and execute a store procedure (create_user @name @email @password OUTPUT) for each row and then create an output file with the new created password (a random password is created for each user and the stored procedure has an output @password)
I tried to "plug" an "Excel source" to an "Execute SQL task" which execute my store procedure.
But as a SSIS beginner I don't really understand how to pass the parameters (name and email in the Excel file) to my stored procedure. There is the concept of variable a for each loop thing... but i dont really know how to start.
I would be greatfull if someone could tell me a solution to my problem.
Cheers
Fabrice
I'm looking for a tool that can schedule stored procedures or queries and save the results to pdf or excel (preferrably both). SQL Server 2000, so 2005 reporting services are out, though if that has the capability I'd like to know because that might convince some of the powers that be to upgrade.
View 4 Replies View RelatedI need to run a SQL Server 2005 stored procedure by pressing a command button in excel 2003. Basically, I want stored procedures run from Excel to get the view/queries from sql.
Searched and tried many solutions here and in other web resources, read about opening the datasource, but I cannot find the exact solution for my problem.
Any help would be greatly appreciated,
Thanks
Hi,What is the syntax of storing the content of an Excelsheet in a predefinedtablewith a stored prcedure.Arno de Jong, The Netherlands.
View 1 Replies View RelatedI'm new to Integration Services and going through the learning challenges one often faces when employing unfamiliar technology. I've created a simple package that executes a parameterized stored procedure and returns the results (multiple rows) into an object variable. How do I export the results to an XLS file? Do I have to use a script task to do this?
Regards,
Orlanzo
Hi to All,
I have the codes below in excel VBA that always returns a "-1" value of the rs.RecordCount. The sql connection is open, BUT IS NOT ABLE TO EXECUTE THE STORED PROCEDURE. Anyone please I need help ;(
Database: SQL Server 2005
Programming language: VBA (Excel 2003)
Dim cn as new ADODB.Connection
Dim cmd as new ADODB.Command
Dim rs as new ADODB.Recordset
Dim connstr as string
connstr = "Provider=SQLOLEDB;" _
& "Data Source=myDataSource;" _
& "Uid=sa;Pwd=mysource;"_
& "Initial Catalog=Emp;"
With cn
.ConnectionString = str
.CommandTimeout = 0
.Open
End With
cmd.CommandText = "sp_LoadEmp"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
Set rs = cmd.Execute
If rs.RecordCount > 0 then
< my codes here >
End If
My Stored Procedure:
SELECT * FROM Emp WHERE EmpAge > 30
When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.
However, I can print the same spreasheet from Excel 2007.
Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?
I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.
Any assistance is appreciated.
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
Thanks
I have the following code to import .csv file into my table in excel. It's being inserted into a table. dbo.ImportedPromoPricing. Table and the .csv file have 3 fields price, code and selling price.
Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] .....
I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server, then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
Hi,
I have created a stored procedure which imports the contents of the excel sheet into the sql server. I am using the syntax as
"declare cur_Inoperative cursor for Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0; Database=D:Nupur_GRAPV2.0Inoperative.xls',Sheet1$) order by 1".
as of now I am hard coding the database path inside the cursor, but i need to pass this path as stored procedure input parameter. Passing the path is working but i am not able to use that passed parameter inside the cursor. can anyone help me in replacing the actual path with the input path variable.
I have a stored procedure in SQL Server which needs a parameter and returns a resultset.
sp_xxx 'parameterValue'
I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.
I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.
I have tried also through an ODC files but I get an error.
Is it possible to do this?
Goal: To run a stored procedure and export its results to Excel.
Challenge: The stored procedure produces multiple result sets. We want to programmatically export each result set into just 1 Excel file but into different Worksheets. Ex: result set 1 into Worksheet 1, result set 2 into Worksheet 2 and so on. Tools like
BCP utility and OPENROWSET are not enabled for use on the server. So those are out of the league for this one.After I run "EXEC [sp_select_ view_ columns_ from_pp]" -- Within the Results tab of Microsoft SQL Server Management Studio, I see:
viewName columnName columnDataType columnLength column
test test_id varchar 8 0
viewName columnName columnDataType columnLength column
test1 test1_id varchar 8 0
viewName columnName columnDataType columnLength column
test2 test2_id varchar 8 0
As you can see from above, that stored procedure sp_select_view_columns_from_pp is producing multiple result sets within the same Results tab in Microsoft SQL Server Management Studio. We want to make it so it exports to Excel into its own worksheets.
Code:
ALTER PROCEDURE [dbo].[sp_select_view_columns_from_pp]
AS
DECLARE @object_id INT;
DECLARE cur CURSOR FOR SELECT object_id
[code]....
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
View 1 Replies View RelatedCan I create an excel file by stored procedure?
View 2 Replies View Related
File Structure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: Table [dbo].[tbl_Test] Script Date: 03/04/2008 09:51:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [numeric](18, 2) NOT NULL CONSTRAINT [DF_tbl_Test_TestValue] DEFAULT ((0)),
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--**************************************************************************************************************************
Stored Procedure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[proc_Ins_Test] Script Date: 03/04/2008 09:52:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name: proc_Ins_Test
*/
ALTER PROCEDURE [dbo].[proc_Ins_Test]
(
@TestValue numeric(18,2)=0
)
AS
BEGIN TRAN
BEGIN
INSERT INTO tbl_Test (TestValue) VALUES (@TestValue)
END
COMMIT TRAN
SELECT MAX(TestID) AS MaxValue FROM tbl_Test
RETURN 1
--**************************************************************************************************************************
(I have it returning a value through SELECT to simulate returning a key value from the newly inserted record. I also use the RETURN value to fetch any error codes.)
'***************************************************************************************************************************
Public Sub PSInsertTest(cnConn As ADODB.Connection)
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim stProcName As String 'Stored Procedure name
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Defines the stored procedure commands
stProcName = "dbo.proc_Ins_Test" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = cnConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
'Append Parameters
With cmd
.Parameters.Append .CreateParameter("@return_value", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@TestValue", adNumeric, adParamInput)
.Parameters.Item("@TestValue").NumericScale = 2
.Parameters.Item("@TestValue").Precision = 18
.Parameters.Item("@TestValue").Value = 123.45
End With
'Execute stored procedure and return to a recordset
Set rst = cmd.Execute
If Not rst.EOF Then
rst.MoveFirst
MsgBox (rst.Fields("MaxValue").Value)
Else
MsgBox ("No Value")
End If
'Close database connection and clean up
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
End Sub
'***************************************************************************************************************************
It goes ahead and inserts the record, but gets an error when I try to retrieve the "SELECT"ed value. I get the same results with the following statement:
rst.Open cmd, , adOpenStatic, adLockReadOnly
The error is: Operation is not allowed when the object is closed
If I use the following command, I get the error below:
rst.Open cmd.Execute
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Any insights would be helpful
Bob
Hi All
I've been googling this for a while now and can't seem to find any elegant answers.
I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.
Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?
His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).
In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.
Thanks
GW
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies View RelatedI am running into an issue while executing a sproc from Excel VBA. Everything connects fine, and I am passing a parameter, however, after a few seconds, it seems like the connection receives a "completed" command and continues down it's code, but the sproc is still executing. The result is that I never receive the record set from the sproc.Here is the code snippet from VBA:
' Create Recordset objects.
Dim cmd As New ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim sConnString As String
Dim rs As ADODB.Recordset
Dim strQry As String
Dim rowCount As Long
[code]....
And here is the sproc that is being called. the first thing it performs after the "IF" block (there are multiple steps that would consecutively be called after this, but all of the data hinges on this first step working) is a TRUNCATE statement. After running a SQL profiler while executing the VBA code, I consistently see an "account log out" entry; almost as if the connection from the Excel workbook is sent a disconnect instruction. The sproc continues to run and perform the rest of the script in the "IF" block, but the returned recordset is never returned back to Excel.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
[code]....
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.