Problem scheduling a package

Hi,
I have a package saved in the msdb database. When I right click on it and execute, it works fine through the dts utility program.

When I schedule it, it always errors out with the message "The command line parameters are invalid".

The package is password protected. The command line in the dts utility reads: /DTS "\MSDB\<package name>" /SERVER aawork2/DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I have to enter my password to view this.

In the job step, if I click the command line, it reads: /DTS "/MSDB/<package name>" /SERVER aawork2/DECRYPT <password> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I suspect its something to do with the password protection and the fact the job runs it under the service account.

Thanks in advance for any help.

Asim.
No, the problem (most likely) is that the package path has forward slashes instead of backward slashes. Try changing them to back slashses. Also put a space between the server name and the /DECRYPT option. These were bugs that should be fixed by RTM.

Thanks,
Matt|||Matt, it works. Thanks! This was very helpful.

Asim.

Problem running multiple SP's

Hello,
I have two SP that are similar to the one below. When trying to run both of
them through Query analyzer the first one will execute properly but the
second one will say that it executed but doesn't really update any fields.
The only difference between the two is the name of the table that gets
updated and the number of tests. If I run either one independently through
there own query windows they both work.
TIA for any help.
CREATE PROCEDURE [dbo].[sp_UpdateRF] AS
-- Update RF Grades
DECLARE @.TestName VARCHAR(30)
DECLARE @.TestGrade CHAR(10)
DECLARE @.Employee_ID char(7)
DECLARE curRF CURSOR FOR SELECT TestName, TestGrade, Import.Employee_ID
FROM Import, RF
WHERE Import.Employee_ID = RF.Employee_ID
AND SUBSTRING(TestName, 1, 1)= '5'
OPEN curRF
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curRF INTO @.TestName, @.TestGrade, @.Employee_ID
IF SUBSTRING(@.TestName, 3, 1) = '1'
BEGIN
UPDATE RF
SET RF.[1] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[1] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '2'
BEGIN
UPDATE RF
SET RF.[2] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[2] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '3'
BEGIN
UPDATE RF
SET RF.[3] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[3] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '4'
BEGIN
UPDATE RF
SET RF.[4] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[4] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '5'
BEGIN
UPDATE RF
SET RF.[5] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[5] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = '6'
BEGIN
UPDATE RF
SET RF.[6] = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
AND RF.[6] IS NULL
END
IF SUBSTRING(@.TestName, 3, 1) = 'F'
BEGIN
UPDATE RF
SET RF.F = @.TestGrade
WHERE @.Employee_ID = RF.Employee_ID
END
END
CLOSE curRF
DEALLOCATE curRF
GOOn Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:

>Hello,
>I have two SP that are similar to the one below. When trying to run both of
>them through Query analyzer the first one will execute properly but the
>second one will say that it executed but doesn't really update any fields.
>The only difference between the two is the name of the table that gets
>updated and the number of tests. If I run either one independently through
>there own query windows they both work.
>TIA for any help.
Hi XImhotep,
The problem is that you have some statements in the wrong order. You
should always have a FETCH _before_ testing @.@.FETCH_STATUS. If you don't
have a fetch before that, you'll end up testing the last fetch status of
the previously executed cursor.
The proper order of events is
DECLARE CURSOR
OPEN CURSOR
FETCH FIRST
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something
FETCH NEXT
END
CLOSE CURSOR
DEALLOCATE CURSOR
However, most cursors are not needed at all. In 99% of the situations, a
set-based alternative will be faster, shorter, easier to read and hence
easier to maintain.
Your post doesn't reveal enough of your tables to make it worth an
attempt at rewriting it. However, if you post more information about
this, I'll be happy to have a look (and many others will too). See
www.aspfaq.com/5006 for an explanation of the information you should
provide if you s help.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||XImhotep wrote:
> Hello,
> I have two SP that are similar to the one below. When trying to run
> both of them through Query analyzer the first one will execute
> properly but the second one will say that it executed but doesn't
> <SNIP>
Try adding some debug code to the procedures and see if they are both
running the updates. Also, how are you executing the two procedures? Do
you have two EXEC statements in the same QA window that are being
executed as a batch?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for the help. I missed the fetch statement at the bottom. Everyhting
is working now.
Thanks again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:08isg1lm0qc9s5f8001gd2relr27k9s568@.
4ax.com...
> On Thu, 25 Aug 2005 15:14:47 -0700, XImhotep wrote:
>
> Hi XImhotep,
> The problem is that you have some statements in the wrong order. You
> should always have a FETCH _before_ testing @.@.FETCH_STATUS. If you don't
> have a fetch before that, you'll end up testing the last fetch status of
> the previously executed cursor.
> The proper order of events is
> DECLARE CURSOR
> OPEN CURSOR
> FETCH FIRST
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- do something
> FETCH NEXT
> END
> CLOSE CURSOR
> DEALLOCATE CURSOR
> However, most cursors are not needed at all. In 99% of the situations, a
> set-based alternative will be faster, shorter, easier to read and hence
> easier to maintain.
> Your post doesn't reveal enough of your tables to make it worth an
> attempt at rewriting it. However, if you post more information about
> this, I'll be happy to have a look (and many others will too). See
> www.aspfaq.com/5006 for an explanation of the information you should
> provide if you s help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Problem referencing a global temporary table

