February 18, 2010 at 2:18 am
Hi,
when I try to create a procedure like
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID(N'[dbo].[newBackupRestore]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.newBackupRestore
GO
CREATE PROCEDURE [dbo].[newBackupRestore] @log_id INT,@restore_type VARCHAR(255),@db_name VARCHAR(255) OUTPUT,@save_path AS VARCHAR(MAX),@zip_path AS VARCHAR(MAX) AS
SET NOCOUNT ON
DECLARE
@helperInt1 AS INT,
@helperInt2 AS INT,
@helperInt3 AS INT,
@helperInt4 AS INT,
@helperInt5 AS INT,
@helperStr1 AS VARCHAR(MAX),
@helperStr2 AS VARCHAR(MAX),
@helperStr3 AS VARCHAR(MAX),
@helperStr4 AS VARCHAR(MAX),
@helperStr5 AS VARCHAR(MAX),
@helperStr6 AS VARCHAR(MAX),
@helperStr8000 AS VARCHAR(8000),
@ptrval AS BINARY(16),
@backup_id AS INT,
@tmp_backup_id AS INT,
@tmp_db_name AS VARCHAR(255),
@snapshot_state INT,
@success INT
CREATE TABLE ##keys(type VARCHAR(20),key_name VARCHAR(255),parent_id INT,parent_type CHAR(2),parent_name VARCHAR(255),parent_owner VARCHAR(255))
CREATE TABLE #restore_table(id INT,type CHAR(2))
[...]
all is fine. When scripting a database with Database Publishing Wizard I get a script like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newBackupRestore]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[newBackupRestore] @log_id INT,@restore_type VARCHAR(255),@db_name VARCHAR(255) OUTPUT,@save_path AS VARCHAR(MAX),@zip_path AS VARCHAR(MAX) AS
SET NOCOUNT ON
DECLARE @helperInt1 AS INT, @helperInt2 AS INT, @helperInt3 AS INT, @helperInt4 AS INT, @helperInt5 AS INT, @helperStr1 AS VARCHAR(MAX), @helperStr2 AS VARCHAR(MAX), @helperStr3 AS VARCHAR(MAX), @helperStr4 AS VARCHAR(MAX), @helperStr5 AS VARCHAR(MAX), @helperStr6 AS VARCHAR(MAX), @helperStr8000 AS VARCHAR(8000), @ptrval AS BINARY(16), @backup_id AS INT, @tmp_backup_id AS INT, @tmp_db_name AS VARCHAR(255), @snapshot_state INT, @user_state VARCHAR(255), @success INT
CREATE TABLE ##keys(type VARCHAR(20),key_name VARCHAR(255),key_owner VARCHAR(255),parent_name VARCHAR(255),parent_owner VARCHAR(255))
CREATE TABLE #table(id BIGINT,type CHAR(2))
[..]
'
END
But when executing the second code in an EXEC statement, it throws "invalid column name 'key_owner'" but the body of this procedure is in both cases the same!?
February 18, 2010 at 3:53 am
I apologise in advance if I am missing the obvious, but doesn't the definition of ##keys in the first code sample include a column called 'key_owner', whereas the second version does not?
Copy and paste error? Old scripting output?
Paul
February 18, 2010 at 4:36 am
OK, thanks, that's really a different version and a copy&paste failure. The script body of the first one should look like the second one.
But this could be the problem. The first posted script is in production and I try to create with this script the second procedure in another database. And you figured it out already, there is a difference between columns in ##keys. So the question: could it be, that the used ##keys table in the second procedure collides with the real production script, which uses an slightly different ##keys table?
Is there a way to prevent this collision? I have to use a global temporary table to use data in some sub procedures called by newBackupRestore or am I wrong?
February 18, 2010 at 5:26 am
Let me be up-front about this: it's very difficult to say what the cause is from the data presented so far.
But, let me have a crack at it anyway.
Yes, it is quite possible for the existing table structure to produce that error.
A possible workaround is to ALTER the global temporary table and add the extra column as NULLable.
Only you have the details to hand to see whether this is safe to do or not, given your existing code base.
The other option is to add to column and run the new code after that inside a transaction, to prevent other code seeing the table in a weird state.
Sorry, I don't normally do ALL CAPS, but that is very important!
Paul
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply