Bcp utility with stored procedure

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 dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

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

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

Batch T-SQL Scripts

In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @. plus the path and file name. So, if I have a script called E:\CreateTable.sql, I can simply enter @.CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:\CreateTable.sql file (there may be one or more commands).

Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?

Thanks,

hmscottI don't know of a way to do it from a script in either Query Analyzer or OSQL, but you can open a new file in Query Analyzer and execute it that way and you can use the ED command in OSQL to allow you to incorporate the file (via the editor) there too.

-PatP|||Not that I've seen...the closest is

File>Open>filename.sql

[CTRL]+E

And Whore-acle is so much more painful in SQL+...I guess each has it's own pluses...

Did you ever use mask.sql?

Oh, and osql could run in a command line pretty easily...just make sure you redirect the output...

But why bother, unless you're releasing a script to production...|||Thnx guys. Just wishful thinking...

Batch scripts

Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example

Batch scripts

Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate tha
t
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check ou
t
SQL Agent.
HTH
John
"kailash" wrote:

> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example

Batch scripts

Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with example
Hi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:

> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example