I'm having difficulty referencing a global temporary table in a stored procedure. My stored procedure will execute correctly for days on end, but then all of a sudden it will stop working and I will get messages like:

Invalid object name '##TableName'.

and

Cannot drop the table '##TableName, because it does not exist in the system catalog.

==========================================================================

In my stored procedure I am first creating my temporary table using the syntax below. I need to create a seed value from a parameter, so that is why I am using the SET @.SQL and EXEC(@.SQL) statements:

SET @.SQL =
'CREATE TABLE ##TableName (
[Value1] [varchar] (11) ,
[Value2] [numeric](13, 0) ,
[Value3] [varchar] (30) ,
[Value4] [varchar] (30) ,
[Value5] [int] IDENTITY (' + CAST(@.Variable AS VARCHAR(10)) + ', 1) NOT NULL )'
EXEC(@.SQL)

I then begin a transaction and perform an INSERT INTO statement into this temp table, and then later perform a SELECT statement, and finally a DROP statement.

Any ideas or can anyone point me in the right direction as to why this works SOME of the time?

Just top make sure that you know that a global temporary table can be only at one time per server. So if any other users also executes the procedure and drops the "global" temporary table, it won′t be accessible in the other session anymore. Temp Table with one dash '#' are session specific, so can created in every session. Global ones, with two dashes '##' are global created. Every user sees them and can manipulate (or even drop) them.

So I guess any other process dropped your global temporary table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for the info. I am aware of the difference between the global-specific temp table vs. the session-specific temp table. In fact, I chose to create the global-specific temp table so that I can reference the table after the EXEC(@.SQL) statement runs.
|||

It is hard to tell what might be wrong with the code without seeing some sort of repro script. Note that if there are multiple references to a global temporary table for example, SQL Server will delete it automatically after all users referencing the table have disconnected from the server. This can lead to unpredictable behavior if you use multiple connections but doesn't seem to be the case here. In any case, you can modify the code to use just a temporary table instead by doing following:

create table #.... (

columns except the identity column

)

exec ('alter table # add ... identity column with seed')

insert into #...

select * from #...

This approach doesn't require any dynamic SQL except for the few DDLs and it is easier to read/debug also.

|||

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."

Problem referencing a global temporary table

I'm having difficulty referencing a global temporary table in a stored procedure. My stored procedure will execute correctly for days on end, but then all of a sudden it will stop working and I will get messages like:

Invalid object name '##TableName'.

and

Cannot drop the table '##TableName, because it does not exist in the system catalog.

==========================================================================

In my stored procedure I am first creating my temporary table using the syntax below. I need to create a seed value from a parameter, so that is why I am using the SET @.SQL and EXEC(@.SQL) statements:

SET @.SQL =
'CREATE TABLE ##TableName (
[Value1] [varchar] (11) ,
[Value2] [numeric](13, 0) ,
[Value3] [varchar] (30) ,
[Value4] [varchar] (30) ,
[Value5] [int] IDENTITY (' + CAST(@.Variable AS VARCHAR(10)) + ', 1) NOT NULL )'
EXEC(@.SQL)

