Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @.table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
(uncorrect syntax)

Could someone give me a clue

Thanx,

Hennie de Nooijerhdenooijer@.hotmail.com (Hennie de Nooijer) wrote in message news:<191115aa.0311040343.674519a6@.posting.google.com>...
> Hi
> I'm executing SP_SpaceUsed in a stored procedure like this :
> Exec ('SP_SpaceUsed '+ @.table)
> This works great but when i want to execute it for a table in a other
> database i'm running in to troubles. Things i tried is this :
> Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
> syntax)
> Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
> (uncorrect syntax)
> Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
> syntax)
> Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
> (uncorrect syntax)
> Could someone give me a clue
>
> Thanx,
> Hennie de Nooijer

exec('exec ' + @.database + '..sp_spaceused ' + @.table)

Simon

executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

executing job on another server

EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
I am on server3 and have to run a from here on server7 does anyone know the
correct syntax I should use for this task
thanks
for any help
SammyHTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know
> the
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||You even have to connect to the remote server, or write a on the remote
server SP to execute the statement you mentioned.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know
> the
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||See sp_add_jobserver in BOL.
AMB
"Sammy" wrote:

> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know th
e
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||EXEC server7.msdb.dbo.sp_start_job @.job_name = 'mailresults'
Once the linked server is setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ubwsBLjWFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You even have to connect to the remote server, or write a on the remote
> server SP to execute the statement you mentioned.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
>

Executing Dynamic SQL with out Select Permission

