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

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 ('@.','#')

Externally Linked Reports

Let's say I have a report which displays employee details based on employee Id. The Employee Id is passed to the report as a parameter. Lets say you can select this from the dropdown list on the report. All of these reports are deployed on report server 2005. What I also have is another application where you manage the employee data.

What I want to do is put a link in the management application to the eomployee detail report and pass the employee id in the link, so that the user does not have to select the employee id on the report. I know that we can provide a link to the reports, but I don't know if we can also pass parameters to those reports in that link.

Any help is appreciated, thanks!!

This is pretty straight forward. We are doing the same thing like so:

http://domainname/reportserver/Pages/ReportViewer.aspx?%2fOurReports%2fReportName&rs:Command=Render&Parameter=InsertParamValueHere

Essentially you can just add &Parameter=Value to the URL to pass the parameter.

|||

All you need to use is a query string. For example:http://servername/appname/page.aspx?id=EMPLOYEE_ID

Cheers!

External Linked Servers

Hi All,

I am doing some research about External Linked Servers and am hoping that someone can point me towards some best practices information and let me know about any gotchas that I should look out for when using this capability in applications.

Thanks in advance,

Jay, I'm doing the same thing. If you find out anything would you let me know and I will do the same for you. Thanks|||

Have you seen this information about linked servers in the SQL Server 2005 Books Online?

http://msdn2.microsoft.com/en-us/library/ms188279.aspx

Hope this helps,
Vaughn

External images

I'm having problems getting external images to show up in a report. As
suggested in other posts in this group - the resource is linked to a fully
qualified server name.
The reporting services is locked down to authenticated users. The image url
is to the same server and I've tried setting access to both anonymous access
and authenticated users. I don't believe it is a permissions problem
because I can paste the url in a browser and view the image with no problem.
When I right click on the red x (where the image should be) and go to
properties and look at the url - it only shows the server name and not the
rest of the url (folder and file name.) When I review the data that provides
the url for the external image - the correct url is in there. I copy and
paste to the browser - the image comes up fine.
This really has me stumped - because everything seems to be right. I have
this running in three sites with no problem and don't see what I'm doing
different here. The only difference is that this is running in a tightly
locked down hospital setting. Is there some browser setting that can be set
in a policy that would restrict a web page from pulling info from multiple
sources or something?
Thanks,
DavidHi David,
Welcome to the MSDN newsgroup.
As for the image displaying problem, based on my experience, we could
troubleshoot from two aspects, the client-side browser and the server-side
report deisgn. Have you try visisting the report page from other client
machines to see whether the image can display well? If also suffering the
problem, it is likely the report or the image part is not correctly
rendered. Also, as you mentioned that when you right click the "X" and view
URL, it doesn't point to the correct image file path ,yes? I suggest you
also use the browser's "View Source" menu item to check the HTML source of
the report page, if the <img> element is not assigned the correct "src"
attribute, it is likely the server report's design has some thing incorrect
that cause this problem.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I looked at the source code and it says this for the image <IMG class="r1"
SRC="">
I verified the data in the dataset and it is coming back with the correct
url for the image.
When I look at the source for a page that is rendering correctly (in a
different location) it's coming back with the following
<IMG class="r1"
SRC="</DIV></TD><TD">http://hvsql01/ReportServer?%2fCardioDx%2fNuclear&EncounterID=22004&rs%3aFormat=HTML4.0&rs%3aImageID=189"></DIV></TD><TD
ROWSPAN="3" style="WIDTH:3.18mm">
I'm a little suprised at the url for the image source. Looks like it's
coming from the reportserver instead of directly from the source. Makes
sense I guess having to generate the pictures in other formats would require
pulling it through the rendering engine.
Everything looks the same as the source running successfully in another
environment.
Thanks for helping narrow this down however - not sure what to do at this
point.
I'm running SQLRS with service pack 2. One of the fixes I tried to apply was
to add an unattended mode user with the rsconfig app. Nothing changed but
it accepted it.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:5X1KGl1QGHA.1592@.TK2MSFTNGXA03.phx.gbl...
> Hi David,
> Welcome to the MSDN newsgroup.
> As for the image displaying problem, based on my experience, we could
> troubleshoot from two aspects, the client-side browser and the server-side
> report deisgn. Have you try visisting the report page from other client
> machines to see whether the image can display well? If also suffering the
> problem, it is likely the report or the image part is not correctly
> rendered. Also, as you mentioned that when you right click the "X" and
> view
> URL, it doesn't point to the correct image file path ,yes? I suggest you
> also use the browser's "View Source" menu item to check the HTML source of
> the report page, if the <img> element is not assigned the correct "src"
> attribute, it is likely the server report's design has some thing
> incorrect
> that cause this problem.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your respone David,
I've also performed some further tests on my side and did found some clues.
Actually, what you have seen (I'm a little suprised at the url for the
image source. Looks like it's coming from the reportserver instead of
directly from the source) is the expected behavior because SQL Reporting
service does not directly simply put the external image's address in the
report's rendered html. Instead, the report server will first retrieve the
image stream itself than provide a url point to the image stream from the
report server. Therefore, this make it important that we need to make sure
our report server application can correct access that image under its
running security account. See the below note in BOL:
====================External Images
You can include stored images in a report by specifying a URL to the image.
When you use an external image in a report, the image source is set to
External and the value for the image is the URL address to the image (for
example, http://<servername>/images/image1.jpg).
When the report is previewed in Report Designer, preview uses the
credentials of the user to display the image. When the report is run on the
report server, the report server uses the unattended execution account to
retrieve the image. If the unattended execution account is not specified,
the image is retrieved using no credentials (anonymous user account). If
either of these accounts have insufficient rights to access the image, the
image will not be displayed in the report. For more information about the
unattended execution account, see Configuring an Account for Unattended
Report Processing.
====================
And based on my test, when I specify an invalid url ( or a url the report
server can not access). the "src" attribute of the <img> tag will be empty
, just like <img ... src="http://pics.10026.com/?src=" /> , and this conforms to what you'r
encountering also, correct? So I think the problem is possibly caused by
the web url based image is not correctly accessed by the report server
application.
So far I think there're two things we can check:
1. security, whether the images are protected by any authentication which
require certain security context(account) to access
2. web proxy setting , since you can visit the image in browser, is there
any proxy setting in browser which is not utilized by the report server
appliation that cause the image unaccessible?
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Steven,
As I mentioned in my last post - I had already set up the system for
unattended execution and permissions were set for Domain Admin.
That leaves me with your second suggestion which I don't understand. Can
you explain a little further what I'm supposed to be looking for? When RS
processes the image - does it use the proxy server to get the image? Is
that what you are saying?
Thanks,
David
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:sSZ3o1ARGHA.7144@.TK2MSFTNGXA03.phx.gbl...
> Thanks for your respone David,
> I've also performed some further tests on my side and did found some
> clues.
> Actually, what you have seen (I'm a little suprised at the url for the
> image source. Looks like it's coming from the reportserver instead of
> directly from the source) is the expected behavior because SQL Reporting
> service does not directly simply put the external image's address in the
> report's rendered html. Instead, the report server will first retrieve the
> image stream itself than provide a url point to the image stream from the
> report server. Therefore, this make it important that we need to make
> sure
> our report server application can correct access that image under its
> running security account. See the below note in BOL:
> ====================> External Images
> You can include stored images in a report by specifying a URL to the
> image.
> When you use an external image in a report, the image source is set to
> External and the value for the image is the URL address to the image (for
> example, http://<servername>/images/image1.jpg).
> When the report is previewed in Report Designer, preview uses the
> credentials of the user to display the image. When the report is run on
> the
> report server, the report server uses the unattended execution account to
> retrieve the image. If the unattended execution account is not specified,
> the image is retrieved using no credentials (anonymous user account). If
> either of these accounts have insufficient rights to access the image, the
> image will not be displayed in the report. For more information about the
> unattended execution account, see Configuring an Account for Unattended
> Report Processing.
> ====================> And based on my test, when I specify an invalid url ( or a url the report
> server can not access). the "src" attribute of the <img> tag will be empty
> , just like <img ... src="http://pics.10026.com/?src=" /> , and this conforms to what you'r
> encountering also, correct? So I think the problem is possibly caused by
> the web url based image is not correctly accessed by the report server
> application.
> So far I think there're two things we can check:
> 1. security, whether the images are protected by any authentication which
> require certain security context(account) to access
> 2. web proxy setting , since you can visit the image in browser, is there
> any proxy setting in browser which is not utilized by the report server
> appliation that cause the image unaccessible?
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>|||Hi David,
The proxy I means is the proxy your webclient (like browser) need to visist
external internet resources. For example, when you use IE to visit some
certain resources, maybe you're configured to use some proxy server in IE's
connection setting(or you've used any proxy client which automatically
configure this for you). However, for report server application ,it may not
utilize the same setting as IE, thus, there may occur some issue when
report server application try visit some external resources. So I'm not
sure whether your network environment is something like this.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I just came back from the installation and there are no proxy servers set
up.
Do you have any other suggestions? I really do not have any idea what
could be wrong here.
Thanks - David
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:ypy%23J4oRGHA.6492@.TK2MSFTNGXA03.phx.gbl...
> Hi David,
> The proxy I means is the proxy your webclient (like browser) need to
> visist
> external internet resources. For example, when you use IE to visit some
> certain resources, maybe you're configured to use some proxy server in
> IE's
> connection setting(or you've used any proxy client which automatically
> configure this for you). However, for report server application ,it may
> not
> utilize the same setting as IE, thus, there may occur some issue when
> report server application try visit some external resources. So I'm not
> sure whether your network environment is something like this.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Just spent 4.5 hours on the phone and an open ticket with Microsoft and
determined that there is a bug in IIS that doesn't pass permissions on to
local virtual directories.
Finally - the work around for this is to put a file path in where the url is
supposed to go for the image. This worked great - bypassed problems iis is
having and seems to run a little faster too.
Here's a "for instance" sent by the tech at MS. Put whatever path to your
image.
file:///c:/WINNT/Zapotec.bmp
David
<drchips@.nospam.nospam> wrote in message
news:%23lj9EowQGHA.1328@.TK2MSFTNGP10.phx.gbl...
> I'm having problems getting external images to show up in a report. As
> suggested in other posts in this group - the resource is linked to a
> fully qualified server name.
> The reporting services is locked down to authenticated users. The image
> url is to the same server and I've tried setting access to both anonymous
> access and authenticated users. I don't believe it is a permissions
> problem because I can paste the url in a browser and view the image with
> no problem.
> When I right click on the red x (where the image should be) and go to
> properties and look at the url - it only shows the server name and not the
> rest of the url (folder and file name.) When I review the data that
> provides the url for the external image - the correct url is in there. I
> copy and paste to the browser - the image comes up fine.
> This really has me stumped - because everything seems to be right. I have
> this running in three sites with no problem and don't see what I'm doing
> different here. The only difference is that this is running in a tightly
> locked down hospital setting. Is there some browser setting that can be
> set in a policy that would restrict a web page from pulling info from
> multiple sources or something?
> Thanks,
> David
>|||Thanks for your followup David,
So the problem still be caused by the IIS side, really a bit surprising.
Anyway, glad that you've got it working now.
If there's anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Extended Stored Procedures -> loading linked files

Hello everybody

I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension)... clear? :)

