Extract Data from Excel 2007 on Vista

Hello all,

I am in the process up testing an upgrade from XP to Vista, and the only thing that I am running into is that my linked servers for Excel that I defined no longer work.

The spreadsheet that I am trying to open is in the 97-2003 format (not the 2007 format), yet I keep getting the same error message "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST1"

Has anyone successfully created a linked server to an Excel spreadsheet on Vista, and if so, please can you provide some insight into what I am doing wrong.

I tried creating a linked server on an XP box running MS Office 2007, and it worked without any issues.

All comments welcome.

thanks

Steve

Where is the excel located in ? If it is in a UAC controlled folder (which would need special permissions / elevation) you willprobably not be able to access the file unless you disbaled UAC and restartet the computer. If this is not the case, you should check if the the file is accessible to you (if you use windows authentication and have the linked server confgured for using the Windows Authentication of the logged in user) or if the SQL Server service account has permissions to access the file (in case that you are either using SQL Server authentication or you configured the linked server to use the SQL Server service account credentials)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

Thanks for the response. I changed the log on properties of the SQL Server service to use a name user instead of the built in Network Service user and that seemed to fix the problem.

Steve

sql

Extract Data from Excel 2007 on Vista

Hello all,

I am in the process up testing an upgrade from XP to Vista, and the only thing that I am running into is that my linked servers for Excel that I defined no longer work.

The spreadsheet that I am trying to open is in the 97-2003 format (not the 2007 format), yet I keep getting the same error message "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST1"

Has anyone successfully created a linked server to an Excel spreadsheet on Vista, and if so, please can you provide some insight into what I am doing wrong.

I tried creating a linked server on an XP box running MS Office 2007, and it worked without any issues.

All comments welcome.

thanks

Steve

Where is the excel located in ? If it is in a UAC controlled folder (which would need special permissions / elevation) you willprobably not be able to access the file unless you disbaled UAC and restartet the computer. If this is not the case, you should check if the the file is accessible to you (if you use windows authentication and have the linked server confgured for using the Windows Authentication of the logged in user) or if the SQL Server service account has permissions to access the file (in case that you are either using SQL Server authentication or you configured the linked server to use the SQL Server service account credentials)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

Thanks for the response. I changed the log on properties of the SQL Server service to use a name user instead of the built in Network Service user and that seemed to fix the problem.

Steve

Extract data from 350 seperate Excel Files

We have used a template for 350 excel files and now we are trying to
extract certain information from these files to either one excel file
or to an access database. The problem is that in this template the rows
are not necessarily the same in each file. (E.G. If a company started
in 1995 the corresponding rows and columns for the 2000 data will be
different than a company that started in 1999.) I also would like to
change the column headings to rows and the rows into heading columns. I
know its a big task and I am not so sure how to begin. Any thoughts
would be appreciated.<acaseutk@.gmail.com> wrote in message
news:1142284202.900306.197310@.i40g2000cwc.googlegroups.com...
> We have used a template for 350 excel files and now we are trying to
> extract certain information from these files to either one excel file
> or to an access database. The problem is that in this template the rows
> are not necessarily the same in each file. (E.G. If a company started
> in 1995 the corresponding rows and columns for the 2000 data will be
> different than a company that started in 1999.) I also would like to
> change the column headings to rows and the rows into heading columns. I
> know its a big task and I am not so sure how to begin. Any thoughts
> would be appreciated.
I sympathise with you. I seem to have spent much of my career trying to
educate accountants that a spreasheet is a totally lousy way to store data.
My suggestion is that you use Excel macros or cut and paste to get the data
as straight as you can first. Then try saving the files in delimited form
(again you can automate with macros) and import from the intermediate format
to some staging tables. Then you have LOTS of validation and transformation
to do.
You can try DTS or Integration Services straight from the Excel sheets but
in my experience this rarely works in your situation. Each file will have
different formatting, column widths, heading, etc and DTS will choke again
and again unless you are lucky. I can't say I've tried it with IS though -
maybe some things have improved.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How did you know I was an accountant...Haha. Thanks for the suggestion
I will look into it.

