May 4, 2004 at 8:17 am
I have a problem using DTS with UDF object (under SQLServer 2000 sp3).
Attempt to transfer data from a database to another one using DTS fails if you use UDF that depends on a view. It seems to transfer objects in the wrong order.
Checking dependences, all seem to be ok.
Do someone know how to manage it ?
Many thanks for your help.
May 7, 2004 at 8:00 am
This was removed by the editor as SPAM
May 10, 2004 at 6:08 am
Can you sample it?
PSS ID: 300272
http://support.microsoft.com/default.aspx?scid=kb;en-us;300272
discribes a similar case, it was fixed under sp1.
May 10, 2004 at 7:10 am
First of all, thanks to wz700 for this answer, it gives me an hope!
I sow this article but I'm working under SQL2K sp3 and I find my situation a bit different from which the describe in it.
I try to explain you better my case:
MY TABLE:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_ditta]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb_ditta]
GO
CREATE TABLE [dbo].[tb_ditta] (
[cx_c_ditta] [int] NULL ,
[cx_d_nome] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[tb_ditta] TO [public]
GO
MY VIEW:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_ditta]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_ditta]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_ditta
AS
SELECT cx_c_ditta, cx_d_nome
FROM tb_ditta
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[vw_ditta] TO [public]
GO
MY UDF:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DG_fn_Ditta]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DG_fn_Ditta]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.DG_fn_Ditta ()
RETURNS table
AS
return (
select cx_c_ditta, cx_d_nome from vw_ditta
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[DG_fn_Ditta] TO [public]
GO
When I try to tranfer to a different SQL2K sp3 I got some error and some warnings:
From Source LogFile
::Warning::
Transfer Status: Scripting dependent object drops
The object 'dbo.DG_fn_Ditta' not being transfered is dependent on the table 'dbo.tb_ditta' being transfered.
From Destination LogFile
::Error::
In file W2K.TransferObj_Source.UDF in statement on line 6
Transfer Status: Creating User Defined Functions on destination database
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'vw_ditta'.
::Error::
In file W2K.TransferObj_Source.UDF in statement on line 41
Transfer Status: Creating User Defined Functions on destination database
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.DG_fn_Ditta'.
...and this is my UDF error file:
(line 6)
/****** Object: User Defined Function dbo.DG_fn_Ditta Script Date: 10/05/2004 14.34.54 ******/
CREATE FUNCTION dbo.DG_fn_Ditta ()
RETURNS table
AS
return (
select cx_c_ditta, cx_d_nome from vw_ditta
)
GO
(line 41)
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[DG_fn_Ditta] TO [public]
GO
Many thanks in advance for any ideas you can give me!
May 11, 2004 at 4:01 am
It works for me.
Here are steps for verification:
Create table, view, function as described on source server.
Create DTS package on source server.
define source and destination server connections (both sql server sp3).
define transfer data task fro source to destination.
On Transform data task properties, source tab, click SQL query: as select * from DG_fn_Ditta().
On Destination tab, create a table by click on Create button.
on Transformations tab, define tranfer type as Copy column.
Run it, on destination server, select from the table.
May 11, 2004 at 4:39 am
Yes, I agree with you BUT I'm using a "Copy SQL Server Objects Task" , not a simple Data Transfer Object. Using this task, if I add a new table/object it's automatically copied in destination database without modifying my DTS.
Can you try in this way?
Thanks a lot...
May 12, 2004 at 4:33 am
Not work, even workaround described in article 300272 did not work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply