Enter the file storage type of field SourceID [char] when using xp_cmdshell

Question

Enter the file storage type of field SourceID [char] when using xp_cmdshell

kklenk

Old Hand

Points: 337
More actions
July 19, 2011 at 1:40 pm

#390016

Hello all,
Trying my hand at xp cmdshell to get a tab delimited text file. Should I be using SSIS instead?
Here is my command I'm trying to run in QA on Sql 2005
DECLARE @FileName varchar(150),
@bcpCommand varchar(5000)
SET @FileName = REPLACE('c:\TEst\test_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp database..table out'
SET @bcpCommand = @bcpCommand + @FileName + '-c -t, -T '
EXEC master..xp_cmdshell
@bcpCommand
and I get the below result. SourceID is the first field in the table. All data types in the table are varchar
Enter the file storage type of field SourceID [char] when using xp_cmdshell
It creates the file in the folder but it is empty. The table has 100 rows.

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I use SSIS for that kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

GSquared (7/19/2011)
I use SSIS for that kind of thing.

+1 on SSIS

If you must use bcp do yourself a favor and move the call into a SQL Agent CmdExec or PowerShell job step, or better yet into a PowerShell script and call the PowerShell script from an Agent job.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 3

You need to specify the types that output for BCP.

You can get native database types etc.

I would not use SSIS, bcp is much quicker to design and you can run it on any system without having to fiddle with packages.

SSIS has it's place though for for a one liner. BCP export is a champion and its pretty fast.

Because your bcp doesn't have a switch for native DB types it's asking yu to convert it, just add the switch and you should be 100%

I think -n instead of -c is native db types.

BTW, native is also the fastest type to export a file.

Cheers

Jannie

GSquared SSC Guru Points: 260824 More actions · Answer 4

Since you can create an SSIS package by running through the Export Wizard, I really can't say it's complex or difficult to build.

Building a text file export by going into BIDS and building it should take about 2 minutes under normal circumstances, especially if you're exporting a whole table, a whole view, or a query you've already written.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kklenk Old Hand Points: 337 More actions · Answer 5

Thanks everyone,

So I've gone the SSIS route. I have it working. I want to implement ftp'ing the file now. I've seen a couple posts on how to do this but I'm not quite getting it. The file name is variable. I can ftp a static file name fine but not the variable file name. I read something about creating a script file to do this but I don't know how. Do I need to open a different discussion for this?

GSquared SSC Guru Points: 260824 More actions · Answer 6

Nah. Same problem, just step two on it.

The SSIS FTP object uses a file connection to manage what it uploads. In the file connection, make the connection string into an expression, and then use whatever variables you need in order to specify that. Then set the connection to validate at runtime.

I have an SSIS package that uploads multiple files every day doing that. The file names are generated by an expression that appends a formatted date to a string variable. Works beautifully. Getting the string right took a few minutes work, because I'm less familiar with SSIS's string tools than I am with T-SQL's, but it wasn't that big a deal.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kklenk Old Hand Points: 337 More actions · Answer 7

GSquared, thanks for the reply. I created a variable that is the entire Path. I created an expression that uses the variable plus the datetime, very similar to what you are doing.

So the text file is created correctly with the date time but the ftp task fails.

Error at FTP Task: Failed to lock variable "c:\fileupload\test.txt" for read access with error 0xc0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable formt eh Variables collection on a container during execution of the package and the variable is not there"

I've done both create the variable with package scope and created two variables one for the flat file connection scope and one for the ftp task scope but in both instances there is an error.

Thanks for any help.

GSquared SSC Guru Points: 260824 More actions · Answer 8

Is the file-create step before the FTP step in the package (green arrow from one to the next in the control flow)?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon