Add Reference to Excel in Scripting tool in SSIS 2005

How can I add a reference to the Microsoft excel 2003 in the script tool in SSIS 2005? I need to automate Excel for formatting.

Thank you.

Dan

Adding refereneces to external assembleis is documented in Books Online -

Referencing Other Assemblies in Scripting Solutions

http://technet.microsoft.com/en-us/library/ms136007.aspx

For Office applications like Excel that are COM, you will need to download the PIAs if you have not already.You will need to reference the PIA.

Download - http://www.microsoft.com/downloads/details.aspx?FamilyID=3C9A983A-AC14-4125-8BA0-D36D67E0F4AD&displaylang=en

General page of on PIAs if you are not familar already - http://msdn2.microsoft.com/en-us/library/aax7sdch.aspx

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?
Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen
|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>
|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:
> Mikus wrote:
> > How do i add linked server whoose source file is not located on server itself?
> >
> > I want to create linked server which uses excel file as it's source, but
> > this file is located on my pc(named "mix")
> >
> > I want to do something like this...
> >
> > EXEC sp_addlinkedserver 'MikusExcelSource',
> > 'Jet 4.0',
> > 'Microsoft.Jet.OLEDB.4.0',
> > '\\mix\silo\2005 9 Sep_new.xls',
> > NULL,
> > 'Excel 5.0'
> > GO
> >
> > any ideas ?
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>> Mikus wrote:
>> How do i add linked server whoose source file is not located on server itself?
>> I want to create linked server which uses excel file as it's source, but
>> this file is located on my pc(named "mix")
>> I want to do something like this...
>> EXEC sp_addlinkedserver 'MikusExcelSource',
>> 'Jet 4.0',
>> 'Microsoft.Jet.OLEDB.4.0',
>> '\\mix\silo\2005 9 Sep_new.xls',
>> NULL,
>> 'Excel 5.0'
>> GO
>> any ideas ?
>> Have you tried to add the linked server with the syntax you suggest?
>> There're no problems as such to add a linked server using an UNC path.
>> The only limitation that I've seen, is that if the files for the linked
>> server isn't placed on the server it self, you'll have to use this
>> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
>> add a linked server on Server A having the files placed on Server B,
>> I'll have to connect to Server A with Remote Desktop and open up QA and
>> run my query. If I open QA on my own desktop and connect to server A,
>> I'll get an error if I try to use the linked server.
>> There might be ways around this issue, but I've never been able to find
>> a way to get around it.
>> Regards
>> Steen
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itsel
f?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server its
elf?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the table
s
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server at Excel VBA

I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

Hi there,

Provider string should look something like the following:

@.provstr=N'DRIVER={<driver name of your linked server, in this case Excel} ;SERVER=<address if your server where the driver resides>;DATABASE=<database name>; USER=<username>; PASSWORD=<password>; OPTION=3'

I found this here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304918&SiteID=1

Also, this may help: http://www.databasejournal.com/features/mssql/article.php/10894_3085211_2

Since you're not executing within the QA of management studio, I'm guessing that you only need to put Excel's driver name in your provider string, but don't take my word as I haven't tried doing this in VB.

Hope this helps!

Isa

Add Interactivity in exported Excel:Web report

Hi,

Specs:

I am using SSAS 2005, Excel 2003 (Office 2003 professional with sp2)

I browse the cube in SSAS 2005 BI studio and then export it to Excel.

This excel file provides interactivity, meaning that I double-click on a expandable cell , then excel issues a mdx query and retrieves the values.

Issues:

1. When I save this excel file, it displays that it is a read-only version and still allows me to save the excel file in the following formats: excel, web page (html and mhtml), xml file. Also it displays a checkbox for "Add Interactivity".

The saved web page (html or mhtml) or xml files do not provide interractivity. the reports are not able to expand cells or issue mdx queries. they allow to write on cells, but no double click.

How can I provide this?

I would want to save them as web pages, and then publish them to the web server.

2. The excel olap report that I exported yesterday, (excel 2003 file). like I mentioned before , give interactivity. but after I checked the same report after a day.

They are displaying the following error on double clicking or clicking on a drop down:

"xml for analysis parser: The xxxxxxx session id cannot be found. Either the session does not exist or it has already expired."

and clicking on 'Ok' it displays this message:

"Errors in the oledb provider. An error occured while loading the connection dialog box for prompting."

3. Do these excel olap reports provide the actions implemented in the cube?

I have created some drillthrough actions in the cube. how can I invoke them from the exported excel olap report?

