Exporting A Table To Excel Using Bcl Utility (Newbie)

Oct 25, 2006

I am trying to learn SQL 2005 Express and I am having a problem exporting (if that is the correct word) a table to Excel.

I have created a view in the Northwind database called MyCustomerView and want to practice working with the table.

From the command prompt:

bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

I then get the error message:

c:> bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

SQLState = HYT00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timeout expired

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This is on a single machine running Widows XP Home.

Help Please

View 2 Replies


ADVERTISEMENT

Exporting Data From Excel Into Sql Server - Newbie

Aug 31, 2007

Using SQL Server 2005 Standard

The basic question
What is the best way to export data from an excel spreadsheet into a sql server table?
My Application
Getting data indicating hours worked from employee timesheets into a centralised DB, then running analysis reports on it.
The columns and datatypes in the excel sheet are as follows:
Week (int) | EmployeeID (int) | JobNum (int) | ActivityNum (int) | Hours (int)

There will be a new excel file each week that, once the employee has filled out the data, would need to be saved and exported to the sql table. The columns in the sql table are exactly the same as the excel table with the addition of a RecordID primary key column.

Can I create a macro button that they can push when they have completed their timesheet OR would it be better to tell the employees to save copies of their timesheets in a certain folder on the company network and then run a batch on all the files in the folder at the end of the day?

Or is there another more efficient solution? Would I use SSIS for this or something else?

I've never used SSIS before and am a newbie at SQL Server too.

Thanks for any help you can give me.

View 3 Replies View Related

Exporting A Table To Excel

Oct 5, 2001

I'm trying to export a table from SQL7 to Excel. Only thing is that I'd like to specify the Excel spreadsheet filename at run time. The name of the spreadsheet needs to contain the current date e.g. "table1_20011005.xls", "table1_20011006.xls". Is there any way I can do this ? I've looked at DTS but it seems you need to specify the spreadsheet file name and cannot alter it.

View 2 Replies View Related

Exporting Sql Table Into Excel.

Jul 19, 2007

Hi,
I have a windows form in visual studio and am trying to export a table from the local sql database into a excel worlbook. I followed this example: (http://support.microsoft.com/kb/307029/en-us) that uses the example northwind mdb database and it worked ,but when I try to change the connection string I get an error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Could anyone let me know the correct way to go about this. My ultimate aim is to export a report.

View 11 Replies View Related

Need Help Exporting Excel To SQL 2005 Table

Apr 23, 2008

Scenario :
SQL table has 2 columns SQ1, SQ2
Excel sheet has three columns : EX1, EX2, EX3

SQ1 matches with EX2
SQ2 matches with EX3

I would like to insert the rows where EX1='X'. EX1 is the left most column in th Excel sheet.

Here's what I am trying. Could you suggest any changes/suggestions?

-- Link server logic
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Config_spreadsheet_load')EXEC master.dbo.sp_dropserver @server=N'Config_spreadsheet_load', @droplogins='droplogins'
GO

--This is the link logic to connect the spreadsheet to the database
sys.sp_addlinkedserver 'Config_spreadsheet_load',
Excel',
'Microsoft.Jet.OLEDB.4.0',
In : @ExcelfileName,
'excel 8.0;
IMEX=1;
GO

-- Insert Plan Codes, for the column (PLC_NEW_COL) marked as ‘X’
insert into Plan_code (SQ1,
SQ2)
-- **** How can I have the logic for checking EX1='X'
where PLC_NEW_COL like ‘X’
-- Once the data has been created, clean-up

View 1 Replies View Related

Exporting Sql Server Table Details To Ms-Excel

Dec 6, 2006

I want to export the datas of a table into a excel file using an Sql Query.

When I execute that query the Excel file should be created automatically in the given path with all the datas in that table.

Can anybody give me a solution for this problem?

View 4 Replies View Related

Exporting Data From Excel Sheet To Db Table

Oct 9, 2007

i have to export the data from excel sheet to database table. for that i created linked server in sql 2000. after creating i get the SQLOLEDB error when i expand the linked server in enterprise manager.Can anyone help me in solving this issue.

View 5 Replies View Related

Exporting Excel Sheet To Sql Server Table

Oct 24, 2007

Hi All ,

I am new to Sql Server and all the DBA stuff. My boss wants me to export a folder containing excel sheet data to sql server table . To be more precise , he wants to automate the DTS process where instead of exporting each and individual worksheet to the DB , he wants me to maintain a separate folder of these sheets and export that folder.

Please do lemme know , how do I do this .

Thanks,
Aparna

View 4 Replies View Related

Exporting Excel Data To An Existing SQL Table

Nov 21, 2007

Hello,

I would like to create a stored procedure that opens a data connection for an excel file that I have saved. I would then like to export the excel data into an existing SQL table. Can anyone tell me the best way to go about this?

Thanks

View 2 Replies View Related

Sql Server Express 2005 Newbie: Bcp Utility

Mar 8, 2008

I'm trying to learn how to use the bcp utility... but I keep getting an error message that says:
SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].SQLState = HYT00, NativeError = 0Error = [Microsoft][SQL Native Client]Login timeout expiredSQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
 The command i'm running is:
