Post reply

Problem with data types from linked server

  • June 18, 2003 at 7:13 pm

    #82384

    We have two SQL Servers on two computers: SQL1 and SQL2. SQL1 has SQL2 linked with a link name SQL2_LINK.

    We have Database1 in SQL1 and Database2 in SQL2. In both databases, we have the following user defined data types:

    MyUserDefTypeX -> int

    MyUserDefTypeY -> datetime

    We have a table in SQL2.Database2 called Table2 that looks like this:

    Create Table Table2

    (

    Col1 binary(16),

    Col2 nvarchar(30),

    Col3 MyUserDefTypeX,

    Col4 MyUserDefTypeY,

    Col5 MyUserDefTypeX

    )

    A view in SQL1.Database1 that does this:

    Create View ViewTable2

    As

    Select * From [SQL2_LINK].[Database2].[dbo].[Table2]

    Now when we try to discover the schema of this view in Query Analyzer or by using SQLDMO objects, we get this:

    Col1 binary(16),

    Col2 nvarchar(30),

    Col3 int, -- PROBLEM HERE, not MyUserDefTypeX

    Col4 datetime, -- PROBLEM HERE, not MyUserDefTypeY

    Col5 int -- PROBLEM HERE, not MyUserDefTypeX

    SQL Server returns only the base data type for user defined types when a view references a remote table (on a linked server), instead of returning the correct user defiend data type name. Is there any way to have SQL Server tell us the name of the user defined data type insterad of the base type?

  • June 19, 2003 at 4:10 pm

    #462296

    First let me say this. I HATE USER DATATYPES!

    OK, now that I got that off my chest. 🙂 User datatypes are a nice easy way to model databases. Especially if you are working in something like Er/Win or Embarcadero. But in my opinion they are more trouble than they are worth in the actual database.

    The problem you are seeing is that if Server 1 has the user defined datatype MyUserDefTypeX defined as int and Server 2 has it defined as Float how would SQL Server know what to use in the view if it didn't go down to the base definition? Another issue that I have seen is that if you use a user defined datatype on a server with Latin1_General_Bin as the default sort order your user defined datatype becomes case sensitive. Thus causing many more headaches.

    Gary Johnson

    DBA

    Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply