How to move data accross servers and databases

1

Hi,
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
itm

2

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.

3

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

4

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

5

Thanks that is an idea.