Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's lead program manager for SQL Server management tools, at questions@sqlmag.com.
How can I find out the maximum row size for a table in which all the varchars are fully populated?
The only way to find a SQL Server table's maximum row size is to query the system tables (or the INFORMATION_SCHEMA views) and add up the byte counts. But note that Microsoft doesn't recommend querying system tables, which can change from release to release. Also note that the total row size never exceeds about 8060 bytes in SQL Server 2000 or 7.0.
We want to upgrade our SQL Server 7.0 servers to Service Pack 2 (SP2). However, we have a Data Transformation Services (DTS) job that copies a table from one of our servers to a business partner's server that runs SQL Server 7.0 SP1. To run the DTS job properly, should the business partner upgrade its server to SP2?
Your partner can keep SP1 when you upgrade to SP2, but only if you password-protect all DTS packages that pass between servers with different service packs. Microsoft has corrected the incompatibility problem in SQL Server 7.0 SP3.
I want to schedule a Visual Basic (VB) 6.0 executable (.exe) program by using the SQLAgentCMDExec account to create a job through SQL Server 7.0 Enterprise Manager. The .exe program should use Universal Naming Convention (UNC) paths to create a file on a shared directory on the SQL Server 7.0 machine. The .exe file works fine when I run it from the server by double-clicking it or by opening a command prompt. However, it doesn't work when I run it from Enterprise Manager. The job's account owner has full access to the shared directory on which I want to create the file. How can I get the executable to work from Enterprise Manager?
The job's security depends on the job owner's access privileges. If the job owner is a SQL Server systems administrator (sa), the service account that SQL Server runs under also runs the .exe file. If the job owner isn't an sa, SQL Server uses the SQLAgentCMDExec account as the security context to run the job. Therefore, the SQLAgentCMDExec account needs the rights to do the same tasks that your executable does. The job won't run with the owner you specified because the SQLServerAgent service can't log in as that user without using a Windows NT password, which SQL Server doesn't support.
I heard that Microsoft is going to stop supporting SQL Server 7.0 soon, so we should upgrade our SQL Server 7.0 machines to SQL Server 2000. Can you confirm this rumor, and if it's true, when will the company end SQL Server 7.0 support?
Microsoft supports the current SQL Server release and the release that preceded it, including providing fixes. The company continues to support earlier releases but doesn't provide patches to any new problems that users find. So, SQL Server 7.0 support will continue until SQL Server's next major release comes out. However, Microsoft released SQL Server 2000 only in September 2000, so the company will continue to support SQL Server 7.0 for quite some time.
I need to create two new columns in my Customer table—one that holds an order count and one that holds the last order date. However, the following UPDATE statement, which I tried to use to set these new columns, doesn't work:
UPDATE CUSTOMER
SET cust_oqty = cust_oqty + 1, cust_lastod
= order_date
FROM ORDER
JOIN CUSTOMER
ON cust_id = order_cust_id
ORDER BY order_date
When I use a SELECT statement to test the join on a customer's three orders, the query returns three rows. However, when I run the UPDATE statement, the Customer table's order count is 1, not 3, and the last order date is correct from the last query row. Another option is to use two subqueries in a SET clause, using COUNT(*) and MAX(order_date) for each column, as Listing 1 shows. Do I have other choices?