I then begin a transaction and perform an INSERT INTO statement into this temp table, and then later perform a SELECT statement, and finally a DROP statement.

Any ideas or can anyone point me in the right direction as to why this works SOME of the time?

Just top make sure that you know that a global temporary table can be only at one time per server. So if any other users also executes the procedure and drops the "global" temporary table, it won′t be accessible in the other session anymore. Temp Table with one dash '#' are session specific, so can created in every session. Global ones, with two dashes '##' are global created. Every user sees them and can manipulate (or even drop) them.

So I guess any other process dropped your global temporary table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thank you for the info. I am aware of the difference between the global-specific temp table vs. the session-specific temp table. In fact, I chose to create the global-specific temp table so that I can reference the table after the EXEC(@.SQL) statement runs.|||

It is hard to tell what might be wrong with the code without seeing some sort of repro script. Note that if there are multiple references to a global temporary table for example, SQL Server will delete it automatically after all users referencing the table have disconnected from the server. This can lead to unpredictable behavior if you use multiple connections but doesn't seem to be the case here. In any case, you can modify the code to use just a temporary table instead by doing following:

create table #.... (

columns except the identity column

)

exec ('alter table # add ... identity column with seed')

insert into #...

select * from #...

This approach doesn't require any dynamic SQL except for the few DDLs and it is easier to read/debug also.

|||

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."

Problem of optimization in complex stored procedure

Hello,

I have a procedure stored in my application which takes a very very long time to execute.

It deals with the relations of compositon of a product (a computer is made up of a screen, a keyboard and electronic components).

The subject is about component/ sub component relations (article or product breakdown) also called non hierachical relationships.

The products have a code composed of 22 characters (ARTICLE_CODE) but the links in the breakdown between articles are builted on the first 12 characters (ROOT_ARTICLE_CODE).

So in the relationship table we can find an article (V_ROOT_PART_NUMBER_CODE) associated with several articles (N_ARTICLE_FATHER_ID) because they share the 12 first characters

Example

N_ARTICLE_ID;N_FATHER_ID;N_DATASOURCE_ID;N_SOURCE_ID;V_ROOT_PART_NUMBER_CODE;V_NODE_LABEL;B_ACTIVE;V_FATHER_ROOT_PART_NUMBER_CODE;V_FATHER_NODE_LABEL;B_FATHER_ACTIVE

1005;20637;2;14;5M4K85638000;5M4K85638000 - TEST7;1;TK18J1066002;TK18J1066002 - TEST1;1

1005;20638;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67000;A2HG31T67000 - TEST2;1

1005;20639;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67002;A2HG31T67002 - TEST3;1

1005;23946;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD231000;T5D8FD231000 - TEST4;1

1005;24034;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109862;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109876;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109877;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

9560;706;2;12;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;706;2;12;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;706;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;707;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9560;707;2;-6;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9554;707;2;-6;5F4RE5132202;5F4RE5132202 - TEST11;1;5F4RE5145002;5F4RE5145002 - TEST6;1

705;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST12;1;5F4RE5145002;5F4RE5145002 - TEST6;1

18405;707;2;-6;5F4RE5145220;5F4RE5145220 - TEST13;1;5F4RE5145002;5F4RE5145002 - TEST6;1

