Q: In one of your articles, you stated that replication works over TCP/IP networks. Can SQL Server 6.5 handle replication over the Internet? And I can't find documentation about how to create a datasource name (DSN) that is physically connected to a particular IP address (server IP address).
You're asking two different questions: "Can SQL replication work over the Internet?" and "How do I create a DSN that points to a SQL Server on the Internet?" To answer the first question, the good news is that SQL replication certainly does work over the Internet. After all, the Internet is nothing more than a big TCP/IP-based network.
With regard to the second question, configuring an Open Database Connectivity (ODBC) DSN for Internet access is simple, but it really doesn't have anything to do with replication. The reason is that SQL Server doesn't use DSNs during replication, unless you're using a non-SQL Server ODBC subscriber. Set up the DSN as you usually would, with two small changes, as you see in Screen 1, page 211:
- Specify the IP address in the Network Address text box
- Specify the proper TCP/IP socket NetLib in the Network Library text box. Use DBMSSOCN for Windows NT or Windows 95 clients and DBMSSOC3 for Win16 clients.
If you're using WINS, you might be able to use a simple NetBIOS name; if you're using DNS, specify a traditional Internet-style name such as servername.domainname.com (e.g., www.winntmag.com).
Q: How do I strip tabs out of the data in one of my columns?
Jonathan Ausubel ([email protected]) posted the elegant solution shown in Listing 1, page 211, on Steve Wynkoop's SQL Server mailing list (way to go, Jonathan!). As you probably know, char(9) is the ASCII code for the tab symbol.
Q: I take a lot of grief about Windows NT's security problems. Does SQL Server add any security?
SQL Server 6.x lets you use the encryption option of the Multiprotocol Network Library to encrypt data over the wire for both 16-bit and 32-bit clients. But basically, SQL Server relies on the NT remote procedure call (RPC) API to encrypt network traffic. The NT RPC uses 40-bit RC4 encryption, the maximum allowed for export. NT distributed in the US for US use is, by default, capable of 128-bit encryption. When you apply a service pack to NT, the system asks you whether you want to "upgrade" your encryption routine. If you say yes, it changes to the reduced 40-bit encryption.
Q: Why does SQL Enterprise Manager (EM) sometimes show a negative number for the size of a device?
You'll see a negative device size if the device you're editing is on a drive that has more than 2GB of free space, because anything that's larger than 2GB needs more than a 4-byte integer to express its size. The good news is that Service Pack 3 fixes this problem. When this problem occurs in earlier versions of SQL Server, you can change the device size in the dialog box, or you can use the DISK RESIZE command to manually increase the device size.
ADO Connections to SQL Server
Brian and two of his colleagues at Spectrum (Jeff Ward and Steve Vago) recently ran into an interesting problem with using ActiveX Data Object (ADO) to connect to a SQL Server database. Perhaps their experience can save you time. We've summarized some internal email to show you how problems get solved in the real world.
Jeff: I'm using ADO connections to execute stored procedures to insert and update records in a database. Because I sometimes want to ensure that I've established a one-to-many relationship correctly, I'm using ADO transactions in my Visual Basic (VB) code. For instance, I begin a transaction on the ADO connection, insert a publisher record, insert many related book records with a foreign key of the previously inserted publisher ID, and then commit or roll back the transaction, depending on the success or failure of all the database updates. This process seems to work fine. The problem occurs when I start a transaction, update or insert records in two different tables, and then try to insert a record into an intersection table that has foreign key relationships with those other two tables. If I remove the foreign key constraints on the intersection table, I have no problem (other than having to give up referential integrity, of course), but with the constraints, the INSERT statement times out. I know the ADO transaction puts a page lock on tables, but the only difference between these two scenarios seems to be a foreign key constraint to one table that is locked as opposed to foreign key constraints to two tables that are locked. Any thoughts? Thanks.
Brian: If the problem is in locking, you can usually use sp_who to see what connections ADO is blocking. One possibility is that ADO opens multiple connections in some situations. SQL Server does a SELECT under the covers to check the foreign key constraint, so you could expect the blocking problem if ADO is using multiple connections. I'd use sp_who to see which connections are open and which connections are being blocked. Grab the spids from sp_who, and then use Database Consistency Checker (DBCC) INPUTBUFFER to determine which Transact-SQL (T-SQL) commands ADO is executing on each connection. Let me know what you find out.
Jeff: Thanks for the suggestions. I found that because my VB code used the syntax
cmd.ActiveConnection = objConn
instead of
Set cmd.ActiveConnection = objConn
ADO established a second SQL connection for the call to update the intersect table. The ADO connection already had an open transaction with a previous call to update a record with a foreign key relationship, and this transaction was blocking the SELECT executed to check constraints on the second SQL connection.
We looked into why leaving off the SET statement would cause this situation to manifest itself. We found that calling the Execute method of the ADO Command object requires either a valid open connection or a connection string. Using a connection string establishes a second SQL connection, which isn't assigned to an ADO connection object (this process is documented). I browsed through the interfaces of the connection and command objects and found some interesting information. First, the ActiveConnection property of the command object is polymorphic, appearing as both a put and a putref; thus, you can assign a string value or an object reference to the ActiveConnection property. Second, the ConnectionString property of the Connection object just happens to be its default property. Consequently, the syntax
Set objcmd.ActiveConnection = objConn
would use putref to assign the objConn object reference to the ActiveConnection property. The syntax
objcmd.ActiveConnection = objConn
is essentially the same as the syntax
objcmd.ActiveConnection = objConn.ConnectionString
In this case, you would use the put method to assign the string value to the ActiveConnection property and thus cause the Execute method to establish a secondary SQL connection. In our scenario, the page lock invoked by the already existing connection blocked the second SQL connection.
Capturing Complex DBCC Output in a Temp Table
In our November column, we gave you a tip for tracking log utilization by capturing output from Database Connectivity Checker (DBCC) perflog into a table for subsequent processing. This technique works because SQL Server 6.5 introduced the ability to insert the result set of a stored procedure into a table. When you capture DBCC output into a table, you can manipulate it with Transact-SQL (T-SQL) commands. The original code is in Listing 2.
This approach works fine for most situations, but some DBCC commands such as CHECKDB don't return their information as a result set as ordinary T-SQL commands do, so you can't use our technique for CHECKDB. SQL Server Most Valued Professional (MVP) Roy Harvey offers a modified version, shown in Listing 3, page 213, that works with CHECKDB.
Listing 3 works a lot like Listing 2—you just insert the result set of a stored procedure into a table so that you can process the output. The difference between the two listings is that you no longer create a wrapper procedure to run the real command (DBCC CHECKDB, in this case) from which you want to capture the output. Instead, you use xp_cmdshell to run the DBCC command. DBCC CHECKTABLE doesn't return a result set you can use, but xp_cmdshell does. Running the DBCC command through xp_cmdshell solves the problem by letting you capture the output for later processing. Our compliments to Roy!
We ran across a related technique that uses a Microsoft Windows NT Server Resource Kit utility (dumpel.exe) for dumping event logs. You run this character-mode utility from the command line and supply optional parameters such as types of messages (application, security, or system), destination filename, and filters (e.g., only events generated by SQL Server).
You can also use the undocumented extended stored procedure xp_eventlog to read event log information from within T-SQL. The xp_eventlog procedure accepts Security, Application, or System as a parameter
xp_eventlog "System"
and returns the event log information from the server that SQL Server is running on.
Database Transfer with SQL-DMO
We've talked about SQL Distributed Management Objects (SQL-DMO) in several columns and provided several interesting examples such as using SQL-DMO and Object Linking and Embedding (OLE) Automation procedures to perform bcp operations directly from Transact-SQL (T-SQL). Several people have asked whether you can design a custom database transfer utility. The answer is yes—it's very easy with our old friend SQL-DMO.
Microsoft Knowledge Base article Q152801, "INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object" (http://premium.microsoft.com/support/kb/articles/q152/8/.01.asp), includes code examples that show how to use SQL-DMO objects and OLE Automation stored procedures to create your own T-SQL-based database transfer utilities.
New White Papers
The Microsoft SQL Server Developer's Resource Kit (http://www.microsoft.com/sql/reskit.htm) is a "compilation of technical materials targeted at developers of large-scale SQL Server applications." The kit's goal is "to provide developers with the key technical information needed to design and to develop great applications on SQL Server." (The material in quotes is pure Microsoftese, in case you weren't sure.) The following are the newest papers in the resource kit:
- "Embedding Microsoft SQL Server in Your Application" (This paper addresses licensing the engine for use in your commercial application)
- "Migrating Oracle Applications to Microsoft SQL Server" (Oracle's Design and Migration Services—[email protected]—also has conversion kits and white papers for migrating to Oracle from Microsoft Access, SQL Server, and just about any other database)
- "Upsizing Microsoft Access Applications to Microsoft SQL Server"
- "Developing International Database Applications Using Microsoft SQL Server"
- "A Developer's Guide to Transaction Processing Systems"
- "Developing Distributed Applications Using Microsoft SQL Server 6.5 Replication"
Another white paper we like (it's been out for half a year now, but you might have missed it) is "SQL Server 6.5 Web Application Deployment Guide." Digital Foundry wrote it, so you can download it (387KB) from either Digital Foundry (http://www.digitalfoundry.com/products/ipd/sqlwadg.html) or from Microsoft (http://www.microsoft.com/sql/inet/sqlinetdeploy.htm).
Microsoft writes that the deployment guide contains
- An overview of the components of a Web-based application and strategies for selecting the right combination of hardware, software, and development tools for building and deploying Web-based applications
- A discussion about the importance of database services when building strategic Web-based applications
- A design methodology for creating datacentric applications for the Web and strategies for designing an effective user interface and database and planning data access from HTML
- An in-depth discussion about how to use SQL Server, Internet Database Connector (IDC), and Active Server Pages (ASPs) to implement dynamic Web pages
- Strategies for deploying and supporting a Web-based application
- Best of all, a fully functional intranet phone directory application, including full source code
Online Troubleshooting Wizards
We make a habit of checking Microsoft's Knowledge Base for new articles related to SQL Server. We ran across two articles you might have missed. They're online wizards for solving problems with SQLMail (http://support.microsoft.com/support/tshoot/sqlmail.asp) and basic SQL Server setup (http://support.microsoft.com/support/tshoot/sqlsetup.asp). You might want to bookmark them.
More on BORK
In addition to offering a wealth of technical information, the SQL Server section of Microsoft's BackOffice Resource Kit (BORK) contains several great utilities to make your life easier. We introduced some utilities in our November column; here are a few more utilities the SQL BORK provides.
Multiserver Excel Backup Macro. This Distributed Management Objects (DMO)-based utility shows you how to create a multiserver Microsoft Excel macro. The Visual Basic for Applications (VBA) macro backs up all databases from all servers on the LAN to a central file share. Then, the macro calculates the percentage of space used and displays the results in an Excel spreadsheet. This utility serves its purpose well, and checking out someone else's code is a great way to learn the basics of DMO. This macro reminds us that you can find many creative ways to use DMO to extend SQL administrative functionality.
SQLhdtst. SQLhdtst, a hardware stress test utility, has been around for a while, but most people don't know about it. SQLhdtst helps you figure out whether a bug is in SQL Server or some other piece of the computer equation (blame it on the hardware, right?). SQLhdtst helps isolate low-level stability problems in the OS or hardware by performing a high-intensity, multithreaded file system stress test. Curiously, you run SQLhdtst when SQL Server isn't running, but the I/O profile that the utility generates is similar to what would be happening if SQL Server were processing an I/O intensive application. You'll know SQL Server isn't to blame for certain types of strange data corruption if SQLhdtst reports stability problems.
CHECKSMS.SQL. This utility isn't in the SQL part of the Resource Kit—it's in Microsoft's System Management Server (SMS) portion—but if you're running SMS, CHECKSMS.SQL is a nice way to check the status of your SQL servers from SMS. (Basically, CHECKSMS.SQL calls sp_monitor.) Be sure to look at the code first, and supply your server's name where the code requires it.
SQL Server Community
Be sure to visit the SQL Server Community at http://www.winntmag.com/sql. The community is a support network for industry professionals working with Microsoft SQL Server. The site offers key industry news, links to Windows NT Magazine articles about SQL Server, a SQL FAQ, and links to other Internet resources. In the forum, SQL Server professionals can post questions to their peers and the community leaders, or conduct discussions on key industry topics.
Send your questions to us individually or collectively at [email protected].
