BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:
> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>

bcp error when queryout is stored procedure

I am receiving an error when executing bcp out and queryout is “exec stored
procedure”. Please see details below:
use workarea
go
if object_Id('P_TMP') is not null drop proc P_TMP
go
create PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * INTO #TMP FROM SYSOBJECTS
SELECT * FROM #TMP
END
go
exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
"C:\Temp\TMP.TXT" -c -Ssqldev -N'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#TMP'.
[code]
alter PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SYSOBJECTS
END
go
[/code]
It should work.
Cristian Lefter, SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||Hi,
Scope of temp (#) table will be lost in next session . So try using global
temp (##) table.
Thanks
Hari
SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||I posted previous code just as an example, what I think is incorrect BCP
behavior. Applications (including bcp) should not be aware how stored
procedure produces result set.
Real stored procedure is very large and it populates temporary table through
its all execution. I will consider use of global or "permanent" tables to
work around this, but it will not be trivial, because the stored procedure
will be executed in multiple processes at same time.
Does anybody know how I can submit bug fix (improvement) request to Microsoft?
Thank you for your responses!
Vygandas
MCDBA, MCSD
|||BCP tries to get how the result set will look like in order to generate the file format correctly.
It uses SET FMTONLY ON for this. You can try adding SET FMTONLY OFF in the beginning of he "query
(proc)" you will execute so that the temp table will actually be created, but be aware that the proc
then will be executed twice!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:424A4746-2C0C-4D00-B529-82FE4856E371@.microsoft.com...
>I posted previous code just as an example, what I think is incorrect BCP
> behavior. Applications (including bcp) should not be aware how stored
> procedure produces result set.
> Real stored procedure is very large and it populates temporary table through
> its all execution. I will consider use of global or "permanent" tables to
> work around this, but it will not be trivial, because the stored procedure
> will be executed in multiple processes at same time.
> Does anybody know how I can submit bug fix (improvement) request to Microsoft?
> Thank you for your responses!
> Vygandas
> MCDBA, MCSD
>