Did Microsoft enhance Declarative Referential Integrity (DRI) support in SQL Server 2000?
Microsoft hinted several years ago that it would support cascading DRI and disappointed many people when that feature didn't make SQL Server 7.0. With SQL Server 2000, however, the wait is over. SQL Server 2000 supports cascading DRI, a feature that Microsoft Access users have enjoyed for years. What's the difference between previous SQL Server versions' restrictive DRI and cascading DRI? Let's say you have the classic parent-to-child (one-to-many) relationship of customers and orders. Restrictive DRI prevents deletion of a customer with active orders, whereas cascading DRI assumes you know what you're doing and simply deletes the related orders when you delete a customer.
SQL Server 2000's SQL-92 syntax for the CREATE TABLE command supports cascading actions on updates and deletes:
CREATE TABLE mytable (mycol1 character (30) NOT NULL, --more column names, data types go here CONSTRAINT mytable_pk PRIMARY KEY (mytableID) --note syntax to designate primary key --column --naming the constraint is optional CONSTRAINT mytable_fk FOREIGN KEY (mytableID) REFERENCES mydependent_table, ON DELETE|UPDATE CASCADE)
You can implement cascading deletes in SQL Server 7.0 and earlier by writing code or suitable update and delete triggers.
Have you ever needed to connect to a SQL Server running on a different TCP/IP port (the default is 1433), only to discover that you didn't have the SQL Server Client Configuration Utility installed on your systems?
Been there; done that. As you know, changing the default client-side network library (NetLib) is easy if you have the SQL Server Client Configuration Utility. But if this tool isn't on your system, changing the default setting can be difficult.
You can attack this problem three different ways. You can install the SQL Server client tools on your machine, hack the Registry, or use code to set the NetLib information within your application or within the Data Source Name (DSN) or Universal Data Link (UDL) your application uses. Installing the tools is, of course, too simple and obvious a solution, so let's look at the other two solutions.
You can find the default NetLib information in a value called DSQUERY in the following Registry key: KKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo. (Note that Registry key settings and names might change between SQL Server releases. This answer addresses SQL Server 7.0 Registry key settings and names. SQL Server 2000 NetLibs may change by the final release.) If the DSQUERY value isn't in the Registry key, simply add it with a type of REG_SZ. Also, make sure you back up your Registry before you start fiddling with these values. The correct DLL NetLib names are
dbmssocn—Win32 Winsock TCP/IP dbmsspxn—Win32 SPX/IPX dbmsrpcn—Win32 Multi-Protocol (Windows RPC) dbmsvinn—Win32 Banyan VINES dbnmpntw—Win32 Named Pipes
Now, let's say your server, MyServer, is listening on port 1499 instead of 1433. You would need to set the DSQUERY value to DBMSSOCN,MyServer,1499. You can also add alternative server names, as you could if you were using the Client Configuration tool. To create an additional entry for a server called NewServer that listens on port 1433 over the TCP/IP sockets NetLib, you'd create a new value called NewServer in the ConnectTo key. The value would be of type REG_SZ, and you'd set the string to DBMSSOCN,NewServer,1433. Although you can more safely and easily add a client-side server alias by using the Client Configuration Utility, it's good to know that if you don't have the utility installed, you can manually add this information.
This answer can't include all the information you need to set your NetLib in all possible circumstances. But you can easily find the correct string for a new ConnectTo value by using a machine that has the Client Configuration Utility installed. Just create a new server entry that meets your needs, then look in the ConnectTo key to see which values SQL Server set.
Setting the NetLib information directly in your application is fairly easy. For information about this topic, see the Microsoft articles "HOWTO: Set the SQL Server Network Library in an ADO Connection String" (http://support.microsoft.com/support/Kb/articles/q238/9/49.asp) and "INF: ODBC SQL Server Connection Parameters" (http://support.microsoft.com/support/Kb/articles /q137/6/35.asp).
I'm developing an OLAP solution that maintains security roles and processes cubes by using a custom solution that I developed with Visual Basic for Applications (VBA) within Microsoft Excel. My solution uses SQL Server's Decision Support Objects (DSO), the object model that is part of SQL Server 7.0 OLAP Services. The problem is that users must be members of the OLAP Administrators group to run the application. Is a good workaround available for this restriction?
Unfortunately, only members of the OLAP Administrators group can use the DSO object library against a particular server. To work around the OLAP Administrators group restriction, you can write a server-based Visual Basic (VB) COM object that Microsoft Transaction Server (MTS) hosts. Just design the COM object to run in the context of a user assigned to the OLAP Administrators group and to perform actions on behalf of a regular user.
I wrote the following simple program to process a SQL Server Multidimensional OLAP (MOLAP) database:
Sub Main()
Set dsoServer = New DSO.Server
Set dsoDB = dsoServer.MDStores("s2olap")
dsoDB.Process
dsoServer.CloseServer
Set dsoDB = Nothing
Set dsoServer = Nothing
End Sub
The program works fine when I run it interactively from the command line, but it doesn't work when I invoke it through the scheduler. I thought the problem might be related to permissions, so I restarted the SQL Agent Service under my domain account and rescheduled the job. But the program still didn't work. Can you help me solve this mystery?
You were on the right track: The problem is permission related. Only OLAP Administrators have administrator control over a cube. This control includes the ability to run Decision Support Objects (DSO) code against the server. By default, the user account you use to install the OLAP server on a particular computer receives OLAP Administrators privileges on that computer. On initial installation, OLAP Services establishes a Windows NT local group named OLAP Administrators and adds the logged-on user to this group. Only members of this group can administer the OLAP Server. Unfortunately, administrator security doesn't have multiple degrees or levels. Users either are or aren't OLAP Administrators, depending on whether they're in the OLAP Administrators group.
To make your code work, the NT account your job runs under in the SQL Agent (or whatever scheduler you're using) must be a member of the OLAP Administrators local group on the target OLAP Server. But keep in mind that the account will then have full administrator rights for all cubes and databases on the affected OLAP Server. If giving users full administrator rights isn't acceptable, you can write a server component that runs in the context of an account that is a member of the correct OLAP Administrators group. Your job could then ask the server component, hosted through Microsoft Transaction Server (MTS), IIS, or some other mechanism, to perform OLAP administration commands for you.
When I delete a transaction log file (.ldf), sometimes SQL Server 7.0 creates a new one. But other times when I delete a log file, I get an error message such as
Database 'LogTestDB' cannot be opened because some of the files could not be activated.
What's going on?
If you delete a transaction log file, SQL Server 2000 and SQL Server 7.0—under certain conditions—will rebuild a new 1MB transaction log file. The first condition is that you must shut down SQL Server without error before log file deletion to ensure database consistency. SQL Server will then rebuild the .ldf file only if it's the PRIMARY log file and the log isn't spread over multiple .ldf files. SQL Server will also rebuild deleted or renamed user .ldf or TEMPDB .ldf physical transaction log files and add an unable to activate entry in the current ERRORLOG file. If you delete an .ldf file and these conditions aren't met, you won't be able to open the database.
You can get into trouble by assuming that SQL Server will rebuild the log file. For example, SQL Server 7.0 makes you jump through hoops to shrink a log file, and people occasionally take a shortcut and simply delete the log, thinking SQL Server will automatically recreate it. But if you delete the log file without understanding the rules for recreating the log file, you can find yourself with a database that nobody can access.
