Tips from the SQL Server MVPs - 02 Feb 2001

Downloads
19842.zip

Editor’s Note: Welcome to SQL Server Magazine’s monthly, Web-only series of SQL Server tips brought to you by the Microsoft SQL Server Most Valuable Professionals (MVPs). Microsoft introduced the MVP program in the 1990s to recognize members of the general public who donate their time and considerable computing skills to help users in various Microsoft-hosted newsgroups. MVPs are nominated by Microsoft support engineers, team managers, and other MVPs who notice a participant's consistent and accurate technical answers in various electronic forums and other peer-to-peer venues. For more information about the MVP program, go to http://support.microsoft.com/support/mvp/. The MVPs donate their SQL Server Magazine author fees for these tips to the World Food Programme. To donate free food to a hungry person today, visit http://www.thehungersite.org.

How can I configure SQL Server merge replication to work only in one direction?

Merge replication by default lets you pass data in both directions: Changes at a publisher are sent to a subscriber, and changes at a subscriber are sent back to a publisher. In some cases, you might want to use merge replication, but you want data to flow in only one direction. The SQL Server GUI doesn’t provide an interface to do this. But remember that the merge agent is only an executable file (replmerg.exe). The merge job in SQL Server Agent simply calls this executable and passes command-line arguments to it. One of those optional command-line arguments is ExchangeType, which has three possible values: 1 specifies push, 2 specifies pull, and 3 specifies two-way (default). If you set up merge with a push subscription and also set the ExchangeType to 1, then SQL Server will push data from the publisher to the subscriber. But SQL Server will not send data back from the subscriber to the publisher.

How do I tune merge replication for performance?

Merge replication is the slowest replication method based on raw performance figures. It’s slow because it involves a lot of work. Merge replication can keep up with some of the most demanding environments in operation, but over time, if you don’t maintain your merge replication system, performance will degrade. Here are some tips for keeping merge replication running at peak levels, based on users’ experience running merge replication in some of the largest configurations in existence:

• Merge replication can work with identities. With SQL Server 7.0, you have to manually partition the identity to avoid primary key conflicts. SQL Server 2000 lets you dynamically manage the identity partitions. However, in either case, you must use the not for replication option on the identity column. This option prevents the identity from being reseeded during replication operations.
• SQL Server 7.0 doesn’t handle parent-child relationships in the form of foreign keys. In order to prevent foreign-key conflicts, you must use the not for replication option on all foreign keys. You don’t need to use this option if you’re using SQL Server 2000.
• Use static memory management. If you’re using merge replication on a machine, turn off dynamic memory management. Dynamic memory management consumes resources that replication wants and drags down performance. Using static memory management can net you a 5 percent to 25 percent performance increase.
• Do NOT turn off dynamic resizing. The last thing you want is a database to resize during a merge operation. If you forget to resize your database and it runs out of space, the entire system stops. Dynamic sizing is your safety net. But you need to manually manage the size of your replicated databases to ensure that you aren't taking a performance hit while a replication operation is running.
• Manage metadata. The merge replication metadata tables—in particular MSmerge_contents and MSmerge_genhistory—grow very quickly. DBAs commonly find several million rows in these tables within a few days or weeks. As the size of metadata tables grows, performance suffers. SQL Server ships with a procedure called sp_mergecleanupmetadata, which cleans up the metadata. Make sure you understand the requirements before you run this procedure. You can find an alternate and equally effective—but unsupported—procedure at http://www.mssqlserver.com/replication/purgemetadata.asp.
• Reindex. You need to periodically reindex the MSmerge_contents and MSmerge_genhistory tables. Reindexing will remove the index fragmentation that occurs and will improve performance.
• Increase processing power at high levels of the merge hierarchy. As your merge hierarchies get deeper, place more resources at the higher levels. This approach will facilitate the exponentially increasing transactions that the upper levels of a hierarchy process as you add more levels.
• Place your merge jobs in an endless loop. This advice runs counter to the common wisdom that endless loops are a bad thing. In 999 times out of 1000, you can fix any errors that occur in merge replication by simply stopping the merge agent and restarting it. When merge replication encounters an error, the job running the merge agent will stop. Fixing merge means restarting the agent. Because you can almost always fix merge replication by simply restarting the agent, the last step in the merge job should be to return to step one if it fails. This approach lets merge replication self-heal without requiring manual intervention.

What do I need to know about configuring a mail profile for SQL Server 2000 and SQL Server 7.0?

You can configure a SQL Server mail profile so you can send and receive email by running T-SQL code—for example, by using the xp_sendmail extended stored procedure to send email. You can also configure a mail profile for SQL Server Agent so that email will be sent to operators automatically when, for example, a certain job fails or an alert is invoked. The mail profile identifies the mail service and connection details such as POP3 and SMTP server, login name, and password. Most of the steps for configuring a mail profile for SQL Server and for SQL Server Agent services are essentially the same as configuring a mail profile for your own mail box, so you’ll be able to send and receive email from your mail client. But you need to know about the following considerations, which refer to SQL Server and not to both SQL Server and SQL Server Agent:

• In order for you to use any mail service, the SQL Server account must start under a proper Windows NT account and not a local system account because you need access to the LAN because the SQL Server and SQL Server Agent services need to be able to access the mail server. The local account can’t do this.
• You create the mail profile by logging on to the machine that is running SQL Server by using the NT domain and account that the SQL Server service is running under. Note that the mail profile is logon-specific in the same way that the My Documents folder is logon-specific. If you create the mail profile under your account—for example, TORVER\RogersonT—but SQL Server is running under TORVER\SQLServer, then SQL Server won’t see or be able to use the mail profile.
• Before trying to create your mail profile, make sure that the SQL Server machine can connect to the mail server. Simple ping tests will probably suffice at this stage.

