Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Stored procedures (SQL Server 7)

    I have an SQL problem and wasn't sure where to post it. Seeing as alot of Access databases use SQL to store data I thought i'd try here first.
    The section below with the master..xp_sendmail and the @ signs works on its own in an SQL server agent job or through the query analyzer, but as soon as I put the IF statement in so the job only runs if there are no records matching the criteria, I get the error Line 3: Incorrect syntax near 'master'.
    If I try and save the section that works on its own as a stored procedure in the EducationList database (as opposed to the master database) I get the same error.

    IF (SELECT Count(tblEducationSourceTable.EducationNumber) AS CountOfEducationNumber FROM tblEducationSourceTable WHERE (((tblEducationSourceTable.CountryId) Is Null))) >0
    BEGIN
    master..xp_sendmail
    @recipients = 'philipj@education.co.uk',
    @subject = 'List of Education Numbers where CountryID is unallocated.',
    @message = 'File of postcodes where null or less than 5 characters,',
    @query = 'SELECT rtrim(tblEducationSourceTable.EducationNumber) as EducationNumber FROM tblEducationSourceTable WHERE (((tblEducationSourceTable.CountryId) Is Null))',
    @attach_results = true,
    @no_header = false,
    @dbuse = 'EducationList'
    END

    Any help would be much appreciated.
    P.s. If anybody noticed a post appear a while ago, and then disappear - I was looking too in depth and didnt read the @attach_results = true part to attach the attachment. I noticed it as soon as I read it myself after posting it. Had a closer look at this one and I dont think its as simple.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL Stored procedures (SQL Server 7)

    The problem may be that you are trying to get a count of null records which I don't think you can do as the count uses grouping functionality and you can't group on nulls. (At least not in Access) I would try to run a simple query to get the count of nulls. If you still get the error mesage, create a query to convert the null records to some identifier and then count on the identifier to run the test >0.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Stored procedures (SQL Server 7)

    Hi Phil,

    I'm a little green (well, a LOT green) with some of the T-SQL commands, but I'll give you my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> anyway...

    Try using EXEC immediately before xp_sendmail:<pre>--..........
    BEGIN
    EXEC xp_sendmail
    @recipients = 'philipj@education.co.uk',
    @subject = 'List of Education Numbers where CountryID is unallocated.',
    @message = 'File of postcodes where null or less than 5 characters,',
    @query = 'SELECT rtrim(tblEducationSourceTable.EducationNumber) as EducationNumber
    FROM tblEducationSourceTable WHERE (((tblEducationSourceTable.CountryId) Is Null))',
    @attach_results = true,
    @no_header = false,
    @dbuse = 'EducationList'
    END</pre>

    HTH <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Stored procedures (SQL Server 7)

    Quick update:
    I changed the master..xp_sendmail part to the following and it would then work in the query analyser but not as a job - the same error occured.
    { CALL "master"."dbo"."xp_sendmail" }

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Stored procedures (SQL Server 7)

    Thanks for your replies. Sorry Gary, I should have mentioned that I had tested the If statement with another statement between Begin and End and it worked fine, the problem was with calling the procedure.
    Thanks Mark, that was it - I changed the code to below and it works fine. Thanks for your help, I am now happy - our data entry manager is now really busy - its what we live for at work!

    IF (SELECT Count(tblEducationSourceTable.EducationNumber) AS CountOfEducationNumber FROM tblEducationSourceTable WHERE (((Len([postcode]))<=5 Or (Len([postcode])) Is Null) AND ((tblEducationSourceTable.CountryId)=1 Or (tblEducationSourceTable.CountryId)=2 Or (tblEducationSourceTable.CountryId)=3 Or (tblEducationSourceTable.CountryId)=4 Or (tblEducationSourceTable.CountryId)=10))) >0
    BEGIN
    EXEC master..xp_sendmail
    @recipients = 'philipj@education.co.uk',
    @subject = 'List of incomplete postcodes.',
    @message = 'File of Education Numbers where postcode is null or less than 5 characters.',
    @query = 'SELECT rtrim(tblEducationSourceTable.EducationNumber) as EducationNumber FROM tblEducationSourceTable WHERE ((Len([postcode])<=5 Or (tblEducationSourceTable.Postcode) Is Null) AND ((tblEducationSourceTable.CountryId)=1 Or (tblEducationSourceTable.CountryId)=2 Or (tblEducationSourceTable.CountryId)=3 Or (tblEducationSourceTable.CountryId)=4 Or (tblEducationSourceTable.CountryId)=10))',
    @attach_results = true,
    @no_header = false,
    @dbuse = 'EducationList'
    END

Posting Permissions

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