We help IT Professionals succeed at work.

Must declare the scalar variable "@ErrorCounts"

615 Views
Last Modified: 2017-05-14
Can anyone help me to fix this error.

Code :

USE [BBBAdmin]
GO

/****** Object:  StoredProcedure [dbo].[sp_check_sqlerrorlog]    Script Date: 3/23/2017 11:55:54 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE procedure [dbo].[sp_check_sqlerrorlog]
as

set nocount on
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF

declare @server_name        nvarchar(100)
declare @instance_name  nvarchar(20)
declare @db_name            nvarchar(128)
declare @db_status            nvarchar(128)
declare @vertext        varchar(50)
declare @version        char(20)

declare @datestring            varchar(50)
declare @LogDate_Cur      datetime
declare @ErrorText_Cur      varchar(255)

declare @sql_str            varchar(255)
declare @errorcounts      int
declare @returncode            int

set @datestring = convert(nvarchar(2),datepart(day, getdate())) + N'-' + datename (month, getdate()) + N'-' + datename (year, getdate())

select @server_name = CONVERT(sysname, SERVERPROPERTY('ServerName'))
select @instance_name = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
select @vertext = CONVERT(sysname, SERVERPROPERTY('ProductVersion'))
select @version = case when @vertext like '%8.0%' then 'SQL2000'
            when @vertext like '%9.0%' then 'SQL2005'
            when @vertext like '%10.0%' then 'SQL2008'
              end

set @errorcounts = 0
set @returncode = 0

if @version = 'SQL2000'
  begin
    INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts (Server_Name, ErrorCounts) values (@server_name, @ErrorCounts)
    CREATE TABLE [dbo].[#SQL_ErrorLog_2000](
      [errortext] [text] NULL,
      [continuationrow] [int] NULL
      )
    insert into #SQL_ErrorLog_2000 exec master.dbo.xp_readerrorlog

    declare LogCur_2000 cursor dynamic
      for select errortext from #SQL_ErrorLog_2000
      where left(convert(varchar, errortext),10) >= convert(varchar,dateadd(hour,-24, getdate()))
            and (errortext like'%[fF]ail%' or errortext like '%[Ee]rror%')
        and errortext not like '%Error: 3023%'
            and errortext not like '%Error: 0x2098%'
            and errortext not like '%Error: 0x5%'
        and errortext not like '%Error: 18456%'
            and errortext not like '%Errorlog%'
             and errortext not like '%Login%'
            and errortext not like '%DBCC%'
            and errortext not like '%CHECKDB%'

    open LogCur_2000
    fetch next from LogCur_2000 into @ErrorText_Cur

    while (@@fetch_status = 0)
      begin
          INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, ErrorText) values (@server_name,@ErrorText_Cur)
          set @errorcounts = @errorcounts + 1

          fetch next from LogCur_2000 into @ErrorText_Cur
      end
    close LogCur_2000
    deallocate LogCur_2000
   
    declare DBStatusCur cursor dynamic
        for select name
            from master.dbo.sysdatabases
            where dbid > 4
             
    open DBStatusCur
    fetch next from DBStatusCur into @db_name

    while (@@fetch_status = 0)
    begin
      SELECT @db_status = convert(nvarchar,DATABASEPROPERTYEX(@db_name, 'Status'))
      if @db_status <> 'online'
        begin
          select @ErrorText_Cur = 'Status of ' + @db_name + ' is ' + @db_status
            INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, ErrorText) values (@server_name, @ErrorText_Cur)
            set @errorcounts = @errorcounts + 1
          end
      fetch next from DBStatusCur into @db_name
    end
   
    close DBStatusCur
    deallocate DBStatusCur
      
      if @errorcounts > 0
          begin
            update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts
              set ErrorCounts = @ErrorCounts
              where server_name = @server_name
        end
  end
else  
  begin
-- Process SQL 2005 or later version
    INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts (Server_Name, ErrorCounts) values (@server_name,@ErrorCounts)
    CREATE TABLE [dbo].[#SQL_ErrorLog](
      [logdate] [datetime] NULL,
      [processinfo] [varchar](100) NULL,
      [errortext] [text] NULL
      )
    insert into #SQL_ErrorLog exec master.dbo.xp_readerrorlog

    declare LogCur cursor dynamic
        for select logdate, errortext from #SQL_ErrorLog
        where logdate >= dateadd(hour,-24, getdate())
      and (errortext like'%[fF]ail%' or errortext like '%[Ee]rror%')
      and errortext not like '%Error: 3023%'
      and errortext not like '%Error: 0x2098%'
      and errortext not like '%Error: 0x5%'       
      and errortext not like '%Error: 18456%'
      and errortext not like '%Errorlog%'
      and errortext not like '%Login%'
            and errortext not like '%DBCC%'
            and errortext not like '%CHECKDB%'

    open LogCur
    fetch next from LogCur into @LogDate_Cur, @ErrorText_Cur

    while (@@fetch_status = 0)
      begin
          INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, LogDate, ErrorText) values (@server_name,@LogDate_Cur,@ErrorText_Cur)
          set @errorcounts = @errorcounts + 1
        fetch next from LogCur into @LogDate_Cur, @ErrorText_Cur
      end
    close LogCur
    deallocate LogCur
   
    declare DBStatusCur cursor dynamic
        for select name
            from master.dbo.sysdatabases
            where dbid > 4
             
    open DBStatusCur
    fetch next from DBStatusCur into @db_name

    while (@@fetch_status = 0)
    begin
      SELECT @db_status = convert(nvarchar,DATABASEPROPERTYEX(@db_name, 'Status'))
      if @db_status <> 'online'
        begin
          select @LogDate_Cur = ''
          select @ErrorText_Cur = 'Status of ' + @db_name + ' is ' + @db_status
            INSERT INTO NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, LogDate, ErrorText) values (@server_name, @LogDate_Cur, @ErrorText_Cur)
            set @errorcounts = @errorcounts + 1
          end
      fetch next from DBStatusCur into @db_name
    end
   
    close DBStatusCur
    deallocate DBStatusCur
      
      if @errorcounts > 0
        begin
            update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts
              set @ErrorCounts = @ErrorCounts + 1
              where server_name = @server_name
        end
  end
return


GO
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
last part of your code

      if @errorcounts > 0
        begin
            update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set @ErrorCounts = @ErrorCounts + 1
              where server_name = @server_name
        end

Open in new window


>>>

      if @errorcounts > 0
        begin
            update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set ErrorCounts = @ErrorCounts + 1
              where server_name = @server_name
        end

Open in new window


or

      if @errorcounts > 0
        begin
            update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set ErrorCounts = ErrorCounts + 1
              where server_name = @server_name
        end

Open in new window


not sure what your logic is
.Net Senior Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
nope, issue is here

update NJUNPPIDEAR01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set @ErrorCounts = @ErrorCounts + 1
              where server_name = @server_name

Open in new window


first occurence of ErrorCounts should be table column, so it should not have @ in front...
the second depends on your logic...
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Here is the code again :

USE [BBBAdmin]
GO

/****** Object:  StoredProcedure [dbo].[sp_check_sqlerrorlog]    Script Date: 4/21/2017 9:11:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE procedure [dbo].[sp_check_sqlerrorlog]
as

set nocount on
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF

declare @server_name        nvarchar(100)
declare @instance_name  nvarchar(20)
declare @db_name            nvarchar(128)
declare @db_status            nvarchar(128)
declare @vertext        varchar(50)
declare @version        char(20)

declare @datestring            varchar(50)
declare @LogDate_Cur      datetime
declare @ErrorText_Cur      varchar(255)

declare @sql_str            varchar(255)
declare @errorcounts      int
declare @returncode            int

set @datestring = convert(nvarchar(2),datepart(day, getdate())) + N'-' + datename (month, getdate()) + N'-' + datename (year, getdate())

select @server_name = CONVERT(sysname, SERVERPROPERTY('ServerName'))
select @instance_name = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
select @vertext = CONVERT(sysname, SERVERPROPERTY('ProductVersion'))
select @version = case when @vertext like '%8.0%' then 'SQL2000'
            when @vertext like '%9.0%' then 'SQL2005'
            when @vertext like '%10.0%' then 'SQL2008'
              end

set @errorcounts = 0
set @returncode = 0

if @version = 'SQL2000'
  begin
    INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts (Server_Name, ErrorCounts) values (@server_name,@ErrorCounts)
    CREATE TABLE [dbo].[#SQL_ErrorLog_2000](
      [errortext] [text] NULL,
      [continuationrow] [int] NULL
      )
    insert into #SQL_ErrorLog_2000 exec master.dbo.xp_readerrorlog

    declare LogCur_2000 cursor dynamic
      for select errortext from #SQL_ErrorLog_2000
      where left(convert(varchar, errortext),10) >= convert(varchar,dateadd(hour,-24, getdate()))
            and (errortext like'%[fF]ail%' or errortext like '%[Ee]rror%')
        and errortext not like '%Error: 3023%'
            and errortext not like '%Error: 0x2098%'
            and errortext not like '%Error: 0x5%'
        and errortext not like '%Error: 18456%'
            and errortext not like '%Errorlog%'
             and errortext not like '%Login%'
            and errortext not like '%DBCC%'
            and errortext not like '%CHECKDB%'

    open LogCur_2000
    fetch next from LogCur_2000 into @ErrorText_Cur

    while (@@fetch_status = 0)
      begin
          INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, ErrorText) values (@server_name,@ErrorText_Cur)
          set @errorcounts = @errorcounts + 1

          fetch next from LogCur_2000 into @ErrorText_Cur
      end
    close LogCur_2000
    deallocate LogCur_2000
   
    declare DBStatusCur cursor dynamic
        for select name
            from master.dbo.sysdatabases
            where dbid > 4
             
    open DBStatusCur
    fetch next from DBStatusCur into @db_name

    while (@@fetch_status = 0)
    begin
      SELECT @db_status = convert(nvarchar,DATABASEPROPERTYEX(@db_name, 'Status'))
      if @db_status <> 'online'
        begin
          select @ErrorText_Cur = 'Status of ' + @db_name + ' is ' + @db_status
            INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, ErrorText) values (@server_name, @ErrorText_Cur)
            set @errorcounts = @errorcounts + 1
          end
      fetch next from DBStatusCur into @db_name
    end
   
    close DBStatusCur
    deallocate DBStatusCur
      
      if @errorcounts > 0
          begin
            update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts
              set ErrorCounts = @ErrorCounts
              where server_name = @server_name
        end
  end
else  
  begin
-- Process SQL 2005 or later version
    INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts (Server_Name, ErrorCounts) values (@server_name,@ErrorCounts)
    CREATE TABLE [dbo].[#SQL_ErrorLog](
      [logdate] [datetime] NULL,
      [processinfo] [varchar](100) NULL,
      [errortext] [text] NULL
      )
    insert into #SQL_ErrorLog exec master.dbo.xp_readerrorlog

    declare LogCur cursor dynamic
        for select logdate, errortext from #SQL_ErrorLog
        where logdate >= dateadd(hour,-24, getdate())
      and (errortext like'%[fF]ail%' or errortext like '%[Ee]rror%')
      and errortext not like '%Error: 3023%'
      and errortext not like '%Error: 0x2098%'
      and errortext not like '%Error: 0x5%'       
      and errortext not like '%Error: 18456%'
      and errortext not like '%Errorlog%'
      and errortext not like '%Login%'
            and errortext not like '%DBCC%'
            and errortext not like '%CHECKDB%'

    open LogCur
    fetch next from LogCur into @LogDate_Cur, @ErrorText_Cur

    while (@@fetch_status = 0)
      begin
          INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, LogDate, ErrorText) values (@server_name,@LogDate_Cur,@ErrorText_Cur)
          set @errorcounts = @errorcounts + 1
        fetch next from LogCur into @LogDate_Cur, @ErrorText_Cur
      end
    close LogCur
    deallocate LogCur
   
    declare DBStatusCur cursor dynamic
        for select name
            from master.dbo.sysdatabases
            where dbid > 4
             
    open DBStatusCur
    fetch next from DBStatusCur into @db_name

    while (@@fetch_status = 0)
    begin
      SELECT @db_status = convert(nvarchar,DATABASEPROPERTYEX(@db_name, 'Status'))
      if @db_status <> 'online'
        begin
          select @LogDate_Cur = ''
          select @ErrorText_Cur = 'Status of ' + @db_name + ' is ' + @db_status
            INSERT INTO NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog (Server_Name, LogDate, ErrorText) values (@server_name, @LogDate_Cur, @ErrorText_Cur)
            set @errorcounts = @errorcounts + 1
          end
      fetch next from DBStatusCur into @db_name
    end
   
    close DBStatusCur
    deallocate DBStatusCur
      
      if @errorcounts > 0
        begin
            update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts
              set ErrorCounts = @ErrorCounts
              where server_name = @server_name
        end
  end
return



GO
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Anush, what for you sent us the code again?
You should give us a feedback for our comments like the SQL Server instance and database collation to see if it's case sensitive or not.

Author

Commented:
Vitor,

You made my day. Collation was set up differently on this server and now everything looks after lil modifications to the script.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
objection...

you changed the code
 if @errorcounts > 0
        begin
            update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set ErrorCounts = @ErrorCounts
              where server_name = @server_name
        end

Open in new window


which I mentioned, which was the main cause of the error...

return back it to org as
 if @errorcounts > 0
        begin
            update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set @ErrorCounts = @ErrorCounts
              where server_name = @server_name
        end

Open in new window


and you w'll see the error again...
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Just to add that Elvio was the first one to point to collation issue.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
@Vitor

I believe the issue was here

update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set @ErrorCounts = @ErrorCounts
              where server_name = @server_name

Open in new window


it should be
update NJUNPPIDERA01.BBBAdmin.dbo.SQL_ErrLog_Counts 
              set ErrorCounts = @ErrorCounts
              where server_name = @server_name

Open in new window


nothing to do with collation!

op changed the code + collation fix, which I believe nothing to do with original issue...
So I suggested op, use the original code with collation fix
if it still does not work, then my fix is the solution...
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Huseyin, I agree that's odd to have an update to set a variable value but that doesn't return any error and this can be proved with a simple test:
DECLARE @NewValue INT = 1

UPDATE TableName
SET @NewValue = @NewValue

------------------------------------------------
(3 row(s) affected)

Open in new window

But the collation can affect a variable name if it's case sensitive and then @errorcounts <> @ErrorCounts.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
Thanks Vitor :)

ok, it does not create any error, but it is a bug/typo that should be fixed too :)
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
As per author's comment that confirmed the collation issue.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.