Thursday 21 April 2016

Execute Stored Procedure On a Linked Server

What is the RPC and RPC Out option on a SQL Server linked-server?

★★★★★
★★★★
★★★
★★
★
Someone asked me this week – what are the relevance of the RPC and RPC Out settings on a linked server? Do you need both of them or only one of them set to True?
The documentation is online in this article, but it doesn’t clearly show examples or provide enough context, so I will try to do that here.http://technet.microsoft.com/en-us/library/ms186839(v=SQL.105).aspx
1. The first RPC setting is mainly for a legacy feature called Remote Server, which is documented here. http://msdn.microsoft.com/en-us/library/ms190187.aspx
You probably will not be using remote servers in SQL Server 2005 -SQL Server 2014 versions.
If you do happen to use “remote servers”, the RPC setting enables a certain security feature. If it is turned off, when the second server (the one receiving the login attempt from the first server to which the client connected) tries to validate the remote login, it fails with error
18482 “Could not connect to server ‘%.*ls’ because ‘%.*ls’ is not defined as a remote server. Verify that you have specified the correct server name. %.*ls.”
EXEC master.dbo.sp_serveroption @server=N’MYREMOTESERVER’, @optname=N’rpc’, @optvalue=N’true’ 
 
Kudos to my peer Ignacio Alonso Portillo for helping figure out this legacy option.

2. The RPC OUT  setting is very pertinent to linked servers on SQL Server 2005 -SQL Server 2014 versions.
Think about an RPC (Remote Procedure Call) as being a stored procedure being run remotely from Server 1 to linked Server 2 “myserver”.
There are various syntaxes, the first using 4 part naming servername.databasename.schemaname.procedurename , the second here using EXECUTE(databasename.schemaname.procedurename ) AT servername convention.
We can test with a simple procedure sp_helpdb that lives in master database, and will list out the databases and some of their key properties.
  • EXEC [myserver].master.dbo.sp_helpdb
  • EXEC (‘master.dbo.sp_helpdb’) AT myserver
These kind of remote stored procedure calls will be blocked unless RPC OUT is set to True.
Msg 7411, Level 16, State 1, Line 1  Server ‘myserver’ is not configured for RPC.

You can set it to True or False in the linked server’s properties (a right click menu in from the Linked Server Name in SQL Server Management Studio) .

Or you can set it by calling the stored procedure to set the server options like this test:
 – Test RPC OUT as false and get the error
EXEC master.dbo.sp_serveroption @server=N’MYSERVER’, @optname=N’rpc out’, @optvalue=N’false’
GO
EXEC [myserver].master.dbo.sp_helpdb
– Msg 7411, Level 16, State 1, Line 1
– Server ‘myserver’ is not configured for RPC.
GO
 — Test RPC OUT as true and see success
EXEC master.dbo.sp_serveroption @server=N’MYSERVER’, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC [myserver].master.dbo.sp_helpdb
– Command(s) completed successfully.

-------------------------------------------------------------------------------------------------------------------
This article is from:
https://blogs.msdn.microsoft.com/jason_howell/2014/10/01/what-is-the-rpc-and-rpc-out-option-on-a-sql-server-linked-server/
Posted by at No comments:

Monday 4 April 2016

Support for DB functions in Code first (Entity Framework 6.1)

Install-Package EntityFramework.CodeFirstStoreFunctions -Pre

After the package has been installed copy and paste the code snippet from below to your project. This code demonstrates how to enable store functions in Code First.
public class Customer
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public string ZipCode { get; set; }
}
 
public class MyContext : DbContext
{
    static MyContext()
    {
        Database.SetInitializer(new MyContextInitializer());
    }
 
    public DbSet<Customer> Customers { get; set; }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new FunctionsConvention<MyContext>("dbo"));
    }
 
    [DbFunction("MyContext", "CustomersByZipCode")]
    public IQueryable<Customer> CustomersByZipCode(string zipCode)
    {
        var zipCodeParameter = zipCode != null ?
            new ObjectParameter("ZipCode", zipCode) :
            new ObjectParameter("ZipCode", typeof(string));
 
        return ((IObjectContextAdapter)this).ObjectContext
            .CreateQuery<Customer>(
                string.Format("[{0}].{1}", GetType().Name,
                    "[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
    }
 
    public ObjectResult<Customer> GetCustomersByName(string name)
    {
        var nameParameter = name != null ?
            new ObjectParameter("Name", name) :
            new ObjectParameter("Name", typeof(string));
 
        return ((IObjectContextAdapter)this).ObjectContext.
            ExecuteFunction<Customer>("GetCustomersByName", nameParameter);
    }
}
 
public class MyContextInitializer : DropCreateDatabaseAlways<MyContext>
{
    public override void InitializeDatabase(MyContext context)
    {
        base.InitializeDatabase(context);
 
        context.Database.ExecuteSqlCommand(
            "CREATE PROCEDURE [dbo].[GetCustomersByName] @Name nvarchar(max) AS " +
            "SELECT [Id], [Name], [ZipCode] " +
            "FROM [dbo].[Customers] " +
            "WHERE [Name] LIKE (@Name)");
 
        context.Database.ExecuteSqlCommand(
            "CREATE FUNCTION [dbo].[CustomersByZipCode](@ZipCode nchar(5)) " +
            "RETURNS TABLE " +
            "RETURN " +
            "SELECT [Id], [Name], [ZipCode] " +
            "FROM [dbo].[Customers] " +
            "WHERE [ZipCode] = @ZipCode");
    }
 
    protected override void Seed(MyContext context)
    {
        context.Customers.Add(new Customer {Name = "John", ZipCode = "98052"});
        context.Customers.Add(new Customer { Name = "Natasha", ZipCode = "98210" });
        context.Customers.Add(new Customer { Name = "Lin", ZipCode = "98052" });
        context.Customers.Add(new Customer { Name = "Josh", ZipCode = "90210" });
        context.Customers.Add(new Customer { Name = "Maria", ZipCode = "98074" });
        context.SaveChanges();
    }
}
 
class Program
{
    static void Main()
    {
        using (var ctx = new MyContext())
        {
            const string zipCode = "98052";
            var q = ctx.CustomersByZipCode(zipCode)
                .Where(c => c.Name.Length > 3);
            //Console.WriteLine(((ObjectQuery)q).ToTraceString());
            Console.WriteLine("TVF: CustomersByZipCode('{0}')", zipCode);
            foreach (var customer in q)
            {
                Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
                    customer.Id, customer.Name, customer.ZipCode);
            }
 
            const string name = "Jo%";
            Console.WriteLine("\nStored procedure: GetCustomersByName '{0}'", name);
            foreach (var customer in ctx.GetCustomersByName(name))
            {
                Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
                    customer.Id, customer.Name, customer.ZipCode);  
            }
        }
    }

------------------------------------------------------------------------------------------------------------------------------------
This article is from: http://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/

}
Posted by at No comments:
Subscribe to: Posts (Atom)