Quick check SQL connection and process details with current SQL text

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Comments posted to this topic are about the item Quick check SQL connection and process details with current SQL text

  • Sarath Vellampalli

    SSCertifiable

    Points: 5822

    Hi Venugopal,

    This script was not working when i create the view i am getting following error.

    Msg 102, Level 15, State 1, Procedure sqlexec, Line 16

    Incorrect syntax near '.'.

    Regards,

    Sarath

    Kindest Regards,

    Sarath Vellampalli

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Sharath,

    What version of SQL Server are you using?

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Sarath,

    I guess you are executing the script all together at once. You are supposed to execute it in two steps.

    Step 1:

    create view sqlexec as

    SELECT

    s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cpu,s.memusage,s.status,s.net_library,s.sql_handle

    ,e.text as SQL

    FROM

    sys.sysprocesses s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e

    and then you have to execute the select statement.

    select d.name,s.* from sqlexec s,sys.sysdatabases d where s.dbid=d.dbid order by login_time desc

  • Sarath Vellampalli

    SSCertifiable

    Points: 5822

    I am executing this script in SQL server 2005, and first step itself i am getting below error.

    Msg 102, Level 15, State 1, Procedure sqlexec, Line 7

    Incorrect syntax near '.'.

    Kindest Regards,

    Sarath Vellampalli

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Sharath,

    Please send me the script that you are executing. I will check and let you know.

  • Sarath Vellampalli

    SSCertifiable

    Points: 5822

    I am executing the below script

    create view sqlexec as

    SELECT

    s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cpu,s.memusage,s.status,s.net_library,s.sql_handle

    ,e.text as SQL

    FROM

    sys.sysprocesses s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e

    Kindest Regards,

    Sarath Vellampalli

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Sharath,

    What is the service pack level, I checked the script in both SQL Server 2005 SP2 and SP3.....I don't have SP1 to test.

    In both the instances I tested. I don't see any issue.

    Can you execute just the select statement and let me know if you can get the result.

    SELECT

    s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cpu,s.memusage,s.status,s.net_library,s.sql_handle

    ,e.text as SQL

    FROM

    sys.sysprocesses s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e

  • Sarath Vellampalli

    SSCertifiable

    Points: 5822

    When i execute the below select statment i am getting same error and i am using SQL SERVER 2005 SP2

    SELECT

    s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cpu,s.memusage,s.status,s.net_library,s.sql_handle

    ,e.text as SQL

    FROM

    sys.sysprocesses s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e

    Error is:

    Msg 102, Level 15, State 1, Line 6

    Incorrect syntax near '.'.

    Please copy the code from here and execute in your system.

    Kindest Regards,

    Sarath Vellampalli

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    Can we do a webex or something may be we can use teamviewer as it is personal purpose only. If you agree for me connecting to your system and checking it I prefer that so we can fix it soon.

  • Sigerson

    Hall of Fame

    Points: 3697

    I get the same error as cusvenus. The only way I could get the initial view query to run was to comment out references to sys.dm_exec_sql_text, as below.

    create view sqlexec as

    SELECT

    s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cmd,s.blocked,s.cpu,s.memusage,s.physical_io,s.status,s.net_library,s.sql_handle

    --,e.text as SQL

    FROM

    sys.sysprocesses s

    --CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e

    I'm no expert, but could this be due to a security setting that you have turned on and we don't?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

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

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