I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_executesq
l
@.sql I am executing the dynamic sql, few of my procedures are getting input
parameter for table name and/or column names also. Now the database user is
modified with privileges, he has assigned only execute Permission. How to
solve this problem.You can't. If you use dynamic SQL you need permissions on the underlying
tables. Passing in table names and column names as parameters to a stored
procedure is not a good idea anyway, and the problem you have run into is
only one of the issues (see http://www.sommarskog.se/dynamic_sql.html). If
you can explain what you are actually trying to do, someone here can come up
with a better solution.
Jacco Schalkwijk
SQL Server MVP
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
>I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
>sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
> input
> parameter for table name and/or column names also. Now the database user
> is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||Unfortunatly you can't, with dynamic SQL you must have Select Permission on
the table.
If you tell us what you are tryng to do we could possible sugest an
alternative.
Peter
Do not arouse the sleeping dragon, for you are crunchy and taste good with
ketchup.
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also.
Care to explain just WHY you are doing that? The usual reasons are poor
database design and/or poor coding practices. The solution is almost always
not to do it. Dynamic SQL comes with a lot of incovenient baggage: security
vulnerabilities; performance implications; maintenance and reliability
issues; cost to develop and support.
David Portas
SQL Server MVP
--
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||I don't know if this applies in your case, but it helped avoid dynamic SQL
on a project of mine. If you need to query across multiple partitioned
tables (ex: SALES_2004, SALES_2003, etc), then consider using a partitioned
view (basically a view of unionized tables). When a new table is added, then
you can re-create the view that includes the new table reference.
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
input
> parameter for table name and/or column names also. Now the database user
is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.

execute stored procedure (with parameters) with an "exec" command

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@.id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@.parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.It's DBNull.Value|||Try:

sqlPar = new SqlParameter("@.parent_id", DBNull.Value)|||Perfect, it works, but now I have another problem.
I get this error:
This SqlTransaction has completed; it is no longer usable.

When I run Commit or Rollback code.|||Are you committing the transaction twice accidentally?|||No, I'm sure.
The only "strange" thing I do it's to call 2 different functions in my class to begin and commit/rollback transaction.|||Then you will have to show some code...|||'CConn is my class
CConn.BeginTransaction()

Dim arParameters as new ArrayList()

Dim sqlParOutput = new SqlParameter("@.id", SqlDbType.Int)
sqlParOutput.Direction = ParameterDirection.Output
arParameters.Add(sqlParOutput)

Dim sqlPar = new SqlParameter("@.parent_id", DBNull.Value)
arParameters.Add(sqlPar)

objTransaction = CConn.ExecuteNonQuery("sp", arParameters, false)

if objTransaction is nothing then objTransaction = CConn.ExecuteNonQuery("...")

if objTransaction is nothing then
CConn.CommitTransaction()
else
CConn.RollbackTransaction()
end if

The Begin, Commit and Rollback functions in CConn class simply call the same functions of SqlConnection object.
The ExecuteNonQuery function override standard function.|||This helps sort of not at all. We do not know what your class is doing. Is the return from CConn.ExecuteNonQuery a transaction object? What is the significance of objTransaction being nothing?|||Sorry, you're right.
Here the class methods:

 Public function BeginTransaction() as SqlTransaction
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 'Start a local transaction
 Me.myTransaction = Conn.BeginTransaction()
 end function
 Public sub CommitTransaction()
 Me.myTransaction.Commit()
 Me.Conn.Close()
 end sub
 Public function RollbackTransaction() 'transaction as SqlTransaction)
 Me.myTransaction.Rollback()
 Me.Conn.Close()
 end function
 Public function ExecuteNonQuery(ByVal SQL As String, optional sqlParameters as ArrayList = nothing, optional toClose as boolean = true) as object
 Dim objReturn as object
 if Me.Conn is nothing then
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 else
 if Me.Conn.State <> ConnectionState.Open then Me.Conn.Open()
 end if
 Dim sqlCmd As New SqlCommand(SQL, Me.Conn)
 'Must assign transaction object to Command object for a pending local transaction
 if not Me.myTransaction is nothing then sqlCmd.Transaction = Me.myTransaction
 Try
 if not sqlParameters is nothing then
 sqlCmd.CommandType = CommandType.StoredProcedure
 Dim sqlPar as SqlParameter
 for each sqlPar in sqlParameters
 sqlCmd.Parameters.Add(sqlPar)
 next
 else
 sqlCmd.CommandType = CommandType.Text
 end if
 sqlCmd.ExecuteNonQuery()
 Catch e As Exception
 objReturn = e
 End Try
 'must be clean up?
 if toClose then
 Me.Conn.Close()
 sqlCmd.Dispose()
 Me.Conn.Dispose()
 end if
 return objReturn
 End function

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execute SQL task

I want to perform following using Execute SQL task

declare @.LogID int, @.OperationID int

exec usp_CreateLog

@.DataProvider='ABC'

,@.Source_Original = ''

,@.NoTables = 3

,@.UserName = '@.userName'

,@.LogID =@.LogID output

select @.LogID as LogID

I want @.LogID as output. Please suggest how to achive in SSIS

Use OLEDB COmmand, ans in the SQL Command write EXEC SP_Name ?,?,?,?,? OUTPUT

And in the second tab link the input/output columns

Regards!

|||

Can you please explain in detail as I am new in SSIS.

EXEC SP_Name ?,?,?,?,? OUTPUT

What is ? ?

|||

can you please give more detail as am new in SSIS

what is ? in the sql statement... considering my sql statement.

|||

This is your stored procedure that you can create in your database.

I didnt tested yet, but is more or less this:

Code Snippet

CREATE PROCEDURE usp_CreateLog

@.MyDataProvider char(2),

@.MySource_Original varchar(20),

@.MyNoTables int,

@.MyUserName char(10)

AS

DECLARE @.LogID int, @.OperationID int

INSERT INTO [MyTable] (DataProvider, SourceOriginal, NoTables, Username)

VALUES (@.MyDataProvider, @.MySource_Original, @.MyNoTables, @.MyUername)

SET @.LogID= SCOPE_IDENTITY()

RETURN @.LogID

Now, create an OLEDB Command to execute it.

In the second tab of OLEDB Command, link the input and output columns, and use your own variables [@.@.User::] or system variables [@.@.system::]

I can give some images if you want.

Regards!

|||

dont worry, I willl help you...

If you need I can give some images!

Regards!

|||

thanks for detail. It works

Can u please give me one more help

I want to execute following function with parameter LogId

like

set @.Dest= dbo.fn_GetArchiveFileName (?)

And I want @.Dest as output string

Can u please help me in this

|||

Explain me better...

Where you need to execute the function? In database? In the controlFlow? Dataflow?

Give me more details!

Regards!

Execute SQL dynamically

Hi,
I want to execute some SQL statement that has more than 4000 characters, is
there any way to do it?
I'm using the exec sp_executesql command as following:
declare @.SQLString nvarchar(4000)
select @.SQLString = 'SQL statement more than 4000 characters'
exec sp_executesql @.SQLString
ThanksFerreira wrote:

> Hi,
> I want to execute some SQL statement that has more than 4000 characters, i
s
> there any way to do it?
> I'm using the exec sp_executesql command as following:
> declare @.SQLString nvarchar(4000)
> select @.SQLString = 'SQL statement more than 4000 characters'
> exec sp_executesql @.SQLString
> Thanks
declare @.var1 navarchar(4000)
declare @.var2 navarchar(4000)
set @.var1 = 'bla bla ..................'
set @.var2 = 'aaaabbbb ...............'
exec (@.var1 + @.var2)
Regards
Amish Shah|||http://www.sommarskog.se/dynamic_sql.html
Madhivanan
amish wrote:
> Ferreira wrote:
>
> declare @.var1 navarchar(4000)
> declare @.var2 navarchar(4000)
> set @.var1 = 'bla bla ..................'
> set @.var2 = 'aaaabbbb ...............'
> exec (@.var1 + @.var2)
> Regards
> Amish Shah|||Ferreira (Ferreira@.discussions.microsoft.com) writes:
> I want to execute some SQL statement that has more than 4000 characters,
> is there any way to do it? > I'm using the exec sp_executesql command as
> following:
> declare @.SQLString nvarchar(4000)
> select @.SQLString = 'SQL statement more than 4000 characters'
> exec sp_executesql @.SQLString
In addition to the other posts, note that if you are on SQL 2005, you can
use the new data type nvarchar(MAX) instead. This data type is unlimited
like ntext, but does not have all the restrictions of ntext.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

execute dts package using xp_cmdshell

How to use xp_cmdshell to execute dts package?
I used the following command:
EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword /Npackagename'
This returns an error and package did not executed. Any suggestions?
Thank you very much!What is the error message?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||The error message are as follow:
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The device is not ready.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
The above datafile mentioned by this error message located in my computer
which is different from sql server. If I can execute the package manually
successfully, I do not see why it can not open the file through the stored
procedure call.
"Dan Guzman" wrote:
> What is the error message?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> > How to use xp_cmdshell to execute dts package?
> > I used the following command:
> > EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> > /Npackagename'
> >
> > This returns an error and package did not executed. Any suggestions?
> >
> >
> > Thank you very much!
>
>|||You have identified your own problem...
WHen you execute the package using DTSrun from YOUR command prompt it runs
on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
and files...Either copy the directory/files to the sql server, or use a UNC
name to point back to a share on your PC..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||I am not familiar with UNC name you mention here. I looked up the help file
from SQL server but could not find the information. Can you specify how can
I "use a UNC name to point back to a share on my PC"? What is UNC name?
I did not executed the package from command prompt. I executed it from
enterprise manager without a problem. The enterprise manager runs the
package with the drive letter on file. When I save the package, the package
should contain all the drive letter info on my pc. My confusion is why
xp_cmdshell did not recognize the saved package which should contain all the
drive and file info and point it to the file on my computer.
I may sound silly, I just need someone to point this out or provide a link
to any some kind of tutorial on this subject.
Thank you very much!
"Wayne Snyder" wrote:
> You have identified your own problem...
> WHen you execute the package using DTSrun from YOUR command prompt it runs
> on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
> or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
> and files...Either copy the directory/files to the sql server, or use a UNC
> name to point back to a share on your PC..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> > How to use xp_cmdshell to execute dts package?
> > I used the following command:
> > EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> > /Npackagename'
> >
> > This returns an error and package did not executed. Any suggestions?
> >
> >
> > Thank you very much!
>
>

execute DBCC on linked server

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

EXEC [linkedserver].master.dbo.sp_executesql N'DBCC USEROPTIONS'

|||

Thanks Mark, your suggestion works fine but now I have another problem when I do the following for example:

INSERT [sometable] EXEC [LinkedServer].pubs.dbo.sp_executesql N'EXEC sp_helpfile'

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed the instructions in article 839279 but still get the error, any ideas?. I don't need DTC transaction support, is there a way to get the results without involving MSDTC ?

Regards,

A.E

|||

If you've tried everything in

http://support.microsoft.com/kb/839279

then there's not a lot more I can add.

BTW, the last time I saw this, it was fixed simply by specifying
SET XACT_ABORT ON before the EXEC.