Questions, Answers, and Tips About SQL Server

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 (Jonathan.Ausubel@transamerica.com) 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.

   Previous  [1]  2  Next