?
Solved

CAST or CONVERT: invalid attributes specified for type 'int'

Posted on 2008-06-18
9
Medium Priority
?
2,102 Views
Last Modified: 2012-06-27
This stored procedure was working on Sql Server 2000. However it is not working on Sql Server 2005.

How can i correct it?

The full error is :
Msg 291, Level 16, State 1, Procedure itemleri_bul, Line 32
CAST or CONVERT: invalid attributes specified for type 'int'

CREATE procedure itemleri_bul 
@StrUserID varchar(30)
AS
DECLARE
@length int, -- stritem uzunluu
@i int, -- item'in stritem deki yeri
@dwid int, 
@dur int,
@duar int,
@StackSize1 int,
@Sira int,
@Row int,
@name varchar(100),
@extname varchar(100),
@ext int
 
 
    set @i = 14*0+1
    set @length = 401
 
select @row=count(*) from userdata where struserid=@StrUserID
 
delete from itemler where struserid=@StrUserID
 
if @row>0 
  begin
 
WHILE @i < @length
Begin
 
Select @dwid=cast(cast(substring(cast(substring(strItem, @i,4) as varbinary(4)), 4, 
1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strItem, @i,4) as 
varbinary(4)), 2, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int(4)),
@dur = cast(cast(cast( substring(strItem, @i+5, 1) as varbinary(1))+cast(substring(strItem, @i+4, 1) as 
varbinary(1)) as varbinary(2)) as smallint),
@StackSize1 = cast(cast(cast( substring(strItem, @i+7, 1) as varbinary(1))+cast(substring(strItem, @i+6, 1) as 
varbinary(1)) as varbinary(2)) as smallint),
        @StrUserID = strUserID
        From UserData
        Where strUserID = @StrUserID
 
 
 
 
insert into itemler (dwid,stacksize,durability,struserid,sira,itembasicname,extname) 
values(@dwid,@stacksize1,@dur,@StrUserID,(@i-1) / 8,@extname,@name )
 
set @i=@i+8
 
end
end
 
GO

Open in new window

0
Comment
Question by:shamanq11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 21818303
Sorry to be a pain, but can you show some of the data you're trying to convert? That might really help understand that block of code there...
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 21823739
I'd start troubleshooting this by braking the casts into individual pieces that get stored in temporary variables, to see exactly which cast is failing.

That would let you examine the data for that field, to see what can't be cast.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 21824677
Your statement includes a cast "as int(4)".  In SQL 2000, due to a bug in the parser, this worked.  However, the syntax should simply be "as int" - if you want a different size, specify bigint or smallint.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

Expert Comment

by:rob_farley
ID: 21827652
Yes, it'll be the int(4). I scanned your code yesterday looking for this type of thing, but didn't notice it. Well done to bhess1 for noticing it.

Rob
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 21827692
It wasn't easy.  I have taken to copying the statements pasted here and breaking them down through indentation, like below (this is the code above) which helps make issues like this much more obvious.
CREATE procedure itemleri_bul 
@StrUserID varchar(30)
AS
DECLARE
    @length int, -- stritem uzunluu
    @i int, -- item'in stritem deki yeri
    @dwid int, 
    @dur int,
    @duar int,
    @StackSize1 int,
    @Sira int,
    @Row int,
    @name varchar(100),
    @extname varchar(100),
    @ext int
 
set @i = 14*0+1
set @length = 401
 
select @row=count(*) from userdata where struserid=@StrUserID
 
delete from itemler where struserid=@StrUserID
 
if @row>0 
begin
 
    WHILE @i < @length
    Begin
     
        Select @dwid=
            cast(
                cast(
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 4, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 3, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 2, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 1, 1) 
                    as varbinary(4)
                ) as int(4)
            ),
            @dur = 
            cast(
                cast(
                    cast(
                        substring(strItem, @i+5, 1) 
                        as varbinary(1)
                    ) + 
                    cast(
                        substring(strItem, @i+4, 1) 
                        as varbinary(1)
                    ) 
                    as varbinary(2)
                ) 
                as SMALLINT
            ),
            @StackSize1 = 
            cast(
                cast(
                    cast(
                        substring(strItem, @i+7, 1) 
                        as varbinary(1)
                    ) + 
                    cast(
                        substring(strItem, @i+6, 1) 
                        as varbinary(1)
                    ) 
                    as varbinary(2)
                ) 
                as SMALLINT
            ),
            @StrUserID = strUserID
        From UserData
        Where strUserID = @StrUserID
         
        insert into itemler (
            dwid,
            stacksize,
            durability,
            struserid,
            sira,
            itembasicname,
            extname
            ) 
        values(
            @dwid,
            @stacksize1,
            @dur,
            @StrUserID,
            (@i-1) / 8,
            @extname,
            @name 
        )
         
        set @i=@i+8
         
    end
end
 
GO

Open in new window

0
 
LVL 15

Expert Comment

by:rob_farley
ID: 21827755
Yeah... I should've cut and pasted into something else that would let me search better... but didn't. Guess I'm slack...
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question