Q:
I'm trying to find the easiest way to poll SQL Server systems and route email to certain people if the server doesn't respond. One approach is to have the Windows NT scheduler run a simple batch file running ISQL and sendmail.exe every few minutes. What do you suggest?
The polling solution is easy to implement and maintain, but many vendors provide utilities that can do this task for you. We've heard a lot of praise for NetIQ's AppManager (http://www.netiq.com), which lets you monitor tuning and maintenance statistics for every BackOffice application on your network, and we know other vendors have products with similar capabilities. Do readers have favorite ways to handle this problem?
Q:
Can you use triggers for tasks other than referential integrity checks?
You can use triggers to do almost anything you want. You usually use them for referential integrity, but triggers can be any code that you call in conjunction with data modification operations.
Q:
I need to create a table that has 21 FOREIGN KEY constraints. I can create this table, but when I try to insert a row, I receive an error message (Too many tables involved in this query). How many foreign keys can I define for one table?
Our search of SQL Server 6.5's Books Online (BOL) found this excerpt: "A table can have a maximum of 31 FOREIGN KEY references. This limit is an absolute upper limit, but the maximum may be lower depending on the number of work tables the server has to create to enforce the constraint, and the limit varies by the type of query being executed. FOREIGN KEY constraints are not enforced for temporary tables." This upper limit (31) is tied to the fact that a query can't reference more than 16 tables. You'll be able to reference up to 63 foreign keys in SQL Server 7.0.
Another issue to keep in mind: You don't automatically get indexes with foreign keys the way you do with primary keys. If you want to create an index for your foreign keys column, you need to do it explicitly. Finally, remember that SQL Server doesn't enforce FOREIGN KEY constraints for temporary tables.
Q:
I just installed Service Pack 4 (SP4) on a couple of test servers, and the @@microsoftversion value I'm getting is 406271, the same as it was for SP3. I use the @@microsoftversion value to maintain an accurate count of the service packs our Windows 95 servers are running. The alternative option, the @@version variable, changes format across versions of SQL Server, so it has always caused me problems. Do you have any suggestions?
Microsoft has confirmed that the global variable @@microsoftversion will not return correct information in versions later than SP3. You need to use @@version to programmatically determine which version of SQL Server you're running. The @@microsoftversion variable returns a number similar to 406271, or 0x000632FF in hexadecimal. The low word of this value (0x0006) contains the major server number, and the high word (32FF) is divided into the minor server version (32) and the build number (FF). This storage technique means that 255 is the highest build number that @@microsoftversion can represent. Unfortunately, SP4's build number is 281. Microsoft states in Books Online (BOL) that you must always use @@version for custom version tracking because @@microsoftversion is for internal use only. This problem is a good example of why you shouldn't use unsupported features even if they're exposed.
Q:
How do you hide an object in the Enterprise Manager (EM)?
In this context, hiding an object means that it won't show up in the list when you select the Manage Tables option from the EM. To hide an object, you modify the display of an object's status column. Every user table in a database is represented by a row in sysobjects and will have a value for type = 'U'. The second bit of the sysstat column in sysobjects controls whether an object will be displayed in the table GUI view of EM. You can turn off this bit, which prevents the table from appearing in EM's GUI, by following these steps:
- Open ISQL/w and USE the pubs database.
- In the Query text box, type
SELECT * FROM sysobjects
WHERE type = 'U'
- Change the sysstat value of publishers from 83 to 81. (The sysstat value of 83 represents the bit setting 1010011. The second bit from the right is the display bit, which you must turn off: 1010001. This bit setting corresponds to sysstat value 81.)
- Allow updates to the system tables by entering the following statements in ISQL/w:
sp_configure 'allow updates', 1
RECONFIGURE with override
go
- Update sysobjects by entering the following statements in ISQL/w:
SET sysstat= (sysstat^2)
WHERE name = 'publishers'
sp_configure 'allow updates', 0
RECONFIGURE with override
go
This command tells the system to flip the current setting of the second bit in sysstat. The first time you run this command, it turns off the bit; rerunning the command turns on the bit.
Q:
Sometimes Open Database Connectivity (ODBC) 3.5 chooses the wrong connection when I'm using connection pooling with similar data source names. What's happening?
Connection pooling is the major new feature in ODBC 3.5. Connection pooling is a process that enables an application to create a connection and save it in a pool. ODBC keeps the pool of connections open and hands out a connection from the pool when a request for a new connection comes in. This process is more efficient than repeatedly opening and closing a connection.
In certain circumstances, ODBC 3.5 (the version included with the Windows NT 4.0 Option Pack) can choose the wrong connection. When a client application uses SQLConnect, the OBDC Driver Manager looks at only the characters in the first half of the Data Source Name (DSN), USERID, and Password to decide whether OBDC Driver Manager needs to choose a connection in the pool. Similarly, when a client application uses SQLDriverConnect, the OBDC Driver Manager looks at only the characters in the first of half of the connection string.
For example, when a client application is using two DSNs (e.g., MyDatabase1 and MyDatabase2), a connection to MyDatabase2 can incorrectly satisfy a request for a connection to MyDatabase1. The problem occurs only when the two strings have the same length and the names and the passwords differ only in their second halves. When MyDatabase1 and MyDatabase2 have different schemas, the application will probably experience SQL errors such as Table xxx does not exist. But when the two databases have the same schemas (as in a row-partitioning situation), selects, updates, and inserts could go to the wrong database. Applications using ActiveX Data Object (ADO) or Object Linking and Embedding Database (OLE DB) can also encounter this problem because these services call ODBC. To avoid the problem, make sure names have different prefixes instead of different suffixes when you name a set of related data sources.
Prev. page
[1]
2
next page 
