Potential name conflict problems when multiple items in different DB Namespaces have the same name

OdatNurd

I'm offering this up as a potential bug/issue with the caveat that I am by no means a DataBase (or LINQPad) expert and as such this behaviour may well be to be expected or desired. This is in reference to my comment in this thread regarding tables that seem to be missing from the database.

Based on some out-of-band comments from kingkeith I uncovered what appears to be the reason why the table I mentioned was missing.

To recap, I have a SQL Server 2008 R2 database deployed on three distinct servers, and in all cases there is at least one table (named Price) that doesn't appear in the table list for the connection. I tested across both LINQPad 4 as well as the latest release and beta builds of LINQPad 5.

The table itself doesn't appear special in any way as far as the schema goes, and despite the fact that it doesn't appear in the list of tables in the connection (even after a refresh), manually querying from it via SQL works just fine, although something like Price.Take(10) in a C# expression query generates an error that the name doesn't exist in the current context.

Hints from keith about whether or not the table appears in the Intellisense popup eventually led to what appears to be an explanation for this issue.

The servers in question have SQLFinancials installed, which is an external assembly that provides some functions to the database. The extension itself has a function in it named Price.

As far as the Database itself is concerned, the function is only invocable as Excel.Price(), and in the tree view in LINQPad it appears under Excel > Functions > Price in the tree.

As mentioned above, Price.Take(10) doesn't work because it doesn't know what that is, and PRICE.Take(10) (with different case) generates a similar error, only the full symbol name is the signature of the aforementioned function.

It seems as though since the function is distinctly located in an appropriate part of the tree view and also appears to have a different case as far as the C# mode is concerned that LINQPad is discarding it as a table because there's another item with the same name or something along those lines.

I'm not sure if that's an actual bug or something that needs to be done when symbols of the same name appear in different contexts, though.

JoeAlbahari

Are you able to post some simple DDL to recreate the problem? (I know this might be tricky, without relying on external modules.)

OdatNurd

I'm not sure if this counts as simple, but here is an example that's as minimal as possible that replicates what I'm seeing.

The first bit is this simple C# code that implements the function that will be referenced shortly.

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace CLRFunctions
{
    public static class LP_Test
    {
        [SqlFunction (DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlDouble PRICE ()
        {
            return 42.0;
        }
    }
}

With the exception of changing the target version of .NET to 2.0 so that SQL Server wouldn't get mad about it, I didn't customize anything about the build at all.

The Database side of things is demonstrated by this:

USE Master
GO

CREATE DATABASE TestDB ON PRIMARY 
( 
  NAME = 'TestDB', 
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf'
)
GO

USE TestDB
GO

CREATE TABLE dbo.Price(
    Security_Id CHAR(25) NOT NULL,
    CONSTRAINT PK_Price PRIMARY KEY CLUSTERED (Security_Id ASC)
    )
GO

CREATE ASSEMBLY CLRDemo
AUTHORIZATION dbo
FROM 'C:\Users\tmartin\Desktop\CLRDemo.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE SCHEMA ExtNS
GO

CREATE FUNCTION ExtNS.PRICE()
RETURNS FLOAT WITH EXECUTE AS CALLER
AS 
    EXTERNAL NAME CLRDemo.[CLRFunctions.LP_Test].PRICE 
GO

This executes without errors, and the tree view in LINQPad appears as the following (even after a refresh):



From an SQL Query, SELECT'ing from the table works, as does calling EXTNS.Price(), but in C# only the function version appears in the intellisense popup and as far as the tree is concerned only the function exists.