But sometimes links are missing between identifiers (I have already an existing link between two articles. I have just added an article (new N_ARTICLE_ID) common on the first 12 characters with one of the previous elements.

I must thus create a new link father/ child because of this common element.

I can create several links if the article is linked with seral articles at a component level (the same keyboard can be used in several models of computers)

I make the same procedure for the father (by adding the links with existing child for an article common at 12 characters).

The problem is that it is a very very long procedure.

I have 75000 articles and it is necessary at least 48H for me to manage them.

Could you help me to optimize the code of this stored procedure?

Thank you by advance

Code Snippet

CREATE TABLE [ARTICLE] (
[N_ARTICLE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[V_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[V_PART_NUMBER_LABEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_ARTICLE_CODE_UNIQUE] UNIQUE NONCLUSTERED
(
[V_CODE]
) ON [PRIMARY]
GO

CREATE TABLE [ARTICLE_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL ,
[N_ARTICLE_FATHER_ID] [bigint] NOT NULL ,
[N_DATASOURCE_ID] [bigint] NOT NULL ,
[N_SOURCE_ID] [bigint] NOT NULL ,
[V_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_ACTIVE] [bit] NULL ,
[V_FATHER_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_FATHER_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_FATHER_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID],
[N_ARTICLE_FATHER_ID],
[N_DATASOURCE_ID],
[N_SOURCE_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE] FOREIGN KEY
(
[N_ARTICLE_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE1] FOREIGN KEY
(
[N_ARTICLE_FATHER_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_SOURCE] FOREIGN KEY
(
[N_SOURCE_ID]
) REFERENCES [SOURCE] (
[N_SOURCE_ID]
)
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Code Snippet

CREATE PROCEDURE [dbo].[LOAD_ARTICLE_BREAKDOWN_PACKAGE]
@.ARTICLE_ID AS BIGINT= NULL,@.ARTICLE_FATHER_ID AS BIGINT= NULL, @.DATASOURCE AS BIGINT= NULL, @.SOURCE AS BIGINT= NULL
AS
BEGIN

SET NOCOUNT ON;
If @.ARTICLE_ID IS NOT NULL AND @.ARTICLE_FATHER_ID IS NOT NULL AND @.DATASOURCE IS NOT NULL AND @.SOURCE IS NOT NULL
BEGIN
/*Table pour mettre tous les pn fils
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

/*Table pour mettre tous les pn père
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

Declare @.LABEL1 as CHAR(50)
Declare @.SPARE1 as BIT
Declare @.LABEL2 as CHAR(50)
Declare @.SPARE2 as BIT
Declare @.ARTICLEC as BIGINT
Declare @.ARTICLEF as BIGINT
/*information sur le premier PN*/

SET @.LABEL1 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE1 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))))

/*information sur le second PN*/

SET @.LABEL2 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE2 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))))
/*recherche les articles communs a 12 dans la première boucle pour le fils*/
/*recherche les articles communs a 12 dans la seconde boucle pour le père*/
DELETE FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=-6) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=@.SOURCE) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, @.SOURCE AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLE_FATHER_ID) AND (PN1.N_ARTICLE_ID = @.ARTICLE_ID);
END
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
BEGIN
SET @.ARTICLEC = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
BEGIN
SET @.ARTICLEF = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_ARTICLE_ID = @.ARTICLEC) AND (N_ARTICLE_FATHER_ID = @.ARTICLEF)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, -6 AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLEF) AND (PN1.N_ARTICLE_ID = @.ARTICLEC);
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEF
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEC
END
END

