Solved

dump this into a table variable

Posted on 2009-04-09
27
324 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:dbaSQL
  • 18
  • 8
27 Comments
 
LVL 11

Expert Comment

by:N R
Comment Utility
You mean you want to put those variables into a table or the output from executing the procedure?  You can create a Table1
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
@sourceserver and @destinationserver are linked.  always have been.  
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
so...this is probably a new question....
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
...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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
MSDTC problem corrected, chap.  back to the Msg 7391 when attempting to dump proc into table
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
also, same failure when i preface with 'begin tran', and close with 'commit tran'
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
>>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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
very, very interesting.  i am anxious to hear what you think
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
did it work?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
yessir.  sorry... that's what i meant when i said 'got it'
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Good deal.  Some sort of funky transaction thing I guess.  At least it works now!
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
curious.... but agreed.  at least it's working now
thank you much for all of your input, chap
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
hey chap, are you around?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
disregard, chap.  all good
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now