Local Linked Server

When using OPENQUERY you will sometimes want to make calls to the same server you are working from. The most common reason for this is to query the output of a stored procedure into a temporary table. For that example, follow this link: (Insert Results of Stored Procedure Into Table) Before doing that you will have to create a linked server. You can either create the server programatically: [cc lang=”sql”] EXEC sp_addlinkedserver @server=’LOCALSERVER’, @srvproduct=’SQLSERVER’, @provider=’SQLNCLI’, @datasrc=’SERVERNAMEINSTANCENAME’ [/cc] Or you can create the linked server manually: In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) The “New Linked Server” Dialog appears. Type in a friendly name that describes your local server (without spaces). I use LOCALSERVER. Provider – Select “Microsoft OLE DB Provider for SQL Server” Product Name – type: SQLSERVER (with no spaces) Datasource – type the SERVERNAMEINSTANCENAME ProviderString – Blank Catalog – Optional (If entered use the default database you will be using) Within the same Dialog on the left menu under “Select a Page”, select Security Select the radio button named “Be made using the login’s current security context” Click OK, and the new linked server is created

Continue reading ...

Insert Stored Procedure Results Into Table

Since stored procedures are generally the most efficient way to call and retrieve data, it’s natural to want to encapsulate logic in them and call them when needed. With this use also comes the need to retrieve the data they return. When you already know the datatypes and columns being returned from the procedure, this is a relatively easy task. Let’s start by looking at an example call. [cc lang=”sql”] — Create a sample stored procedure CREATE PROCEDURE GetList AS BEGIN SELECT ListName = ‘MyList’ ,ListNumber = 1 END GO — this table will house our results CREATE TABLE #List ( ListName varchar(25), ListNumber int ) — finally, execute and insert into our table INSERT INTO #List ( ListName, ListNumber ) EXEC dbo.GetList [/cc] That was relatively easy, however dynamically retrieving the result set from a stored procedure without knowing the columns and datatypes returned in advance has historically proven to be a difficult task. Fortunately with the advent of OPENROWSET, our life as developers has become much simpler. Using openrowset is very simple when we do not have procedures that take parameters, However when parameters become involved the game changes, however there is a workaround. In using OPENROWSET we need to turn on the ability to run distributed queries on your server. To enable this is simple, given you have the appropriate permissions. It’s a simple process when we do not have to pass in parameters. [cc lang=”sql”] sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE [/cc] Using OPENROWSET […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!