SQL Server 2005 BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SQL Server 2005 BCP

Author

Topic

hari_sql
Starting Member

22 Posts

Posted - 2011-02-23 : 03:07:18

I need to perform BCP corresponding to a complex long query (more than 8000 characters).

EXEC @v_BCPResult = master.dbo.xp_cmdshell @v_BCPStatement, no_output
Here length of variable @v_BCPStatement of datatype varchar(8000) is more than 8000 characters. Due to this BCP fails.

I tried declaring @v_BCPStatement as varchar(max), but then get the following error during execution:

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.

So is this a limitation of SQL Server 2005 BCP command OR is there any work around?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-23 : 04:26:32

Create a Stored Procedure that executes your complex long query.
In your BCP then execute that Stored Procedure instead of the query.

No, you're never too old to Yak'n'Roll if you're too young to die.

hari_sql
Starting Member

22 Posts

Posted - 2011-02-23 : 06:51:25

Hi WebFred,

The code snippet as follows:

-- BCP string to export data
SET @v_BCPStatement = 'BCP "EXEC Spc_MyReport @p_StartDate,@p_EndDate,@p_BatchId" queryout "D:\SQL\MyReport.txt" -c -S'+ @v_Servername + ' -Uadmin -Ppwd'
-- Execute BCP command string, when succesfull the execution returns 0
EXEC @v_BCPResult = master.dbo.xp_cmdshell @v_BCPStatement, no_output
IF @v_BCPResult = 0
PRINT 'Successfully generated output file'
ELSE
PRINT 'Erorr generating output file'

Here PRINT @v_BCPStatement returns:
BCP "EXEC Spc_BatchReport @p_StartDate,@p_EndDate,@p_BatchId" queryout "D:\SQL\MyReport.txt" -c -SMyServer -Uadmin -Ppwd

But @v_BCPResult return 1 indicating BCP is not successful.
What could possibily be wrong here? Thank you.

Kind Regards,
Hari.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 07:01:17

Of course it did not work. Look at how you are calling the stored proc!

EXEC Spc_BatchReport @p_StartDate,@p_EndDate,@p_BatchId

Imagine running that statement on it's own (which is what command shell is doing)

The variables @p_StartDate,@p_EndDate,@p_BatchId don't exist! You haven't declared them!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 07:02:07

You should sustitute values for the parameters in the BCP call. Whatever the right values are for that instance of the call.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

hari_sql
Starting Member

22 Posts

Posted - 2011-02-23 : 07:04:19

Dear Transact Charlie

As I have mentioned above, what I have included is just a snippet from the whole code. I have declared the variables and am passing values when calling the proc.

Kind Regards
Hari.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 07:09:13

but you aren't! You might think you are but....... this is what your BCP string is actually doing!


BCP "EXEC Spc_BatchReport @p_StartDate,@p_EndDate,@p_BatchId" queryout "D:\SQL\MyReport.txt" -c -SMyServer -Uadmin -Ppwd

This bcp string is in its own scope -- The variables you are passing just don't exist in that scope. You would need to be issuing a BCP statement that looks like:


BCP "EXEC Spc_BatchReport '19000101','20100101',100" queryout "D:\SQL\MyReport.txt" -c -SMyServer -Uadmin -Ppwd

If your code doesn't actually look like the snippet you provided then please post the code you are actually trying to run!
Or similar.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

hari_sql
Starting Member

22 Posts

Posted - 2011-02-23 : 07:50:08

Hi Transact Charlie,

The complete code as follows:

CREATE PROCEDURE [dbo].[Spc_BCPBatchReport]
(@p_StartDate varchar(30),
@p_EndDate varchar(30),
@p_BatchId varchar(2),
@p_ExportFilePath nvarchar(500))
AS
BEGIN
SET NOCOUNT ON

-- Variable declarations
DECLARE @v_Query varchar(max),
@v_ExportDate varchar(100), @v_ExportFileName nvarchar(500),
@v_Servername nvarchar(50), @v_DBName varchar(100),
@v_BCPStatement varchar(8000), @v_BCPResult int

-- Get the server name
SELECT @v_Servername = @@servername
-- Get the db name
SELECT @v_DBName = db_name()

-- Get the date string to add to the filename
SELECT @v_ExportDate = REPLACE(@p_StartDate,'/','') + '_' + REPLACE(@p_EndDate,'/','')
-- Get the path and name of Export file
SELECT @v_ExportFileName = @p_ExportFilePath + 'Report' + @p_BatchId + '_' + @v_ExportDate + '.txt'

-- BCP string to export data
SET @v_BCPStatement = 'BCP "EXEC Spc_BatchReport '''+@p_StartDate+ ''',''' +@p_EndDate+ ''',''' +@p_BatchId+ '''" queryout "' + @v_ExportFileName + '" -c -S'+ @v_Servername + ' -Uadmin -Ppwd'

-- Execute the BCP command string
-- When succesfull the execution returns 0
EXEC @v_BCPResult = master.dbo.xp_cmdshell @v_BCPStatement, no_output
IF @v_BCPResult = 0
PRINT 'Successfully generated output file'
ELSE
PRINT 'Error generating output file'
END

Here,
PRINT @v_BCPStatement returns:
BCP "EXEC Spc_BatchReport '03/06/2010','04/06/2010','1'" queryout "D:\DB\BatchExport\Report1_03062010_04062010.txt" -c -SANNAPURNA -Uadmin -Ppwd

But BCP is not being performed and 'Error generating output file' is returned.
Thank you very much for the support.

Kind Regards,
Hari.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 10:29:26

try including the database name in the EXEC string. Or is that sp in the master db?

EXEC xyz.dbo.Spc_BatchReport .....

for example

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources