Prev: Tracking results of changes to database
Next: Dynamic Pivot
From: John Couch on 19 Apr 2010 15:42
declare @lnvc_SQL nvarchar(4000)

select @lnvc_SQL = N'select *
from openquery([HQAPPSQL04], ''set fmtonly
off; ' +
' exec (''''dbcc memorystatus'''')'')'

exec sp_executeSQL @lnvc_SQL

Does anyone know why this only returns 1 result and not all of them?
From: Scott Morris on 19 Apr 2010 15:52
"John Couch" <JohnCouch(a)discussions.microsoft.com> wrote in message
news:BC45BE8C-7982-48B1-8E6C-3F67710E437E(a)microsoft.com...
> declare @lnvc_SQL nvarchar(4000)
>
> select @lnvc_SQL = N'select *
> from openquery([HQAPPSQL04], ''set fmtonly
> off; ' +
> ' exec (''''dbcc memorystatus'''')'')'
>
> exec sp_executeSQL @lnvc_SQL
>
> Does anyone know why this only returns 1 result and not all of them?

From BOL:

Although the query may return multiple result sets, OPENQUERY returns only
the first one.

Also, it is usually important to identify which version/sp level of sql
server that you are using.



From: John Couch on 19 Apr 2010 17:01
This is running across the following versions of SQL Server:

SQL Server 2000 (SP2/SP3/SP4), 2005 (SP1/SP2/SP3), SQL Server 2008 (SP1)

I figured out how to get all the results, but now I get a distributed
transaction error if I try to dump it into a temporary table or table
variable.

declare @lnvc_SQL nvarchar(4000)
,@lnvc_eSQL nvarchar(4000)
,@lnvc_LinkedServer nvarchar(128) = 'LinkedServer'
,@li_Rc int = 0


declare @ltbl_Memory table (Element nvarchar(128)
,Value int)

-- Build Statement for use in retrieving base file information for
the Catalogue
select @lnvc_SQL = N'execute sp_executeSQL ''dbcc memorystatus'''
select @lnvc_eSQL = N'exec [' + @lnvc_LinkedServer +
'].master.dbo.sp_executesql @lnvc_SQL'

-- Grab File Information
insert into @ltbl_Memory (Element, Value)
exec sp_executeSQL @lnvc_eSQL, N'@lnvc_SQL nvarchar(4000)',
@lnvc_SQL

select * from @ltbl_Memory
From: Gert-Jan Strik on 19 Apr 2010 17:19
Have you tried using a regular table? If need be, you can create (and
drop) that on the fly as well using dynamic SQL.

BTW1: when I tried with a temporary table, it seemed to work (although I
got an error stating: "MSDTC on server '...' is unavailable", but you
shouldn't have that problem)

BTW2: your first line that declares 4 local variables and sets 2 default
values is invalid in SQL Server 2000.

--
Gert-Jan


John Couch wrote:
>
> This is running across the following versions of SQL Server:
>
> SQL Server 2000 (SP2/SP3/SP4), 2005 (SP1/SP2/SP3), SQL Server 2008 (SP1)
>
> I figured out how to get all the results, but now I get a distributed
> transaction error if I try to dump it into a temporary table or table
> variable.
>
> declare @lnvc_SQL nvarchar(4000)
> ,@lnvc_eSQL nvarchar(4000)
> ,@lnvc_LinkedServer nvarchar(128) = 'LinkedServer'
> ,@li_Rc int = 0
>
> declare @ltbl_Memory table (Element nvarchar(128)
> ,Value int)
>
> -- Build Statement for use in retrieving base file information for
> the Catalogue
> select @lnvc_SQL = N'execute sp_executeSQL ''dbcc memorystatus'''
> select @lnvc_eSQL = N'exec [' + @lnvc_LinkedServer +
> '].master.dbo.sp_executesql @lnvc_SQL'
>
> -- Grab File Information
> insert into @ltbl_Memory (Element, Value)
> exec sp_executeSQL @lnvc_eSQL, N'@lnvc_SQL nvarchar(4000)',
> @lnvc_SQL
>
> select * from @ltbl_Memory
From: Erland Sommarskog on 19 Apr 2010 18:09
John Couch (JohnCouch(a)discussions.microsoft.com) writes:
> I figured out how to get all the results, but now I get a distributed
> transaction error if I try to dump it into a temporary table or table
> variable.

I've done my share fighting with MSDTC. I have far from always been
successful. But what error message do you get?

> -- Build Statement for use in retrieving base file information for
> the Catalogue
> select @lnvc_SQL = N'execute sp_executeSQL ''dbcc memorystatus'''
> select @lnvc_eSQL = N'exec [' + @lnvc_LinkedServer +
> '].master.dbo.sp_executesql @lnvc_SQL'

You can simplify this a little:

EXEC @sp_executesql = @linkedserver + '.master.sys.sp_executesql'
EXEC @sp_executesql N'DBCC MEMORYSTATUS'

Not that this will resolve your problems with the distributed trransaction
in anyway.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Tracking results of changes to database
Next: Dynamic Pivot