January 5, 2006 at 9:02 am
Hi,
I have a couple of questions that I hope someone able to help.
1. What is the maximum length of the queryout SQL statement could be specified? I remember there is a limit on the length of select statement for the queryout. I can't seem to find any reference on the web anymore.
2. I ran the BCP using queryout with the TSQL to run stored procedure that returns result of select statement. I do each BCP for every table that I need. However, at random, I encountered error:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
It happens at different table everytime when an error occured.
I run my BCP like the following
"bcp" "exec DWS.dbo.usp_RetrieveDataWrapper 'DatabaseName', 'dbo', 'SourceTableName' " QUERYOUT D:\Internet\BCPSender\Working\Package3\DestinationFileName.dat -N -k -SServerName -T
My Stored procedure is as the following:
CREATE PROCEDURE dbo.usp_RetrieveDataWrapper @DatabaseName varchar(50),
@TableOwner varchar(50),
@TableName varchar(128)
AS
BEGIN
-- Declare local variables
DECLARE @SQLStatement varchar(4096)
EXEC dbo.usp_RetrieveDataBase @DatabaseName = @DatabaseName,
@TableOwner = @TableOwner,
@TableName = @TableName
SELECT @SQLStatement = SQLStatement FROM TableExportList
WHERE IsNull(TableOwner, 'dbo') = IsNull(@TableOwner, 'dbo') AND TableName = @TableName
IF @SQLStatement IS NULL
BEGIN
exec('SELECT * FROM ' + @DatabaseName + '.' + @TableOwner + '.' + @TableName)
END
ELSE
BEGIN
exec(@SQLStatement)
END
END
GO
Thanks.
January 9, 2006 at 8:00 am
This was removed by the editor as SPAM
August 1, 2006 at 2:50 pm
i too have the same question, but am still plugging away
at it this thing.
having trouble just getting the sp to work
bcp "sp_thingy mydb" queryout c:\output.bcp
_________________________
January 23, 2007 at 2:31 pm
I have just finished my second bout with this error message. Both times, the root cause had to do with the size of the output. The first fix was to increase the packet size (-a switch) to it's maximum value of 65535. This worked for a while, but I started getting the error again, so this time to fix it, I had to break up my data into a size that would not break BCP. Of course, BCP overwites the file, so each output segment had to be to a different file. Once the files were BCP'd out, I went through the loop again to merge the segment files together into one and delete the segment files.
In my case, the maximum number of records I could BCP without error was 250. Below is my code (the HTML format screwed up my :
Create
Procedure [dbo].[CreateFile]
@SessionID varchar
(50),
@dbName varchar
(100),
@FileName varchar
(100)
AS
/***********************************************
Author - Danny Gilbert
Date - July 27/2006
--------------------------------------------------
Updates
Jan 23 2006 D. Gilbert Break write out to 250 lines each to avoid BCP error.
*************************************************/
set
nocount on
declare
@ip varchar(20), @Path varchar(1000), @FullFileName varchar(1000), @rc int, @msg varchar(200), @maxlines int
Set @maxlines = 250
declare
@ln varchar(1000)
set
@path = 'D:\Files\'
set
@FullFileName = @path + @filename
declare
@cmd varchar(8000)
set
@cmd = 'Del ' + char(34) + @FullFileName + char(34) + ' /Q'
Declare
@1stLine int, @startline int, @endline int, @nomore bit, @writecount int, @w int
set
@writecount = 0
set
@nomore = 0
Select
top 1 @1stLine = recno from edsFileWrite where sessionid = @sessionid order by recno
Select
@writecount = count(*)/@maxlines from edsFileWrite where sessionid = @sessionid
if
@writecount % @maxlines <> 0 OR @writecount = 0
set @writecount = @writecount + 1
set
@w = @writecount
set
@endline = @1stline + @maxlines
WHILE
@WriteCount > 0
BEGIN
set @cmd = 'bcp "SELECT ln FROM ' + @dbname + '.dbo.FileWrite where sessionid = ' + char(39) + @sessionid + char(39) + ' AND recno between ' + cast(@1stLine as varchar(100)) + ' AND ' + cast(@Endline as varchar(100)) + ' order by recno" queryout ' + char(34) + @FullFileName + cast(@writecount as varchar(10)) + char(34) + ' -c -S' + @@ServerName + ' -UMyLogin -PMypassword -a65535'
exec @rc = master.dbo.xp_cmdshell @cmd, no_output
Set @WriteCount = @WriteCount - 1
Set @1stLine = @Endline + 1
set @endline = @1stLine + @maxlines
END
set @WriteCount = @w
set
@cmd = 'Type ' + char(34) + @FullFileName + cast(@writecount as varchar(10)) + char(34) + ' > ' + char(34) + @FullFileName + char(34)
Exec
master.dbo.xp_cmdshell @cmd, no_output
set
@cmd = 'Del ' + char(34) + @FullFileName + cast(@writecount as varchar(10)) + char(34) + ' /q'
Exec
master.dbo.xp_cmdshell @cmd, no_output
set
@WriteCount = @WriteCount - 1
WHILE
@WriteCount > 0
BEGIN
set @cmd = 'Type ' + char(34) + @FullFileName + cast(@writecount as varchar(10)) + char(34) + ' >> ' + char(34) + @FullFileName + char(34)
exec @rc = master.dbo.xp_cmdshell @cmd, no_output
set @cmd = 'Del ' + char(34) + @FullFileName + cast(@writecount as varchar(10)) + char(34) + ' /q'
Exec master.dbo.xp_cmdshell @cmd, no_output
Set @WriteCount = @WriteCount - 1
END
if
@rc = 0
BEGIN
set @rc = 2
set @msg = 'File created on ' + @@ServerName + ' at ' + @filename
END
else
BEGIN
set @rc = 1
set @msg = 'Error - BCP error creating XML file'
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply