Solved

Msg 402 Error in Code SQL in Sql Server 2005 Enviroment

Posted on 2008-06-09
3
2,181 Views
Last Modified: 2010-04-21
Am getting the following error message and do not understand why.
Msg 402, Level 16, State 1, Line 21 - The data types varchar and varchar are incompatible in the modulo operator.  I have validated part of the code as indicated below.  I am new to this enviroment so any explanation as to why the code is worng is greatly apprecieated

Code being used -
declare @tabtemp table (i int identity, sl_effected_user_id varchar(20)null)
declare @rows int
declare @var1 as varchar(20)
declare @query as varchar(8000)

insert into @tabtemp
select sl_effected_user_id
from table1

----------------------------I have validated to here and all is good

Select @rows = @@ROWCOUNT
While @rows >0

Begin

Select @var1 = sl_effected_user_id
from @tabtemp WHERE i = @Rows

set @query = '
insert into table3

select sl_id, sys_creation_date, operator_id, application_id, sl_app_id,
sl_effected_environment, sl_effected_profile_id, sl_effected_user_id,
sl_location, sl_event_description
from table2
where sl_effected_user_id like ('% +@var1+ ')
'
print @query

SET @Rows = @Rows -1
exec (@query)
END
0
Comment
Question by:frogman22
3 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
ID: 21746074
gin

Select @var1 = sl_effected_user_id
from @tabtemp WHERE i = @Rows

set @query = '
insert into table3

select sl_id, sys_creation_date, operator_id, application_id, sl_app_id,
sl_effected_environment, sl_effected_profile_id, sl_effected_user_id,
sl_location, sl_event_description
from table2
where sl_effected_user_id like (%' +@var1+ ')'
print @query

SET @Rows = @Rows -1
exec (@query)
END
Tags:
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 21746099
set @query = '
insert into table3

select sl_id, sys_creation_date, operator_id, application_id, sl_app_id,
sl_effected_environment, sl_effected_profile_id, sl_effected_user_id,
sl_location, sl_event_description
from table2
where sl_effected_user_id like '+'''% '+@var1+ ''''
0
 

Author Closing Comment

by:frogman22
ID: 31465532
the first solution fixed the % issue but when added caused a new error.  The second solution has fixed the problem.  Thanks for all the help and rapid response
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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

19 Experts available now in Live!

Get 1:1 Help Now