select convert(nvarchar(255), 4343)

Should do the trick.

CONVERT takes the column name, not a string containing the column name; your current expression tries to convert the string A.my_NvarcharColumn to an integer instead of the column content.

SELECT convert (int, N'A.my_NvarcharColumn') FROM A;

should instead be

SELECT convert (int, A.my_NvarcharColumn) FROM A;

Simple SQLfiddle here.

Here are some options.

• CBool—Converts a value to a Boolean data type.
• CDate—Converts a value to a Date data type.
• CInt—Converts a value to an Integer data type.
• CStr—Converts a value to a String data type.
• CVar—Converts a value to a Variant data type.

Syntax

• CBool(expression)
• CByte(expression)
• CCur(expression)
• CDate(expression)
• CDbl(expression)
• CDec(expression)
• CInt(expression)
• CLng(expression)
• CSng(expression)
• CStr(expression)
• CVar(expression)

Converting to an Integer Data Type

The CInt function takes a numeric or string value and converts it to an Integer data type. The argument is required and needs to represent a value within the range of -32,678 to 32,767. If the argument contains a decimal, Access rounds to the next whole number. A value of .5 or higher is rounded up; anything lower is rounded down. Some examples of CInt functions follow:

Dim MyDouble, MyInt
MyDouble = 2345.5678    ' MyDouble is a Double.
MyInt = CInt(MyDouble)    ' MyInt contains 2346.

IN SQL, you could use CAST.

SELECT
   CAST([Column] AS INT) AS [Column Name]
FROM [Table]
WHERE ...

Or, if you wanted to SUM off the SQL query.

SUM(CAST([Column] AS INT)) AS [Column Name]

"I have checked the column and all it has in it is nulls or integers."

You might have checked the data visually, in that case you have garbage in the data which doesn't show up visually.

The trick is to find those rows, and those rows only. Below show you how you can do that. In your WHERE clause, use IS NOT NULL so you don't return the rows which are NULL and also use TRY_CAST to find those that aren't convertible to int:

DROP TABLE IF EXISTS #t

CREATE TABLE #t(c1 int identity, c2 nvarchar(10))

INSERT INTO #t(c2) VALUES ('1')
INSERT INTO #t(c2) VALUES ('123')
INSERT INTO #t(c2) VALUES (NULL)
INSERT INTO #t(c2) VALUES ('12' + CHAR(9)) --Note that CHAR(9) is TAB

--It looks like data is fine!
SELECT * FROM #t

--But conversion fails because of TAB
SELECT c1, CAST(c2 AS int)
FROM #t

--How to find the failed rows, and those only?
SELECT c1, c2
FROM #t
WHERE c2 IS NOT NULL
AND TRY_CAST(c2 AS int) IS NULL

Assuming your first table is named A with a column named MyIntColumn of type int and your second table is named B with a column named MyNVarcharColumn of type nvarchar, you could do

From A
Inner Join B On A,MyIntColumn = Try_Cast(B.MyNVarcharColumn As int) 

Tom

Use the convert function.

SELECT CONVERT(varchar(10), field_name) FROM table_name

are you sure the error is related to the casts and not to the where conditions? check that the column types are matching

You have a few problems here:

The @@IDENTITY is a system function contains the last identity value that is generated when an INSERT, SELECT INTO, or BULK COPY statement is completed. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.

In your case, you have an INSTEAD OF INSERT trigger, so there is no INSERT.

This below query is completely wrong and will gives wrong results, it works as expected only if one row inserted, if there is more than 1 row, then those variables will hold just the values of one row, and you will lose the other values of the other rows, cause the pseudo INSERTED may contains 1 or more rows

select @AgentCode=AgentCode,
       @NationalCode=NationalCode,
       @FirstName=FirstName,
       @LastName=LastName,
       @IsActive=IsActive 
from inserted

Now, looking to your table, you already have an IDENTITY column, so you don't need to a TRIGGER at all, you can just make a computed column as

CREATE TABLE [dbo].tblAIAgent),
    [NationalCode] [bigint] NOT NULL 
    CONSTRAINT [DF_tblAIAgent_NationalCode]  DEFAULT ((0)),
    [FirstName] nvarchar NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Name]  DEFAULT (''),
    [LastName] nvarchar NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Family]  DEFAULT (''),
    [IsActive] [bit] NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Active]  DEFAULT ((1)),
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_tblAIAgent] PRIMARY KEY ([Counter])
    );

UPDATE:

According to your comment "a computed column can no longer be selected as the PK. I want this column to be placed in other relevant tables as a FK.I wrote the trigger to get the column value instead of the computed column so that I can select the column as the primary key". You are trying to make it a PRIMARY KEY so you can do as

CREATE TABLE T(
  Counter INT IDENTITY(1,1) NOT NULL,
  OtherCol INT,
  Computed AS CONCAT('Agent_', CAST(Counter AS VARCHAR(10))) PERSISTED,
  CONSTRAINT PKT PRIMARY KEY(Computed)
);

CREATE TABLE TT(
  ReferenceComputedColumn VARCHAR(16) NOT NULL,
  OtherColumn INT,
  CONSTRAINT FK_ReferencedComputedColumn 
      FOREIGN KEY(ReferenceComputedColumn) 
      REFERENCES T(Computed)
)

INSERT INTO T(OtherCol) VALUES
(1), (2), (3);

INSERT INTO TT(ReferenceComputedColumn, OtherColumn) VALUES
('Agent_1', 10),
('Agent_3', 20);

SELECT *
FROM T LEFT JOIN TT 
ON T.Computed = TT.ReferenceComputedColumn;

See how it's working.

See also this article Properly Persisted Computed Columns by Paul White.