Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    7

    Unanswered: stored procedure or batch with views

    How do I create the select statements <below> into one stored procedure or batch file querying view tables>


    /* In isql utility program, select statement work with view
    and creating temporary table. */
    1> select a.id, b.name, a.group_name, a.table_name, a.time
    2> into c#list
    3> from collect_list_v a, collection_v b
    4> where a.id = b.id
    5> go

    1> select * from #clist
    2> go

    1> select grouplist.name from grouplist
    2> where grouplist.name not like "[Kkdt]%"
    3> and not exists
    4> (select * from #clist
    5> where #clist.group_name = grouplist.name)
    6> go

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: stored procedure or batch with views

    I'm not sure what your asking, so I'll make the following assumption:

    Do you want to create a stored procedure with these statements? I'm assuming c#list is a typo and you mean #clist.

    Basically wrap everything inside a create procedure statement and remove the individual "go" statements

    create procedure proc_name

    as

    select a.id, b.name, a.group_name, a.table_name, a.time
    into #clist
    from collect_list_v a, collection_v b
    where a.id = b.id

    select * from #clist

    select grouplist.name from grouplist
    where grouplist.name not like "[Kkdt]%"
    and not exists
    (select * from #clist where #clist.group_name = grouplist.name)


    go

  3. #3
    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    7

    Re: stored procedure or batch with views

    Richard,

    I tried stored procedure and received error msg
    The SQL Server is terminating this process.

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: stored procedure or batch with views

    Is there a stack trace in the error log? Normally this is if ASE has killed a client process. If so can you post it.

    Richard.

  5. #5
    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    7

    Re: stored procedure or batch with views

    Originally posted by richardcrossley
    Is there a stack trace in the error log? Normally this is if ASE has killed a client process. If so can you post it.

    Richard.
    Richard,

    kernel SQL causing error: exec clist
    server SQL Text: exec clist
    kernel curdb=5 pstat=)x10100 lasterror=0
    kernel preverror=0 transtate =0
    kernel curcmd=195 program=isql
    kernel pc:0x49b268 pcstkwalk+0X20
    kernel pc:0x49b17c uscstkgentrace+0x178
    kernel pc:0x77ed0 terminate_process+0xa88
    kernel pc:0x488bb8 kisignal+0x80
    kernel pc:0xff21b4d0 _setuid+0x60
    kernel pc:0x286454 s_setup_tabsdes+0x1cc
    kernel pc:0x28616c s_setuptables+0xcc
    kernel pc:0x281dc8 s_execute+0xd60
    kernel [Handler pc: 0x295080 s_handle installed by the following function:-]
    kernel pc:0x292afc sequencer+0xee0
    kernel pc:0x288b7c execproc+0x6e8
    kernel pc:0x2827f0 s_execute+0x1788
    kernel [Handler pc: 0x295080 s_handle installed by the following function:-]
    kernel pc:0x292afc sequencer+0xee0
    kernel [Handler pc:0xblac0 hdl_backout installed by the following function:-]
    kernel [Handler pc:0x258b94 ut_handle installed by the following function:-]
    kernel [Handler pc:0x258b94 ut_handle installed by the following function:-]
    kernel pc:0x99898 conn_hdlrx0x1c8c
    kernel pc:0x4abfbc _coldstart+0x4
    kernel end of stack trace

    I couldn't copy the file.

    Thanks

  6. #6
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: stored procedure or batch with views

    Originally posted by kdrice

    kernel pc:0xff21b4d0 _setuid+0x60
    kernel pc:0x286454 s_setup_tabsdes+0x1cc
    kernel pc:0x28616c s_setuptables+0xcc
    kernel pc:0x281dc8 s_execute+0xd60
    kernel [Handler pc: 0x295080 s_handle installed by the following function:-]
    kernel pc:0x292afc sequencer+0xee0
    kernel pc:0x288b7c execproc+0x6e8
    kernel pc:0x2827f0 s_execute+0x1788

    Thanks
    It looks like the ASE is executing the stored and trying to resolve the tables and columns used within the stored procedure. Do you have any user defined types in the database your selecting from? If so are they in tempdb?

    The next step is break down the stored procedure into chunks and see which bit its failing on.

    Try this...

    create proc proc_name as
    select a.id, b.name, a.group_name, a.table_name, a.time
    from collect_list_v a, collection_v b
    where a.id = b.id
    go

    It's just the first select without building the temporary table. If this works, try adding the temp table and so on. If the temp table doesn't work try creating a normal table. You'll need to enable select into/bulkcopy/pll sort to do this.

    Richard.

Posting Permissions

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