Read the field note on the table record link in sql server ?

PM53PM53 Member Posts: 5
edited 2015-08-24 in NAV Three Tier
Hi,

I'm looking for read the field note on the table record link in sql server.

I have find how read the field record ID with that :
SELECT CONVERT(varchar,substring([Record ID],8, LEN([Record ID]))) as 'ID',* FROM [Record Link]

But I can't find how read the field note in sql ](*,)

Any ideas ?

Thank for your help

Answers

  • deV.chdeV.ch Member Posts: 543
    Don't know if theres an easier way:

    Make .Net Assembly that uses BinaryReader to Read the data and give it back as string. Register that assembly in SQL Server. Call the assembly.
    public class NAVNoteHandler
        {
            [SqlFunction(DataAccess = DataAccessKind.Read)]
            public static string GetNoteAsString(string NoteID)
            {
                try
                {
                    using (SqlConnection connection = new SqlConnection("context connection=true"))
                    {
                        connection.Open();
                        SqlCommand command = new SqlCommand(string.Format("select [Note] FROM [Record Link] WHERE [Link ID] = {0}", NoteID), connection);
    
                        SqlDataReader reader = command.ExecuteReader();
    
                        if (reader.Read())
                        {
                            SqlBytes bytes = reader.GetSqlBytes(0);
                            MemoryStream ms = new MemoryStream(bytes.Value);
    
                            BinaryReader BinReader = new BinaryReader(ms);
                            return BinReader.ReadString();
                        }
                    }
                    return "null";
                }
                catch (Exception ex)
                {
                    return string.Empty;
                }
            }      
        }
    

    And here is a tutorial on how to register an assembly to sql server:

    http://www.codeproject.com/KB/database/ ... n_SQL.aspx

    Basicly you need call these T-SQL statements to Get a Result:
    exec sp_configure 'clr enabled',1  
    reconfigure  
    go 	
    
    CREATE ASSEMBLY NAVNoteHandler
    AUTHORIZATION dbo
    FROM 'C:\sqldotnet\ClassLibrary1.dll'
    GO
    
    use [Demo Database NAV (6-0)]
    go
    CREATE FUNCTION usp_UseNAVNoteHandler  
    (  
     @id as nvarchar(200)  
    )   
    RETURNS nvarchar(200)  
    AS EXTERNAL NAME NAVNoteHandler.[SQLProcedures.NAVNoteHandler].GetNoteAsString
    GO    
        
        
    Select [Demo Database NAV (6-0)].[dbo].[usp_UseNAVNoteHandler]('1')  as [Text]
    

    IMPORTANT: You need to set the Property "Compressed" on the Note field in Table "Record Link" to false, otherwise you will get compressed output => nonsense
  • PM53PM53 Member Posts: 5
    When I try to execute this code :
    use [NAVPROD311211]
    go
    CREATE FUNCTION usp_UseNAVNoteHandler 
    ( 
    @id as nvarchar(200) 
    )   
    RETURNS nvarchar(200) 
    AS EXTERNAL NAME NAVNoteHandler.[SQLProcedures.NAVNoteHandler].GetNoteAsString
    GO  
    
    I've this message :
    Msg 6505,
    The type 'SQLProcedures.NAVNoteHandler' is not found in the assembly 'ClassLibrary1'.

    Nevertheless I see my assembly in my database -> Assembly -> NAVNoteHandler

    My NavNoteHandler.cs :
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Server;
    using System.Data.SqlClient;
    using System.IO;
    using System.Data.SqlTypes;
    
    namespace ClassLibrary1
    {
        public class NAVNoteHandler
        {
            [SqlFunction(DataAccess = DataAccessKind.Read)]
            public static string GetNoteAsString(string NoteID)
            {
                try
                {
                    using (SqlConnection connection = new SqlConnection("context connection=true"))
                    {
                        connection.Open();
                        SqlCommand command = new SqlCommand(string.Format("select [Note] FROM [Record Link] WHERE [Link ID] = {0}", NoteID), connection);
    
                        SqlDataReader reader = command.ExecuteReader();
    
                        if (reader.Read())
                        {
                            SqlBytes bytes = reader.GetSqlBytes(0);
                            MemoryStream ms = new MemoryStream(bytes.Value);
    
                            BinaryReader BinReader = new BinaryReader(ms);
                            return BinReader.ReadString();
                        }
                    }
                    return "null";
                }
                catch (Exception ex)
                {
                    return string.Empty;
                }
            }
        }
    }
    

    Where is the error ? :(

    Thanks for you help

    I'm sorry for my English, I'm French...
  • deV.chdeV.ch Member Posts: 543
    Since your Namespace is "Classlibrary1" you need to call it like this:
    use [NAVPROD311211]
    go
    CREATE FUNCTION usp_UseNAVNoteHandler
    (
    @id as nvarchar(200)
    )
    RETURNS nvarchar(200)
    AS EXTERNAL NAME NAVNoteHandler.[ClassLibrary1.NAVNoteHandler].GetNoteAsString
    GO
  • PM53PM53 Member Posts: 5
    Ok, it works but I can't to find the property "Compressed" on the "Note" field...

    Where are you going for to find this property ?

    I use SQL Server 2008 x64 / Nav 2009 SP1 RTC

    Screenshot

    Thanks for your help
  • deV.chdeV.ch Member Posts: 543
    oh, maybe i wasn't clear enough about that, the property is a NAV property you find it in the field property of the Note field.
  • PM53PM53 Member Posts: 5
    Ok, nice it works !!!

    Thank you very much. =D> =D>

    Good day
  • wicwic Member Posts: 96
    Hi,
    after setting the field property "compressed" to NO, some client (RTC) for some users only crashed. Any idea?

    thanx a lot
    regards
    CHris
    #### Only one can survive ######
  • deV.chdeV.ch Member Posts: 543
    Did you check the eventlog? Serverside and Clientside?
    Please post these logs

    And since it's only for some clients, Are you using the same builds on all clients? Which build are you using?
  • analyzethatanalyzethat Member Posts: 1
    I tried to follow the instructions and I created the assembly and the SQL-function.
    However when I run the function it only shows 'D' as a result for every record from the [Record Link] table.
    What am I doing wrong?
  • mc55mc55 Member Posts: 1
    Hi, I have copied and checked your script. It runs but all notes return "D". What have I done wrong? How can I fix? Thanks for your help.
  • 3N1GM43N1GM4 Member Posts: 1
    edited 2022-07-15
    I followed this and was able to get it to work, however after I did the step to set the Note field as Uncompressed in NAV, I was then able to simply return the note content by casting the image type Note field to varbinary and then varchar:
    select cast(cast(Note as varbinary(max)) as varchar(max)) from [Record Link]
    
    So people may find that easier or more convenient if they're trying to return Note content in SQL.
Sign In or Register to comment.