x

Error Msg 8116, Level 16, State 1, Line 2

Error Message: Msg 8116, Level 16, State 1, Line 2 Argument data type varchar is invalid for argument 3 of convert function ============================================== The process started failing right after the database was moved to a new SQL Server 2012 server. It was previously on a SQL Server 2008 server, and the syntax used is valid in that environment, but it is NOT valid in the SQL Server 2012 environment. This is the Stored Proc; It was

 CREATE PROCEDURE [dbo].[cc_usp_OnBaseUsersAddedToManagerGroup]
 AS
 BEGIN
     SET NOCOUNT ON
     
     DECLARE @RecCount INT, 
             @tableHTML  NVARCHAR(MAX)
             
     IF OBJECT_ID('tempdb..#LoginResults' , 'U') IS NOT NULL
         BEGIN
             DROP TABLE #LoginResults
         END
             
     SELECT [UsrAcct].[UserName], [UsrAcct].[UserNum], CONVERT(VARCHAR(20),GETDATE() - 1,110) AS AddDate
     INTO #LoginResults
     FROM [hsi].[UserAccount] AS [UsrAcct] --ua
     INNER JOIN
         (SELECT DISTINCT [UsrXGrp].[UserNum]
                         FROM [hsi].[UserXUserGroup] AS [UsrXGrp] --uxg
                         INNER JOIN [hsi].[UserGroup] AS [UsrGrp] --ug
                             ON [UsrXGrp].[UserGroupNum] = [UsrGrp].[UserGroupNum]
                         WHERE [UsrGrp].[UserGroupName] = 'MANAGER') AS [UsrGpNm] --ugs
                             ON [UsrAcct].[UserNum] = [UsrGpNm].[UserNum]
     WHERE [UsrAcct].[LicenseFlag] & 512 = '512'
         AND [UsrAcct].[UserNum] NOT IN (SELECT [UserNum] FROM [cc_tblMasterPerm])
 
     SELECT @RecCount = COUNT(*) FROM #LoginResults
 
     IF @RecCount > '0'
         BEGIN
             SET @tableHTML =
                 N'<H3>The following user(s) were added to the MANAGERS group within the last business day.</H3>' +
                 N'<table border="1">' +
                 N'<tr><th>OnBase UserID</th><th>Date Added</th></tr>' +
                 CAST ( ( SELECT TD = [UserName], '',
                                 TD = [AddDate], ''
                         FROM #LoginResults AS [LR]
                         ORDER BY [UserName]
                         FOR XML PATH('tr'), TYPE 
                         ) AS NVARCHAR(MAX) ) +
                 N'</table> <br><br> This email generated by cc_usp_OnBaseUsersAddedToManagerGroup on OnBase ONBS039<br><br>';
 
                 EXEC [msdb].[dbo].[sp_send_dbmail]
                 @recipients = 'Mia.Johnson@CGSAdmin.com;William.Reed@CGSAdmin.com;Donald.Catanzaro@CGSAdmin.com;Craig.Danitz@CGSAdmin.com;Chris.Deal@CGSAdmin.com',
                 @body = @tableHTML, 
                 @body_format = 'HTML',
                 @subject ='User Added to MANAGERS Group',
                 @profile_name ='SVC_SQLServer'
                 
             INSERT [cc_tblMasterPerm] ([UserName],[UserNum],[DateAddedToTable])
             SELECT [UserName], [UserNum], CONVERT(VARCHAR(20),GETDATE() - 1,'110')
             FROM #LoginResults    
             
             INSERT [A70TPCGSMSQL004].[OnBaseGeneral].[dbo].[tblEmailLogging] ([strNotification]) VALUES ('Users Added to Managers Group')
         END
 END
 SET ANSI_NULLS OFF
 
 GO

Columns;

XPriMasterKey (int. not null) usernum (int, null) username (char(75), null) DateAddedToTable (smalldatetime, not null) ===========================

more ▼

asked Apr 06 at 03:34 PM in Default

avatar image

Gehima2016
1 1

question reverted to original form so the answer is now understandable.

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think you have '' around the 3rd argument in the INSERT which throws the error.

This:

  INSERT [cc_tblMasterPerm] ([UserName],[UserNum],[DateAddedToTable])
          SELECT [UserName], [UserNum], CONVERT(VARCHAR(20),GETDATE() - 1,'110')
          FROM #LoginResults 

should be

  INSERT [cc_tblMasterPerm] ([UserName],[UserNum],[DateAddedToTable])
          SELECT [UserName], [UserNum], CONVERT(VARCHAR(20),GETDATE() - 1,110)
          FROM #LoginResults  
more ▼

answered Apr 06 at 07:09 PM

avatar image

JohnM
14.2k 3 7 14

Hi JohnM,

That was it. You the best. Thank you very much.

Apr 07 at 10:36 PM Gehima2016
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476

asked: Apr 06 at 03:34 PM

Seen: 93 times

Last Updated: 4 days ago

Copyright 2017 Redgate Software. Privacy Policy