SQL Developer returns 0 rows

 

SYMPTOMS

When running a query [SELECT count(*) from <table_name>] on a table in SQL Developer, a count of 0 is returned.

If the same SELECT is run as the same user in SQL*Plus, then a positive row count is returned.

CAUSE

The query is dependent on the results of USERENV('LANG').

This will give different results for SQL Developer and SQL*Plus.

In order for USERENV('LANG') - and the query using this - to give the same results, the LANGUAGE part of NLS_LANG needs to be modified.

SOLUTION

Change the LANGUAGE part of NLS_LANG such that USERENV('LANG') returns the same for SQL Developer and SQL*Plus.

Example:

Tools->Preferences->database ->NLS Parameters from "English" to American"
set NLS_LANG=AMERICAN_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => US
And
set NLS_LANG=DUTCH_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => NL

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle"

 https://stackoverflow.com/questions/14499977/cannot-create-an-instance-of-ole-db-provider-error-as-windows-authentication-u

SQL SERVER get execution plan based on SPID

 SELECT CONVERT(XML, c.query_plan) AS ExecutionPlan

FROM sys.dm_exec_requests a with (nolock)

OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

OUTER APPLY sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c

LEFT JOIN sys.dm_exec_query_memory_grants m (nolock)

ON m.session_id = a.session_id

AND m.request_id = a.request_id

JOIN sys.databases d

ON d.database_id = a.database_id

WHERE  a.session_id = @@SPID --replace @@SPID with the SPID number for which you want to capture query plan

ORDER BY a.Start_Time

SQL SERVER GET INDEX SIZE

 SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,

       OBJECT_NAME(i.OBJECT_ID) AS TableName,

       i.[name] AS IndexName,

       i.index_id AS IndexID,

       8 * SUM(a.used_pages)/1024 AS [Indexsize(MB)]

FROM sys.indexes i

JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units a ON a.container_id = p.partition_id

where i.name in ('IX_WIPIN',

'IX_CreatedOn',

'PK_T_SerialNo4',

'IX_T_SFCompTrace_IntSN',

'IX_Component',

'IX_TDID_ExtSerialNo',

'IX_WIPIN',

'IX_T_TDStructure_MBSN')

GROUP BY i.OBJECT_ID, i.index_id, i.[name]

ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

SQL Server Search for index usage

 SELECT 

OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND i.name='IX_WIPIN' -- your index name here

AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'

AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

SQL Server Anonymous Block

 set nocount off;

declare @row as bigint;

declare @qty as int;

declare @yr as int;

set @qty=100;

set @yr=2017;

begin

delete from sample_table

insert into dx_rowcounter(modulename,rowcountt) values(concat(@yr,'_',@qty),@@rowcount)

end

SQL SERVER Last Restart

 SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Cannot connect to a newly installed SQL SERVER

 https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/