Tags » SQL Query

Select On-hand Per Date In AX 2012 Using SQL Query

If you know database structure in AX 2012, you can directly select data from SQL Server. Of course select data from SQL Server are faster than select data from AX 2012 framework. 332 more words

Dynamics AX

Understanding the SQL Query Window

Knowing where to look to run a query is also extremely helpful to getting started.  There are three locations within SQL Server that you need to be aware of to get started. 158 more words

SQL

TSQL to find users and their access on a given database

TSQL to find users and their access on a given database

--
SELECT      dp1.name AS DatabaseRoleName,
            COALESCE(DP2.name, 'No members') AS DatabaseUserName
FROM        sys.database_principals AS dp1
LEFT  JOIN  sys.database_role_members AS drm
      ON    drm.role_principal_id = dp1.principal_id
LEFT  JOIN  sys.database_principals AS dp2
      ON    dp2.principal_id = drm.member_principal_id
WHERE       dp1.type = 'R'
ORDER BY    dp1.name 



SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;   

--
--
SQL

TSQL to find size of all databases on a Server - Azure

--
-- DatabZse Name and Database Size

select
       @@serverName as ServerName,
    db.name as DatabaseName,
       (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
from
       sys.databases db 
order by db.name 


-- DB Size and additional details including pool name 
select
       @@serverName as ServerName,
    db.name as DatabaseName, db.create_date, db.state_desc, 
   (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
,do.edition, do.service_objective, do.elastic_pool_name
from
       sys.databases db JOIN sys.database_service_objectives do on db.database_id = do.database_id
order by db.name 
--
--
SQL

Fastest way to insert 1 million rows

Here is the fastest way to insert 1 million rows to a table in SQL Server

Credit goes this post: Generate and Insert 1 million rows into simple table… 118 more words

技术笔记

Find In-Memory table in a given database

Note: In Azure if we use premium database functions we can not downgrade those databases into standard pricing tier. We need to remove premium database features before downgrading to Standard tier. 101 more words

SQL

To get a report of all printers connected to a computer using SCCM reports

I had to find a way to get all the printers attached to a particular host computer by using SQL reporting services.

Prerequisite – Please make sure that “Printer Device” and “Printer Share” classes has been enabled in hardware inventory. 108 more words

Printers