Dynamically Determine Linked Servers

Oct 26, 2007

In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done?

CREATE FUNCTION dbo.stfGetSupervisorOrgUnitAssignments(@SupervisorID INT)

RETURNS @ReturnTable TABLE(Org_Unit INT PRIMARY KEY)

AS

BEGIN

DECLARE @Server VARCHAR(100)

SELECT @Server = dbo.stfGetLinkedServerName()

INSERT INTO @ReturnTable(Org_Unit)

SELECT ReplicationID FROM OPENQUERY(@Server, '....')

END

Any help would be greatly appreciated.

View 6 Replies

Stored Procedure Dynamically Determine Columnname

Dec 3, 2006

I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON;
declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END

View 1 Replies View Related

SELECT - Dynamically Determine Fields To Return

Dec 6, 2004

Hello,

I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.

Which is the most effecient way to handle this ?

1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?

In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.

2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?

In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?

3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.

This leads to a lot of code repitition.

4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.

But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.

5. Any other possible approaches ??

Looking forward to your responses,

Thanks much,

bmgun.

View 3 Replies View Related

ODBC TCP/IP Dynamically Determine Port Check Box

Aug 16, 2006

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

View 8 Replies View Related

Dynamically Determine Subtotal Visibility In Matrix?

Feb 22, 2007

I'm using SSAS2005 SP2 as a data source, and RS2005 SP2 for reporting.

With the upgrade to sp2, users assigned to an area in a sales location tree no longer get a value returned for AGGREGATE(MyValue.Value) when looking at a region higher than they are assigned.

My MATRIX reports now look like:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_______|
Region Total___________________|_______|
Grand Total____________________|_______|

Before sp2, they had the 5 and 3 values repeated in all total rows.

My thought was to hopefully dynamically hide the sub region and region groupings and their related subtotals - but when trying to do this, only the grand total shows, and its values are blank.

When a person can only see Area1, I'd like the report to look like this:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|

but if they could see everything from the cube, I would like those sections to show, because they have values in them:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_10__5_|
Region Total___________________|_20_11_|
Grand Total____________________|_42_13_|

View 3 Replies View Related

Determine All The SQL 2000 Servers On The Local Net

Jan 27, 2003

Hi, is there a way of how to find all the SQL 2000 servers on our local Network. We have a lot of development servers around the company and I need a tool (or somthing) that could help me find the server name and the version of it.

Thx
Dim

View 5 Replies View Related

SQL Server 2008 :: How To Determine Baseline For Servers

Apr 9, 2015

I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.

I have of course ensured there is a backup strategy in place to secure the data.

View 1 Replies View Related

How To Determine, Inside A Function, If A Linked-server-query Returned Results

Feb 13, 2004

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!