Anyway.

All the required files are placed in the BINN dir of the server.

The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.

How can I load this file into SQL Server's heap?

Yours
MikeYou need to checkout the Win32 level LoadLibrary() call to explicitly load the DLL. You will need to explicitly FreeLibrary the DLL before you exit from the xp call.

Expressions not evaluating values?

Hi,

I use an expression in a column text box to dynamically compute the column title.

The problem must have something linked to the way expressions generally works. I do not understand it clearly.

In this example, I use a SWITCH function to test the numerical value of a 1 row 1 column dataset.

The problem is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

How can I get this test to work if the value tested is greater than the value in the dataset?

Thanks

Philippe

Bellow is the code.

-

=switch(

First(Fields!HeaderCount.Value, "HeadersCount") < 2

, nothing

, First(Fields!HeaderCount.Value, "HeadersCount") = 2

, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",2,")-3)

, First(Fields!HeaderCount.Value, "HeadersCount") > 2

, Parameters!Headers.Value.Substring(

Parameters!Headers.Value.IndexOf(",2,")+3

, Parameters!Headers.Value.IndexOf(",3,")-Parameters!Headers.Value.IndexOf(",2,")-3

)

)

Philippe wrote:

is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

Note, If the dataset contains 2, the expression will return an error because I try to test >2 in the last case.
if the dataset contains 3 or greater, it works fine.

