Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel

deny permission to create temporary tables

Dear All,

This is my first post to this forum.

I would like to know if there is any way to restrict users from creating temp tables.

Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.

Thanks in advance.

Best Regards,

Chetan Jain

Just by executing a query, users may be using space in TempDb -that is what it is designed for. Query execution may, totally on its own volition, create temporary tables in TempDb. TempDb can growth large if the users are executing queries that require a lot of temporary storage to gather data to work with -JOINs with large resultsets, large resultsets to be sorted, etc.

Are the users creating tables 'temp' tables (starting with [#], or [##]? Or, are they creating tables?

Normally, the users' temp objects are removed from TempDb when the user connection is closed.

Perhaps the real issue is trying to determine how much space TempDb requires in order to support your users query needs, and then giving TempDb adaquate disk space.

|||

You can not stop any user from creating temprory objects in tempdb. ofcourse you can stop creating permanent table in tempdb by DDL trigger. but DDL trigger can not sense temp table.

create TRIGGER db_trg_RestrictTableChanges

ON DATABASE

FOR CREATE_Table, ALTER_Table, DROP_Table

AS

SET NOCOUNT ON

rollback

the above mentioned trigger will stop creating permanent tables in tempdb but even this can not stop temporary table

Madhu

|||Madhu's suggestion is certainly a valid one, but, unfortunately, only works in SQL 2005 (and higher).|||

Thanks for the information!. The real issue is denying explicit statements like "create table #temp" or "create table ##temp"

Best Regards,

Chetan

|||

As Madhu indicated, you can't even deny creating temp tables in SQL 2005 using the new DDL Triggers -and you also can't do so in SQL 2000.

Just make sure that the users are logging out, and then their connection will be cleared, and the space used for any temp tables will be released.