Error - F1 F1 F1 F1 F1 - Help

reza.ariyan

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 :

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
******************************************

:oops:

reza.ariyan

http://www.megaupload.com/?d=Z14FTQDS
This is my SQLPackage

Brian Donahue

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:

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]
    )

reza.ariyan

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

reza.ariyan

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 ?

Brian Donahue

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_po ... smexe.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.