it is clearly the test which fails because if I replace the action by a fixed string it still return an error.

|||

You are using the switch() function. Since it is a function, all arguments are evaluated before the switch functionality is executed.

I recommend to write a custom code function that uses the VB switch statement and call the custom code function from the expression.

-- Robert

|||

Hi,

I have made some research on the Custom Code however I could not find documentation nor examples that show how to use parameters or dataset values within the custom code. If I create a function like this How can I access the report items?

Public Function Headers(ByVal Column as Integer) As String
Return CStr(Microsoft.VisualBasic.Switch( _
First(Fields!HeaderCount.Value, "HeadersCount") < Column _
, nothing _
, First(Fields!HeaderCount.Value, "HeadersCount") = Column _
, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",Column,")-3) _
, First(Fields!HeaderCount.Value, "HeadersCount") > Column _
, Parameters!Headers.Value.Substring( _
Parameters!Headers.Value.IndexOf(",Column,")+3 _
, Parameters!Headers.Value.IndexOf(",Column + 1,")-Parameters!Headers.Value.IndexOf(",Column,")-3 _
) _
) _
)
End Function

I did find a much simpler solution though.

The string I use contains a variable number of names separated by indexes, i.e.

,1,Charles,2,Tom,3,Laura,4,Rick

I have a report with a fixed number of columns, i.e 50 columns and I populate the columns title by pulling from the string, i.e. Column 3 title will be Laura.

I have converted the string so each name will have trailing spaces. Since now each item has the same lenght, I do not need anymore the switch, I can do simply this in each column with just another index number. Then I use an expression to control the visibility of the column.

Title

=Parameters!Headers.Value.Substring(Parameters!Headers.Value.IndexOf(",3,")+3, 85)

Visibility

=iif(First(Fields!HeaderCount.Value, "HeadersCount")<3,True,False)

If I spend the time to build this instead of using the Matrix report, it is because the Matrix report has 2 majors issues for me.

1) You cannot have column titles for your categories

2) You cannot have the categories values repeated

Because of the strict format requirement I have, I am obliged to use the PIVOT SQL operator and the dynamic column population. My users want a pivoted flat file they can put in Excel and build a pivot table with it. You cannot do that with a matrix report, too bad. That would be so much easy.

Philippe

expressions linked to parameters

Is it possible to link expressions to report parameters
for instance i have WTD, MTD and YTD sales targets in my DB and have
WTD, MTD and YTD as report Parameters can i do this?
If i select MTD link the Expression to say if MTD then Show MTD sales
target etc etcOn Jun 8, 10:23 am, blueboy <matt_me...@.hotmail.com> wrote:
> Is it possible to link expressions to report parameters
> for instance i have WTD, MTD and YTD sales targets in my DB and have
> WTD, MTD and YTD as report Parameters can i do this?
> If i select MTD link the Expression to say if MTD then Show MTD sales
> target etc etc
If I'm understanding you correctly, you will want to access the values
of a parameter as opposed to if a parameter value has been selected or
not. Something like this should work:
=iif(Parameters!SalesTargetParam.Value = "MTD", Fields!
MTDSalesTarget.Value, Nothing)
Of course, this should also work:
=switch(Parameters!SalesTargetParam.Value = "WTD", Fields!
WTDSalesTarget.Value, Parameters!SalesTargetParam.Value = "MTD",
Fields!MTDSalesTarget.Value, Parameters!SalesTargetParam.Value ="YTD", Fields!YTDSalesTarget.Value, true, nothing)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

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
>