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.