Extra columns in Excel with SP2

I would like to upgrade to Reporting Services service pack 2 so that I can
specify report colours and specify to move the header into Excel's header.
However when we upgrade to Service Pack 2 it causes extra totals columns to
appear at the end of the months in the generated reports. These columns
could possibly acceptable if they were titled as Total however they are not.
Can anybody make any suggestions in how to generate the report in excel
without the additional columns appearing in the excel output? Many thanks.Does anybody have any ideas about how to do this - any guesses even would be
appreciated. Thanks
"Nicola Jones" wrote:
> I would like to upgrade to Reporting Services service pack 2 so that I can
> specify report colours and specify to move the header into Excel's header.
> However when we upgrade to Service Pack 2 it causes extra totals columns to
> appear at the end of the months in the generated reports. These columns
> could possibly acceptable if they were titled as Total however they are not.
> Can anybody make any suggestions in how to generate the report in excel
> without the additional columns appearing in the excel output? Many thanks.

Extra character

Hi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
FrenkHi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
Frenk

If this is not a display problem, you can use replace() function to clean up the text.

e.g.
select replace(col,'@.',space(0))
from tb|||but the @. can not be seen in the database, and sometime it shows # instead could you please let me know what's happen|||Do those characters occur as the first character?|||no, it occurs at last character

that I normally do, first go to the last character, then press space bar and backspace, the @. or # will be disappeared|||Run this query and see if you see those characters

Select SubString(yourCol,1,len(yourCol)-1) from yourTable|||I can not see those character, the last character of each reocrd is missing.|||Well. Use that query to display in your application
If you want to remove those from table, then

Update yourTable Set yourCol=SubString(yourCol,1,len(yourCol)-1) where SubString(yourCol,1,len(yourCol)-1) in ('@.','#')

Extra Browser window when exporting?

Hello,
I'm using the HTML Viewer and URL access to render my reports. When I change the format (for example to excel) on the HTML Viewer, and click the "export" link beside the drop down. A new browser window pops up and a save box prompting me to specify the location of where to save. After I choose the save destination, download the new formated report, and close the save box, the newly opened browser window remains open.

Is there any way to automatically close that browser window? or maybe even not show that browser window at all?

Thanks in AdvanceUnfortunatly there is no way to get rid of this extra box.|||Oh Well... Thanks for the response

Extra Browser window when exporting?

Hello,
I'm using the HTML Viewer and URL access to render my reports. When I change the format (for example to excel) on the HTML Viewer, and click the "export" link beside the drop down. A new browser window pops up and a save box prompting me to specify the location of where to save. After I choose the save destination, download the new formated report, and close the save box, the newly opened browser window remains open.

Is there any way to automatically close that browser window? or maybe even not show that browser window at all?

Thanks in AdvanceUnfortunatly there is no way to get rid of this extra box.|||Oh Well... Thanks for the response

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

Externally triggered DTS to import excel data to SQL server

I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!You could get a stored procedure to start the scheduled job which is running the DTS package. A basic stored procedure to run the job would be:

CREATE PROCEDURE sp_StartDTS

AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The DTS job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax. There is also a success/fail return code which you could use in the ASP page|||Originally posted by jasper627
I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!

to overcome bad data in the Excel spreadsheet you could import the data into a holding table that will allow nulls or other bad data, then run some SQL over the table identitfying good records by updating a bit field in the table. If the types of data errors are known and can be fixed automatically eg NULL should be 0 then you could fix that either in the DTS package with a VB script or later with SQL.

External table is not in the expected format

Hi,

I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called

External Table is not in the expected format

System.Data.OleDb.OleDbException: External table is not in the expected format

Any help will be appreciated.

Regards,

Karen

Try to save the Excel file in cvs format before the importing. There can be something in the excel document that mess up the import.

|||

Thanks for ur answer.. but the other 6 files work fine.|||

