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

    Question Unanswered: getting an error?

    How can I get the results in this way as if I was doing a standard select statement, but I need to have comma seperated results?

    This is giving me all sort of errors depending on what I change, so it is obviously very wrong....

    Basically I need to find a way how to select all of the data and comma delimit it, but I can't use parameter @query as it doesn't work most of the time,gives an error, so I have to select all this data in a differnet way and send it using @message, could somebody help me with that..please


    DECLARE @returnDay int
    DECLARE @messageBody varchar (8000)

    SELECT @returnDay = DatePart(day,GetDate())
    If @returnDay = 31
    SELECT @messageBody = 'Select + a.HospitalName +','a.HospitalCode + ','c.ProductName + ','b.UnitsDiscarded + ',' + b.DateEntered + ',' b.DateCompleted + ',' b.CompiledBy' + CHAR(10)

    SELECT @messageBody = @messageBody + 'FROM Ivana_test.dbo.Units b
    inner join Hospitals a ON (a.HospitalID = b.HospitalID)'+ Char(10)

    SELECT @messageBody = @messageBody + 'inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)'+ CHAR(10)

    SELECT @messageBody = @messageBody + ' order by a.HospitalID'
    Last edited by zobernjik; 05-31-04 at 00:33.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Are you trying to do something like this?

    declare @message varchar(8000)
    select @message = ''

    select top 10 @message = @message + name + ',' + CAST(id AS VARCHAR(55)) + ',' + xtype + ',' + CAST(uid AS VARCHAR(55)) + ','
    from sysobjects

    select LEFT(@message,LEN(@message)-1)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks for your help.

    I am not sure that will give what I am after, I need to use this query (SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy
    FROM Ivana_test.dbo.Units b
    inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    order by a.HospitalID)

    in a way that I can email those results comma delimited through the attachment query

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You could use something like this and just mail them the text file. Not sure if that meets your needs or not.

    DECLARE @query NVARCHAR(2000)
    SELECT @query = 'osql -n -w 5000 -E -s "," -d master -Q "
    SELECT
    a.HospitalName,
    a.HospitalCode,
    c.ProductName,
    b.UnitsDiscarded,
    b.DateEntered,
    b.DateCompleted,
    b.CompiledBy
    FROM
    Ivana_test.dbo.Units b
    INNER JOIN Hospitals a ON (a.HospitalID = b.HospitalID)
    INNER JOIN Products c ON (b.ProductID = c.ProductID)
    INNER JOIN FateOfProducts d ON (d.FateID = b.FateID)
    ORDER BY
    a.HospitalID" -o "c:\test.txt"'
    EXEC xp_cmdshell @query
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    I have tried to run this just to see if it creates the file and it should be easy from there.

    I run it and it is saying one row affected but file never gets created, any suggestions?

    Thanks

Posting Permissions

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