Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Question Unanswered: bcp - invalid object name

    I have a stored proc that I have written to execute the bcp. I am getting an invalid object name error that points at my database.


    set @strSQL = 'bcp "SELECT convert(varchar(8), s.RecordId) as RECORDID, s.NAME,' +
    's.COMPANY, s.ADDRESS1, ' +
    's.ADDRESS2, s.ADDRESS3, s.CITY, s.STATE, ' +
    's.ZIP, s.ZIP4, convert(varchar(8), s.StdId) as STDID, ' +
    'convert(varchar(8), s.FileId) as FILEID, ' +
    'c.DPBC, c.CRT, c.LOT, c.LOTORD, ' +
    'c.COUNTY, c.COUNTYNUM, c.CONGCODE, ' +
    'c.ERROR_STAT, c.DSN, c.BIC ' +
    'FROM ' +
    '00007003.dbo.tblStd s ' +
    ' left join ' + '00007003.dbo.tblCass c ' +
    'on c.RecordId = s.RecordId ' +
    'WHERE s.FileId = ' + cast(@intFileId as varchar) + '" queryout ' + @strOutputFile +
    ' -f ' + @strFormatFile + ' -T'

    exec xp_cmdshell @strSQL

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does the SELECT statement execute ok on its own?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    It will only work as:

    SELECT convert(varchar(8), s.RecordId) as RECORDID, s.NAME,
    s.COMPANY, s.ADDRESS1,
    s.ADDRESS2, s.ADDRESS3, s.CITY, s.STATE,
    s.ZIP, s.ZIP4, convert(varchar(8), s.StdId) as STDID,
    convert(varchar(8), s.FileId) as FILEID,
    c.DPBC, c.CRT, c.LOT, c.LOTORD,
    c.COUNTY, c.COUNTYNUM, c.CONGCODE,
    c.ERROR_STAT, c.DSN, c.BIC
    FROM
    [00007003].dbo.tblStd s
    left join [00007003].dbo.tblCass c
    on c.RecordId = s.RecordId
    WHERE s.FileId = @intFileId


    If I put I do this (below), it doesn't work...
    set @strcmdline = 'SELECT convert(varchar(8), s.RecordId) as RECORDID, s.NAME,' +
    's.COMPANY, s.ADDRESS1, ' +
    's.ADDRESS2, s.ADDRESS3, s.CITY, s.STATE, ' +
    's.ZIP, s.ZIP4, convert(varchar(8), s.StdId) as STDID, ' +
    'convert(varchar(8), s.FileId) as FILEID, ' +
    'c.DPBC, c.CRT, c.LOT, c.LOTORD, ' +
    'c.COUNTY, c.COUNTYNUM, c.CONGCODE, ' +
    'c.ERROR_STAT, c.DSN, c.BIC ' +
    'FROM ' +
    '[00007003].dbo.tblStd s ' +
    ' left join ' + '[00007003].dbo.tblCass c ' +
    'on c.RecordId = s.RecordId ' +
    'WHERE s.FileId = ' + cast(@intFileId as varchar)
    exec @strCmdLine

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    did you try print @strcmdline, cut and paste, then try and run that ?

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    The SQL works, but the bcp won't. I get this error...

    SQLState = 42S02, NativeError = 208
    Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '00007003.dbo.tblStd'.
    SQLState = 42000, NativeError = 8180
    Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Put the query in a sproc and exec the sproc, instead of the sql from bcp.

  7. #7
    Join Date
    Mar 2009
    Posts
    7
    I still need to specify a database that the stored proc resides in.

  8. #8
    Join Date
    Mar 2009
    Posts
    7
    So I am getting the database to be recognized through a series of stored procedures.

    Now I am getting an Unexpected EOF Encountered in BCP data-file. I checked my format file and I have all of the appropriate columns. However, I am outputting a fixed length file. Do I need to make sure that when the query is run that all of the data is at those appropriate field lengths per the .fmt file?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •