Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at savvy@sqlmag.com.
To run a bulk copy program (bcp) command from inside a Data Transformation Services (DTS) package, I'm using a T-SQL task that contains the following statement:
EXEC master..xp_cmdshell "BCP HOLD..DETAIL in D:\DETAIL.FMT /SNT2 -T"
However, the DTS package doesn't report an error if the bcp command fails. How can I detect whether the bcp command executed successfully?
The DTS package doesn't report a bcp error as long as xp_cmdshell runs successfully. However, xp_cmdshell may run even if the bcp job fails completely or loads only part of your input data. You need a more definitive way to check for errors. Without additional data, I can't say which error-handling approach would work best for you, but here are two options to consider.
First, you can use the -e parameter with your bcp command to tell bcp to generate an error file as it runs. If the size of the error file is 0 bytes, the bcp task worked. Checking file size from T-SQL is a simple task: Just use the undocumented xp_getfiledetails extended stored procedure. For example, the following statement
xp_getfiledetails 'c:\temp\bcpout.err'
produces the result set that Table 1, page 22, shows. You can then use an EXEC operation to move this result set into a temporary table that you've created and select the Size column, which gives you the file's size in bytes. The following code snippet shows an example of how to perform such an EXEC operation:
INSERT INTO #GetFileDetailsOut EXEC master..xp_getfiledetails 'c:\temp\bcpout.err'
SELECT Size FROM #GetFileDetailsOut
where #GetFileDetailsOut is a table that you've created to match xp_getfiledetails' result set. A Size column value greater than 0 means that the bcp command failed.
If your goal is to simply use a DTS package to load data into SQL Server, a better solution is to bypass xp_cmdshell and bcp entirely and use DTS's custom BulkInsert task instead. This task is essentially a GUI wrapper for the T-SQL Bulk Insert command. Because T-SQL's Bulk Insert runs in-process with SQL Server and bypasses the Network Library layer, it's typically faster than the command-line bcp. And the BulkInsert task gives you much more control over processing within the DTS package because you're using a native DTS task designed specifically for loading data.
I'm using SQL Server 7.0 and trying to access a linked server by using a command such as
SELECT * INTO test FROM server2.db1.dbo.table1
I receive an error message that says Server2 is not configured for DATA ACCESS, but I can access both server1 and server2 from Enterprise Manager. Why am I receiving this error message?
The error message is telling you that the Data Access option for the linked server, which SQL Server enables by default, is disabled. To enable this option from Enterprise Manager, navigate to the Linked Servers tab, right-click the linked server's name, and select Properties, Server Options. Then, make sure that the Data Access check box is selected. Your query should now run without problem.
I'm running SQL Server 2000, and I need to store SQL Server Profiler output in a table. I could simply open my captured trace file from Profiler's GUI and use the Save as Trace Table option, but I don't want to use Profiler's GUI because it sends each row of the trace file as a separate insert to the table. In the past, I used the TraceFile COM object, which Microsoft shipped as part of the Microsoft SQL Server 7.0 Resource Guide (available as part of the Microsoft BackOffice 4.5 Resource Kit), to read the file and load the trace file to a table. However, Microsoft hasn't released a SQL Server 2000 version of the TraceFile COM reader. Do you know of another way that I can easily load my Profiler trace data into a table?
SQL Server 2000 has extended Profiler's trace-file management capabilities in many ways, including the addition of a new table-valued system function called fn_trace_gettable(). This function returns trace-file information in table format. So if you want to load trace data into a SQL Server table, you could use a command such as
SELECT * INTO MyTraceTable
FROM ::fn_trace_gettable
('c:\my_trace.trc', default)
You'll find that this function provides a fast and easy way to load trace data.
Prev. page
[1]
2
next page 
