dump this into a table variable

v2000, i have a job which runs this each day across 12 different endpoints.  (basically, a job that loops thru it 12 times)

Under normal conditions, the output of that is exactly one record.  I want/need to pump that into a log table.  Having some problems getting around that declaration of the @begin and @end.  I have to use that, however, in order to pass the appropriate @start and @stop to the proc.

My output is simple.. just 12 fields... one record.  

Any suggestions are appreciated.


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

Open in new window

LVL 17
dbaSQLAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
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'
0
 
Nathan RileyFounderCommented:
You mean you want to put those variables into a table or the output from executing the procedure?  You can create a Table1
0
 
dbaSQLAuthor Commented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dbaSQLAuthor Commented:
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].


0
 
dbaSQLAuthor Commented:
@sourceserver and @destinationserver are linked.  always have been.  
0
 
chapmandewCommented:
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
0
 
dbaSQLAuthor Commented:
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?
0
 
dbaSQLAuthor Commented:
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].
0
 
dbaSQLAuthor Commented:
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.....
0
 
chapmandewCommented:
so...this is probably a new question....
0
 
dbaSQLAuthor Commented:
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
0
 
chapmandewCommented:
...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
0
 
dbaSQLAuthor Commented:
MSDTC problem corrected, chap.  back to the Msg 7391 when attempting to dump proc into table
0
 
dbaSQLAuthor Commented:
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'

Open in new window

0
 
dbaSQLAuthor Commented:
also, same failure when i preface with 'begin tran', and close with 'commit tran'
0
 
dbaSQLAuthor Commented:
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.

0
 
chapmandewCommented:
>>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.
0
 
dbaSQLAuthor Commented:
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].
0
 
dbaSQLAuthor Commented:
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

Open in new window

0
 
dbaSQLAuthor Commented:
very, very interesting.  i am anxious to hear what you think
0
 
chapmandewCommented:
did it work?
0
 
dbaSQLAuthor Commented:
yessir.  sorry... that's what i meant when i said 'got it'
0
 
chapmandewCommented:
Good deal.  Some sort of funky transaction thing I guess.  At least it works now!
0
 
dbaSQLAuthor Commented:
curious.... but agreed.  at least it's working now
thank you much for all of your input, chap
0
 
dbaSQLAuthor Commented:
hey chap, are you around?
0
 
dbaSQLAuthor Commented:
disregard, chap.  all good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.