Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Add services to SSIS IServiceProvider model?

Is there any mechanism for being able to add custom services into the SSIS service provider model so that they can be used by components at design and runtime? i.e. if I can currently access service implementations such as the IDtsConnectionService from the Initialize method of a custom component (or in a UI type editor), is there a way to add a service of my own?

Thanks

Interesting - we've never thought about this. What kind of services do you plan to add there? Note that the services are for design time only, they are no available at runtime.|||

Thankyou for the response - actually design-time only services would still be useful in this case - I'm writing custom components against a JD Edwards Oracle database, but I'm having to interpret the data dictionary schema for business users to assist them in writing their queries (it has to be available offline).

I currently have metadata available to the components so that they can present meaningful field information to users before it is mapped onto the underlying schema, but it would be extremely useful if my components could utilise a service that wrapped this metadata functionality for them.

Ad hoc access to OLE DB provider MSOLAP has been denied

Hi:

When I am trying to connect to OLAP I am getting this error...Ad hoc access to OLE DB provider MSOLAP has been denied.You must access this provider through a linked server.

I have configured the linked server.How do i fix this...?

Thanks,

Pramod

This is not an AS specific issue and out of my area of expertise, but it appears to be a security issue. Doing a quick search on the error message it appears by default that only SQL system admins are allowed to use OpenRowset/OpenDataSource queries. The information at http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.oledbprovidersettings.disallowadhocaccess.aspx may be usefull in resolving this.

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.4.0' has been denied

Trying to open a SQL Server query but am getting this message:

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

Heres the script:

Insert into OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[PivotSh$]
(Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month, Qtr,
Description_Contract, Activity_Type_Grouped, Sum_of_Spells)

SelectProvider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End as Qtr,
Description_Contract,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
SUM(Total_Spells) as Sum_of_Spells

From[Busobjects].Capacity_Planning.dbo.tbl_BaseLine_UHL_Spells_Tre nd_0708 a

Inner Join OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[SPECIALTY TREND by Month$] as PR
On PR.P1 = Commissioner_Code
AND PR.P2 = LSS_Flag
AND PR.P3 = Description_Contract
AND PR.P4 = (Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type END)

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_HRG_Tariff_0708 d
on a.HRG_Code = d.HRGCode

Groupby Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End,
/*Specialty_Code,*/ Description_Contract, --Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
--Specialised_Services_Code + ': '+c.Description

Quote:

Originally Posted by flickimp

Trying to open a SQL Server query but am getting this message:

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

Heres the script:

Insert into OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[PivotSh$]
(Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month, Qtr,
Description_Contract, Activity_Type_Grouped, Sum_of_Spells)

SelectProvider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End as Qtr,
Description_Contract,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
SUM(Total_Spells) as Sum_of_Spells

From[Busobjects].Capacity_Planning.dbo.tbl_BaseLine_UHL_Spells_Tre nd_0708 a

Inner Join OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[SPECIALTY TREND by Month$] as PR
On PR.P1 = Commissioner_Code
AND PR.P2 = LSS_Flag
AND PR.P3 = Description_Contract
AND PR.P4 = (Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type END)

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_HRG_Tariff_0708 d
on a.HRG_Code = d.HRGCode

Groupby Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End,
/*Specialty_Code,*/ Description_Contract, --Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
--Specialised_Services_Code + ': '+c.Description

Try the following query to read the data from excel sheet

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\Administrator\Desktop\rolemenu.xls',
'SELECT * FROM [Sheet1$] where MenuItemId is not null')

make sure your excelsheet is on server...

for more information visit the following link

http://www.databasejournal.com/features/mssql/article.php/3692831

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.