Prev: Tracking results of changes to database
Next: Dynamic Pivot
From: John Couch on 19 Apr 2010 19:21
I should have clarified the declare portion. This code is being executed from
a SQL Server 2008 Instance, and that part is valid, the part that is ""
generic per say wis the Linked Server call itself to the 2000-2008 instances
in my environment. I am attempting to use CLR atm as an alternative, but will
try a permenant table first. Thanks for the feedback.

"Gert-Jan Strik" wrote:

> 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: John Couch on 22 Apr 2010 09:03
In case anyone is interested, I found a resolution to this issue. I used a
CLR procedure. Deploy it as an assembly with EXTERNAL access, and it will
work. Considering it is only my 2nd CLR procedure, please don't knock the
code too bad. I am open to suggestions, just not a beating.

using System;
using System.Collections;
using System.Security.Principal;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
//[Microsoft.SqlServer.Server.SqlProcedure]
//public static void xp_MemoryStatus(string invcServerName)
public static IEnumerable xp_MemoryStatus(string invcServerName)
{

ArrayList resultCollection = new ArrayList();

/// Retrieve the Windows account token for the current user.
WindowsIdentity newIdentity = null;
WindowsImpersonationContext newContext = null;

try
{
newIdentity = SqlContext.WindowsIdentity;
newContext = newIdentity.Impersonate();

if (newContext != null)
{
using (SqlConnection sqlConn = new SqlConnection("Data
Source=" + invcServerName + ";Integrated Security=true;"))
{

SqlCommand sqlCmd = new SqlCommand(@"DBCC MEMORYSTATUS",
sqlConn);

sqlConn.Open();

using (SqlDataReader sqlRdr = sqlCmd.ExecuteReader())
{
while (sqlRdr.Read())
{
object[] ob = new object[sqlRdr.FieldCount];
sqlRdr.GetValues(ob);
resultCollection.Add(ob);


}

sqlRdr.Close();
}

newContext.Undo();
sqlConn.Close();
}
}
else
{
throw new Exception("User Impersonation Failed!");
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
finally
{
if (newContext != null)
{
newContext.Undo();
}
}

return resultCollection;
}
};



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