BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invali
d
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Invalid Instance()).
NULLTry,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:

> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happen
s
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Inva
lid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Co
nnectionOpen
> (Invalid Instance()).
> NULL
>
>

BCP Error

Hi All,
I am getting the following error when I execute the following BCP command fr
om query analyser.
BCP Command
=========
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAd
min_dbo_tbl_CheckServerRoleMembers_20040
331161058 in "c:\Temp\DBAdmin_Fwd_MA
XDRBUS02_DBAdmin_tbl_CheckServerRoleMemb
ers.bcp" /S204.5.2.11,1942 /UDBAdmi
nUser /PDtyrimby0 /c /b1000
/m1'
Error
===
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAd
min_dbo_tbl_CheckServerRoleMembers_20040
331161058 in "c:\Temp\DBAdmin_Fwd_MA
XDRBUS02_DBAdmin_tbl_CheckServerRoleMemb
ers.bcp" /S204.5.2.11,1942 /UDBAdmi
nUser /PDtyrimby0 /c /b1000
/m1'
Can any help me how solve this problem
Thanks & Regards
BalajiHi,
Replace '/' with '-' (Hyphen) in the BCP command.
-S204.5.2.11,1942 -UDBAdminUser -PDtyrimby0 -c -b1000 -m1
Thanks
Hari
MCDBA
"Balaji" <anonymous@.discussions.microsoft.com> wrote in message
news:4C7F90DC-EDAB-474A-B62E-5E6C72700BD8@.microsoft.com...
> Hi All,
> I am getting the following error when I execute the following BCP command
from query analyser.
> BCP Command
> =========
> xp_cmdshell 'c:\MSSQL7\BINN\BCP
SQLManager.dbo. z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_dbo_tbl
_CheckServerRoleMembe
rs_20040331161058 in
" c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_t
bl_CheckServerRoleMembers.bcp"
/S204.5.2.11,1942 /UDBAdminUser /PDtyrimby0 /c /b1000 /m1'
> Error
> ===
> xp_cmdshell 'c:\MSSQL7\BINN\BCP
SQLManager.dbo. z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_dbo_tbl
_CheckServerRoleMembe
rs_20040331161058 in
" c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_t
bl_CheckServerRoleMembers.bcp"
/S204.5.2.11,1942 /UDBAdminUser /PDtyrimby0 /c /b1000 /m1'
> Can any help me how solve this problem
> Thanks & Regards
> Balaji

BCP Error

I am trying to bcp data out of one table into another copy of the same table
on another server. When I do I get the following error. The cmd that I am
using is:
BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
It works on 33 of the 35 tables. Both of the tables that fail have a column
called [function] that is char 255. I have tried the command with the '-
n'
and without it. Any suggestions?
SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Se
rver
Driver][SQL Server]Incorrect syntax near the keyword 'Function'.You might try the '-q' parameter so that quoted identifiers are used. Also,
save yourself some grief by avoiding reserved words as column names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:0F740E25-C090-418F-991E-88BAEBFA31EE@.microsoft.com...
>I am trying to bcp data out of one table into another copy of the same
>table
> on another server. When I do I get the following error. The cmd that I am
> using is:
> BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
> It works on 33 of the 35 tables. Both of the tables that fail have a
> column
> called [function] that is char 255. I have tried the command with the
'-n'
> and without it. Any suggestions?
> SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL
Server
> Driver][SQL Server]Incorrect syntax near the keyword 'Function'.

BCP Entire DB in MYSQL 2000

I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".

Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.

BCP derived fields

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by dipu vp

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)

i have not tried this, but here's an idea:

i think you can BCP into an existing table. so your FMT (format file) could be different from that of the existing table structure. can you try to have an format file based on Location, UserLocId structure and have an existing table with an extra calculated filed called UserCode with default value rtrim(Location) + '_' + ltrim(UserLocId )

i don't know if it will work but worth a shot.

BCP Deployment

Hi all,

I am doing a project that involved with the big volume of data to be
uploaded to the SQL server. Due to the limitation at my employer's
site, I couldn't have the bulk admin right. I am looking at the
command BCP and wonder if there is any copyright issues if I were to
use BCP and to deploy the command?

Thanx.Hi

The use of BCP and the other client tools are governed by the EULA. Without
knowing the exact details of what you are trying to do it would not be
possible to say if you are covered or not.

John

"CY Lim" <limcyang@.yahoo.com> wrote in message
news:ea2b1aa7.0310200251.3d96823a@.posting.google.c om...
> Hi all,
> I am doing a project that involved with the big volume of data to be
> uploaded to the SQL server. Due to the limitation at my employer's
> site, I couldn't have the bulk admin right. I am looking at the
> command BCP and wonder if there is any copyright issues if I were to
> use BCP and to deploy the command?
> Thanx.

BCP delivery the: character

Using the BCP utility ->

With a degree character in the flat data file, can anyone deliver this to a table without SQL Server changing it to stacked bar:

CREATE TABLE "dbo"."F_conv"
(
"col1" VARCHAR(21) NOT NULL
)
;

myformat.fmt

8.0
1
1 SQLCHAR 0 30 "\r\n" 1 col1 ""

mydata.dat file

1332 NS 4 Tall 32 oz

bcp command

bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"I don't think declaring the column as VARCHAR(...) will satisfy your requirements. I changed your sample to NVARCHAR(32) for the column and got a little closer (char(166) instead of char(176) for the degree sign) and then was able to perform a REPLACE on the column. I am pretty sure you also need to address collation of the column to ensure that your data gets imported correctly.|||Try taking BCP out of the picture, and use:CREATE TABLE dbo.tDstachon (
col1 CHAR(21) NOT NULL
)
GO

BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt'
BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt' WITH (CODEPAGE = 'ACP')

SELECT * FROM dbo.tDstachon-PatP|||thanks guys.

cheers,
d.