Failing job 1

[Zarnaya]

Hello all!

I have strange proble which I'm trying to fix.

I have a job, which calls stored procedure.
Use msdb
go
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
go
EXECUTE [dbo].[Job Bevis]
go

[dbo].[Job Bevis] listing:
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Job Bevis] Script Date: 05/12/2010 12:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Job Bevis] AS select @@SERVERNAME 'Servername'
-- 1. Account
select 'Returns information about the accounts of MS SQL Server ' '1. Account'
exec master..sp_helplogins

-- 2. Server Role
select 'Returns information about the members per Server role' '2. Server Role'
exec master..sp_helpsrvrolemember

-- 3. Users per Database
select 'List of special users per database' '3. Users per Database'
declare
@name sysname
,@SQL nvarchar(600)

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable

CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL
, [USER_NAME] sysname NOT NULL
, [ROLE_NAME] sysname NOT NULL)

declare c1 cursor for
select name from master.dbo.sysdatabases open c1 fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''

/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable

-- 4. Permissions Tables
select 'Returns information about user permission for tables in the database' '4. Permissions Tables'
select '"4. Permissions Tables" Doesnt function after SQL Server 2005 upgrade 2009-06-01. ITAM has knowledge about this.'
-- exec tplvpv.dbo.SP_Job_Bevis

-- 5. Permissions Views
select 'Returns information about user permission for views in the database' '5. Permissions Views'

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmp_helprotect'))
drop table #tmp_helprotect

create table #tmp_helprotect
(Owner varchar(25)
,Object varchar(50)
,Grantee varchar(25)
,Grantor varchar(25)
,ProtectType varchar(25)
,[Action] varchar(25)
,[Column] varchar(25))
insert into #tmp_helprotect exec sp_helprotect
select Grantee, Object, ProtectType, [Action],[Column]
from #tmp_helprotect h JOIN sysobjects so ON substring(so.name,1,30)=h.Object and so.xtype='v' and h.[Action]!='Select'
order by 1
drop table #tmp_helprotect

Job fails with next error:

(41 row(s) affected)

(247 row(s) affected)
Msg 8152, Level 16, State 2, Line 3
String or binary data would be truncated.

Anyone have ideas?

Thanks in advance.

 

[simian336]

Not that familure sysname but by definition...

sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30)

It might be to small to hold the values your putting in them.

Simi

 

[Zarnaya]

And how can I fix that?

 

[markros]

In your temp table definition instead of sysname try using nvarchar(1000), for example.

PluralSight Learning Library

 

[Zarnaya]

In your temp table definition instead of sysname try using nvarchar(1000), for example.

Sorry, I'm not good enough in scripting, so I didn't understood what did you exactly mean.

 

[markros]

Do you know which particular line gave you the error?

Take a look here

create table #tmp_helprotect
    (Owner varchar(25)
    ,Object  varchar(50)
    ,Grantee  varchar(25)
    ,Grantor  varchar(25)
    ,ProtectType  varchar(25)
    ,[Action]   varchar(25)
    ,[Column]  varchar(25))

Perhaps varchar(25) is not enough. Try using varchar(100) for each column instead assuming the error comes from the query inserting data into this table.



PluralSight Learning Library

 

[Zarnaya]

Problem solved.
Thanks a lot, markros!