Post reply

Not able to create sp

  • June 23, 2009 at 8:26 am

    #99770

    .........

  • June 23, 2009 at 8:32 am

    #1013988

    Which error do you get?

  • June 23, 2009 at 8:38 am

    #1013993

    kiranjanga2 (6/23/2009)

    Can u pls edit this sp for running in sqlserver2000.

    Thanks in advance.

    Why don't you tell us what errors and what lines, and we'll advice you on resolving them.

    Personally, I'm not going to do your work for you. That's nearly 300 lines of SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2009 at 9:20 am

    #1014030

    error is below.

  • June 23, 2009 at 9:24 am

    #1014033

    kiranjanga2 (6/23/2009)

    error is below.

    (EXECUTE cannot be used as a source when inserting into a table variable.)

    there's your error. change your table variable to a temp table instead.

    looks like the lines that are using the table @xp_results need to switch to temp tables:

    INSERT INTO @xp_results

    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2009 at 10:34 am

    #1014096

    .........

  • June 23, 2009 at 10:45 am

    #1014101

    kiranjanga2 (6/23/2009)

    can u pls change that code and post the complete code.

    ????

  • June 23, 2009 at 11:23 am

    #1014123

    ..........

  • June 23, 2009 at 11:27 am

    #1014126

    you'll get much more out of this doing it yourself. there is more than one error in your code. Don't be afraid of making changes to it.

    Like almost all forums, we enjoy helping those who help themselves. If you show some effort, we tend to help or be able to help a lot more. Asking us to test a 300 line proc that you yourself have never put any effort into tends to make many posters look for easier posts to try and help out with.

    here's a quick synopsis of what i found:

    you'd have to fix the issue above by changing the table variable to a temp table; we already saw that...the errors SQL returns are very specific and informative, but you might need to google one or two if you are new to this.

    anther example, is someone is trying to divide @@version by an integer..@@version returns something like this:

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    an NVARCHAR like that couldn't return an integer.

    another issue i found was

    you are also referencing some tables without the full path, msdb.dbo, so the proc fails when run from master: search for "FROM " and find any queries that pull from the masb database that is missing the full databasename.username.tablename syntax.

    finally, you reference a table that does not exist at the very end:

    insert into Job_status fails, where removing the "insert into" would return the results of the last query.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2009 at 1:46 pm

    #1014232

    kiranjanga2 (6/23/2009)

    can u pls change that code and post the complete code.

    You really must be kidding, right? This is your code, why should we do your work for you? You were shown where the problem was, go fix it and see what else may be wrong after that. Shouldn't be too difficult, after all you know how to cut and paste, right?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply