NetTalk Central
NetTalk Web Server => Web Server - Ask For Help => Topic started by: trent on February 03, 2014, 07:09:14 PM
-
Hi Debra,
I tried creating the v_ActiveTransactions view but Management Studio returns an error: Incorrect syntax near 's_ec' on line 31
Line 31 = sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
Now I can't see page 2 of the first topic - are you able to paste the second create view script in this topic please?
Also I added the 'Performance' tab to my WebServer months ago and it has never worked - the values are always zero. Has anyone else encountered this?
Regards,
Trent
-
What version of SQL are you running? I think for the dynamic management views (the ones that start with dm_) you have to be running at least 2008. But the code below is what you need.
My performance tab has been 0's since I upgraded to 7.32, it worked it 7.25. But the logging in the embeds in the MultiHost demo app was more helpful to me than the performance tab.
CREATE View [dbo].[dv_ActiveTransactions]
as
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
dtat.transaction_begin_time AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan],
s_er.blocking_session_id
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
Join sys.dm_tran_active_transactions DTAT
on s_tdt.transaction_id = dtat.transaction_id
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
Where DATEDIFF(mi,dtat.transaction_begin_time,GETDATE())>=2
GO
Create View [dbo].[dv_CursorSessions]
as
select login_time,status,last_request_start_time,last_request_end_time,
ses.transaction_isolation_level, ses.lock_timeout,row_count, c.*, t.text
from sys.dm_exec_sessions ses
cross apply sys.dm_exec_cursors(ses.session_id) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
where ses.is_user_process=1 and ses.session_id <> @@SPID
GO
-
The client is using SQL Server 2008 R2 Express SP2.
Now I get this error when creating the first view:
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
The second create view shows an error as well:
Msg 102, Level 15, State 1, Procedure dv_CursorSessions, Line 6
Incorrect syntax near '.'.
Line 6 = cross apply sys.dm_exec_cursors(ses.session_id) c
Regards,
Trent
-
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
This indicates a connection loss to the server, either network issues, your session was killed something along those lines.
I have attached the scripts as an sql file to this post. Maybe a character is getting in there that should not be with the post.
You can send me a PM if you still have issues with the scripts and we can connect with skype or something.
[attachment deleted by admin]
-
Hmm that's weird because the SQL instance is on the same server that I'm running the script on. There shouldn't be any network issues.
Tried the scripts again from the zip file but same error messages. I'll PM you.
Regards,
Trent
-
Hi Everyone,
Just thought I would give an update on this. I've fixed the SQL Sleeping Connections issue and also the table locking issues by figuring out MARS! The way SQL Server works with MARS enabled means you shouldn't have to manually monitor any connections or have to kill any 'Sleeping' or 'Suspended' connections.
Here are the steps I used to resolve these issues:
1) Removed all of Clarion's Server Side Auto Inc from the dictionary and hand code. Replaced with Geoff Bomfords 'GWB Auto Inc' template. Added the 'Set Lock Timeout' on and off to each {PROP:SQL} and 'Fetch' that the template uses. Do not use the Clarion's Server Side Auto Inc at all as this will cause table locking issues even with MARS enabled.
2) Set each SQL table in the dictionary to use a global variable for the 'Driver Options' to test which driver options work best. In the end the Driver Options that worked best for me are: '/MULTIPLEACTIVERESULTSETS=TRUE /BUSYRETRIES=5 /LOGONSCREEN=FALSE /VERIFYVIASELECT=TRUE'. Using these driver options means you do not need to 'SEND' the MARS command in hand code before opening the very first SQL table as the Clarion Help states. You can add this to the FM3 'Driver string (for connect)' option if your first SQL table to be opened is not already in the dictionary.
3) Install the 'SQL Server Native Client Drivers' on EVERY client workstation and also the server/workstation that the WebServer is running on if the WebServer is not running on the main SQL Server machine. If the workstation does not have 'sqlncli' installed then MARS will NOT work. Any version of the drivers above SQL Server 2005 should work.
Thank you to everyone who helped.
Hope this helps other people out there.
Regards,
Trent