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