SQL Server Q & A - 01 Nov 1999

I have a Data Transformation Services (DTS) package that transfers data from a dBase V file (which has only one table) to a table on my SQL Server database. How can I pass the source location and the destination database name to DTS and execute DTS if the source location is different every time I run the Visual Basic (VB) application?

Listing 1, page 74, takes an existing package (saved as a COM file), loads it, and resets the DataSource property. Note that after the package is loaded, you have full access to the entire DTS Object model, so if you know your connection name, you can modify any of the attributes. See Don Awalt and Brian Lawton, "Unleash the Power of DTS" (May 1999), for more information about Connection object properties.

I want to trace and save information about who updated, deleted, or inserted a record in a specific table. SQL Server doesn't have a history log for this purpose. Can I create one? Also, if I use a trigger, can I get the user ID of anyone who makes a transaction?

You can use SQL Profiler to record information about who makes changes to data and tables. Using the trigger isn't reliable enough because the trigger isn't guaranteed to capture bulk loads and it generates extra overhead. For other ways to track transactions, see Reader to Reader, "Create a User Activity Log" (October 1999).

I want to replicate from SQL Server 6.5 to 7.0, as SQL Q & A (July 1999) mentions, until I can upgrade to 7.0. The sync task completes successfully, but the distribution fails with the error Unable to connect to 'MAINFRAME01'. Why does the task manager connect for the sync but not for the distribution task?

The snapshot agent connects only to the publisher and distributor, and the distribution agent connects to the subscriber and distributor. You need to verify that the service account for the SQLServerAgent can connect to all the relevant machines, so that the distribution doesn't fail. Log in to your distribution server machine as the service account, and make sure you can log in to the other SQL Servers. After this login check, you can connect for the distribution task.

Is the ability to restore single tables from a backup in SQL Server 6.5 missing in 7.0?

Restoring single tables was a new feature in SQL Server 6.5, but it has several limitations and problems, such as the lack of transactional consistency in the backup/restore process for a single table. Because one of Microsoft's biggest goals is to maintain proper transactional consistency, the developers eliminated the single-table backup/restore feature in SQL Server 7.0. But, because of strong customer feedback about this feature, Microsoft will provide a different solution in a future release that will be more consistent with Microsoft's new file architecture.

I recently migrated from SQL Server 6.5 to 7.0 and installed Service Pack 1 (SP1). My application depends heavily on xp_cmdshell and reading and writing files from remote servers in several trusted domains. In SQL Server 6.5, I can read and write files from remote servers by assigning a domain account to SQL Server service and granting access to the service account and to various shares in the trusted domains. However, by default in SQL Server 7.0, nonadministrators use a SQLAgentCmdExec account when they use xp_cmdshell to execute commands. How do I turn off the option Nonadministrators use the SQLAgentCmdExec account when executing commands via xp_cmdshell?

Microsoft eliminated this option in SQL Server 7.0. Instead, you can make your users sa role members, which won't give them any more permissions than they had with access to xp_cmdshell. You can grant the appropriate permissions from the SQLAgentCmdExec account on the files that you want to let users access.

How can you show a table description (not the name) in SQL Server 7.0 the way you can with Sybase Database Server and the DB2 Server?

If you want all the details about a table in SQL Server 7.0, run

    EXEC sp_help Also, you can see this information in the SQL Server Enterprise Manager in the Databases, Tables folder. Simply right-click on any table to get a description or the data from the table.Through DTS, I've created a job in SQL Server that uploads my data from a .csv file into a table, and now I want to rename this file as the current date with a .txt extension (e.g., 11-15-99.txt). The date needs to change with the system date, and the file needs to rename as soon as the data successfully finishes uploading.You need to add an ActiveX script task to your package and execute it after you import the data from the .csv file to the table (as if it's using the On Success precedence constraint). Then in the ActiveX script task, you can use native VBScript functions to go out to the file system and rename the file.I'm running a data warehouse application on SQL Server 6.5. 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. I set the OPEN OBJECTS parameter at 5000. How can I determine the appropriate parameter value? Microsoft fixed the problem with temporary tables and open objects in Service Pack 3 (SP3) for SQL Server 6.5. Make sure that you've upgraded to at least SP3, although Microsoft recommends SP5. I don't know of any way to monitor how many object handlers are currently open within SQL Server. Your best bet (if you have enough memory) is to bump up open objects to a higher number to represent the maximum number of open tables, stored procedures, views, etc., that you have open.In SQL Server 6.5, I often get Open Data Services (ODS) error code 17832. I've sniffed the entire network and I can't find a problem. Now the event log is full of ODS messages with the same error code. What can I do?The ODS messages are just informational; you can't do anything to stop them from appearing. Look at article Q109787 at http://support.microsoft .com/servicedesks/msdn/ for more detailed information about this error. Article Q109787 says that a variety of SQL Server communication-related errors are possible and that, in general, these errors don't indicate a problem with SQL Server, but rather a network, network configuration, or a client-application 
problem.Editor's Note: Send your SQL Server-related questions to [email protected].