Create procedure hanging

[Luvsql]

I've created a sp and because of the multitude of joins and thousands of records it creates, I know that it will cause significant performance issues, so I will be scheduling it to run at night and use the table it updates for our reports.

When I am trying to create the procedure within EM, the check syntax is fast with no errors, but when I click on Ok, it sits and hangs for quite a while, then I look back and it has deleted most of the syntax.

I have deleted the sp and pasted the syntax in query analyzer, but it's now at 6 minutes without doing anything. Why is it taking so long just to create the procedure (ie creating the procedure should not actually run the syntax)?

 

[JayKusch]

Just because the syntax check comes back clean and fast does not mean the SP is phyically correct. You may have some joins to remote servers that may have rights issues that are not being returned. May be you are using cross database calls w/ the same issue.

Try breaking the SP down into workable parts. As each part "saves" correctly, reopen the SP add the next code block and continue. Maybe you can find the rouge section this way.

Thanks

J. Kusch

 

[SQLSister]

You could post the procedure code, probably something in it is not right.

Questions about posting. See faq183-874

 

[Luvsql]

If I remove the delete statement, it creates in seconds.

ie

create procedure TEST
as
insert into table1
select statement
go

This creates fine and in seconds.

create procedure TEST
as
delete table1
go
insert into table1
select statement
go


This hangs and doesn't create.

 

[Luvsql]

I created 2 steps for my job (one to delete table, second to run sp). It's crazy that I can't add a delete into the sp.

 

[mrdenny]

You can't put go statements inside a stored procedure. The problem that you are having is that when it gets to the first go it uses that as the end of the procedure. The rest of the code is being executed.

[COLOR=green]
create procedure TEST
as
delete table1
go
[/color]
[COLOR=red]
insert into table1
select statement
[/color]
go

The green code is being created as the procedure, while the red code is simply being executed against the server.

The GO command is called a batch seperator. Since stored procedures can only support a single batch within there code, the GO command ends the stored procedure.

Denny

--Anything is possible. All it takes is a little research. (Me)