SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
sqlnewbie17
sqlnewbie17
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 1279
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".
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Koen Verbeeck
    Koen Verbeeck
    One Orange Chip
    One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

    Group: General Forum Members
    Points: 27331 Visits: 13268
    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 :-D


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

    MCSE Business Intelligence - Microsoft Data Platform MVP
    Lynn Pettis
    Lynn Pettis
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39285 Visits: 38529
    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.

    Cool
    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)
    sqlnewbie17
    sqlnewbie17
    Mr or Mrs. 500
    Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

    Group: General Forum Members
    Points: 559 Visits: 1279
    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;
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4116 Visits: 7865
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • sqlnewbie17
    sqlnewbie17
    Mr or Mrs. 500
    Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

    Group: General Forum Members
    Points: 559 Visits: 1279
    I got the code working properly.
    Lynn Pettis
    Lynn Pettis
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39285 Visits: 38529
    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.

    Cool
    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)
    sqlnewbie17
    sqlnewbie17
    Mr or Mrs. 500
    Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

    Group: General Forum Members
    Points: 559 Visits: 1279
    Yes,I do plan to get it done using SSIS and SSRS.
    Koen Verbeeck
    Koen Verbeeck
    One Orange Chip
    One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

    Group: General Forum Members
    Points: 27331 Visits: 13268
    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 :-D)


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

    MCSE Business Intelligence - Microsoft Data Platform MVP
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search