/*if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Comment ca va Rabiwan?

Bon, je ne suis pas sur de pouvoir t'aider facilement. Continuons ca en english.

I can try to give a couple clues.

You use quite a few sub-queries. They are slow

You use quite a few DELETE statement, they are slow

Indexing the temp tables may or may not help, it takes time to index them and it is not sure that query plan will use these indexes.

I guess you should have a second look at the entire solution and see if you could have any use of recursive CTE's

Philippe

|||

Bonjour philippe,

ca va, ca va merci mais mieux quand ce problème sera arrang.

ok for the sub queries I will find a solution for that with an denormalization of the column V_CODE for have a new column at 12 characters.

For the delete I can set a new field in my tempory table and do an update when the data is processing.

The update is less consuming?

I use SQL SERVER 2000 SP4, So I think there is not CTE.

And for finish the index is only on primary key. But when I delete an element in the tempory table, the index is reload?

Rabbiwan

Problem of optimization in complex stored procedure

Hello,

I have a procedure stored in my application which takes a very very long time to execute.

It deals with the relations of compositon of a product (a computer is made up of a screen, a keyboard and electronic components).

The subject is about component/ sub component relations (article or product breakdown) also called non hierachical relationships.

The products have a code composed of 22 characters (ARTICLE_CODE) but the links in the breakdown between articles are builted on the first 12 characters (ROOT_ARTICLE_CODE).

So in the relationship table we can find an article (V_ROOT_PART_NUMBER_CODE) associated with several articles (N_ARTICLE_FATHER_ID) because they share the 12 first characters

Example

N_ARTICLE_ID;N_FATHER_ID;N_DATASOURCE_ID;N_SOURCE_ID;V_ROOT_PART_NUMBER_CODE;V_NODE_LABEL;B_ACTIVE;V_FATHER_ROOT_PART_NUMBER_CODE;V_FATHER_NODE_LABEL;B_FATHER_ACTIVE

1005;20637;2;14;5M4K85638000;5M4K85638000 - TEST7;1;TK18J1066002;TK18J1066002 - TEST1;1

1005;20638;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67000;A2HG31T67000 - TEST2;1

1005;20639;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67002;A2HG31T67002 - TEST3;1

1005;23946;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD231000;T5D8FD231000 - TEST4;1

1005;24034;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109862;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109876;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

1005;109877;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1

9560;706;2;12;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;706;2;12;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;706;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

14049;707;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9550;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9560;707;2;-6;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1

9554;707;2;-6;5F4RE5132202;5F4RE5132202 - TEST11;1;5F4RE5145002;5F4RE5145002 - TEST6;1

705;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST12;1;5F4RE5145002;5F4RE5145002 - TEST6;1

18405;707;2;-6;5F4RE5145220;5F4RE5145220 - TEST13;1;5F4RE5145002;5F4RE5145002 - TEST6;1

But sometimes links are missing between identifiers (I have already an existing link between two articles. I have just added an article (new N_ARTICLE_ID) common on the first 12 characters with one of the previous elements.

I must thus create a new link father/ child because of this common element.

I can create several links if the article is linked with seral articles at a component level (the same keyboard can be used in several models of computers)

I make the same procedure for the father (by adding the links with existing child for an article common at 12 characters).

The problem is that it is a very very long procedure.

I have 75000 articles and it is necessary at least 48H for me to manage them.

Could you help me to optimize the code of this stored procedure?

Thank you by advance

Code Snippet

CREATE TABLE [ARTICLE] (
[N_ARTICLE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[V_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[V_PART_NUMBER_LABEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_ARTICLE_CODE_UNIQUE] UNIQUE NONCLUSTERED
(
[V_CODE]
) ON [PRIMARY]
GO

CREATE TABLE [ARTICLE_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL ,
[N_ARTICLE_FATHER_ID] [bigint] NOT NULL ,
[N_DATASOURCE_ID] [bigint] NOT NULL ,
[N_SOURCE_ID] [bigint] NOT NULL ,
[V_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_ACTIVE] [bit] NULL ,
[V_FATHER_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_FATHER_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_FATHER_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID],
[N_ARTICLE_FATHER_ID],
[N_DATASOURCE_ID],
[N_SOURCE_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE] FOREIGN KEY
(
[N_ARTICLE_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE1] FOREIGN KEY
(
[N_ARTICLE_FATHER_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_SOURCE] FOREIGN KEY
(
[N_SOURCE_ID]
) REFERENCES [SOURCE] (
[N_SOURCE_ID]
)
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Code Snippet

CREATE PROCEDURE [dbo].[LOAD_ARTICLE_BREAKDOWN_PACKAGE]
@.ARTICLE_ID AS BIGINT= NULL,@.ARTICLE_FATHER_ID AS BIGINT= NULL, @.DATASOURCE AS BIGINT= NULL, @.SOURCE AS BIGINT= NULL
AS
BEGIN

SET NOCOUNT ON;
If @.ARTICLE_ID IS NOT NULL AND @.ARTICLE_FATHER_ID IS NOT NULL AND @.DATASOURCE IS NOT NULL AND @.SOURCE IS NOT NULL
BEGIN
/*Table pour mettre tous les pn fils
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

/*Table pour mettre tous les pn père
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/

CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)

ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]

Declare @.LABEL1 as CHAR(50)
Declare @.SPARE1 as BIT
Declare @.LABEL2 as CHAR(50)
Declare @.SPARE2 as BIT
Declare @.ARTICLEC as BIGINT
Declare @.ARTICLEF as BIGINT
/*information sur le premier PN*/