How can I specify which network library to use to connect to my SQL Server in the connection string?

SQL Server 2000’s new data-access stack lets you specify directly within the connection string which network library (among other parameters) to use. For example, to connect to an instance of SQL Server listening over TCP/IP, port 1433, on server www.myserver.com, you can specify tcp:www.myserver.com,1433 as the server name. In a similar way, if you want to connect to a local instance named MYINST01 listening over named pipes, you can use np:\\.\pipe\MSSQL$MYINST01\sql\query.

How can I embed the results of a stored procedure in my SELECT or SELECT INTO query?

Results returned from a stored procedure as rowsets can’t be embedded in SELECT or SELECT INTO statements. You can, however, use the OPENQUERY or OPENROWSET functions—which you usually use to query remote heterogeneous environments—to query your local server. The advantage of using the OPENQUERY or OPENROWSET functions is that you can invoke a stored procedure, and get a rowset, which you can refer to as a table. Consider the following GetAuthorsForState stored procedure created in the Pubs sample database. This stored procedure accepts a state as a parameter and returns all authors for the supplied state:

CREATE PROC GetAuthorsForState @state AS char(2)
AS
SELECT * FROM Authors WHERE state = @state

To refer to your local server as a linked server, you must first turn on the data access server option:

EXEC sp_serveroption <server_name>, 'data access', 'true'

Now you can invoke the stored procedure by using the OPENQUERY function, which performs a pass-through query against a provided linked server, and then submit a SELECT * query on the result. For example, in order to get all authors from California, you can submit the following query:

SELECT * FROM OPENQUERY(<server_name>, 'EXEC Pubs..GetAuthorsForState 'CA'')

You can also invoke the stored procedure without referring to your server as a linked server. Instead, you issue an ad-hoc query against your server by using the OPENROWSET function. Because the OPENQUERY and OPENROWSET functions return a rowset that you can refer to as a table, you can use those functions in SELECT INTO statements:

SELECT * INTO #CAAuthors
FROM OPENROWSET('SQLOLEDB', '<server_name>';'<user>';'<password>',
  'EXEC Pubs..GetAuthorsForState 'CA'')

Or even in JOIN queries:

SELECT au_fname, au_lname, title
FROM
    OPENROWSET('SQLOLEDB', '<server_name>';'<user>';'<password>',
      'EXEC Pubs..GetAuthorsForState 'CA'') AS A
  JOIN
    Titleauthor AS TA ON A.au_id = TA.au_id
  JOIN
    Titles      AS T  ON TA.title_id = T.title_id

Note that SQL Server 2000 solves this problem much more elegantly by letting you create user-defined functions (UDFs) that return a table.

How can I insert the results from an EXEC command or a stored procedure into a table variable?

You can’t directly insert the results of an EXEC command or a stored procedure into a table variable (Microsoft introduced the new table datatype in SQL Server 2000). For example, the following script produces an error:

DECLARE @MyTable TABLE (id INT NOT NULL)

INSERT @MyTable EXEC ('SELECT id FROM sysobjects')

As a workaround, you can use the OPENQUERY or OPENROWSET functions, as the following script shows:

DECLARE @MyTable TABLE (id INT NOT NULL)

INSERT @MyTable SELECT * FROM OPENQUERY( <server_name>, 'SELECT id FROM sysobjects' )

How can I pass a list from a listbox into a stored procedure in SQL Server 7.0? I’ve considered creating a temporary table to pass the data, but that approach seems sloppy.

Depending on what the stored procedure does with the list of values, you could pass the list in as a comma-separated string in a single-input parameter and parse the string in the stored procedure. The example in Listing 1 uses this approach to construct an IN clause. You could modify this example to populate a temporary table that’s created in the stored procedure.

How can I randomly sort query results?

To randomly order rows, or to return x number of randomly chosen rows, you can use the RAND function inside the SELECT statement. But the RAND function is resolved only once for the entire query, so every row will get same value. Dejan Sarka, a SQL Server trainer from Slovenia, has suggested that we use ORDER BY to sort the rows by the result from the NEWID function, as the following code shows:

SELECT *
FROM Northwind..Orders
ORDER BY NEWID()

SELECT TOP 10 *
FROM Northwind..Orders
ORDER BY NEWID()

How can I use Visual Basic (VB) to get a list of SQL Server computers running in the same network as the client computer?

You can use the ListAvailableSQLServers method from SQL Distributed Management Objects (SQL-DMO), which returns a NameList object with the list of available servers in the same network as the client computer. If the client is member of an NT domain, the code in Listing 2 will return a list of servers only on the client’s domain:

To use the SQL-DMO object library, you need to set a reference to it from your development environment. Note that in high-security environments, you can hide any service registration or broadcast under Windows NT or Windows 2000. You can run the command net config srv /H:Y or net config server /HIDDEN:Y. After you run this command, you won’t see the server in Network Neighborhood, and service broadcasts will be hidden. But you can still access the server if you know its name.

How can I dynamically change a connection's filename in a DTS package?

If you have an import or export process for which the filename regularly changes—for example, your filename represents the extract date—you can dynamically alter the filename during package execution. Place the VBScript in Listing 3 in an ActiveX Script Task, and use an On Success constraint to ensure that it executes before the DataPump Task executes.

In SQL Server 2000, you can use the Dynamic Properties Task, but you’ll still need the ActiveX Script Task to derive the filename. Assign the filename to a global variable from which the Dynamic Properties Task can read the value and set the Connection property.