Bcp utility with Stored Proc - 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 2000 Forums

Transact-SQL (2000)

Bcp utility with Stored Proc

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-18 : 13:46:05

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @FILEPATH NVARCHAR(50))
AS
DECLARE @cmd varchar(2000)
BEGIN
set @cmd = 'bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3\ASTRO' + ' -q ' + ' -c -T -e' + @filePath+'.log -o '
+ @filePath+ '_out.log'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
END

Any suggestions and inputs would be helpful.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 13:54:30

What is this auth after the proc name? Is it a parameter value you are passing? >> "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS auth"
If so it should be in quotes.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-18 : 14:19:40

AUTH is table name which I am passing as parameter to generate the insert statements for the same. Even after I put the quotes I get the error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 14:48:32

Can you post the updated script?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-18 : 14:57:15

Below is the updated script for the stored proc and if I print the @cmd I get the following

bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS 'auth'" QUERYOUT C:\AICMS.sql -S NV-DEVSQL3\ASTRO -q -c -T -eC:\AICMS.log -o C:\AICMS_out.log

alter PROCEDURE INSERTTEST2 ( @FILEPATH NVARCHAR(50))
AS
DECLARE @cmd varchar(2000)
BEGIN
set @cmd = 'bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS ''auth''" '
+ 'QUERYOUT' + ' ' +@filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3\ASTRO' + ' -q ' + ' -c -T -e' + @filePath+'.log -o '
+ @filePath+ '_out.log'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
END

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 15:02:00

Can you post the script for the SP_GENERATE_INSERTS proc?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-18 : 15:11:32

I am able to execute sp_generate_inserts from the query analyzer and get the results. Its only when I include in stroed proc and use bcp utility it fails.

Anyways I am using the sp_generate_inserts from the following site

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 19:09:23

have you tried running the bcp command from the command prompt rather than via xp_cmdshell ?
Run the below directly in command prompt

bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS 'auth'" QUERYOUT C:\AICMS.sql -S NV-DEVSQL3\ASTRO -q -c -T -eC:\AICMS.log -o C:\AICMS_out.log

KH
[spoiler]Time is always against us[/spoiler]

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-18 : 22:27:41

I get the same error when I run the bcp command through cmd prompt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources