SQL Server Performance Forum – Threads Archive

Urgent: DTS incosistent error

Hi All, I running SQL severe 2000 under windows 2003. I am trying to import data from ODBC source using the wizard from outside Enterprise manager (i.e. from under the "Import and Export Data" menu item of SQL Server programs group) – everything is OK: I am able to connect to the source and import the data. Now, when I try to do that from inside Enterprise manager I get 2 different error messages depends from where I am trying to do so: 1. from the DTS wizard (in the tollbar) I can’t connect to the ODBC source and I get a message: "unable to log on to the severe with the specified user id and password etc.) and I can’t go next to the "Destination" part. 2. from a DTS package designer, I try to open a new connection object to the ODBC source and when I click OK I get worse message: HRESULTS (ox800…) unexpected error occurred …" and I can’t do anything. The security context under which Enterprise Manager is running is the local Administrator one. The SQL Server runs under the same account (not local system account) and I working on the local server – The are no clients involved – every thing is local. The source file of the ODBC resides under local drive (E<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> and the permissions there are full control for Everyone. Firstly, I don’t understand why there is a difference in the security context between working outside EM and from inside, while every thing happening is under the Administrator rights. Secondly, How can I find out what is the security context I am working with, whatever from inside or outside EM. Finally, If it’s not the security context issue, what could it be ?? last comment: the ODBC source is a MYOB file. I would appreciate any help. Aviel Iluz Database Administrator Pacific Micromarketing Melbourne Australia www.pacmicro.com.au

What is the level of service pack on SQL Server?
And also the MDAC version?
What is the authentication mode used on SQL?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

There is a good chance that one method is using your credentials to connect and the other is using the sql server service account and/or odbc datasources setup on the server. One of those likely doesnt have the necesasry permissions while the other way does. Chris

Satya, the versions as follows: 1. Win 2003 Standard Edition (apears also in the general tab of SQL Server properties as Windows NT 5.2(3790) )
2. SQL Server SP3 8.00.760
3. MDAC 2.80.1022.0 Chris, this exactly what I don’t understand: why are the credentials differnt ? every thing is under the Administrator account and every thing is done localy on the actual server. Any help, please !! Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Right, I think there could be a chance of mismatch of system DLLs installed on the server/machine where EM is failed with the error, as you clear out the login used is a local administrator with required privileges. Have you tried to use the DTS package from other machine and schedule it to run. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks, Satya. No I have not tried to use client machine. I will try it, but I want to be able to work localy.
I have not seen any thing about that in Microsoft KB. Do you know any thing about where to find a solution for this problem ? Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

The second error cleary represents some sort of mismatch of files in client tools, try to install client tools on this machine and workout. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, I can’t work on client machine because the ODBC source configuration must be on the server and the only way to access it and for DTS to speak with it, is on the server. Also, I have "cleaned" the machine from the ODBC and SQL installations, and reinstalled them again under local administrator with no networking or any other domain privileges involved. SQL server + tools were installed and then SP3a (under win 2k3), but again the same problem: External DTS wizard – works. Internal (inside EM) DTS wizard or Package Designer: fails to connect to ODBC (Failure error in the wizard, HRESULT unspecified error in the designer). I don’t know what is the problem. I am trying to use DLL utility that shows which DLL exactly are loaded in each scenario. I hope it will have some answers for me.
If you have any other recommendation, please advice. Thanks Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Well, I would go as a last resort :
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;300635
INFO: How to Obtain the Latest MDAC 2.6 Service Pack
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>