Error - F1 F1 F1 F1 F1 - Help

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Error - F1 F1 F1 F1 F1 - Help

Postby reza.ariyan » Mon Sep 28, 2009 9:33 pm

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: Select all
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
 
Posts: 4
Joined: Mon Sep 28, 2009 9:11 pm

Postby reza.ariyan » Mon Sep 28, 2009 9:38 pm

reza.ariyan
 
Posts: 4
Joined: Mon Sep 28, 2009 9:11 pm

Postby Brian Donahue » Wed Sep 30, 2009 4:01 pm

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: Select all
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]
    )
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby reza.ariyan » Sat Oct 03, 2009 8:42 am

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.
reza.ariyan
 
Posts: 4
Joined: Mon Sep 28, 2009 9:11 pm

Postby reza.ariyan » Sat Oct 03, 2009 8:50 am

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 ?
reza.ariyan
 
Posts: 4
Joined: Mon Sep 28, 2009 9:11 pm

Postby Brian Donahue » Sat Oct 03, 2009 11:56 am

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests