October 25, 2010 at 6:39 am
I am having problems with using BCP to import into SQL Server, I havent used it before and have been reading on various sites on how it works. After many different attempts I have got this far (below) and still I am unable to import into a table.
I'm trying to find out where I have gone wrong
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp MyDatabase.dbo.MyTableName IN C:\BCP\Appts25102010.csv -eC:\BCP\Err.txt -c -t, -T -S'+ @@SERVERNAME
PRINT @sql
EXEC master..xp_cmdshell @sql
This is whats returned when I execute
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
I have checked permissions on C:\BCP and have given anything to do with SQL Server full permissions on the folder
Attached is a copy file of the CSV file I want to import
How can I get this to work?
I had posted this similar on another website but have not got anywhere with it and learnt more since then
October 25, 2010 at 8:30 am
this will look for the file on the C drive of the SQL server, is that where the file is?
---------------------------------------------------------------------
October 25, 2010 at 9:47 am
Yes, I created the C:\BCP\ folder for this
October 25, 2010 at 10:48 am
Do you have the same number of columns in the table as are in your file?
October 25, 2010 at 3:30 pm
Yes, I double checked that first 🙂
I tried using the out commands and that worked by exporting a table into a text file.
Would it be a better idea to import the data into a temp table, and do this by building it into a stored procedure along with code above?
October 26, 2010 at 5:30 am
Do you realize your specified field terminator is not the same as what's in the file?
You're specifying a comma. The field terminator appears to be a semicolon.
EDIT: You may also have to put the -S + @@ServerName before the -T. You need the server to log into before the credentials, I believe. But test it first by fixing the field terminator before trying this.
October 26, 2010 at 6:36 am
Do you realize your specified field terminator is not the same as what's in the file?
You're specifying a comma. The field terminator appears to be a semicolon.
Ah yes, I wondered what the comma was for 🙂 now I know. Ok I changed that to a semicolon and didnt have any joy with that either
EDIT: You may also have to put the -S + @@ServerName before the -T. You need the server to log into before the credentials, I believe. But test it first by fixing the field terminator before trying this.
I tried this as below
SELECT @sql = 'bcp LincsPCTReport.dbo.tblLincsPCTAppts IN C:\BCP\LincsPCTAppts.csv -eC:\BCP\Err.txt -c -t; -S'+ @@SERVERNAME+'-T'
and this returned an error
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
October 26, 2010 at 6:46 am
Do you have a proxy set up for xp_cmdshell?
If so does it have access to the file?
Far away is close at hand in the images of elsewhere.
Anon.
October 26, 2010 at 6:50 am
David Burrows (10/26/2010)
Do you have a proxy set up for xp_cmdshell?If so does it have access to the file?
Sorry I dont know what you mean, how would this be one?
October 26, 2010 at 7:03 am
jez.lisle (10/26/2010)
David Burrows (10/26/2010)
Do you have a proxy set up for xp_cmdshell?If so does it have access to the file?
Sorry I dont know what you mean, how would this be one?
The error you posted indicates one of two things, the data file does not exist or SQL Server does not have permission
From BOL
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.
CopyEXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'
For more information, see http://msdn.microsoft.com/en-us/library/ms190359(v=SQL.90).aspx
Far away is close at hand in the images of elsewhere.
Anon.
October 26, 2010 at 7:40 am
jez.lisle (10/26/2010)
EDIT: You may also have to put the -S + @@ServerName before the -T. You need the server to log into before the credentials, I believe. But test it first by fixing the field terminator before trying this.
I tried this as below
SELECT @sql = 'bcp LincsPCTReport.dbo.tblLincsPCTAppts IN C:\BCP\LincsPCTAppts.csv
-eC:\BCP\Err.txt -c -t; -S'+ @@SERVERNAME+'-T'
and this returned an error
Try putting a space before the -T so that it's not part of the Servername.
+ ' -T'
EDIT: Putting it in a code box to see if the space will show up better.
October 27, 2010 at 7:50 am
Dear ,
Chk the File Format From where ur Importing the data to the database and then chk ur Path which u have provided .There r u type of import of data one by Ur Query and another by ur GuI.
By GUI ,where u have to import just right click it then u will find the import Wizzard and then follow it .
October 27, 2010 at 8:27 am
Ivan,
Your post is almost incomprehensible to me. Could you edit out the abbreviations and put real words in their place so I can understand what you're trying to say?
October 27, 2010 at 10:04 am
Ivan Mohapatra (10/27/2010)
Dear ,Chk the File Format From where ur Importing the data to the database and then chk ur Path which u have provided .There r u type of import of data one by Ur Query and another by ur GuI.
By GUI ,where u have to import just right click it then u will find the import Wizzard and then follow it .
If I understand you right, then yes I checked the file location along with how I have written it and it's correct. Do you also mean use SSMS to import data into tables? I would use that or a SSIS package if I had anything other than SQL Server Express to use. Therefore I am learning how BCP works and using that for my database
October 27, 2010 at 11:04 am
jez.lisle (10/27/2010)
Ivan Mohapatra (10/27/2010)
Dear ,Chk the File Format From where ur Importing the data to the database and then chk ur Path which u have provided .There r u type of import of data one by Ur Query and another by ur GuI.
By GUI ,where u have to import just right click it then u will find the import Wizzard and then follow it .
If I understand you right, then yes I checked the file location along with how I have written it and it's correct. Do you also mean use SSMS to import data into tables? I would use that or a SSIS package if I had anything other than SQL Server Express to use. Therefore I am learning how BCP works and using that for my database
Nope... I mean you can if you want but BULK INSERT may be a better option. It's very similar to BCP except that it doesn't require the use of xp_CmdShell. You only need to have bulk admin privs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply