Solved

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

Posted on 2008-06-18
9
1,923 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
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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
Comment Utility
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:bhess1
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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:
bhess1 earned 500 total points
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now