We are planning to move from proclarity due to many of its rigidness. I feel that a software tool must provide for as much as customization as possible. If customization is not built in then it must atleast provide libbraries to write custom code and add on or modify existing funtionality. something similar to sharepoint.

although sharepoint from the face front seems rigid, it provides a lot of customizations. and as a last resort, if does not provide a built in customization method for something, you can pretty much hack into it and define whatever you want.

for example, in proclarity I want to allow multi select in the dropdown? Not possible at all. If I want users to double click and perform a defined 'action' , Not possible, proclarity displays all actions of all cells in a tab! if there are 100 actions , then users must search and select!

If i want the contents of one dropdown to depend on another using mdx. Not possible in proclarity.

I have a list of inconsistencies, which might not be the general usage for everybody, but in the world of software, How much can you really generalize?

Please do help in the excel questions....

Regards

Vijay,

1&2. When you save excel file, you have to find there publish button, and not use Save button. I do not have Excel 2003 in front ofme, but in Excel 2000, from Menu you have to choose "Save As Webpage" then "Publish", select Pivot Table then you click on "Add Interactivity with :" and select "Pivot table functionality". This will give you interactivity and no issues with session

3. No actions are implemented by default in excel. Microsoft has a code on their pages that you have to use to make drilltrough work.

Vidas Matelis

Add Interactivity in exported Excel:Web report

Hi,

Specs:

I am using SSAS 2005, Excel 2003 (Office 2003 professional with sp2)

I browse the cube in SSAS 2005 BI studio and then export it to Excel.

This excel file provides interactivity, meaning that I double-click on a expandable cell , then excel issues a mdx query and retrieves the values.

Issues:

1. When I save this excel file, it displays that it is a read-only version and still allows me to save the excel file in the following formats: excel, web page (html and mhtml), xml file. Also it displays a checkbox for "Add Interactivity".

The saved web page (html or mhtml) or xml files do not provide interractivity. the reports are not able to expand cells or issue mdx queries. they allow to write on cells, but no double click.

How can I provide this?

I would want to save them as web pages, and then publish them to the web server.

2. The excel olap report that I exported yesterday, (excel 2003 file). like I mentioned before , give interactivity. but after I checked the same report after a day.

They are displaying the following error on double clicking or clicking on a drop down:

"xml for analysis parser: The xxxxxxx session id cannot be found. Either the session does not exist or it has already expired."

and clicking on 'Ok' it displays this message:

"Errors in the oledb provider. An error occured while loading the connection dialog box for prompting."

3. Do these excel olap reports provide the actions implemented in the cube?

I have created some drillthrough actions in the cube. how can I invoke them from the exported excel olap report?

We are planning to move from proclarity due to many of its rigidness. I feel that a software tool must provide for as much as customization as possible. If customization is not built in then it must atleast provide libbraries to write custom code and add on or modify existing funtionality. something similar to sharepoint.

although sharepoint from the face front seems rigid, it provides a lot of customizations. and as a last resort, if does not provide a built in customization method for something, you can pretty much hack into it and define whatever you want.

for example, in proclarity I want to allow multi select in the dropdown? Not possible at all. If I want users to double click and perform a defined 'action' , Not possible, proclarity displays all actions of all cells in a tab! if there are 100 actions , then users must search and select!

If i want the contents of one dropdown to depend on another using mdx. Not possible in proclarity.

I have a list of inconsistencies, which might not be the general usage for everybody, but in the world of software, How much can you really generalize?

Please do help in the excel questions....

Regards

Vijay,

1&2. When you save excel file, you have to find there publish button, and not use Save button. I do not have Excel 2003 in front ofme, but in Excel 2000, from Menu you have to choose "Save As Webpage" then "Publish", select Pivot Table then you click on "Add Interactivity with :" and select "Pivot table functionality". This will give you interactivity and no issues with session

3. No actions are implemented by default in excel. Microsoft has a code on their pages that you have to use to make drilltrough work.

Vidas Matelis

Add Date to Report Name and add server name too

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

ADD A COLUMN AND POPULATE IT WITH A SINGLE VALUE FROM A USER VARIABLE

Hi !

I have one Excel source with ... say 2 columns, I have an SQL Server destination with 3 columns.

I want the third column to be populated with a single value stored in a User::Variable.

Can someone help !

Jacques...

Just add a Derived Column transformation and assign your User::Variable to the derived column.

That's it.

|||

Thanks Sbastien !

Jacques

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.