September 22, 2010 at 2:57 am
hope you guys could help. if i execute the statement below it gives me an error:
DECLARE @ServerName nvarchar(50)
DECLARE @command_01 nvarchar (100)
set @ServerName = 'DEV\SERVER1'
set @command_01 = 'EXEC ['+@ServerName+'].master.dbo.xp_fixeddrives'
create table #Drive
(
Drive char (1)
, FreeSpace int
)
INSERT #Drive
exec sp_executesql @command_01
OLE DB provider "SQLNCLI10" for linked server "DEV\SERVER1" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DEV\SERVER1" was unable to begin a distributed transaction.
but if i just execute the @command_01 script it gives me results
EXEC [DEV\SERVER1].master.dbo.xp_fixeddrives
September 22, 2010 at 3:10 am
set @ServerName = 'DEV\SERVER1'
put this also in [dev\server1]
----------
Ashish
September 22, 2010 at 3:18 am
@learningforearning
this line actually has the square brackets on it
set @command_01 = 'EXEC ['+@ServerName+'].master.dbo.xp_fixeddrives'
the result of @command_01 runs properly if I execute the script manually however if I use
exec sp_executesql @command_01
that's the time I get errors
September 22, 2010 at 3:18 am
Maybe this http://vstoolsforum.com/forums/p/170/366.aspx can help you.
September 22, 2010 at 3:22 am
['+@ServerName+'].master.dbo.xp_fixeddrives'
the result of @command_01 runs properly if I execute the script manually however if I use
sorry didnt noticed that
exec sp_executesql @command_01
that's the time I get errors
try
EXEC master..xp_cmdshell @command_01
----------
Ashish
September 22, 2010 at 3:33 am
@learningforearning - thanks for the reply, but I am now getting the following error:
exec master..xp_cmdshell @command_01
'EXEC' is not recognized as an internal or external command, operable program or batch file.
September 22, 2010 at 3:35 am
Can you please display the output of this sp_configure.
September 22, 2010 at 3:39 am
nameminimummaximumconfig_valuerun_value
allow updates0100
backup compression default0100
clr enabled0100
cross db ownership chaining0100
default language0999900
filestream access level0200
max text repl size (B)-121474836476553665536
nested triggers0111
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
server trigger recursion0111
show advanced options0100
user options03276700
September 22, 2010 at 3:40 am
what are the values for Ad Hoc Distributed Queries ?
September 22, 2010 at 3:42 am
Run this
sp_configure @configname = 'show advanced options',@configvalue = 1
go
reconfigure
go
if you are not able to see all the options.
September 22, 2010 at 3:43 am
Run this
sp_configure @configname = 'show advanced options',@configvalue = 1
go
reconfigure
go
if u are not able to see all the options.
September 22, 2010 at 3:45 am
nameminimummaximumconfig_valuerun_value
access check cache bucket count01638400
access check cache quota0214748364700
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0100
backup compression default0100
blocked process threshold (s)08640000
c2 audit mode0100
clr enabled0100
common criteria compliance enabled0100
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0111
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
EKM provider enabled0100
filestream access level0200
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06400
max full-text crawl range025644
max server memory (MB)16214748364721474836472147483647
max text repl size (B)-121474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0111
open objects0214748364700
optimize for ad hoc workloads0100
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0100
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0100
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
xp_cmdshell0111
September 22, 2010 at 4:02 am
exec master..xp_cmdshell @command_01
'EXEC' is not recognized as an internal or external command, operable program or batch file.
the problem is because of this
set @command_01 = 'EXEC ['+@ServerName+'].master.dbo.xp_fixeddrives'
remove the extra exec here
so your @command_01 will be '['+@ServerName+'].master.dbo.xp_fixeddrives'
hope it helps
----------
Ashish
September 22, 2010 at 4:05 am
Run this and try your statements again.
sp_configure @configname= 'Ad Hoc Distributed Queries',@configvalue = 1
go
reconfigure
go
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply