Red Gate forums :: View topic - Error - F1 F1 F1 F1 F1 - Help
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Error - F1 F1 F1 F1 F1 - Help

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
reza.ariyan



Joined: 28 Sep 2009
Posts: 4

PostPosted: Mon Sep 28, 2009 9:33 pm    Post subject: Error - F1 F1 F1 F1 F1 - Help Reply with quote

When im trying to restore my backup (SQLPackage2.exe) this error occur :
***************************************************
Ambiguous column name 'ID'.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[fn_SynchronizeColumn]
(
@ColumnsName_SplitedByComma NVARCHAR(MAX),
@TableName NVARCHAR(120)
)
RETURNS TABLE
AS RETURN
( SELECT [ID],[ColumnName]
FROM dbo.fn_GetColumns(@TableName)
INNER JOIN ( SELECT *
FROM dbo.fn_GetSplited(@ColumnsName_SplitedByComma)
) AS temp ON [Value] = [ColumnName]
)
***************************************************

i was created a fn_GetColumns manually :


Code:
CREATE FUNCTION [dbo].[fn_GetColumns] ( @Table NVARCHAR(120) )
RETURNS @ColumnsList TABLE
    (
      [ID] INT IDENTITY
               NOT NULL,
      [TableName] [nvarchar](50) NOT NULL,
      [ColumnName] [nvarchar](50) NOT NULL,
      [IsIdentity] [bit] NOT NULL,
      [IsPrimary] [bit] NOT NULL
    )
AS BEGIN
    DECLARE @TableName NVARCHAR(120) ;
    DECLARE @ColumnName NVARCHAR(50) ;
    DECLARE @IsIdentity BIT ;
    DECLARE @PrimaryColumnName NVARCHAR(50) ;
    DECLARE @IsPrimary BIT ;
    DECLARE crsFI CURSOR
        FOR ( SELECT DISTINCT
                        sys.tables.name AS TableName,
                        sys.all_columns.name AS Columns,
                        sys.all_columns.is_identity AS IsIdentity,
                        dbo.vw_PK_Columns.PKColumn
              FROM      sys.tables
                        INNER JOIN sys.all_columns ON sys.tables.object_id = sys.all_columns.object_id
                        LEFT OUTER JOIN dbo.vw_PK_Columns ON sys.all_columns.name = dbo.vw_PK_Columns.PKColumn
              WHERE     sys.tables.name = @Table
            )
    OPEN crsFI
    FETCH NEXT FROM crsFI INTO @TableName, @ColumnName, @IsIdentity,
        @PrimaryColumnName
    WHILE  @@FETCH_STATUS = 0
        BEGIN
            IF @PrimaryColumnName = @ColumnName
                SET @IsPrimary = 1
            ELSE
                SET @IsPrimary = 0
            INSERT  @ColumnsList
            VALUES  (
                      @TableName,
                      @ColumnName,
                      @IsIdentity,
                      @IsPrimary
                               
                    )
            FETCH NEXT FROM crsFI INTO @TableName, @ColumnName, @IsIdentity,
                @PrimaryColumnName   
        END         
    CLOSE crsFI
    DEALLOCATE crsFI
    RETURN
   END


