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

bcp utility

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks allYou can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Mike (mckeyes@.gmail.com) writes:

Quote:

Originally Posted by

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP out in UTF8 format

How to generate a file using BCP in UTF8 format. The table contains varchar,nvarchar and money as dataytpe for some columns. Will the –w switch in BCP for data file type would solve the problem because SQL server stores the data in UCS 2.0 format.
No, BCP will save it as UCS2
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:48BA7BC9-61DF-45FF-8D8F-586C21D0CF07@.microsoft.com...
> How to generate a file using BCP in UTF8 format. The table contains
> varchar,nvarchar and money as dataytpe for some columns. Will the -w
> switch in BCP for data file type would solve the problem because SQL
> server stores the data in UCS 2.0 format.
>

BCP generates 0x00 characters

Hello,
I'm using BCP with a .FMT file to generate exports of some tables.
BCP sometimes messes up the file by placing 0x00 characters (ASCII value 00)
in the export. It looks like BCP translates NULL values or empty fields like
this.
Strange enough, this doesn't happen all the time. It will happen every once
in a while (data changes all the time). When it happens, it messes up the
entire file.intGod (intGod@.discussions.microsoft.com) writes:
> I'm using BCP with a .FMT file to generate exports of some tables.
> BCP sometimes messes up the file by placing 0x00 characters (ASCII value
> 00) in the export. It looks like BCP translates NULL values or empty
> fields like this.
> Strange enough, this doesn't happen all the time. It will happen every
> once in a while (data changes all the time). When it happens, it messes
> up the entire file.
My guess the 0x00 characters are there, because they appear in the data.
How does your format file look like?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" wrote:
> My guess the 0x00 characters are there, because they appear in the data.
> How does your format file look like?
Dear Erland,
I found the root of the problem but I don't know why BCP behaves like this.
The problem is: sometimes (only sometimes) BCP will treat an empty field
incorrectly and insert a 0x00 character instead of the string. By using a
CASE on that field (case fieldName when '' then ' ' else fieldName), the
problem is solved. Still, it's annoying.
I've never come across this problem but I've never used .fmt files before.
I've always used tab-delimited.|||intGod (intGod@.discussions.microsoft.com) writes:
> I found the root of the problem but I don't know why BCP behaves like
> this.
> The problem is: sometimes (only sometimes) BCP will treat an empty field
> incorrectly and insert a 0x00 character instead of the string. By using a
> CASE on that field (case fieldName when '' then ' ' else fieldName), the
> problem is solved. Still, it's annoying.
> I've never come across this problem but I've never used .fmt files before.
> I've always used tab-delimited.
Since you did not post the format file, I cannot comment much more. But
it appears that you use the queryout option. I have not use queryout myself
much, but I have the impression that it is considered buggy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_revlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
I would appreciate any incite on the solution to this problem as well as the cause.
Thank you in advance.
Robert
If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>
|||If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_re
vlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"
testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must conta
in at least one column
I would appreciate any incite on the solution to this problem as well as the
cause.
Thank you in advance.
RobertIf all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at[
/vbcol]
least one column[vbcol=seagreen]
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

BCP - File generate and name HELP

Hi All
I am trying to generate and name a file with the
following syntax : but get Error: 'bcpSelect' is not
recognized as an internal or external command,
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
DECLARE @.Con varchar(500)
Select @.Con = DataCon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.B = 'Select * from FileNameCreate + +'
Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
c -Usa -P -SUKLUN005\UKLUS0004H'
EXEC master..XP_cmdshell @.str"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09e701c3a606$1561a040$a301280a@.phx.gbl...
> Hi All
> I am trying to generate and name a file with the
> following syntax : but get Error: 'bcpSelect' is not
> recognized as an internal or external command,
>
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> DECLARE @.Con varchar(500)
> Select @.Con = DataCon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.B = 'Select * from FileNameCreate + +'
> Select @.str='bcp' + @.B + 'queryout c:\Lut' + @.A + '.txt -
> c -Usa -P -SUKLUN005\UKLUS0004H'
> EXEC master..XP_cmdshell @.str
You need to leave additional spaces in your command string, and you also
need double quotes around a query used with BCP:
select @.str = 'bcp "' + @.B + '" queryout...'
If you're building dynamic strings to execute, it usually helps to 'SELECT
@.str' just before you execute it, at least during development, to make sure
that the command is being built as you expect.
Simon|||Hi Simon
I have edited the file like this: and get error: SQLState
= 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver]
[SQL Server]Line 1: Incorrect syntax near 'Test2'.
Test2 is correct information from the table....
DECLARE @.A char(100)
DECLARE @.str varchar(200)
DECLARE @.B Char(100)
Select @.B = Datacon from FileNameCreate
Select @.A = SerialNo from FileNameCreate
Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
Usa -P -"SUKLUN005\UKLUS004H"'
EXEC master..XP_cmdshell @.str|||Brian,
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
You have declare @.A and @.B as fixed character data type. Change them
to varchar(100).
Then substitute the following for the exec statement:
print @.str
--EXEC master..XP_cmdshell @.st
It will then be obvious what your problems are.
Linda|||Brian;
I'd suggest that you put a print statement after you have constructed the
dynamic SQL and comment out the EXECUTE. Run the script to see what SQL
statement you are getting. When you are satisfied with the SQL statement,
comment out the print statement and uncomment the EXECUTE statement. Often,
this is much quicker to a solution than getting feedback from others.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:003f01c3a610$76060760$a101280a@.phx.gbl...
> Hi Simon
> I have edited the file like this: and get error: SQLState
> = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver]
> [SQL Server]Line 1: Incorrect syntax near 'Test2'.
> Test2 is correct information from the table....
> DECLARE @.A char(100)
> DECLARE @.str varchar(200)
> DECLARE @.B Char(100)
> Select @.B = Datacon from FileNameCreate
> Select @.A = SerialNo from FileNameCreate
> Select @.str = 'bcp "'+ @.B +'" queryout "c:\Lut.txt" -c -
> Usa -P -"SUKLUN005\UKLUS004H"'
> EXEC master..XP_cmdshell @.str
>|||The Print does help. However i now get the following error
Line 7: Incorrect syntax near 'C:\Lut.txt'.
I have tryed the following:
The file exists.
Using ', " and both.|||This is now corrected by 'queryout C:\Lut.txt'
-S Server name
Now i get: Line 7: Incorrect syntax near 'UKLUN005
\UKLUS004H'.
>--Original Message--
>The Print does help. However i now get the following
error
>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>I have tryed the following:
>The file exists.
>Using ', " and both.
>.
>|||Thanks all
BCP is now working and i am now using it in my live
system..............
>--Original Message--
>This is now corrected by 'queryout C:\Lut.txt'
>-S Server name
>Now i get: Line 7: Incorrect syntax near 'UKLUN005
>\UKLUS004H'.
>
>>--Original Message--
>>The Print does help. However i now get the following
>error
>>Line 7: Incorrect syntax near 'C:\Lut.txt'.
>>I have tryed the following:
>>The file exists.
>>Using ', " and both.
>>.
>.
>