SQLTeam.com | Weblogs | Forums

How to move data accross servers and databases


I have a unique problem. Where I am working I am not allowed to use link servers or any of the XP command shell scripts; of for that matter SSIS. What I am doing is getting a list of accounts on one server (in one database) and copying them pasting them into the IN statement of a script that I made. I then connect to another server and run the script in there. I then have to copy the results and put it in a Excel and import it in the other database on the other server.
U just found that that I can use SQL CMD and :CONNECT to connect to the other server and that works great. That is if I past these accounts in here it will work. I would like a way to pull the accounts in the script some how. I tired temp table, table variables, and CTEs, but they did not work.
Any ideas of what I can do would be really appreciated.
Thank you


If they wont let you use linked servers will they let you use openrowset? How about CLR procedures or table valued functions?
You could link tables in access. You could write a .net app to make separate connections to each server/database, especially if this is a recurring task.


Hi first thanks for the reply. Now I do not have much of any thing unfortunately. They will not allow Link servers, or openrowset. I could use MS Access., but would not be allowed to pass dat to there through Access. Unfortunately I do not know how to use that, if they will Delete repeated word me to use it. What would work would be if I can pass the data in through something like that Table variables or CTEs, but unfortunately they will not work; unless I am doing something wrong. I know that this is very limited, it is crazy, but this is what I got to work with. U mentioned a table valued functions, I can use that but I cannot put it on the other server. So if there is a way to make that work that would be great.
Thank you


Try using ADO.net and powershell. You should be able to search for some examples.


Thanks that is an idea.