BCP Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP Errors

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-08 : 17:18:47

guys

I am getting a bcp error when I run the following stored procedure

alter PROCEDURE [dbo].[BCPObjects]
@filePath varchar(80),
@cmode varchar(15)
AS
DECLARE @cmd varchar(2000)
declare @tstamp varchar(15)
BEGIN
set @tstamp = cast(datepart(year, getdate()) as varchar)
+ replicate('0', 2-len(cast(datepart(mm, getdate()) as varchar)))+ cast(datepart(mm, getdate()) as varchar)
+ replicate('0', 2-len(cast(datepart(day, getdate()) as varchar))) + cast(datepart(day, getdate())as varchar)
+ '_' + + replicate('0', 2-len(cast(datepart(mi, getdate()) as varchar))) + cast(datepart(hh, getdate()) as varchar)
+ replicate('0', 2-len(cast(datepart(mi, getdate()) as varchar))) + cast(datepart(mi, getdate()) as varchar)

-- SET @cmd = 'bcp.exe ' + @dbName + '..' + @tbName + ' ' +
set @cmd = 'bcp.exe " SELECT TYPE1, TYPE2, TYPE3 FROM DBO.DOCTYPE1LOV " '
+ @cmode + ' ' +@filePath+@tstamp + '.txt ' + '-S ' +
'testdb\test' + ' -q ' + ' -c -T -e' + @filePath+@tstamp +'.log -o '
+ @filePath+@tstamp + '_out.log'
select @cmd -- + '...'
EXEC master.dbo.xp_cmdShell @cmd
END

EXEC BCPObjects 'c:\', 'queryOUT'

I get the following error any ideas??

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DBO.DOCTYPE1LOV'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 17:22:53

You need to use the 3 part naming convention for the object: DatabaseName.dbo.DOCTYPE1LOB.

Tara Kizer

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-08 : 17:34:15

Thanks that did the trick.

When I am trying to import the text file I am getting the following errors, are my server settings alright....

any ideas??

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''
SET @SQL = 'bcp Colonial..Collin_images IN "c:\source.txt" -c -t";" -T'

EXEC master..xp_cmdshell @SQL
_____________________________________

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 17:36:45

You haven't provided the -S switch.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources