database in transtion when running SP with select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

database in transtion when running SP with select

Author

Topic

tross
Starting Member

20 Posts

Posted - 2011-05-04 : 11:12:03

when the following SP is running I can not open the tables or SP folder of the database without it timing out.
when I try to run "use ch_dw_DB"
I get database in transition

why would a simple select into query lock the DB.
we are using sql 2008
------------ @@version info:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
---------------
SP CODE:
---------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Build_ALCG_Detail]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select * into ALCGDETL
from v_ALCGDETL
-- Insert statements for procedure here
--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

TIA
Tim

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 11:27:35

Ho wis being run - inside a transaction?

It looks like it's using a view so you need to look at that too.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tross
Starting Member

20 Posts

Posted - 2011-05-04 : 12:58:48

the SP is run as a schedule job under the SQLAgent.
there are about 15 jobs that that do simular work.
(read data from a "non-sqlserver db" and populate a table in this DB)
these jobs do not cause this problem only this one does.
all these jobs are built the same.
same view structure, same obdc, same link server, same SP structure, etc...

here is the veiw
------------
view
-------------
CREATE VIEW [dbo].[v_ALCGDETL]
AS
SELECT *
FROM OPENQUERY(odbc1, 'select * from ALCGDETL') Rowset_1

------------------

TIA
Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources