Bcp utility with Stored Proc
-
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
-
Sp_generate_inserts runs fine from query analyzer - I dont think there is a problem with that its only when I usd as part of bcp utility it gives me error.
Basically the purpose of this sp is to generate insert statements for the table which is passed as variable.
I have tried with quotes on it still gives me the same error.
SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.
The code for sp_generate_inserts is at the following link
http://vyaskn.tripod.com/code/generate_inserts_2005.txt
Thanks
-
I get the following
bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS 'auth'" QUERYOUT C:\AICMS.sql -S NV-DEVSQL3 -q -c -T -eC:\AICMS.log -o C:\AICMS_out.log
-
any ideas guys ??
-
am,
I agree with mrpolecat. It is much easier to help someone out when all the code is posted. Others on the forum are much more likely to respond if you provide the create statements for sprocs and tables so we can just test it in our own development environments rather than trying to guess what your code is. That being said, I've got some sample code below. My attempt to write a sample of what you're doing.
Here's a comparison of your EXEC and my EXEC, they're pretty close.
bcp.exe "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS 'auth'" QUERYOUT C:\AICMS.sql -S NV-DEVSQL3 -q -c -T -eC:\AICMS.log -o C:\AICMS_out.log
bcp.exe "EXEC cif.dbo.IFGenerateInsert 'LaborGroups'" QUERYOUT c:/temp/IFInsertTest.sql -S development -U XXX -P XXX -c -T -e c:/temp/IFInsertTest.log -o c:/temp/IFInsertTest_out.log
Why are you using -q option? I left it out.
According to BOL:
-q
Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a space or a quotation mark. Enclose the entire three-part table or view name in double quotation marks (" ").
SAMPLE CODE THAT WORKS:
--Create the sproc that generate the insert statment
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IFGenerateInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IFGenerateInsert]
GO
CREATE PROC IFGenerateInsert (
@TableName varchar(100)
)
AS
--generate an insert statement for testing
declare @insertStmt varchar(500)
set @insertStmt = 'insert into authors (id,name,book) values (1,''Joe Smith'',''Web Analytics'')'
select @insertStmt
GO
--Create the sproc that calls bcp via command shell
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IFInsertTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IFInsertTest]
GO
CREATE PROCEDURE IFINSERTTEST ( @FILEPATH NVARCHAR(50))
AS
DECLARE @cmd varchar(2000)
BEGIN
set @cmd = 'bcp.exe "EXEC cif.dbo.IFGenerateInsert ''LaborGroups''" '
+ 'QUERYOUT '+@filePath+ '.sql '
--update the userid and password
+'-S development -U XXX -P XXX -c -T -e '+ @filePath+'.log -o ' + @filePath+ '_out.log'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
END
GO
--Execute the Sproc
EXEC IFInsertTest 'c:/temp/IFInsertTest'
Hope this helps
-
Thank you for your inputs
I will try it out, sp_generate_inserts code is from the following website.
-
Just a side note here but in 2005 xp_cmdshell is disabled by default (Surface Area Config) and can also be a pretty dangerous thing to allow. Ever think about using SSIS for this stuff?
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
That link doesn't seem to work.
-
Oh bother... all of us missed it
... see anything out of place here, folks??? 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
This proc should be like this... this isn't Oracle or PL/SQL... BEGIN/END are not required in procs but, if you want to use them, you can... but, unlike Oracle, variable declarations must be inside the first begin, no outside of PL/SQL blocks like in Oracle...
alter PROCEDURE INSERTTEST2 ( @FILEPATH NVARCHAR(50))
AS
BEGIN
DECLARE @cmd varchar(2000)
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
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, thank you for the reply but still doesnt seem to work. I get the same
error. I am running out of ideas
SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.
Below is the cmd from PRINT statement.
bcp.exe "EXEC SP_GENERATE_INSERTS 'auth'" QUERYOUT C:\AICMS.sql -S NV-DEVSQL3\ASTRO -q -c -T -eC:\AICMS.log -o C:\AICMS_out.log
Thanks
-
For simplicity, did you try removing the Begin/End?
-
Try this:
Change "EXEC vbaicms_dev.dbo.SP_GENERATE_INSERTS auth"
to "EXEC dbo.SP_GENERATE_INSERTS auth"
.. and instead add the dbname with the -d parameter
(-d vbaicms_dev)
...and/or check if the proc SP_GENERATE_INSERTS is marked as a systemobject.
If it isn't, exec sp_MS_marksystemobject so that it is, then try again.
/Kenneth
-
Look for SUBSTRING in your mysterious SP_GENERATE_INSERTS procedure.
Find why CHARINDEX(...) inside SUBSTRING returns 0 and CHARINDEX(...)-1 becomes "Invalid length parameter".
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply