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

PM53 · 2012-01-12

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

deV.ch · 2012-01-12

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

PM53 · 2012-01-12

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.ch · 2012-01-13

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

PM53 · 2012-01-13

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.ch · 2012-01-13

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.

PM53 · 2012-01-13

Ok, nice it works !!!

Thank you very much. =D> =D>

Good day

wic · 2012-08-14

Hi,
after setting the field property "compressed" to NO, some client (RTC) for some users only crashed. Any idea?

thanx a lot
regards
CHris

deV.ch · 2012-08-14

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?

analyzethat · 2015-02-11

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?

mc55 · 2015-08-24

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.

3N1GM4 · 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.

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

Answers

Categories