There might be something in the 7th file that messes up things. Try CSV and see if it helps. If it doesn't then something else is wrong...

|||

Johram,

I tried converting it to a csv file and getting the same error

|||

OK, you gotta make sure all values in a column is in the same format. Upon import, the first row is scanned in order to determine the datatype of each column. If he find a number in the first column, then he assumes that this is a numerical column. If there is character data in the column somewhere then there will be an error. Maybe it's not character data that's your problem, but such a thing as an empty value or a decimal value when an integer value is expected.

If you open up the 7th file and scan through the rows, then you might spot the deviation?

See this article:http://blog.lab49.com/?p=196

Good luck!

|||

Johram,

Thanks a lot for your help.. the reason i was getting that was i had given

flSubAdvisor.PostedFile.SaveAs(location6)

instead of

flGrowth10K.PostedFile.SaveAs(location6)

and

flSubAdvisor.PostedFile.SaveAs(location6) was equal nothing or it was empty..

any way thanks a lot for ur help.

Regards

Karen

Expression problem in Derived Column Component

I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))

However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.

[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.

I just recreated this scenario using flat file as a source for <first name, last name>. Your formulas work correctly for me in the derived column transform. Where have you exactly defined the "LastColumn"?

Exports to Excel the details of the report but I need summary

The trouble I am having is that I have a drilldown report that exports the detail to Excel, but I want the summary exported to Excel.

I perform the following steps but get wrong results. Please help me identify the correct steps for the correct results. Thanks you.

1) Select Matrix.

2) Right click, select Properties.

3) Select tab Groups.

4) Select item in Columns list, click Edit.

5) Select tab Visibility.

6) Select Initial visibility: Hidden, and click okay to the Grouping and Sorting dialog box.

Now I can export summary to Excel okay, but now I can not expand the summary in the report itself, so I do the following:

7) Do one through six above (but do not close dialog box), then click visibility can be toggled by another report item.

8) Select the report item in the Report Item drop down list.

Now the report functions normally in the Reporting Services report web page, but when I export on the summary level, again it exports the detail to Excel, but what I want it to export is the summary.

Ideas?

Is this forum active? Only when the drilldown functionality is disabled does the summary export to excel, even though the summary column is calculated from the underlying detail columns. As soon as the drilldown feature is enabled, then the export to excel generates the detail data, even though it is the summary being displayed in the Reporting Services report at the time of the export. Ideas? Anyone?|||

http://msdn2.microsoft.com/en-us/library/aa178935(SQL.80).aspx

I believe I found the answer to the question of why this report is exporting to the detail. Click on the link above peruse down to this comment, here:

“For matrices, if the matrix is collapsed when viewed in HTML, exporting to Excel expands it. All rows and columns are visible. ”

Part of my confusion comes from being told by the users that this used to work prior to the service pack 2 upgrade. However, I suspect that the summary was never exported to Excel, even prior to the upgrade.

To export with the appropriate summary, I am recommending that the report move from implementing a matrix, to implementing a table for drilldown.

Exports to Excel the details of the report but I need summary

The trouble I am having is that I have a drilldown report that exports the detail to Excel, but I want the summary exported to Excel.

I perform the following steps but get wrong results. Please help me identify the correct steps for the correct results. Thanks you.

1) Select Matrix.

2) Right click, select Properties.

3) Select tab Groups.

4) Select item in Columns list, click Edit.

5) Select tab Visibility.

6) Select Initial visibility: Hidden, and click okay to the Grouping and Sorting dialog box.

Now I can export summary to Excel okay, but now I can not expand the summary in the report itself, so I do the following:

7) Do one through six above (but do not close dialog box), then click visibility can be toggled by another report item.

8) Select the report item in the Report Item drop down list.

Now the report functions normally in the Reporting Services report web page, but when I export on the summary level, again it exports the detail to Excel, but what I want it to export is the summary.

Ideas?

