active x script task error

Hi,

I am working with an active x script task and I keep getting this error.

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x01DF3DD4.

I have tried to delete all but the first couple of lines but I keep getting this error. Does anyone have any ideas on how I can fix this. This code worked fine in SQL Server 2000.

Thanks

Brian

This is a snippet of my code

Function Main()
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

set mySourceConn = CreateObject("ADODB.Connection")
set rstemp = CreateObject("ADODB.Recordset")
set newtbltemp = CreateObject("ADODB.Recordset")

Main = DTSTaskExecResult_Success
End Function

I tried your script without any modification. It works.

active x script task error

Hi,

I am working with an active x script task and I keep getting this error.

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x01DF3DD4.

I have tried to delete all but the first couple of lines but I keep getting this error. Does anyone have any ideas on how I can fix this. This code worked fine in SQL Server 2000.

Thanks

Brian

This is a snippet of my code

Function Main()
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

set mySourceConn = CreateObject("ADODB.Connection")
set rstemp = CreateObject("ADODB.Recordset")
set newtbltemp = CreateObject("ADODB.Recordset")

Main = DTSTaskExecResult_Success
End Function

I tried your script without any modification. It works.sql

Active Directory Service And IIS Problem

I have a ASP.Net Project to read active directory usernames.

Its working fine when running thru asp development server.

But while running thru IIS Server. It displays the following error

"An exception of type 'System.DirectoryServices.DirectoryServicesCOMException' occurred in System.DirectoryServices.dll but was not handled in user code
Additional information: Logon failure: unknown user name or bad password."

This is my Code :

Public Function readADSusers()
Dim enTry As DirectoryEntry = New DirectoryEntry("LDAP://spiretek.local")
Dim mySearcher As DirectorySearcher = New DirectorySearcher(enTry)
mySearcher.Filter = ("(objectClass=user)")
Dim resEnt As SearchResult
For Each resEnt In mySearcher.FindAll()
Select Case myKey
Case "samaccountname"
TextBox1.Text += myResultPropColl(myKey)(0)
End Select
Next
End Function

Please Help Me......This is not the right forum for this, but you will have to provide network credentials (either the current the process is running with or custom ones) to access the active directory.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

Active Data - binding recordset not working

I have converted some reports to read TTX files (from OLEBD), and then changed the 'runReport' page to discard data, and 'setprivate' data (see below). This works beautifully in the test enviroment, but not on our production servers (windows 2000). Its Crystal 8 on all servers.

If any one has any idea... or needs me th explain in more detail... your help would be appreciated!!!How did you run on production?
Did you create set up and installed there?|||Yes - I copied over the TTX files, the associated report files, and the modified ASP pages to the live web servers. By the way, here is the code used on the ASP pages that binf the active data to the report:

rs.Open "SELECT * from table1",cn,adOpenForwardOnly

set session("oRs")=rs

'Attach to report file, and then reassign data from recordset to it
Set Database = session("oRpt").Database
set Tables = Database.Tables
set Table1 = Tables.Item(1)

session("oRpt").DiscardSavedData
Table1.SetDataSource session("oRs")

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

executeasuser'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

execute as user 'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

AcquireConnection Error - Lookup Component

Hi,

Trying to get a particular SSIS is package working - while running the Package Installation wizard a package is reported with the following errors:

AcquireConnection method fails with error code 0xC0202009 Then Lookup fails validation and returns code 0xC020801C

The odd thing is the Lookup does not exist in the package, it did exist at one point but was deleted.

I have tried searching the XML and can't find any reference to the Lookup

Any suggestions appreciated...

Thanks,

John

Could you check if you are not somehow referencing the old package?|||

Any ideas how?

The steps I've tried are:

Delete all the packages in the server file system store

Check the local package for the Lookup (not found)

Rebuild the solution.

Double click the manifest to deploy on the server to a file store with the validate option turned on.

Validation warning above occurs.

If I open the package in notepad and search for the name of the lookup I don't find anything

|||

You may want to try a brute force here and search for all files with extension .dtsx on your machine. You could find some you do not know about (VS sometimes makes copies). If you do, see if any of them is the troublemaker.

HTH.

Accomodating Dummy Data?

Hello,

I am working with a vb6 Windows application that runs on Tablet PC's.
There are about five SQL Server 2000 databases that tie into the
application. SQL Server MSDE runs on the tablets, and currently there
is only one instance running. There are efforts underway to upgrade the
application to a .NET SmartClient.

One enhancement that needs to be made to the current application will
be to provide a 'training mode' in the application for the users. The
user will use the same application, but will work with 'dummy' data
instead of live data when in training mode.

So far as I can tell, the easiest way to accomodate this would be to
have a second instance of MSDE with 'copies' of all the databases
running, but with the 'dummy' data contained in the databases copies.
The application would have some mechanism for switching to 'training
mode', and maybe by some change of a connection string, the dummy data
would be presented in the application.

So aside from accomodating a 'training mode' feature in the
application, the application would remain the same, and the database
schema for the databases would remain the same. Only the data would
change for 'training mode'.

I'm looking for some ideas on how to approach this. This application is
used by five thousand field agents, and so we can't support this
implementation on a case by case basis. So my thought is that the
solution needs to have 'few moving parts'. If we can do this and get
away from having two instances of MSDE, that would be great. I have not
tried to set up identical databases in different directories on the
same instance (I figure that would cause a problem in a system table or
two someplace, but maybe not), but its one thing I want to investigate.

If you have any ideas on this, please share!

Thank you for your help!

