Thank you guys for responding.
I am having more and more trouble:
To do an experiment, I created a temp table, and tried to inner join that table. Unfortunately that sp is on a linked server, it seems there is some restriction on getting data from a linked server, here is the error I encountered:
create table #t(observanceid int, observancetime datetime, observancetype int)
insert #t(observanceid, observancetime, observancetype) exec [MyServer].MyDB.dbo.listdata
Error1: MSDTC on server 'MyServer' is unavailable
Following a suggestion from http://geekswithblogs.net/narent/archive/2006/10/09/93544.aspx
I got error 2:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
What the hell is going on? How can I resolve this issue?
All I need is:
I need to do some data analysis on a production server, so I don't have write permission to create sp directly on that server. Instead, what I am thinking is:
1. link the server to my shell db/server
2. write sp on my shell db/server
I have new tables created on my shell db/server, which I will need to join with data result returned from the sp on the linked server.
Or, considering the frequency of polling data, is there a way to create a kind of "back up" or view on shell db/server using that stored procedure on the linked server?
That's all my tasks.
Thank you very much.