December 3, 2003 at 8:17 am
I am getting an error when I use the following code.
SET @sSQL = 'SELECT ''' + cast(@server_number as varchar(8)) + ''', ''' + cast(@database_number as varchar(8)) + ''', a.TN, a.rows, a.reserved, a.dpages, a.indxspce, ''' + CAST(@currentdate as varchar(20)) + ''' FROM OPENROWSET(''SQLOLEDB'', ''' + @server_name + '''; ''SQLDBACollector''; '''', ''SELECT u.name + ''''.'''' + o.name as TN, i.rows, i.reserved, i.dpages, (i.used - i.dpages) as indxspce FROM ' + @database_name + '.dbo.sysobjects o INNER JOIN ' + @database_name + '.dbo.sysindexes i ON o.id = i.id INNER JOIN ' + @database_name + '.dbo.sysusers u ON o.uid = u.uid WHERE (o.type = ''''U'''') AND (i.indid IN (0, 1)) AND o.name <> ''''dtproperties'''''') as a'
INSERT INTO [dDBA_IMSDBA].[dbo].[DBA_TABTABLEROWCOUNTSIZE]([SERVER_ID], [DB_ID], [TBL_NM], [ROW_CNT_AMT], [TBL_SPACE_KILOBYT_RSRV_AMT], [TBL_SPACE_KILOBYT_DATA_AMT], [TBL_SPACE_KILOBYT_INDX_AMT], [LAST_UPDT_DTTM])
EXEC sp_executesql @sSQL
Set @iErr = @@ERROR
if @iErr > 0
GOTO Err_Exit
I can not seem to trap the error the code for traping doesn't work. I tried Printing @@ERROR and it does not print. THe error I am receiveing is
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].
Any ideas?
December 3, 2003 at 10:01 am
can you print the resulting sqlvariable before executing it ?
* Noel
December 3, 2003 at 10:11 am
Yes, and it runs fine in Query Analyzer by itself. I also included the INSERT in the variable with same results. It does work on other servers. The SQL variable being executed now is
INSERT INTO [dDBA_IMSDBA].[dbo].[DBA_TABTABLEROWCOUNTSIZE]([SERVER_ID], [DB_ID], [TBL_NM], [ROW_CNT_AMT], [TBL_SPACE_KILOBYT_RSRV_AMT], [TBL_SPACE_KILOBYT_DATA_AMT], [TBL_SPACE_KILOBYT_INDX_AMT], [LAST_UPDT_DTTM]) SELECT '14', '145', a.TN, a.rows, a.reserved, a.dpages, a.indxspce, 'Dec 3 2003 11:54AM' FROM OPENROWSET('SQLOLEDB', 'CUSTOMERSQL'; 'SQLDBACollector'; '', 'SELECT u.name + ''.'' + o.name as TN, i.rows, i.reserved, i.dpages, (i.used - i.dpages) as indxspce FROM xDRX_WEB_MART.dbo.sysobjects o INNER JOIN xDRX_WEB_MART.dbo.sysindexes i ON o.id = i.id INNER JOIN xDRX_WEB_MART.dbo.sysusers u ON o.uid = u.uid WHERE (o.type = ''U'') AND (i.indid IN (0, 1)) AND o.name <> ''dtproperties''') as a
[/quote]
December 3, 2003 at 11:12 am
are you sure MSDTC is running on 'CUSTOMERSQL' Server?
* Noel
December 3, 2003 at 11:19 am
yes, msdts.exe is in Task Manager
December 3, 2003 at 12:24 pm
If it works on QA without quotations and MSDTC is running remotely it has to work in QA with quotations!!
have you tried exec (@str)
instead of sp_executesql
* Noel
December 4, 2003 at 8:37 pm
First off can you do any distributed transactions with the linked server?
Also is one of your servers running Win2003?
If you have a Win2003 machine try the following...
open Component Services (found in Administrative Tools in Control Panel).
Expand out the Component Services node to Computers / My Computer.
Right click on My Computer and click on Properties.
Click on the MSDTC tab on the Properties dialog.
Click on the Security Configuration button and set the values as needed for your environment. Also make sure that the DTC Logon Account is set to "NT AUTHORITY\NetworkService" without the quotes.
Cycle MSDTC and try your query again. If that doesn't work I would suggest you call Product support and get them to help you out. (I just spent 4 hours on the phone with them myself over this issue due to an invalid entry in the Hosts file on one of the servers!)
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply