Problem with bcp in stored procedure when called from vb6

Question

Problem with bcp in stored procedure when called from vb6

Donald Sawford

SSCrazy

Points: 2778
More actions
May 18, 2004 at 8:23 am

#152147

Hi,
I modified a script from a recent thread from CT Klein and wz700's response to it to create a stored procedure toexport all tables to text files (thanks for the help).
The following script works great when called from QA, but we need to include the ability to call this from a vb 6 app that we will distribute. Why, when called from QA does it work great, but when called from an ado command object in vb6, it only exports 4 (always the same ones) of the 42 tables?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- Create procedure
--
-- Requires Path to export to
CREATE PROCEDURE dbo.usp_ExportTables
   @varFilePath VARCHAR(255) = NULL
as
--
--Declare variables
--
SET NOCOUNT ON
DECLARE @continue BIT
Declare @TableName Varchar(1000)
--
--Turn on default value to continue
--
SELECT @continue = 1
--
--Verify that path is valid
--
IF @varFilePath IS NULL OR DATALENGTH(LTRIM(@varFilePath)) < 3
   Begin
   SELECT @continue = 0
   END
--
-- ******************************************************************
-- Data has been validated, either return to the caller with a bad
-- return code or continue on.
-- ******************************************************************
If @continue = 0
   BEGIN
   --
   -- Return to the caller with a bad return code
   --
   RETURN 1
   END
ELSE

   BEGIN
--Build a cursor of all user table names
DECLARE Table_Cursor CURSOR FOR
Select Distinct SO.Name "Tables"
   From SysObjects SO
    Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
    And SO.xtype = 'U'
    Order By SO.Name
OPEN Table_Cursor
--Export records from each table
DECLARE @sql varchar(1000)
FETCH NEXT FROM Table_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
   Set @sql = 'Select * from '+db_name()+'..[' + @TableName + ']'
   --print(@TableName)
   set @sql = 'exec master.dbo.xp_cmdshell ''bcp "'+ @sql + '" queryout ' + @varFilePath + @TableName + '.txt -c'''
   exec(@SQL)
    --print (@SQL)
   FETCH NEXT FROM Table_Cursor INTO @TableName
END
Close Table_Cursor
Deallocate Table_Cursor
-- Return Successful code
RETURN 0
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
END
----------------------------------------------------------
Thanks for any suggestions you might offer.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Peter P SSCrazy Points: 2777 More actions · Answer 1

It sounds like you need to increase the CommandTimeout Property of your Command object. Or, possibly the ConnectionTimeout Property of the Connection object if you are using one.

Pete

Donald Sawford SSCrazy Points: 2778 More actions · Answer 2

Thanks for the suggestion. We used another method to accomplish the goal without the stored procedure, but I will experiment with this anyway as I get time for future reference.

I appreciate your taking the time to respond to the question.