SQL Statement timesout

[UCF87]

I am trying to delete 1000s of records and get and SQL timeout error. An example of what I am trying to delete is:
/nnnnyymm I want to delete all of year 03 and month 03.

DELETE FROM ReportIndex WHERE lprRptName LIKE '%%%%%0303'


Thanks for your help in advance.

 

[ClaireHCM]

Try to
Create index on that column -- Update statistics ---execute

set nocount on
delete from ReportIndex where right(ltrim(rtrim(lprRptName,4))) = '0303'

 

[UCF87]

Now I get the RTRIM requires 1 arguement..
( Did I mention I am new at this.. )

 

[ClaireHCM]

Sorry ,my bad

set nocount on
delete from ReportIndex where right(ltrim(rtrim(lprRptName)),4) = '0303'

 

[UCF87]

Still got Time out expired..

 

[dky1e]

Try this:

set rowcount 100

while @@rowcount > 0
begin
DELETE FROM ReportIndex WHERE lprRptName LIKE '%0303'
end

set rowcount 0

 

[ClaireHCM]

Consider to change Setting configuration.
execute "sp_configure" will return you sql default setting for many option.Look for "query wait","remote query timeout"....

But I never used it ,just a suggestion.

Remember to change it back after finishing the task

 

[UCF87]

dkyle:
Now I get:
Query designer does not support SET STATEMENT SQL Construct.