sql_variant to varchar is not allowed

Question

sql_variant to varchar is not allowed

lan2022nguyen

SSC-Addicted

Points: 459
More actions
April 19, 2023 at 12:17 pm

#4176243
Getting the following error:
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.
====
Table LogonInfo has the following columns:
Spid int
UserName nvarchar
LogonDate datetime
ClientNetAddress varchar
Hostname nvarchar
I have the following trigger to capture/insert into the table above:
```
CREATE OR ALTER TRIGGER [logoninfo] ON ALL SERVER FOR LOGON
as
BEGIN

INSERT INTO Logs.dbo.LogonInfo (Spid, UserName, LogonDate, ClientNetAddress, HostName) 
VALUES (@@SPID, SUSER_NAME(), GETDATE(), CONNECTIONPROPERTY('client_net_address'), HOST_NAME());

END;

ENABLE TRIGGER [logoninfo] ON ALL SERVER
```

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16148 More actions · Answer 1

some of those variables need to be explicitly converted to varchar - example below for @@SPID

INSERT INTO Logs.dbo.LogonInfo (Spid, UserName, LogonDate, ClientNetAddress, HostName) 
VALUES (convert(varchar(200), @@SPID), SUSER_NAME(), GETDATE(), CONNECTIONPROPERTY('client_net_address'), HOST_NAME());

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 2

frederico_fonseca wrote:

some of those variables need to be explicitly converted to varchar - example below for @@SPID
INSERT INTO Logs.dbo.LogonInfo (Spid, UserName, LogonDate, ClientNetAddress, HostName) 
VALUES (convert(varchar(200), @@SPID), SUSER_NAME(), GETDATE(), CONNECTIONPROPERTY('client_net_address'), HOST_NAME());

thanks so much @frederico_fonseca, this worked!