June 23, 2005 at 6:35 am
Hi everybody,
When i run this query
Select * into #user_tbl from OpenDataSource('SQLOLEDB','DataSource=DBServer;User ID=sa;Password=sa').USERDB.dbo.user_tbl
from a Query Analyzer window it runs fine.
However when i use the same statement in a stored procedure using dynamic sql like this:
Create Procedure pdsW_config_user
@from_db_server varchar(256)
,@from_db_name varchar(256)
,@from_db_user varchar(256)
,@from_db_pwd varchar(256)
DECLARE @strSQL varchar(8000),
@err int,@from_db varchar(1500)
Select @from_db='OpenDataSource('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+'DataSource='+@from_db_server+';'
+'User ID='+@from_db_user+';Password='+@from_db_pwd+char(39)+').'+@from_db_name+'.dbo.'
BEGIN TRANSACTION CONFIG
SELECT @strSQL = 'insert into #user_tbl select * from '+@from_db+'User_tbl'
EXEC (@strSQL)
COMMIT TRANSACTION CONFIG
GO
I get this error:
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].
The server has windows 2000 sp4 and sql 2000 sp4.
what could be the reason for this? and what is the solution to it?
nomi
June 23, 2005 at 7:30 am
Maybe this is your prob :
EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.
Vasc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply