I'm running a data warehouse application on SQL Server 6.5 where a nightly process inserts rows and aggregates the data into weekly, monthly, and yearly tables. I consistently get the following error in the Windows NT Application Error Log:
WARNING: Open Objects parameter may be too low. Error: Attempt was made to free up descriptors in localdes. Error: Run sp_configure to increase parameter value.
The Open Objects parameter is set to 5000. How can I determine the appropriate value to run on my applications, without bumping up the setting until the error goes away?
I suspect the problem involves the temporary tables and open objects, which Service Pack 3 (SP3) for SQL Server 6.5 fixed. You need to upgrade to at least SP3, although Microsoft recommends SP5. I don't know how to monitor the number of object handlers SQL Server currently has open. And other than installing a service pack, I don't know of another way than bumping the open objects to a higher number (if you have enough memory). The number needs to represent the maximum number of open tables, stored procedures, views, etc., that you have open.
One of my SQL Server applications can't send email. The SqlAgent.out says
Unable to start mail session (reason: No mail profile defined) or: An attempt was made to send an email when no email session has been established.
In the SQL Mail Configuration dialog box, I typed the same profile name I use for another SQL Server, which email works on. When I click Test, the message appears:
Error 22030: A MAPI error(error number:273) occurred: MapiLogonEX failed due to MAPI error:273: MAPI Logon failed.
How can I send mail?
To configure the mail profile, make sure you're logged in to the account that the service is running under. In SQL Server 7.0, the two mail systems, SQL Mail and the SQL Server Agent mail, are separate. To make SQL Mail work, you need to use the SQL Server service account to configure the mail profile. To make the SQL Server Agent mail work, use the SQL Server Agent service account to configure the mail profile.
SQL Server Books Online (BOL)refers to creating a custom COM component resolver by using the ICustomResolver interface. How do I use the ICustomResolver?
ICustomResolver is the interface and class that all resolvers inherit. ICustomResolver is extensible, so developers can add and develop resolvers as needed. The five sample custom resolvers that come with SQL Server 7.0 (ICustom-Resolver isn't one of them) are the custom stored procedure resolver, publisher-based resolver, subscriber-based resolver, datetime-based resolver, and merge text resolver.
You can find sample code for these resolvers in the SQL Server 7.0 directory \Mssql7\Devtools\Samples \Sqlrepl\. Other than BOL, I don't know of other documentation on ICustomResolver.
When I create a stored procedure in SQL Server 7.0, can I enable a setting to check the validation of the stored procedure? I created a stored procedure with
SELECT test FROM test body
and compiled it, but it returns Invalid object name test when I run it.
You can't enable a setting to check the validation of a stored procedure when you create it. However, a new feature of SQL Server 7.0, dynamic name resolution, resolves object names at runtime. Dynamic name resolution lets the administrator create the programs to access an object without creating the object first.
I'd like to do a data-to-data comparison on the datetime field, but I need to eliminate the time in the datetime field. The stored datetime, which includes the time, doesn't return the expected data in reports, stored procedures, and views. For example, table.date and @date are of datetime @date = 09/14/1999, so a row in the table where table.date = '09/14/1999 12:23 PM' isn't returned in the query
SELECT * FROM table WHERE table.date <= @date
How can I eliminate the time in the datetime field, so I can compare only the date to a date?
Currently, you have to store the date in another field, which is somewhat cumbersome. Use the DATEPART function to remove only the date, so you won't have to compare the time.
What does SQL Server 6.5 Service Pack 5a (SP5a) fix?
SP5a fixed a backup problem in the SQL Server Enterprise Manager. The Web page http://support.microsoft.com/support/ kb/articles/Q215/4/58.ASP discusses the backup problem and how using sp_sysbackuphistory can cause invalid values in the page header information to occur.
I installed SQL Server 6.5 and several client applications, which I connected with SQL Server by using ODBC. One application, developed with Visual FoxPro 5.0, gives the error message 1526, Invalid Transaction State. This error message usually appears when a break occurs between the server and the client computer, such as an energy cut in the client computer. I tried to stop the server and restart it, but I can eliminate the problem only if I reinstall the applications. Surprisingly, after the error occurs, the same error message appears only when users leave a screen in the client application. All the clients have Windows 95. Does the error relate to the ODBC connection?
This error relates to the Visual FoxPro application rather than SQL Server. Any ODBC driver release that is later than the release that comes with SQL Server 6.5 will fix the problem. Also, apply the service pack to the client computer; the service pack updates the ODBC drivers. You can also download the latest Microsoft Data Access Components (MDAC) stack at http://www.microsoft.com/data, which is how Microsoft now delivers ODBC drivers. You might also want to report this problem to the Visual FoxPro newsgroups at the news server msnews.microsoft.com.
I'm using a global variable, and I created a stored procedure to insert a record and return the identity of the record. When I implement an insert trigger on that table, it overwrites the identity variable, and the trigger inserts in a tracking history table. How I can reset the @@IDENTITY variable back to what it was?
Whenever you insert to a table with an identity, even within a trigger, the insertion overwrites the @@IDENTITY. For triggers that have an identity, I usually declare a local variable and capture it right away. For example,
CREATE TRIGGER Mytrigger ON MyIdentTab FOR Insert AS DECLARE @myident int SELECT @myident = @@IDENTITY GO
When you declare a local variable this way, if the identity value changes later within the trigger, you'll still know what the value was when the insert took place. Of course, you need to handle the insert differently for a multiple-row insert.
My Microsoft Data Access Components (MDAC) 2.1.2.4202.3 failed. How can I load the latest SQL Server drivers on a Citrix 1.8 box? Is there a way to install only the SQL Server drivers?
The SQL Server drivers aren't available as a separate package in MDAC. Microsoft hasn't tested SQL Server with the Citrix interface, so first check with Citrix to see whether the company has tested this configuration.
