March 6, 2008 at 5:02 am
HI..., I Wrote Sql Server Store procedure to Copy Table From Server to Client.The Code Running Success... But the problem is new Table structure (Colum data Type..etc) is not same with the old source table... below is my store procedure code... is any body can help me to solve my problem..
thanks..
sory my english not good
STORE PROCEDURE :
--======USAGE==========
--declare @dbProvider char(20)
--set @dbProvider =substring('SQLOLEDB',1,LEN('SQLOLEDB'))
--EXEC REMOTE_COPYTABLE
--@dbProvider ,
--'Data Source=ANDRYCOM;User ID=SA;Password=NFN1507',
--'TRAN_PARK'
--,'PARKSYS_SERVER.dbo.TRAN_PARK'
--======USAGE==========
CREATE PROCEDURE REMOTE_COPYTABLE
@dbProvider char(8),
@RemoteData char(50),
@DestTable SYSNAME,
@SrcTable char(50)
AS
Set dateformat dmy
Declare @errorcode int
Declare @sSQL Char(255)
--===================copy table remote client sql server=======================================\
if exists (select NAME from parksys_server.dbo.SYSOBJECTS where NAME =@DestTable )
set @sSQL='DROP TABLE '+ @DestTable
set @sSQL=substring(@sSQL,1,LEN(@sSQL))
exec (@sSQL)
set @dbProvider =substring(@dbProvider,1,LEN(@dbProvider))
set @SrcTable=substring(@SrcTable,1,LEN(@SrcTable))
set @sSQL=N'SELECT * INTO '+@DestTable+' FROM OPENDATASOURCE('''+RTRIM(@dbProvider)+''',''' +substring(@RemoteData,1,LEN(@RemoteData))+ ''').'+@SrcTable
set @sSQL=RTRIM(substring(@sSQL,1,LEN(@sSQL)))
--print @sSQL
exec (@sSQL)
--===================copy table remote client sql server=======================================/
SET @errorcode=@@ERROR
IF (@ErrorCode <> 0)
BEGIN
RETURN (-@ErrorCode)
END
RETURN (0)
GO
March 6, 2008 at 2:26 pm
Right click the desired table to Generate SQL Script ...;
Include all necessary under the three tabs;
You will get it!:)
But I do not know why you post your question in Replication.
March 11, 2008 at 8:57 pm
Thanks For The Replay..., But That's Not Solve My Problem..
Sorry... mybe i don't make my self clear... what i mean is i try to make a Store Procedure that can make a cloning a table (any table)
from one PC1 to Other PC2...,The code work fine... but The new table structure (Column Properties) especialy for identity, not match with the source table...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply