asked on

Must declare the scalar variable "@ErrorCounts"

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

HainKurt

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

      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

ASKER CERTIFIED SOLUTION

Elvio Lujan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

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...

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anush Yanagandula

ASKER

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ão

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.

Anush Yanagandula

ASKER

Vitor,

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

HainKurt

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ão

Just to add that Elvio was the first one to point to collation issue.

HainKurt

@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ão

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.

HainKurt

Thanks Vitor :)

ok, it does not create any error, but it is a bug/typo that should be fixed too :)

Vitor Montalvão

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

Must declare the scalar variable &quot;@ErrorCounts&quot;

Must declare the scalar variable "@ErrorCounts"