Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-SQL Expand / Collapse
Author
Message
Posted Monday, October 14, 2013 5:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
Already tried that...still getting an error...
Msg 139, Level 15, State 1, Procedure usp_Names, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure usp_Names, Line 12
Must declare the scalar variable "@SQLQuery".
Post #1504574
Posted Monday, October 14, 2013 6:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 1,778, Visits: 5,730
In your version of SQL Server, you cannot use the form

DECLARE @myvar varchar(100) = 'Some Value';

You have to use two statements, one to DECLARE then a SET.

DECLARE @SQLQuery VARCHAR(100);
SET @SQLQuery = 'select * from Table where ID=@ID';

Next problem is that the sp_makewebtask proc knows nothing of your variable @ID...

So try this instead:

DECLARE @SQLQuery VARCHAR(100);
SET @SQLQuery = 'select * from Table where ID='+CAST(@ID AS VARCHAR(20));

This will create a query like this to pass to the sp_makewebtask:

select * from Table where ID=12345



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1504578
    Posted Tuesday, October 15, 2013 2:19 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 8:23 AM
    Points: 12,954, Visits: 10,724
    mister.magoo (10/14/2013)
    In your version of SQL Server, you cannot use the form

    DECLARE @myvar varchar(100) = 'Some Value';

    You have to use two statements, one to DECLARE then a SET.

    DECLARE @SQLQuery VARCHAR(100);
    SET @SQLQuery = 'select * from Table where ID=@ID';



    Huh. I learned TSQL on SQL Server 2005, but I complete forgot you cannot assign a value to a variable while declaring it.

    Time flies by




    How to post forum questions.
    Need an answer? No, you need a question.
    What’s the deal with Excel & SSIS?

    Member of LinkedIn. My blog at LessThanDot.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    Post #1504640
    Posted Tuesday, October 15, 2013 3:21 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 10:18 AM
    Points: 20,677, Visits: 32,271
    You may want to consider rewriting this process using SSRS. The Web Assistant procedures have been deprecated and I don't believe they exist in SQL Server 2008 or later.




    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1504661
    Posted Tuesday, October 15, 2013 12:39 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Wednesday, March 26, 2014 5:09 PM
    Points: 178, Visits: 579
    The code worked for sometime ...
    then it stopped working.
    It gave me these extracts SSIS570.html where 570 is the ID passed as a parameter.
    After testing it a couple of times it started giving me onlySSIS5.html with no data in it.Any other ID I passed say 455 it would give me SSIS4.html with no data in it.Any thoughts where the code got messed up ?
    CODE:
    alter procedure [dbo].[usp_Names]
    (@ID varchar)

    AS
    DECLARE @TaskOutFile VARCHAR(255)
    DECLARE @SQLQuery VARCHAR(100)
    SET @SQLQuery ='select * from dbo.InfectionControl where ID='+CAST(@ID AS VARCHAR(20));
    Select @TaskOutFile = 'C:\SSIS' + @ID + '.html';
    EXEC sp_makewebtask @outputfile =@TaskOutFile ,
    @query = @SQLQuery;
    Post #1504910
    Posted Tuesday, October 15, 2013 12:46 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:47 PM
    Points: 1,778, Visits: 5,730
    It looks a lot like you are protecting some code from public view, but getting it wrong?

    Originally, your parameter @ID was an int, now it seems to be a varchar...

    How about you just change it to print the variables @TaskOutFile and @SQLQuery so you can see what is in them, then correct it?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1504915
    Posted Tuesday, October 15, 2013 7:23 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Wednesday, March 26, 2014 5:09 PM
    Points: 178, Visits: 579
    I got the code working properly.
    Post #1505044
    Posted Wednesday, October 16, 2013 12:09 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 10:18 AM
    Points: 20,677, Visits: 32,271
    Glad you got it working. You should still look at rewriting your process without using the Web Assistant tasks as those procedures are deprecated and your current process will not upgrade to future versions of SQL Server.



    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1505072
    Posted Wednesday, October 16, 2013 11:22 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Wednesday, March 26, 2014 5:09 PM
    Points: 178, Visits: 579
    Yes,I do plan to get it done using SSIS and SSRS.
    Post #1505351
    Posted Wednesday, October 16, 2013 12:34 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 8:23 AM
    Points: 12,954, Visits: 10,724
    sqlserver12345 (10/16/2013)
    Yes,I do plan to get it done using SSIS and SSRS.


    Great. If you have any question on those technologies, don't hesitate to ask.

    (I'll answer the SSIS ones )




    How to post forum questions.
    Need an answer? No, you need a question.
    What’s the deal with Excel & SSIS?

    Member of LinkedIn. My blog at LessThanDot.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    Post #1505371
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse