Problems creating format file.

sql2k sp4

Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.

declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec

output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO

CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping


DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname

SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''

OPEN FormatCard

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END

SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END

CLOSE FormatCard
DEALLOCATE FormatCard

GO

--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.

Problems creating Error File when using Bulk Insert or BCP from xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

I had this error too. It looks like a bug as BULK INSERT works fine without the -ERRORFILE option. It seems that the execution of the BULK INSERT first creates the file without closing it which results in the error message afterwards. As Workaround just don't use the option

Nobsay

Problem with using bcp

Hello all,
I try to execute the code below. Whatever I try I get the error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dc_temp_text'.
This also happens when I put the owner and the database name in from of the
table name.
Can anybody tell me what it is that I'm missing?
TIA
Ronald
CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
begin
dbcc checkident (dc_temp_text, reseed, 0)
declare @.BcpCommand varchar(5000)
declare @.TableName varchar(100)
set @.Tablename = QuoteName('dc_temp_text')
/*
Schrijf het text bestand
*/
delete from dbo.dc_temp_text
insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
set @.BcpCommand = 'bcp "select * from '
set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
set @.bcpCommand = @.BcpCommand + '" queryout
c:\testuser1\text.txt -Uuser -Ppassword -c'
exec master..xp_cmdshell @.BcpCommand
endI assume that you have created the stored procedure in the same database
that you have the table dc_temp_text.
If you comment out the BCP portion I am guessing that the proc works
correctly.
I do not see where you specify the database or the server within the BCP
string.
Instead of
exec master..xp_cmdshell @.BcpCommand
Try
SELECT @.BcpCommand AS HereIsTheBCPstatement
If the command is correct you should be able to run it within DOS from your
PC. Use the output of the query to test your BCP statement.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>|||Keith,

> If you comment out the BCP portion I am guessing that the proc works
> correctly.
Yes, ther is no problem then. The table is filled and everything checks out.

> I do not see where you specify the database or the server within the BCP
> string.
I added that a few minutes later with no effect. I also tried to run the
statement in de dos prompt with the same result.
Any other ideas?
Greetings
Ronald
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>I assume that you have created the stored procedure in the same database
>that you have the table dc_temp_text.
> If you comment out the BCP portion I am guessing that the proc works
> correctly.
> I do not see where you specify the database or the server within the BCP
> string.
> Instead of
> exec master..xp_cmdshell @.BcpCommand
> Try
> SELECT @.BcpCommand AS HereIsTheBCPstatement
> If the command is correct you should be able to run it within DOS from
> your PC. Use the output of the query to test your BCP statement.
> --
> Keith Kratochvil
>
> "Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
> news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||>I added that a few minutes later with no effect. I also tried to run the
>statement in de dos prompt with the same result.
Ok, now we have isolated the problem to the BCP statement.
Type BCP into Query Analyzer
Highlight the string BCP
Hit Shift_F1
Read up on BCP overview within Books Online. You will see that you are be
missing a few params.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:%23U3ffCEdGHA.380@.TK2MSFTNGP04.phx.gbl...
> Keith,
>
> Yes, ther is no problem then. The table is filled and everything checks
> out.
>
> I added that a few minutes later with no effect. I also tried to run the
> statement in de dos prompt with the same result.
> Any other ideas?
> Greetings
> Ronald
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
> news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>|||On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:

>Hello all,
>I try to execute the code below. Whatever I try I get the error:
>Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>'dc_temp_text'.
>This also happens when I put the owner and the database name in from of the
>table name.
>Can anybody tell me what it is that I'm missing?
Hi Ronald,
Where do you put the owner and database name in front of the table name?
The execution of bcp will open a new connection to the server that will
connect to the user's default database. It is really important that you
always database-qualify the tablename on the bcp command.
Also, try what happens if yoou replace
exec master..xp_cmdshell @.BcpCommand
with
PRINT @.BcpCommand
Maybe this helps you see the problem in the generated bcp command. And
if not, you can post the output of the PRINT command here and see if
someone else sees a problem with it.
Hugo Kornelis, SQL Server MVP|||Ronald Hermans (rhermans@.datamedicare.nl) writes:
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
Is the server you are running on a default instance? Or is it a named
instance?
Since you do not specify -S the BCP command will connect to the default
instance. You get can the server name from @.@.servername.
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,
Yes It is a Default instance, but just to make sure I entered the the
servername with the -S parameter. Unfortunatly it had no effect.
Ronald
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns97C06E3A3171Yazorman@.127.0.0.1...
> Ronald Hermans (rhermans@.datamedicare.nl) writes:
> Is the server you are running on a default instance? Or is it a named
> instance?
> Since you do not specify -S the BCP command will connect to the default
> instance. You get can the server name from @.@.servername.
>
>
> --
> 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|||Hugo,
bcp "select * from dc_temp_text" queryout
c:\\text.txt -Sservername -Uuser -Ppassword -c
I tried to put the db name and the owner name in front of the table name
with no effect. I still get the same error.
Ronald
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:ulo462drdh8s5rv72pjm9bd4l9v12afjd8@.
4ax.com...
> On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:
>
> Hi Ronald,
> Where do you put the owner and database name in front of the table name?
> The execution of bcp will open a new connection to the server that will
> connect to the user's default database. It is really important that you
> always database-qualify the tablename on the bcp command.
> Also, try what happens if yoou replace
> exec master..xp_cmdshell @.BcpCommand
> with
> PRINT @.BcpCommand
> Maybe this helps you see the problem in the generated bcp command. And
> if not, you can post the output of the PRINT command here and see if
> someone else sees a problem with it.
> --
> Hugo Kornelis, SQL Server MVP|||bcp "select * from [your_db_name_goes_here].[dbo].dc_temp_text" queryout
"c:\text.txt" -Uuser -Ppassword -c
-oj
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OaINpgMdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> bcp "select * from dc_temp_text" queryout
> c:\\text.txt -Sservername -Uuser -Ppassword -c
> I tried to put the db name and the owner name in front of the table name
> with no effect. I still get the same error.
> Ronald
>|||Hello all,
I think I may have found the problem (and if so, I'm going to cry). The
databasename starts with a 1 (one digit) and I think the bcp tool has a
problem with that.
Can anybody confirm this?
TIA
Ronald
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>