A valid table name is required for in, out, or format options

Question

A valid table name is required for in, out, or format options

John Kotuby

Ten Centuries

Points: 1079
More actions
April 24, 2015 at 2:31 pm

#317027

Why am I getting message "A valid table name is required for in, out, or format options."
I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.
My server instance is "stat-hp\sqlexpress"
The database name is "STATRLO"
Owner is "dbo"
Table name is "PM-allactivity-emaillog_042315"
The bcp comand I am trying to run is:
bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:\database\Activity_c.fmt -S stat-hp\sqlexpress - T
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Yes I know the instance says sqlexpress...it was upgraded.
Can anyone see what the problem is?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'd expect either the keyword IN or OUT to exist in the query, right?

bcp STATRLO.dbo.PM-allactivity-emaillog_042315 in format nul -c -t, -f C:\database\Activity_c.fmt -S stat-hp\sqlexpress - T

i always use query out , but here's two examples i'm sure are correct:

--using a super special 4 char row delimiter to be sure it doesn't exist in the data

--flags explanation:

-- -c = charcater data

-- -t"[||]" = field terminator

-- -r"[~~]" = row terminator

-- -T' = Trusted connection

--out

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

--in

EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

--in via bulk insert

BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = '[||]',

ROWTERMINATOR = '[~~]',

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Kotuby Ten Centuries Points: 1079 More actions · Answer 2

Thank you for your reply Lowell.

I was trying to create a format file that I could later edit... to handle importing a CSV with bad records back into the same table. The CSV has 11 comma delimited text fields surrounded by double quotes. Unfortunately some of the text fields also contain double quotes and therefore fail in a simple data import wizard run.

In fact, that's how I ended up with the table I'm working with. I figured it would be easy to create the format file by using a table with the proper structure. However I'm stuck as you can see.

It looks to me like your BCP examples are actually trying to import or export records. Is that correct?

I thought I was using an example script meant to only create a format file. Maybe I'm mistaken.

John Kotuby Ten Centuries Points: 1079 More actions · Answer 3

Lowell,

I forgot to mention that I tried running your version of my script (copied form your post) and got this error.

"Unkown argument on command line 'nul'"