Is this forum active? Only when the drilldown functionality is disabled does the summary export to excel, even though the summary column is calculated from the underlying detail columns. As soon as the drilldown feature is enabled, then the export to excel generates the detail data, even though it is the summary being displayed in the Reporting Services report at the time of the export. Ideas? Anyone?|||

http://msdn2.microsoft.com/en-us/library/aa178935(SQL.80).aspx

I believe I found the answer to the question of why this report is exporting to the detail. Click on the link above peruse down to this comment, here:

“For matrices, if the matrix is collapsed when viewed in HTML, exporting to Excel expands it. All rows and columns are visible. ”

Part of my confusion comes from being told by the users that this used to work prior to the service pack 2 upgrade. However, I suspect that the summary was never exported to Excel, even prior to the upgrade.

To export with the appropriate summary, I am recommending that the report move from implementing a matrix, to implementing a table for drilldown.

Exporting ToExcel file

Hi, I have a SP that adds an Excel file as a linked server, then tries to
send the result of a query into this file.
I get the following error :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
convert(varchar(10),ProductId), ProductName, Convert (varchar(20),UnitPrice)
from Northwind..Products
[OLE/DB provider returned message: Cannot start your application.
The workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I am executing this on my Laptop(winXP SP2), sql 2000 is on my laptop. So
what authentification is failing?
Thanks in advance
Hi,
Could you please post the exact text of the stored
procedure? You can use sp_helptext.
You may also want to test this script to see if there is
any problem:
sp_dropserver 'EXCELSOURCE', 'droplogins'
go
--Replace 'E:\test.xls' appropriately
sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
@.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
'E:\test.xls' , @.provstr = 'Excel 5.0'
go
Insert ExcelSource...[ExcelTable$] ( A,B,C )
select convert(varchar(10),ProductId), ProductName,
Convert (varchar(20),UnitPrice)
from Northwind..Products
go
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>Subject: Exporting ToExcel file
>Date: Mon, 21 Feb 2005 21:53:01 -0800
>Lines: 18
>Message-ID:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26922
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hi, I have a SP that adds an Excel file as a linked
server, then tries to
>send the result of a query into this file.
>I get the following error :
>--
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. Authentication
>failed.
>Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
>convert(varchar(10),ProductId), ProductName, Convert
(varchar(20),UnitPrice)
>from Northwind..Products
>[OLE/DB provider returned message: Cannot start your
application.
>The workgroup information file is missing or opened
exclusively by another
>user.]
>OLE DB error trace [OLE/DB Provider
'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
>--
>I am executing this on my Laptop(winXP SP2), sql 2000
is on my laptop. So
>what authentification is failing?
>Thanks in advance
>
|||Hello, here is the SP :
CREATE proc sp_write2Excel
(
@.fileName varchar(100),
@.NumOfColumns tinyint,
@.query varchar(200)
)
--Obligation : create an empty Excel file with a fixed name and place on the
server
/*
Usage
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,
-- Number of columns in result
3,
-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
*/
AS
Begin
declare @.dosStmt varchar(200)
declare @.tsqlStmt varchar(500)
declare @.colList varchar(200)
declare @.charInd tinyint
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
set @.charInd=0
set @.colList = 'A'
while @.charInd < @.NumOfColumns - 1
begin
set @.charInd = @.charInd + 1
set @.colList = @.colList + ',' + char(65 + @.charInd)
end
-- Create an Empty Excel file as the target file name by copying the
template Empty excel File
set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' + @.fileName
exec master..xp_cmdshell @.dosStmt
-- Create a "temporary" linked server to that file in order to
"Export" Data
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
-- construct a T-SQL statement that will actually export the query
results
-- to the Table in the target linked server
set @.tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' +
@.colList + ' ) '+ @.query
print @.tsqlStmt
-- execute dynamically the TSQL statement
exec (@.tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
End
GO
"William Wang[MSFT]" wrote:

> Hi,
> Could you please post the exact text of the stored
> procedure? You can use sp_helptext.
> You may also want to test this script to see if there is
> any problem:
> sp_dropserver 'EXCELSOURCE', 'droplogins'
> go
> --Replace 'E:\test.xls' appropriately
> sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
> @.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
> 'E:\test.xls' , @.provstr = 'Excel 5.0'
> go
> Insert ExcelSource...[ExcelTable$] ( A,B,C )
> select convert(varchar(10),ProductId), ProductName,
> Convert (varchar(20),UnitPrice)
> from Northwind..Products
> go
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <eliassal@.online.nospam>
> <532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
> phx.gbl!TK2MSFTNGXA03.phx.gbl
> microsoft.public.sqlserver.tools:26922
> server, then tries to
> error. Authentication
> (varchar(20),UnitPrice)
> application.
> exclusively by another
> 'Microsoft.Jet.OLEDB.4.0'
> Authentication failed.].
> is on my laptop. So
>
|||Hi,
Your script looks good and it works correctly on my test
machine. Based on my research, this issue can occur
because the login used to connect to the SQL Server does
not have enough permission. Please add the following
statement to your SP defination (below EXEC
sp_addlinkedserver):
EXEC sp_addlinkedsrvlogin 'ExcelSource',
'false',NULL,'ADMIN',NULL
then drop the existing SP and create a new SP to test
the problem.
Feel free to let me know if this resolves your problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>References:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
<K8U2ePMGFHA.2840@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Exporting ToExcel file
>Date: Tue, 22 Feb 2005 10:37:04 -0800
>Lines: 168
>Message-ID:
<6D98A325-8651-4FD5-AC3E-ADDE2258B7C1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT FEED01.
phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26924
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello, here is the SP :
>--
>CREATE proc sp_write2Excel
>(
>@.fileName varchar(100),
>@.NumOfColumns tinyint,
>@.query varchar(200)
>)
>--Obligation : create an empty Excel file with a fixed
name and place on the
>server
>/*
>Usage
>exec sp_write2Excel
> -- Target Excel file
> 'c:\temp\NorthProducts.xls' ,
> -- Number of columns in result
> 3,

> -- The query to be exported
> 'select convert(varchar(10),ProductId),
> ProductName,
> Convert (varchar(20),UnitPrice) from
Northwind..Products'
>
>*/
>AS
>Begin
> declare @.dosStmt varchar(200)
> declare @.tsqlStmt varchar(500)
> declare @.colList varchar(200)
> declare @.charInd tinyint
> set nocount on
> -- construct the columnList A,B,C ...
> -- until Num Of columns is reached.
> set @.charInd=0
> set @.colList = 'A'
> while @.charInd < @.NumOfColumns - 1
> begin
> set @.charInd = @.charInd + 1
> set @.colList = @.colList + ',' + char(65 +
@.charInd)
> end
> -- Create an Empty Excel file as the target
file name by copying the
>template Empty excel File
> set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' +
@.fileName
> exec master..xp_cmdshell @.dosStmt
> -- Create a "temporary" linked server to that
file in order to
>"Export" Data
> EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0',
>'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
> -- construct a T-SQL statement that will
actually export the query
>results
> -- to the Table in the target linked server
> set @.tsqlStmt = 'Insert
ExcelSource...[ExcelTable$] ' + ' ( ' +[vbcol=seagreen]
>@.colList + ' ) '+ @.query
> print @.tsqlStmt
> -- execute dynamically the TSQL statement
> exec (@.tsqlStmt)
> -- drop the linked server
> EXEC sp_dropserver 'ExcelSource'
> set nocount off
>End
>GO
>
>"William Wang[MSFT]" wrote:
is[vbcol=seagreen]
,[vbcol=seagreen]
and[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.[vbcol=seagreen]
an[vbcol=seagreen]
2000
>

Exporting to multiple sheets in Excel has limitations ?

guys, I am exporting a report in excel that generates in different
sheets.
It seems that only 3 tab or sheets can be generated.
If the report exceeds 3 sheets all the rest of the sheets combined in
the last sheet.
Can anyone confirm this? Does it have any work around or I am just
doing something wrong?
thanks in advanceHi, I am doing some testing on sending data to Excel myself. I just tested
this and was able to make 5 sheets when I made page brakes between rectagles
in the report. Have you used the device setting RemoveSpace when rendering
to Excel ?
"jhun_garma@.hotmail.com" wrote:
> guys, I am exporting a report in excel that generates in different
> sheets.
> It seems that only 3 tab or sheets can be generated.
> If the report exceeds 3 sheets all the rest of the sheets combined in
> the last sheet.
> Can anyone confirm this? Does it have any work around or I am just
> doing something wrong?
>
> thanks in advance
>

Exporting to Multiple Excel worksheets

I am having trouble exporting my SRS data to multiple Excel worksheets.
I have four tables. I want each of these tables to appear on a
different worksheet even if there is no data for it. For one of my
queries, there was only data for 3 of these tables. First I selected
"Insert a Page Break after this table" for all of my Table properties.
When I exported this output to Excel, it only showed me 3 worksheets.
But I want it to always show me all four worksheets even if there is no
data for that table. And I believe it should still show me something
on this 4th worksheet because in SRS on each table's property I entered
in "No Data Available" for the NoRows table property. Instead what it
does is the Excel file includes this 4th table on the same worksheet as
one of the other tables. And I clearly see the text "No Data
Available", but it's not on its own page.
So again, how do I automatically have all 4 tables show up on 4
different tabs (worksheets).Try selecting "Insert a Page Break Before this table" for your 4th
table and see what happens.
Mike|||I tried this, but this didn't fix it either.
"Bassist695" wrote:
> Try selecting "Insert a Page Break Before this table" for your 4th
> table and see what happens.
> Mike
>|||Try to use GROUP in the layout design. Each group will be distributed as
individual worksheet accordingly.
ironryan77@.gmail.com wrote:
>I am having trouble exporting my SRS data to multiple Excel worksheets.
> I have four tables. I want each of these tables to appear on a
>different worksheet even if there is no data for it. For one of my
>queries, there was only data for 3 of these tables. First I selected
>"Insert a Page Break after this table" for all of my Table properties.
>When I exported this output to Excel, it only showed me 3 worksheets.
>But I want it to always show me all four worksheets even if there is no
>data for that table. And I believe it should still show me something
>on this 4th worksheet because in SRS on each table's property I entered
>in "No Data Available" for the NoRows table property. Instead what it
>does is the Excel file includes this 4th table on the same worksheet as
>one of the other tables. And I clearly see the text "No Data
>Available", but it's not on its own page.
>So again, how do I automatically have all 4 tables show up on 4
>different tabs (worksheets).
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200603/1|||Just return a space character on any of the table fields so that RS will
thing that the table needs to be printed on the fourth sheet as well. If you
have empty it wont return but just insert a space if no records are
available.
Amarnath.
"ironryan77@.gmail.com" wrote:
> I am having trouble exporting my SRS data to multiple Excel worksheets.
> I have four tables. I want each of these tables to appear on a
> different worksheet even if there is no data for it. For one of my
> queries, there was only data for 3 of these tables. First I selected
> "Insert a Page Break after this table" for all of my Table properties.
> When I exported this output to Excel, it only showed me 3 worksheets.
> But I want it to always show me all four worksheets even if there is no
> data for that table. And I believe it should still show me something
> on this 4th worksheet because in SRS on each table's property I entered
> in "No Data Available" for the NoRows table property. Instead what it
> does is the Excel file includes this 4th table on the same worksheet as
> one of the other tables. And I clearly see the text "No Data
> Available", but it's not on its own page.
> So again, how do I automatically have all 4 tables show up on 4
> different tabs (worksheets).
>|||I have tried all of the above suggestions, but none of them work. Have any
of you ever tried to do what I'm doing? Regarding Amarnath's response, it
would not be easy for me to insert a space since I am filtering the data from
one table. In other words, my SP selects data into one table which is then
filtered in SRS based on the grouping. Unless there is a way to form an
expression so that it inserts a space. Is this possible?
Regarding Frog's post, I created a group for this table and set the filter,
but this did not work either. And I have tried adding header and footer
where all of the footers contain the text "End of Record", but for this one
table with no data in it, neither header nor footer display. Only the text I
enter into the NoRows property displays. If I remove the text from NoRows
then there is a big space on that part of the worksheet, but I still only
have 3 total worksheets.
"Amarnath" wrote:
> Just return a space character on any of the table fields so that RS will
> thing that the table needs to be printed on the fourth sheet as well. If you
> have empty it wont return but just insert a space if no records are
> available.
> Amarnath.
> "ironryan77@.gmail.com" wrote:
> > I am having trouble exporting my SRS data to multiple Excel worksheets.
> > I have four tables. I want each of these tables to appear on a
> > different worksheet even if there is no data for it. For one of my
> > queries, there was only data for 3 of these tables. First I selected
> > "Insert a Page Break after this table" for all of my Table properties.
> >
> > When I exported this output to Excel, it only showed me 3 worksheets.
> > But I want it to always show me all four worksheets even if there is no
> > data for that table. And I believe it should still show me something
> > on this 4th worksheet because in SRS on each table's property I entered
> > in "No Data Available" for the NoRows table property. Instead what it
> > does is the Excel file includes this 4th table on the same worksheet as
> > one of the other tables. And I clearly see the text "No Data
> > Available", but it's not on its own page.
> >
> > So again, how do I automatically have all 4 tables show up on 4
> > different tabs (worksheets).
> >
> >

Exporting to MS Excel

We are unable to maintain the format of our 3 column style report when we
export the report to MS Excel. However, we have no problems with maintaining
the 3 column style report format when exporting to .PDF.
Please help!
However, our business depends on this formatted report for our 2400 members
accessing the membership directory.
Thank you.On Feb 23, 11:09 am, Terry <T...@.discussions.microsoft.com> wrote:
> We are unable to maintain the format of our 3 column style report when we
> export the report to MS Excel. However, we have no problems with maintaining
> the 3 column style report format when exporting to .PDF.
> Please help!
> However, our business depends on this formatted report for our 2400 members
> accessing the membership directory.
> Thank you.
Unfortunately, columns are only supported when exported to PDF and
Tiff.
Regards,
Enrique Martinez
Sr. Web/Database Independent Consultant

Exporting to Excel; timeout

Exporting to Excel; timeout

I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”

Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service

Follow this KB http://support.microsoft.com/default.aspx/kb/909678

Exporting to Excel...problems, problems

I'm exporing a simple matrix report to excel. Once exported I want to sort on
a particular column but it errors out..."This operation requires merged cells
to be identically sized." If I highlight all the cells then go to format ->
cells - > uncheck merge cells it still does not work. The numeric data in the
columns is being treated as text. Can someone please offer some suggestions?
the matrix is quite simple. Person, month columns with the data being the
count per month.
Thanks,Did you try using Data/Text to Columns... on the column of data in question?
"Brian L" wrote:
> I'm exporing a simple matrix report to excel. Once exported I want to sort on
> a particular column but it errors out..."This operation requires merged cells
> to be identically sized." If I highlight all the cells then go to format ->
> cells - > uncheck merge cells it still does not work. The numeric data in the
> columns is being treated as text. Can someone please offer some suggestions?
> the matrix is quite simple. Person, month columns with the data being the
> count per month.
> Thanks,

Exporting to Excel without generating report.

Can a report be exported to an Excel sheet directly without showing it into
my web application?
Thanks a lot! :)Yes, use the URL Access Parameters displayed in the BOL.
&rs:Format=Excel
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Tomas Martinez" <TomasMartinez@.discussions.microsoft.com> schrieb im
Newsbeitrag news:FC958C51-856F-4869-8E95-61C64E61604D@.microsoft.com...
> Can a report be exported to an Excel sheet directly without showing it
> into
> my web application?
> Thanks a lot! :)