but this error occur :
******************************************
There is already an object named 'fn_GetColumns' in the database.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[fn_GetColumns] ( @Table NVARCHAR(120) )
RETURNS @ColumnsList TABLE
(
[ID] INT IDENTITY
NOT NULL,
[TableName] [nvarchar](50) NOT NULL,
[ColumnName] [nvarchar](50) NOT NULL,
[IsIdentity] [bit] NOT NULL,
[IsPrimary] [bit] NOT NULL
)
AS BEGIN
DECLARE @TableName NVARCHAR(120) ;
DECLARE @ColumnName NVARCHAR(50) ;
DECLARE @IsIdentity BIT ;
DECLARE @PrimaryColumnName NVARCHAR(50) ;
DECLARE @IsPrimary BIT ;
DECLARE crsFI CURSOR
FOR ( SELECT DISTINCT
sys.tables.name AS TableName,
sys.all_columns.name AS Columns,
sys.all_columns.is_identity AS IsIdentity,
dbo.vw_PK_Columns.PKColumn
FROM sys.tables
INNER JOIN sys.all_columns ON sys.tables.object_id = sys.all_columns.object_id
LEFT OUTER JOIN dbo.vw_PK_Columns ON sys.all_columns.name = dbo.vw_PK_Columns.PKColumn
WHERE sys.tables.name = @Table
)
OPEN crsFI
FETCH NEXT FROM crsFI INTO @TableName, @ColumnName, @IsIdentity,
@PrimaryColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PrimaryColumnName = @ColumnName
SET @IsPrimary = 1
ELSE
SET @IsPrimary = 0
INSERT @ColumnsList
VALUES (
@TableName,
@ColumnName,
@IsIdentity,
@IsPrimary

)
FETCH NEXT FROM crsFI INTO @TableName, @ColumnName, @IsIdentity,
@PrimaryColumnName
END
CLOSE crsFI
DEALLOCATE crsFI
RETURN
END
****************************************** Embarassed
Back to top
View user's profile Send private message
reza.ariyan



Joined: 28 Sep 2009
Posts: 4

PostPosted: Mon Sep 28, 2009 9:38 pm    Post subject: Reply with quote

http://www.megaupload.com/?d=Z14FTQDS
This is my SQLPackage
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Wed Sep 30, 2009 4:01 pm    Post subject: Reply with quote

At a glance, I'd say that both fn_GetColumns and fn_GetSplited return a column called ID. You probably have to qualify the column in the query inside the fn_SynchronizeColumn function.

This function probably ended up intact because either fn_GetColumns or fn_GetSplited has a column called ID added after fn_SynchronizeColumn was created.

You'll probably have to modify the function so that it qualifies the ID column, for instance:
Code:
CREATE FUNCTION [dbo].[fn_SynchronizeColumn]
    (
      @ColumnsName_SplitedByComma NVARCHAR(MAX),
      @TableName NVARCHAR(120)
    )
RETURNS TABLE
AS RETURN
    ( SELECT    [temp].[ID],[ColumnName]
      FROM      dbo.fn_GetColumns(@TableName)
                INNER JOIN ( SELECT *
                             FROM   dbo.fn_GetSplited(@ColumnsName_SplitedByComma)
                           ) AS temp ON [Value] = [ColumnName]
    )
Back to top
View user's profile Send private message
reza.ariyan



Joined: 28 Sep 2009
Posts: 4

PostPosted: Sat Oct 03, 2009 8:42 am    Post subject: Reply with quote

i cant restore it
How can i resolve its problem ...


Last edited by reza.ariyan on Sat Oct 03, 2009 8:52 am; edited 1 time in total
Back to top
View user's profile Send private message
reza.ariyan



Joined: 28 Sep 2009
Posts: 4

PostPosted: Sat Oct 03, 2009 8:50 am    Post subject: Reply with quote

is it possible to edit sqlpakage executable file ? however i was unpacked it with .netReflector .
it's included 2 Compressed resource file
how can i decompress this resource files ?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Sat Oct 03, 2009 11:56 am    Post subject: Reply with quote

That's a toughie... .NET assemblies are not really designed to be "edited", for security and integrity reasons. I could see possibly disassembling and recompiling the assembly. You could, for instance, use Microsoft's ILDasm tool to extract the IL and resources, try WinZip to decompress the resource files, edit them, and recompile the whole thing using ilasm.

http://bettereducation.com.au/it/yaf_postst449_Decompile-a-NET-assembly-using-ildasmexe-and-edit-and-recompile-it-using-ilasmexe.aspx

Because the package code set the "compression" flag, you would probably have to zip the resources again before compiling, but I'm unsure whether Packager's "zlib.dll" can decompress WinZipped files. If you're really, really stuck and can't repackage, then this is the only way I can think of.

Your best option would be to modify the procedure in the database and repackage the whole database.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group