SET @.LABEL1 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE1 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))))

/*information sur le second PN*/

SET @.LABEL2 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)

SET @.SPARE2 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))))
/*recherche les articles communs a 12 dans la première boucle pour le fils*/
/*recherche les articles communs a 12 dans la seconde boucle pour le père*/
DELETE FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=-6) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=@.SOURCE) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, @.SOURCE AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLE_FATHER_ID) AND (PN1.N_ARTICLE_ID = @.ARTICLE_ID);
END
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
BEGIN
SET @.ARTICLEC = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))

WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
BEGIN
SET @.ARTICLEF = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_ARTICLE_ID = @.ARTICLEC) AND (N_ARTICLE_FATHER_ID = @.ARTICLEF)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, -6 AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLEF) AND (PN1.N_ARTICLE_ID = @.ARTICLEC);
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEF
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEC
END
END

/*if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Comment ca va Rabiwan?

Bon, je ne suis pas sur de pouvoir t'aider facilement. Continuons ca en english.

I can try to give a couple clues.

You use quite a few sub-queries. They are slow

You use quite a few DELETE statement, they are slow

Indexing the temp tables may or may not help, it takes time to index them and it is not sure that query plan will use these indexes.

I guess you should have a second look at the entire solution and see if you could have any use of recursive CTE's

Philippe

|||

Bonjour philippe,

ca va, ca va merci mais mieux quand ce problème sera arrang.

ok for the sub queries I will find a solution for that with an denormalization of the column V_CODE for have a new column at 12 characters.

For the delete I can set a new field in my tempory table and do an update when the data is processing.

The update is less consuming?

I use SQL SERVER 2000 SP4, So I think there is not CTE.

And for finish the index is only on primary key. But when I delete an element in the tempory table, the index is reload?

Rabbiwan

problem of connection with sqlserver2005

Hello,

I installed sqlserver2005_express on my PC (localhost) and I try to execute a sql query with ASP.NET.

Here's my page :
Dim mySqlConnection as SqlConnection = new SqlConnection("server=JOE10155\SQLEXPRESS;Trusted_Connection=yes;UID=sa;PWD=xxxx;database=xxxx")
Dim mySqlDataAdapter as SqlDataAdapter = new SqlDataAdapter("SELECT * FROM Entreprise WHERE (EntrepriseNr =999)", mySqlConnection)
Dim myDataSet as DataSet = new DataSet()
mySqlDataAdapter.Fill(myDataSet,"Entreprise")

The problem is :
System.Data.SqlClient.SqlException: Autorisation SELECT refusée sur l'objet 'Entreprise', base de données 'xxxx', schéma dbo'.

if someone have an idea?

Thanks

PS : I use Microsoft SQL Server Management Studio Express.

Hi,

that is French? :-)

I'm not good with it, but sounds as if the user who you connect as (give the credentiuals into connection string) wouldn't have permissions to SELECT fromEntreprise table

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:

> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...[vbcol=seagreen]
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>|||Did you specify an output file for the job step and check for messages in th
at output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmai
l should return a return
code, also accompanied by an error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. Yo
u
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:

> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>
>

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:
> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>|||Did you specify an output file for the job step and check for messages in that output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmail should return a return
code, also accompanied by an error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>> Hi Jesus
>> You don't say how you are sending the emails, but if the procedure does not
>> raise an error and only returns an status code then you would need to check
>> this status code asnd raise your own error to make the step fail.
>> HTH
>> John
>> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. You
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> > Hi Jesus
> >
> > You don't say how you are sending the emails, but if the procedure does
> > not
> > raise an error and only returns an status code then you would need to
> > check
> > this status code asnd raise your own error to make the step fail.
> >
> > HTH
> >
> > John
> >
> > "Jesus Suarez" wrote:
> >
> >> hello people,
> >>
> >> i have a stored procedure that send mails masive to users, this stored
> >> procedures succesfully execute manually, but programming job for execute
> >> this stored procedurre not send mail, but histoyr job its succesfully.
> >>
> >> what its happening?
> >>
> >>
> >>
>
>