reza.ariyan
Joined: 28 Sep 2009
Posts: 4
|
 Posted: Mon Sep 28, 2009 9:33 pm Post subject: Error - F1 F1 F1 F1 F1 - Help |
 |
|
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
******************************************  |
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Wed Sep 30, 2009 4:01 pm Post subject: |
|
|
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]
) |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Sat Oct 03, 2009 11:56 am Post subject: |
|
|
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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
