dump this into a table variable

You mean you want to put those variables into a table or the output from executing the procedure?  You can create a Table1

yes, i know i can create a table -- be it temp, or perm, or variable --- i'm having a problem w/the insert into said table -- syntactically --- because the declaration of @begin and @end is part of the execution.

my inquiry should have been labeled differently.   i just want to dump the results into a table,
and i'm having problems getting around this declaration, when doing so:

declare @begin datetime, @end datetime
set @begin=CONVERT(VARCHAR(50),GETDATE(),101) + ' 00:00'  set @end=DATEADD(mi, 1, GETDATE())


but.  the declaration is required.

embarrassing that it is that simple... thanks, chap.  now, however, w/the attempt to write to said table, it's puking w/this:

Server: Msg 7391, Level 16, State 1, Line 1
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].

@sourceserver and @destinationserver are linked.  always have been.  

try this:

BEGIN DISTRIBUTED TRAN

declare @begin datetime, @end datetime
set @begin=CONVERT(VARCHAR(50),GETDATE(),101) + ' 00:00'  set @end=DATEADD(mi, 1, GETDATE())

insert into tablename(field1, field2, field3...)   --all you have to do is this.  You can insert into a table using the results of a stored proc

exec procedurename @sourceserver='server2',@sourcedb='database2',@destinationserver='server1',@destinationdb='database1',@endpoint='XXXX',@start=@begin,@stop=@end,@flag='Y'

COMMIT DISTRIBUTED TRAN

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'DISTRIBUTED'.


possibly you've gotten something else up there, but aren't you just wrapping it in a begin/commit on the distributed tran?

i pulled 'DISTRIBUTED' out of the commit.  syntax is fine now, but...

Server: Msg 7391, Level 16, State 1, Line 1
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].

MSDTC is on, both boxes.  I thought it may have something to do w/the service, so i stopped/restarted it, and now i get this:  

Server: Msg 8501, Level 16, State 3, Line 1
MSDTC on server 'SERVERNAME' is unavailable.

i've checked, it's on.  this is getting better and better.....

so...this is probably a new question....

well, i'll open another, if you'd like, chap.  source problem is the same --- dump proc results into table.  as i'm trying to debug that, i've now got the MSDTC error

...which is a different error related to your proc dump....the msdtc issue is a setting somewhere.  Im not able to look it up right now

MSDTC problem corrected, chap.  back to the Msg 7391 when attempting to dump proc into table

any ideas on dumping the proc results to the table, chap?  i'm still failing w/this:

Server: Msg 7391, Level 16, State 1, Line 1
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].


yet the procedure, without insertion into the table, completes just fine.

this is terribly important, any insight at all is hugely appreciated.
my execution is attached

declare @begin datetime, @end datetime 
set @begin=CONVERT(VARCHAR(50),GETDATE(),101) + ' 00:00'  
set @end=DATEADD(mi, 1, GETDATE()) 
INSERT table
exec procedure @sourceserver='server1',@sourcedb='database1',@destinationserver='server2',@destinationdb='database2',@endpoint='XXX',@start=@begin,@stop=@end,@flag='Y'

Select allOpen in new window

also, same failure when i preface with 'begin tran', and close with 'commit tran'

http://geekswithblogs.net/sureshg/archive/2006/02/14/69456.aspx

I was looking a numerous similar posts last week, and over the weekend.  One thing that puzzles me:

Start the Component Services administrative tool. To do this, click Start, click Run, type dcomcnfg.exe, and then click OK.
In the console tree of the Component Services administrative tool, expand Component Services, expand Computers, right-click My Computer, and then click Properties.
Click the MSDTC tab, and then click Security Configuration.

In component svcs/computers/msdtc tab -- i don't have a 'Security Configuration'
it's just four chunks:  default coordinator, log information, client network protocol info, and service control status for MSDTS (version 03.00.00.3511)

that is on one of the boxes.  the other one, however, has it.  that one is MSDTC version 5.2.3790.1830

Is this a conflict in the varying versions of MSDTC ?
very odd.  why wouldn't i be feeling this elsewhere

what do you think, chap?
also, what do you think about changing it from 'mutual authentication' to 'no authentication required', per the post in http://geekswithblogs.net/sureshg/archive/2006/02/14/69456.aspx, here:


SP1 changed the settings for MSDTC under Component Services. The installation of SP1
- implicit value is "Mutual authentication required". We changed from "Mutual authentication
 required" to "No Authentication Required" then everything works fine.

>>also, what do you think about changing it from 'mutual authentication' to 'no authentication required', per the post in http://geekswithblogs.net/sureshg/archive/2006/02/14/69456.aspx,

I would try it.

i changed it to 'no authentication required' on the one box where that is available.
similar failure, but not the same:

Server: Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.  A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013:  ISOLEVEL=4096].

got it.  please tell me what you think, and why you feel this is needed, chap.  if you can

begin
set xact_abort on
declare @begin datetime, @end datetime 
set @begin=CONVERT(VARCHAR(50),GETDATE(),101) + ' 00:00'  
set @end=DATEADD(mi, 1, GETDATE()) 
INSERT table (fields.........)
exec procedure @sourceserver='server1',@sourcedb='database1',@destinationserver='server2',@destinationdb='database2',@endpoint='XXX',@start=@begin,@stop=@end,@flag='Y' 
set xact_abort off
end

Select allOpen in new window

very, very interesting.  i am anxious to hear what you think

did it work?

yessir.  sorry... that's what i meant when i said 'got it'

Good deal.  Some sort of funky transaction thing I guess.  At least it works now!

curious.... but agreed.  at least it's working now
thank you much for all of your input, chap

hey chap, are you around?

disregard, chap.  all good