CSDunnHave multiple copies of the databases and parameterize the connection
strings used by the application so that you can connect to the Live
data set or the Sample data set.

--
David Portas
SQL Server MVP
--

Accessing SWebmObjectSet Objects

I'm working in an environment where domain structure and firewall rules only allow access to a SQL Server - including OS - via a SQL Server client connection. I'm attempting to collect various pieces of information for an inventory database that are not normally accessible through standard t-sql calls.

A specific example is collecting network adapter and IP information via WMI and sp_OAxxx procedures. I've been able to interface WMI and retrieve the SWebmObjectSet collection with the information I want, but I can't seem to get to the individual objects in the collection for two reasons. First, t-sql doesn't have any sort of "for each" construct that allows me to iterate through the objects. Second, the Item() method of SWebmObjectSet requires an object path that I haven't been able to enumerate.

I'm avoiding enabling xp_cmdshell in SQL 2005 so calls such as "ipconfig /all" are not at option at this point.

How can I access the individual objects in the collection via t-sql? Is there another technology I might use? Remember - I can only access via a standard SQL client.

Here is some code I've come up with so far.

-- INITIALIZE SCRIPT

DECLARE@.wmiLocatorINT,

@.wmiServicesINT,

@.wmiObjectSetINT,

@.wmiObjectCountINT,

@.wmiObjectINT,

@.wmiNetAdapterNameNVARCHAR(200),

@.wmiNetAdapterIPNVARCHAR(200),

@.loopIdxINT,

@.oleSourceNVARCHAR(500),

@.oldDescNVARCHAR(500),

@.rcBIGINT,

@.msgNVARCHAR(400)

-- INITIALIZE WMI COM OBJECTS

EXEC @.rc = master.dbo.sp_OACreate 'WbemScripting.SWbemLocator', @.wmiLocator OUTPUT

IF @.rc <> 0 BEGIN

PRINT 'Create WMI object failed'

RETURN

END ELSE BEGIN

EXEC @.rc = master.dbo.sp_OAMethod @.wmiLocator, 'ConnectServer', @.wmiServices OUTPUT, '.'

IF @.rc <> 0 BEGIN

EXEC master.dbo.sp_OADestroy @.wmiLocator

RETURN

END

END

-- COLLECT DESIRED DATA

EXEC@.rc = master.dbo.sp_OAMethod @.wmiServices, 'InstancesOf', @.wmiObjectSet OUTPUT, 'Win32_NetworkAdapterConfiguration'

EXEC@.wmiObjectCount= master.dbo.sp_OAGetProperty @.wmiObjectSet, 'Count', @.wmiObjectCount OUTPUT

SELECT@.loopIdx= 0

WHILE @.loopIdx < @.wmiObjectCount - 1 BEGIN

EXEC@.rc= master.dbo.sp_OAMethod @.wmiObjectSet, 'Item', @.wmiObject OUTPUT, @.loopIdx

IF @.rc <> 0 BEGIN

EXEC@.rc= master.dbo.sp_OAGetErrorInfo @.wmiObjectSet, @.oleSource OUTPUT, @.oldDesc OUTPUT

END ELSE BEGIN

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'Caption', @.wmiNetAdapterName OUTPUT

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'IPAddress', @.wmiNetAdapterIP OUTPUT

END

SELECT@.loopIdx= @.loopIdx + 1

END

-- CLEANUP

EXEC master.dbo.sp_OADestroy @.wmiServices

EXEC master.dbo.sp_OADestroy @.wmiLocator

Is it fair to assume that you have completely explored and discarded the various system metadata, security, stastical and configuration functions, as well as the ODBC functions?

Would it be possible to have a Windows Scheduler task that would freqently run, using SQLCmd.exe to populate a table in the server with the desired information?

|||

If you mean the SQL Server metadata, security, statistical and configuration functions, then yes I have. But it is entirely possible I missed something which is why I posted the question.

It is possible to use the Windows Scheduler as you noted. I'm not a fan of installing DBA utilities on every server I manage if I can get away with a centralized solution. Additionally, there are many devices in scope that have firewall rules preventing console access and/or file transfer mechanisms. In short, the only mechanism I have is a standard SQL client. That is not to say that I couldn't petition the security team for relaxed access, but security is king here and it would be a battle I would likely lose.

One solution I've considered is use of the xp_cmdshell. Enabling it in SQL 2005 is possible, but I would prefer to leave it alone if I can.

Bruce.

|||

This really sounds like a task for an administrative WMI script -controlled and executed by the system administrators, retrieving the data and storing it in a central server for you to access. I would think that such an approach would molify the net administrator's security concerns. Easily done with MOM/SMS or whatever monitoring/management software is being used.

Accessing Null UDT's from TSQL

I created a UDT in C# that is working fine except when I request rows where the type is null.

If I use SELECT Film.ToString() FROM ImageData it works fine, or any property they return Null as expected

but if I use SELECT Film FROM ImageData it produces the error below

An error occurred while executing batch. Error message is: Data is Null. This method or property cannot be called on Null values.

I looked through all the examples in C# and my code matches the pattern for nulls exactly. Can anyone shead some light on what attribute I may have to add or method I should override which is not documented.

Could you please check the following topics in BOL?

http://msdn2.microsoft.com/es-es/library/ms131069.aspx

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

When you query the column directly you should get either NULL for the NULL values or the binary representation of the UDT.

Can you check your code against the examples shown in the links above? There is a separate forum for SQL Server CLR that you can follow up also.

|||

This was a bugPlease see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125552

Cheers,