Solved

MSOLEDB Provider for SQL Server: Cannot find data type <whatever>

Posted on 2008-10-30
7
309 Views
Last Modified: 2013-11-05
Recently had to perform an emergency transfer of a SQL Server Database from one server to another - managed to create a virtual drive for the Database files to "live" on, and mounted them to the new server.  Made the server "aware" of the new database, and everything seemed to be rolling fine.

Now, for some reason, it cannot find/access User-Defined Data Types, and there they are, plain as day, under the Database->Programmability->Types->User-defined Data Types.  Is there some sort of process I'v left out to tell the SQL Server that it needs these data types?

Oh yeah - I almost forgot: we're getting this error from an Access Application that directly "talks" to SQL Server.  (Don't laugh too loud - it's supposed to be our Accounting Software.)  If this makes any difference, which I don't believe that it does.

I'm not sure where to go from here...   ?
0
Comment
Question by:LongFist
  • 4
  • 3
7 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 22849035
< transfer of a SQL Server Database from one server to another>
did you install at least sp1 on on new server (check MDAC version too)
0
 
LVL 1

Author Comment

by:LongFist
ID: 22849568
Apparently not.  I ran the following SQL command on the server:

<code>
WITH Version(ver)
AS
(
    SELECT SUBSTRING
    (
        @@VERSION,
        CHARINDEX(' - ', @@VERSION)+3,
        32
    )
)
SELECT Build = LEFT(ver, CHARINDEX(' ', ver))
    FROM Version
</code>

Results: 1 row(s) affected
9.00.1399.06

...meaning I'm running the RTM, no apparent patches or service packs installed.

So, now it's off to find the latest service pack for SQL Server 2005 Standard, right?  Right.

Microsoft Data Access Components (MDAC)  2000.086.3959.00 (srv03_sp2_rtm.070216-1710) --- but wouldn't that be more concerned with data connections than SQL Server's handling of UDTs?  (Just asking...)
0
 
LVL 1

Author Comment

by:LongFist
ID: 22850560
Now patched to version 9.0.3042 - SP2 fully in effect.

No joy, error persists.  Same place: encounters user data type (that exists in the database definition), errors out.  Is there some "association" algorithm that should be run, or something?  Or is SQL Server just that full of "automagical" capacity?

Frustrated, very frustrated...    (...not your fault, mind you, but just noting the mindset to avoid possible misunderstandings...)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 500 total points
ID: 22854110
can you run the Access from the sql server?
what statement was used against sql server? Can you run it on sql server directly?
are you sure it is pointed (linked) to new sql server?

BTW:
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
0
 
LVL 1

Author Comment

by:LongFist
ID: 22867894
Okay - this is extremely embarassing!  It turns out that the problem wasn't in SQL Server at all - no, it was in how the application was handling things.

It turns out that there was a third Database - SYS - that contained (among other things) references to where those UDT's were kept: more lousy development on their part, as this is something SQL Server will do auto-magically, if you let it.  But I guess it's to be expected: they used MSAccess 2K3 as their application "front end", and that's caused no end of troubles when setting it up on machines that have Office 2K7 or the like.  SYS needed backing up, deleting, re-building, and then restoring (overwrite?) from the backup to make it all work right.  And no, it's nothing that we would have thought of in a thousand years...

So, while were doing everything that we knew to do, nobody thought to see if the application platform was short-circuiting the existing system.

I'm truly sorry I bothered everybody, since it was third-party error in the first place.  How do we handle this sort of situation?
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 500 total points
ID: 22870888
no problem - do not be sorry -
Most important it is resolved :)
good job!
0
 
LVL 1

Author Closing Comment

by:LongFist
ID: 31511873
The problem actually resided in the third-party application, not SQL Server itself.  However, without the expert's guidance and creativity, the problem might not have been noticed.  Therefore...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question