Weird error from 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 2005 Forums

Transact-SQL (2005)

Weird error from stored proc

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-16 : 11:03:32

Guys,

I have the following stored procedure - insertest2 which tries to print the results of the stored proc sp_generate_inserts. I get the following error. But the strange thing is stored proc runs fine from sql server 2005 the management studio

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

alter PROCEDURE INSERTTEST2 ( @FILEPATH NVARCHAR(50))
AS
DECLARE @cmd varchar(2000)
BEGIN
set @cmd = 'bcp.exe "EXEC 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 also blog on msdn forums for this issue but it doesnt seem to make sense.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236674&SiteID=1

Any suggestions/inputs would help.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 11:21:33

Use the profiler to see what bcp is executing then try running that - including any set commands.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-16 : 11:30:03

nr

Thank you for your reply, but even after I turn on the profiler it doesnt give anything tangible. Any ideas?? inner stored proc - sp_generate_inserts runs fine from the management studio.

Out of ideas at the moment

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 11:38:30

You already have the SELEECT @Cmd in your proc. So when you execute the proc it must be printing the value in @cmd. how does it look? Can you post tht here?

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

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-16 : 11:39:47

here you go

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 11:49:53

when using BCP always use the 3 part Database.Owner.Object. By default it looks for objects under master only.

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

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-16 : 11:57:24

quote:
Originally posted by dinakar

when using BCP always use the 3 part Database.Owner.Object. By default it looks for objects under master only.

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

Perhaps, and I've seen SQL Server throw eroneous errors before, but not usually (DB2 is a master at eroneous errors)

But since they're executing a sproc, and we seen that, as matter of the process of elimination?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources