Best method to transfer data from transaction tables

Question

Post reply

Best method to transfer data from transaction tables

Faye Fouladi

SSCrazy

Points: 2770
More actions
October 15, 2008 at 11:32 am

#127970

Suppose my database consists of many tables some of them are master table that when I copy the database all the rows from these tables need to be brought over. However, from my transaction tables I only need to bring selected rows.
Waht is the best method for doing this if I don't want to build SSIS packages? I don't want to build
SSIS packages because there are too many tables involved.
Faye

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 1

Create a batch file that BCP's out the data from the source and BCP's in the data to the destination???

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 2

How about using openrowset?

First I tried to establish linked server, but I keep getting the message:

SQL Server 2005: “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”.

I searched the internet how to fix this problem with no vail? How can I create linked server withoutout running into the above problem?

I have already executed the followings:

EXEC sp_addlinkedserver @server=’LinkedServer’,

@srvproduct=''”,

@provider='SQLNCLI',

@datasrc=’SQLB’,--the data source

@provstr="Integrated Security=SSPI; "

Although the following does not generate the delegate:

select uses_self_credential as delegation

from sys.linked_logins as L, sys.servers as S

where S.server_id=L.server_id

and S.name=N'LinkedServer'

Thanks for helping

Faye

SC-564817 Old Hand Points: 398 More actions · Answer 3

Hello,

You don't have to use provider string and other stuff If remote server is SQL Server and problem with your linked server sounds like authentication problem.Up to my knowledge SQL Server 2005 does not allow trusted connection anymore.Try the following thing:

Right click Linked servers give the server name and select Server type SQL server in that pane and be sure to give remote login and password in the security panel and select RPC In and Out true in the options panel.

I am providing sample script for linked server which might give you little bit more information If you wanna use scripting to set-up linked server.

EXEC master.dbo.sp_addlinkedserver @server = 'SQLServername, @srvproduct='SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='servername',@useself=N'False',@locallogin=NULL,@rmtuser='remotelogin',@rmtpassword='########'

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 4

Thanks I will try it later today and will let you know if it worked.

Faye

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 5

I ran this code putting the correct server name and a login and a password for the remote access:

EXEC master.dbo.sp_addlinkedserver @server = 'SQLServername, @srvproduct='SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='servername',@useself=N'False',@locallogin=NULL,@rmtuser='remotelogin',@rmtpassword='########'

Then I right clicked on the linked server and refreshed. I clicked the plus sign on catalog and I got the following message:

SQL Server can not connect using the Named Pipes. (mind you I aalso used the configuration tools to allow remote connection via Named pipes as well as TCP. What else do I need to do?

Thanks

Faye

Faye Fouladi SSCrazy Points: 2770 More actions · Answer 6

Actually upon further reading I realised that sql 2005 does not allow connection to linked servers via integrated security. A sql server login needs to be created on both servers and this login needs to be mapped to the login on the remote server and appropriate permissions need to be granted to this login to execute any kind of "Select into" statement on a linked server.

The SQL Server unleased book has an electronic bonus chapter on this topic. I followed the steps outlined on the book and I accomplished the task I was intended to do.

Faye