bcp territore.dbo.import in territorydata.csv -T -c
territore is my database name... and dbo i think is the owner name.
and the table is called Import
I don't know how to check the default settings to see if remote connections are enabled.  Any help would be appreciated.

View 6 Replies View Related

Problems With Deleting Rows In Destination Table When Exporting To Excel

Mar 29, 2006

Hi,

I export one table to Excel using Export Wizard. Second time I have used this creator I have checked option "Delete rows in destination table" because table already exists in Excel file.

During execution I get an error:

"Error 0xc002f210: Preparation SQL Task: Executing the query "DELETE FROM 'MyTable'" failed with following error: " and here is my translation from my language: "Deleting data from linked table is not supported by this version of ISAM"

I have also saved this package to a file and here is the full connection string to my Excel file:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsprzemekPulpitmyfile.xls;Extended Properties="EXCEL 8.0;HDR=YES";

Few days ago I have been using SQL2000 on the same machine and the same export was working properly.

Any suggestions?



Thanks.

Przemo

View 1 Replies View Related

Exporting A Table With Changing Column Names To An Excel File

Oct 25, 2006

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

View 2 Replies View Related

Exporting Data From SQL Table To Excel File - How To Delete Rows Before Inserting New

Feb 5, 2007

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.

View 7 Replies View Related

Newbie Question ---> Exporting Tables

Sep 3, 2002

Hi all...

I noticed that when transferring tables from the local server to the remote server that some colums lost its properties ... For example, If I am sepcifying a column to have identity seed and identity increment, it is transferred to the server without these properties...


How make a carbon copy of tables from my local to the remote server ?

Thanks in advance

View 1 Replies View Related

Issue With SSRS Report Exporting To Excel With The Matrix Control Inside The Table Control

Jan 27, 2008

Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.

With Thanks
M.Mahendra

View 5 Replies View Related

Dtexec Problem For Excel Source -- Runs Fine Using Execute Package Utility

Mar 3, 2008

I have this problem executing an SSIS pkg using dtexec.



dtexec /f d:smappsssis_pkgsdts_SOAR.dtsx /REP E





Code Snippet

Started: 10:28:58 PMEnd Error



Error: 2008-03-03 22:28:59.00

Code: 0xC0202009

Source: dts_SOAR Connection manager "SourceConnectionExcel"

Description: An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

End Error

Error: 2008-03-03 22:28:59.00

Code: 0xC020801C

Source: Data Flow Task Source - CHANNEL_DATA [1]

Description: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.


Code Snippet







However, it runs fine when I double click the package and execute it using the Execute package utility. That really puzzles me.


The package transfers data from worksheets in excel to SQL Server 2005 DB.

Both source and destination are on the same system.
Please advise.

View 4 Replies View Related

Exporting To Excel

Jun 14, 2001

I have DTS package which exports the data from myTable to myXLSheet - works fine, except this appends data to the existing myXLSheet.

Is there any way of creating a new sheet or clearing out the existing one first???


Thanks

View 2 Replies View Related

Exporting To Excel

Apr 3, 2007

Hi,

Ive installed and imported a db into SQL2005 Express. I now need to export the db to an excel file but I cannot find the import/export feature. Ive tried navigating to the db within the SMSE inteface and right clicking but there is no options available to carry out the task.

Any ideas guys, stuck on this one

DW

View 3 Replies View Related

When Exporting Into Excel

Dec 5, 2007

Does any one know how to name the tabs in reporting services when you export the date to excel. example I want sheet1 to be Summary. Can this be done in rporting services?

View 2 Replies View Related

Exporting To Excel

Jul 20, 2005

HiI am using the DTS package with SQL SERVER 2000. I have a table, and in oneof the columns are links to websites. I am trying to export this to Excel2002 SP3a typical text string stored in the table is=HYPERLINK("www.asite.com","Click")I had hoped the field would be transferred exactly as is and so would appearas a hyper link in the Excel document it creates. Unfortunateld it seems toautomatically put a ' in front of it so if I click on the cell, in the barat the top I get'=HYPERLINK("www.asite.com","Click") - Notice the apostropheso the full text is displayed in the spreadsheet (rather than the wordClick)Does anyone know of a way to get rid of the ' thats being added onthanks in advanceAndy

View 2 Replies View Related

Exporting Into Excel

Mar 7, 2008


Hi,

I have 5 subreports, where each is wrapped in its own rectangle, in one report. The report renders successfully. However, when I try to export the results to Excel, I receive the following error


An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Item has already been added. Ket in dictionary: '_41_201' Key being added: '_41_201'

Any ideas?

The reports defined in the subreports contain drill through links to each other. These reports were originally designed to be drill through reports. However, my user will like to export them into one workbook.

View 1 Replies View Related

Exporting To Excel

Jul 11, 2007

Hi All,



I am creating reports using SSRS 2005.

After deploying my reports to the reporting server i want my reports to be exported to excel.

But when i perform this the formatting of the report gets changed in excel.

The cell in excel gets merged.thus the structure gets distorted.



Does anyone have any idea in this regrard?

Why is it so and what is the solution for the same?



Thanks



Regards

Saurabh



View 14 Replies View Related

Exporting To Excel

Oct 23, 2007

Hi,

Sorry if this is a stupid question, RS isn't my area of expertise, a customer has asked if it was possible to export all the data in an RS report onto one Excel sheet.

She has created a report off an AS2005 cube, there are 71 pages, is there a way of increasing the number of rows per page or even putting them all on one page? Having some 30 sheets in a spreadsheet seems a little excess.

Thanks in advance
Matt

View 5 Replies View Related

Exporting SQL Resultset To Excel

Jan 3, 2003

Hi,

I am having some problems exporting to Excel using SQL 7.

I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?

Thanks.

View 3 Replies View Related

Exporting Date To Excel

Aug 14, 2014

The date in sql appears like this '07/25/2013 00:00:00' but when I export to excel the date shows like this '22-JUL-81 12.00.00.000000000 AM'. When I change format in excel nothing happens.

View 6 Replies View Related

Exporting Ad Hoc Queries To Excel

Feb 19, 2007

Kevin writes "Please forgive me if this is a stupid question. I am working in an environment where we are using SQL Server 2005 and Excel 2003. We have a custom .NET 2005 application which runs against the SQL Server. Users frequently create ad hoc queries in Management Studio to pluck out certain info. They would like to save the results down into an Excel spreadsheet for further analysis; However, we haven't come up with an elegant solution to save these results in Excel and keep the column headings and data types for each column. I have seen transact-sql code to do this in the past using the OPENROWSET command to write to an Excel file; However, I am not confident that this is the best way to skin this particular cat so I thought I would run it by you. So, my question is, What is the BEST way to save ad hoc query results from SQL Server to an Excel spreadsheet so that I preserve the column headings and data type of each column?"

View 2 Replies View Related

Exporting Data To Excel

Jul 15, 2007

Hi,

I am Sree Raj. New to SQL Server.

I am trying to export data to excel and it is giving an error.
Here is the query and error:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:SQL ServerEmp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM Emp_Info

error is:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

Can anybody please help me what is this error about & how to fix this.

Thanks in advance
Sree Raj

View 1 Replies View Related

Exporting Data To Excel From A DTS

Jul 23, 2005

Hi all,I've seen this noted in many posts, but nothing I've checked out givesme any clue on how to do this.Basically as my topic says, I have a DTS and I simply need to exportsome data from a table in MS SQL 2000 to an Excel spreadsheet. I alsoneed to automate this process so it can run nightly and each new day anew spreadsheet will be on a network share for us to pick-up.Can someone point me to the right direction? This needs to be donetotally through the DTS script, so no ImportExport wizard or anythingmanual.Thanks --Sam

View 1 Replies View Related

Exporting SQL To Excel Hourly

Jul 20, 2005

First time here so please bear with me.Set up a DTS package to export data to an excel sheet on an hourlybasis. Problem is, it keeps appending to the same excel sheet.Any idea how to prevent that. All I want to accomplish is that everyhour, the latest data is in the excel sheet and the previous data isdeleted.Thanks in advance!

View 5 Replies View Related

Exporting A Forumal To Excel

Apr 22, 2008

My users have an excel sheet I'm trying to migrate to RS...by and large it's just staic data, which is GREAT for me. But the sheet has one "Total" column, where they locally enter three values then print it off. Is there a way to get a =SUM(X:X,X:X) to migrate to excel so it defaults to 0? If I put it in quotes those come over to excel, if I dont put it in quotes I cant even preview...

Suggestions?
Thanks,
Steve

View 4 Replies View Related

Problem Exporting To Excel

Feb 13, 2007

Hello,

Im using SSRS and i made a report that has a huge quantity of data, and when i want to export it to excel i got the following error.


Server Error in '/Reports' Application.


For more information about this error navigate to the report server on the local server machine, or enable remote errors

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +959
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64





Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

I can export other reports to excel, but not this one. Can anyone help me here?

Thank you

View 2 Replies View Related

Error When Exporting To Excel Only

Aug 28, 2007

I get an error when I export my report to excel. But I do not get this error if I export the report to any other format. The error is:

An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Object reference not set to an instance of an object.

Any ideas what could be causing this error?

This report does contain a couple of sub-reports.

View 1 Replies View Related

Exporting To Excel - File Gets Too Big

Sep 25, 2007

Hi,

I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...

My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...

Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this

thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved