Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: bcp creating file with with one query not with other?

    this is a query
    DECLARE @query varchar(8000)
    SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,
    b.UnitsDiscarded,b.DateEntered,b.DateCompleted,b.C ompiledBy FROM Ivana_test.dbo.Units b
    INNER JOIN Ivana_test.dboHospitals a ON (a.HospitalID = b.HospitalID)
    INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)
    INNER JOIN Ivana_test.dboFateOfProducts d ON (d.FateID = b.FateID)
    ORDER BY a.HospitalID" queryout "c:\test.txt" -c -SCenausttrim -Usa -Pcobalt123'
    EXEC master.dbo.xp_cmdshell @query

    that gives me all the bcp specs as a result

    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"]
    NULL

    and then this query wich is just a test query

    DECLARE @query NVARCHAR(2000)
    SELECT @query = 'bcp "SELECT * from Ivana_test.dbo.Products" queryout c:\test.txt -c -SCenausttrim -Usa -Pcobalt123'
    EXEC master.dbo.xp_cmdshell @query

    creates a file and everything goes through ok

    Could somebody tell me what is the story,can bcp utility support more complex queries.

    I have just realized that my first query does work but only if I put the Select statement into one line otherwise it doesn't work,is anybody else coming across that as well????

    Thanks
    Last edited by zobernjik; 06-03-04 at 01:45.

Posting Permissions

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