none
Using A Scalar Valued Function RRS feed

  • Question

  • Hi,

    I created a simple regex scalar-valued function as explained here, and it seems that as part of its deployment, a new database by the name "DbRegEx" was created under a new SQL Server instance by the name "(localdb)\Projects" (see below image). Under  "DbRegEx" (Programmability > Functions > Scalar-Valued Functions) there's indeed a function by the name "dbo.RegExMatch", which is the function I tried to add. If I right-click it and choose "Execute Function...", it performs exactly as I want.

    But the issue is I want to use it in order to check the values of some column of a table I try to create in database "AdventureWorksDW2012", which appears under another SQL Server instance by the name  "OIE-PC" (see above image). So I connect this database using the following conection string:

    connection_str = """
    Driver={SQL Server Native Client 11.0};
    Server=OIE-PC;
    Database=AdventureWorksDW2012;
    Trusted_Connection=yes;
    """

    But if I try to do it using a command like:

    CREATE TABLE Person
    (
     Id char(9) PRIMARY KEY CHECK (dbo.RegExMatch(Id, '^[0-9]{9}$') = 1),
    LastName VARCHAR(20) NOT NULL,
    FirstName VARCHAR(20) NOT NULL,
    City VARCHAR(20),
    BirthDate DATE CHECK (BirthDate > '1900-01-01'),
    HeightMeters DECIMAL DEFAULT 1.70 NOT NULL CHECK (HeightMeters BETWEEN 1.00 AND 2.50)
    )

    then it fails to understand  "CHECK (dbo.RegExMatch(Id, '^[0-9]{9}$') = 1)", and the following exception occurs:

    ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.RegExMatch", or the name is ambiguous. (4121) (SQLExecDirectW)'

    (It performs just fine if I don't try to operate "dbo.RegExMatch".)

    Can someone explain to me how to solve this?

    Thanks in advance!


    Ofer




    • Edited by Ofer Elboher Wednesday, August 28, 2013 11:22 AM
    Tuesday, August 27, 2013 7:03 PM

Answers

  • Hello,

    Welcome toMSDN Support Forum.

    From your description, your problem may be that how to call a function between two sql services.

    If I have misunderstood, please let me know.

    I notice that the function FuncRegExMatch() is in the sql server (localdb)\Projects but the sql statement will be executed in sql server OIE-PC.

    As far as I know if you want to do an operation between two services you need to build a link first like below:

    EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'
    EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

    After that you can query or call function in anther service.

    And you should call you function like:

    SQLSERVER2. DbRegEx.dbo.FuncRegExMatch(Id, '^[0-9]{9}$') = 1

    More information about how to create a linked server:

    http://msdn.microsoft.com/en-us/library/aa560998.aspx

    If this does not work for you, please let me know.

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Ofer Elboher Wednesday, August 28, 2013 1:23 PM
    Wednesday, August 28, 2013 8:45 AM
    Moderator
  • Okay, okay, after hours of trial and error I eventually succeeded solving it with the help of the great SQL Server Data Tools (that combines with Visual 2012 to a real powerful tool!).

     It seems that SQL Server linked the two databases (as the SQL Server Object Explorer, when displayed in Visual-2012, shows for database "AdventureWorksDW2012" under Security -> Linked Server Logins) though above quoted error message (which it continues to show to this moment if I test the connection).

    Once that's solved, I open a connection with database "AdventureWorksDW2012", and perform the following SQL commands:

    CREATE ASSEMBLY DbRegExAsm
      FROM 'C:\\Path\\DbRegEx.dll'

    CREATE FUNCTION [dbo].[RegExMatchFunc]
    (@strValue NVARCHAR (4000), @strRegEx NVARCHAR (4000))
    RETURNS BIT
    AS
    EXTERNAL NAME [DbRegExAsm].[SqlRegExFunctions].[RegExMatch]

    CREATE TABLE Person
    (
    Id char(9) PRIMARY KEY CHECK (dbo.RegExMatchFunc(Id, '^[0-9]{9}$') = 1),
      ...

    )

    Thanks again, Fred, for your kind help!


    Ofer

    • Marked as answer by Ofer Elboher Wednesday, August 28, 2013 1:24 PM
    Wednesday, August 28, 2013 1:23 PM

All replies

  • Hello,

    Welcome toMSDN Support Forum.

    From your description, your problem may be that how to call a function between two sql services.

    If I have misunderstood, please let me know.

    I notice that the function FuncRegExMatch() is in the sql server (localdb)\Projects but the sql statement will be executed in sql server OIE-PC.

    As far as I know if you want to do an operation between two services you need to build a link first like below:

    EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'
    EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

    After that you can query or call function in anther service.

    And you should call you function like:

    SQLSERVER2. DbRegEx.dbo.FuncRegExMatch(Id, '^[0-9]{9}$') = 1

    More information about how to create a linked server:

    http://msdn.microsoft.com/en-us/library/aa560998.aspx

    If this does not work for you, please let me know.

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Ofer Elboher Wednesday, August 28, 2013 1:23 PM
    Wednesday, August 28, 2013 8:45 AM
    Moderator
  • Hi Fred,

    Thanks for bothering.

    I tried your suggestions, but failed, as follows:

    1) I'm using Visual-2012 to develop a Python project that uses module pyodbc in order to access my database. If I open a connection to database "AdventureWorksDW2012" and then execute the SQL command "EXEC sp_addlinkedserver '(localdb)\Projects', N'SQL Server'", it returns the error message "('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction. (15002) (SQLExecDirectW)")".

    2) If I use "CHECK ((localdb)\Projects.DbRegEx.dbo.FuncRegExMatch (Id, '^[0-9]{9}$') = 1)", it complains on a syntax error. If I use "CHECK ([(localdb)\Projects].[DbRegEx].[dbo].[FuncRegExMatch] (Id, '^[0-9]{9}$') = 1)", it says "Remote function reference '(localdb)\Projects.DbRegEx.dbo.FuncRegExMatch' is not allowed because "the column name '(localdb)\Projects' could not be found or is ambiguous". With "[DbRegEx].[SqlRegExFunctions].[RegExMatch]" it says "Invalid column name 'DbRegEx'".

    3) I tried to follow with my SQL Server the description in http://msdn.microsoft.com/en-us/library/aa560998.aspx, but it fails (see the long message below). That's although the "Server Properties" dialog box for "OIE-PC" (Connections -> Remote Server Connections) show that "Allow remote connections for this server" is checked.

    Something that I couldn't understand in your reply is whether I have to follow both your suggestions (and if so, then in what order), or just select one of the two.

    So I'm absolutely frustrated. I didn't expect this to be so problematic, even not for a standalone novice like me...

    Thanks for helping!

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    The test connection to the linked server failed.

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    SQL Server Network Interfaces: Unexpected error occurred inside a LocalDB instance API method call. See the Windows Application event log for error details.
    [x89C50108].
    OLE DB provider "SQLNCLI11" for linked server "(LOCALDB)\PROJECTS" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI11" for linked server "(LOCALDB)\PROJECTS" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: -1983577848)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=-1983577848&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Ofer



    • Edited by Ofer Elboher Wednesday, August 28, 2013 11:36 AM
    Wednesday, August 28, 2013 11:21 AM
  • Okay, okay, after hours of trial and error I eventually succeeded solving it with the help of the great SQL Server Data Tools (that combines with Visual 2012 to a real powerful tool!).

     It seems that SQL Server linked the two databases (as the SQL Server Object Explorer, when displayed in Visual-2012, shows for database "AdventureWorksDW2012" under Security -> Linked Server Logins) though above quoted error message (which it continues to show to this moment if I test the connection).

    Once that's solved, I open a connection with database "AdventureWorksDW2012", and perform the following SQL commands:

    CREATE ASSEMBLY DbRegExAsm
      FROM 'C:\\Path\\DbRegEx.dll'

    CREATE FUNCTION [dbo].[RegExMatchFunc]
    (@strValue NVARCHAR (4000), @strRegEx NVARCHAR (4000))
    RETURNS BIT
    AS
    EXTERNAL NAME [DbRegExAsm].[SqlRegExFunctions].[RegExMatch]

    CREATE TABLE Person
    (
    Id char(9) PRIMARY KEY CHECK (dbo.RegExMatchFunc(Id, '^[0-9]{9}$') = 1),
      ...

    )

    Thanks again, Fred, for your kind help!


    Ofer

    • Marked as answer by Ofer Elboher Wednesday, August 28, 2013 1:24 PM
    Wednesday, August 28, 2013 1:23 PM