cmd command to get serial number of laptop

cmd  command:

C:\Users\user_name>wmic bios get serialnumber

Laptop_serial_no_CMD_Prompt

Posted in Uncategorized | Leave a comment

how to create a sample/test SSRS report

There are 2 sections here for SSRS report.

  1. Configuring SSRS report
  2. Deploying the SSRS file to report server URL for scheduling report

Section 1: Configuring SSRS report

Step1: open Visual studio from All Program

or

At Run prompt – type devenv and enter.

visual_studio_shortcut_Capture

Step2: Click CREATE project as shown

visual_studio_CREATE_PROJECT

Step 3: Select Report server project wizard & enter your own project name as shown.

create_SSRS_wizard

SSRS_REPORT_WIZARD

Step 5: create a data source.

create_data_Source

  • Step 5.1) click Edit.. to add server details etc
  • Step 5.2) click credentials.. to add login details etc

edit-credentials

edit-credentials_1

Click Next..

Step 6) Paste the data fetching query here as shown and press Next >

paste_query

select_report_type

design_table

deployment_location

formating_report

Check the report format & spacing for your requirements as per above picture.

report_preview

Section 2: Deploying SSRS reports:

Open internet browser with admin permission as shown.

open_IE_as_admin

 Step 3: save the SSRS config as a .rdl file as shown.

SSRS_RDL_file_config_in_SSRS_URL

SSRS_RDL_file_config_in_SSRS_URL_1

 Step 5: you can also open any existing folder & upload the .rdl as shown.

upload the rdl file

upload the rdl file_1

Once the .rdl file is uploaded, click the drop down button & click Manage option as shown

parameters_update_SSRS

parameters_update_SSRS_1

Schedule the report in subscription section as shown.

report_subscription

 

 

 

 

 

 

 

 

Posted in Uncategorized | Leave a comment

Error: User ‘DOMAIN/Login_name’ does not have required Permissions. Verify that sufficient permissions have been granted and windows account control (UAC) restrictions have been addressed.

Problem Description:
User is suddenly unable to access the SSRS Report Link or URL. User gets below error as shown in screen shot.

Error:

User ‘DOMAIN/Login_name’ does not have required Permissions. Verify that sufficient permissions have been granted and windows account control (UAC) restrictions have been addressed.
Assumptions:
  1. We assume that user is having necessary permissions on SSRS & still gets below error related to Permissions which misleads the troubleshooting mechanisms.
  2. User was able to create reports & access the same reports couple of days back but not able to access somehow now.
  3. There could be permission issues although but in our case we had just started to work on SSRS & knowledge on SSRS is limited. So please consider this as one of the approach in solving the issue.

SSRS_link_access_issue

Resolution:

To get this resolved you need to start you Internet Explorer as Administrator -> Run as Administrator
then open the url http://<Server name>/Reports this will take you to the “SQL Server Reporting Services” Home page
Click on the “Folder Settings” button.
Click on the “New Role Assignment” button.
Add the Group or user name: DOMAIN\USER_NAME, and permissions you have logged in as.
Click on Ok button.
This should resolve the issue.
Reference:
https://connect.microsoft.com/SQLServer/feedback/details/622737/user-does-not-have-required-permissions-verify-that-sufficient-permissions-have-been-granted-and-windows-user-account-control-uac-restrictions-have-been-addressed

 

Posted in Uncategorized | Leave a comment

SSIS – access/privileges to grant.

methods to grant access on SSIS

——————————-
https://www.mssqltips.com/sqlservertip/3086/how-to-resolve-ssis-access-denied-error-in-sql-server-management-studio/

https://sysbadmin.wordpress.com/2012/09/04/how-to-grant-access-permission-for-ssis/

http://mssqltrek.com/2011/12/07/connecting-to-the-integration-services-service-on-the-computer-mirrorstand-failed-with-the-following-error-access-is-denied/

https://msdn.microsoft.com/en-us/library/hh213130.aspx

below roles need to be granted to user depending on type of access

-db_ssisadmin
– db_ssisltduser
-db_ssisoperator

Posted in Uncategorized | Leave a comment

performance issue – Memory Metrics to check for MSSQL

–Memory metrics to check

Pages/sec,
Page Faults/sec,
Pages Input/sec,
Pages Output/sec,
Page Reads/sec,
Page Writes/sec,
Available Memory,
Non-pageable memory pool bytes,
Pageable memory pool bytes,
and Committed Bytes

pages/sec (in perfmon)
——————————-
ref : http://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory- pagessec-memory-page-faultssec/

– it is rate at which data pages are read from disk or written to disk.
formula,
– pages/sec = pages input/sec + pages output/sec
– pages/sec value should be < 50
– A high value of Pages/sec indicates intensive memory activity – many pages are
written from disk to RAM and read from RAM to disk.

page faults/sec (in OS – Perfmon)
——————-
ref : http://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory- pagessec-memory-page-faultssec/

– this occurs when the requested data pages are not accessible in current avilable
memory pages(RAM).

– 2 types of Pages fault/sec –

Hard page faults – happens when requested page is not present in physical memory(RAM)
Soft page faults – happens when requested page is available but not accessible since
the other process is currently accessing it.

– when page fault/sec occurs the process execution is put on wait state. OS will
search for requested page & copies to physical memory & then process is allowed to
access.

– There is no specific Page faults/sec value that indicates performance problems.
take a value of page faults/sec in normal situtaion & this can be referenced as
Baseline value.

– during the issue time, collect the value of Page faults/sec over a period of time &
compare with page reads/sec(this also indicates hard page faults). if the value of
page reads/sec also is regulary high during this time, then there is insufficient
Memory on server.
Available bytes (in OS – Perfmon)
—————–

ref – http://www.sqlshack.com/sql-server-memory-performance-metrics-part-2-available-
bytes-total-server-target-server-memory/
– this is the MB/GB amount of memory available to Processes for its execution.
– normal threshold value should always be More than 200 MB. if its constantly less
than 200 MB, then there is a insufficient memory on server.

– in case of insufficient memory, the values of pages/sec, page faults/sec will also
be high as there is not enough memory to store the reqd pages for read/write data.

– When there’s enough memory on the server, the available memory is high, page faults
are rare, and Pages/sec and Page faults/sec are low.
total server memory (in MSSQL Server)
————————

– the amount of memory assigned to the SQL server.

target server memory (in MSSQL Server)
————————-

– the amount of Memory SQL server requires to perform better.

Note– if the target server memory is much Greater than the Total server memory then
there is memory pressure.
Buffer Manager metrics.
===========================

page reads/sec (in mssql server)
—————–
ref – http://www.sqlshack.com/sql-server-memory-performance-
metrics-part-3-sql-server-buffer-manager-metrics-memory-
counters/#sthash.dVz9bpXm.dpuf

– no# of times the physical datapages read from disk per sec.
– value should be < 90. if > 90 would mean insufficient memory or
indexing issues.
– this is server level metrics, the value obtained is for all
databases.
– Please note that this is not the number of pages read from disk.

The value can be obtained from the sys.dm_os_performance_counters
system view

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Page reads/sec’
[\code]
page writes/sec(in mssql server)
—————
– shows the number of times (certain) pages were written to disk on
the server level
– the recommended value for Page writes/sec is below 90

Note: pages/sec = pages input/sec + pages output/sec

– High paging and disk I/O activity indicate insufficient memory. If
the excessive disk activity is not caused by paging (indicated by
normal Page Faults/sec values, compared to the defined metric
baseline), Page reads/sec and Page writes/sec will be high. In this
situation, it’s recommended to check the Lazy writes/sec and Page
Life Expectancy values as well, as the non-zero Lazy writes/sec and
low Page Life Expectancy require attentio
Pages Input/sec (in mssql server)
——————-
– the number of pages brought in from disk every second.
Note: Soft fault = pages input/sec – page faults/sec.
– value: recommended to be lower than 10.

Pages output/sec(in mssql server)
——————

– the number of pages written to disk every second
– these metrics (Pages Input/sec and Pages Output/sec.)
shows how many requested pages are not avilable in memory & had to
be read/write to/from disks.
– Note: pages/sec = pages input/sec + pages output/sec
– if value of Pages/sec > 50 constantly, then there is page faults
happening. additional investigation is needed. monitor disk
behaviour such as disk reads/sec & AVg disk read bytes/sec

– it’s good to check the Page Reads/sec value first and compare it
to Pages Input/sec. If the latter is greater, it indicates a high
page fault rate that can be solved by increasing memory designated
to SQL Server.
– value: recommended to be lower than 10.

– Both Pages Input/sec and Pages Output/sec values are recommended
to be lower than 10.

buffer cache hit ratio(BCHR)
————————-
ref – http://www.sqlshack.com/sql-server-memory-performance- metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/

– buffer cache hit ratio = [ no# of data pages present in pool/no#
of data pages required for process]

i.e lets say process A1 requires 100 data pages to accomplish task.
now the buffer cache has 95 records in buffer pool.

– so accroding to formula, BCHR = (95/100)
– recommended value: more than 90. a lower value indicates memory
issue.

– even after performance imporves, the BCHR value shows little
change.so only this metric alone is not enough to isolate the issue.
so consider Page life expectancy (PLE) for further trouleshooting.
The same as with other Buffer Manager metrics, it’s value can be
obtained from the sys.dm_os_performance_counters view

SELECTobject_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Buffer cache hit ratio’
[\code]
page life expectancy
——————–
ref – http://www.sqlshack.com/sql-server-memory-performance- metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/

– duration is secs a page resides in buffer pool.
– recommended value: above 300 secs(5 mins) & trend should be
stable. monitor this over a period of time. if there is frequent
fluctuation then it indicates memory issues.
– The cause for Page Life Expectancy values below 300 can be poor
index design, missing indexes, mismatched data types, insufficient
memory, etc.

http://www.sqlshack.com/sql-server-memory-performance-metrics-part- 5-understanding-lazy-writes-free-list-stallssec-memory-grants- pending/

http://www.sqlshack.com/sql-server-memory-performance-metrics-part- 6-memory-metrics/

Posted in Uncategorized | Leave a comment

t-sql – links/URL to learn from basics

http://www.techumber.com/2013/05/t-sql-tutorial-for-beginners.htmlhttp:

www.sqlcourse.com/select.html

Posted in t-sql learnings | Leave a comment

backup wordpress blog

https://css-tricks.com/back-wordpress-database/

Posted in Uncategorized | Leave a comment

WordPress Learnings –

write code & make it appear as per your wish.

use below code —

wordpress_blogging_code_upload

after the above code is changed to use (code language = “sql”), it looks like as shown below..

 

use master

go

select * from sys.sysdatabases

 

reference – https://en.support.wordpress.com/code/posting-source-code/

Posted in wordpress blogging | Tagged | Leave a comment

t-SQL Basic Programming for beginners

starting with very basic programming.

topics covered 

Starting t-sql program



-- 1. declare a variable & fetching the value

declare @name varchar(10)
set @name = 'name-xyz'
select '"select - "the variable value is - " ' +@name + '"'

--print '"print ststmnt -" the variable value is - " ' +@name + '"'
go

--2. use 2 GO in code as it will run in batches. any mistake in each section will result in error

use DBAMONITOR
go

declare @SPID int
select @spid = count(*) from dbo.BlockedEvents;
print 'the total no# of SPID in table [dbo.BlockedEvents]- as of '+ cast(getdate() as varchar(11))+ ' is - ' + cast (@spid as varchar(5))
go

-- 3. current user name/login name

select original_login()

or

select suser_sname()
go

SQL JOINS


/*
SQL JOINS EXPLAINATION WITH SIMPLE EXAMPLES
*/
/*
-- create a sample database
create database DBAMONITOR;
go
*/

--1.) inner join

use dbamonitor
go
create table table1
(
emp_id int,
emp_name varchar(10)
)

use dbamonitor
go
create table table2
(
emp_id int,
emp_address varchar(10)
)

insert into table1 (emp_id,emp_name) values (111,'Albert'),
(112,'Belgin'),
(113,'catherine'),
(114,'Debra'),
(115,'Elisaveta'),
(116,'Freida')

insert into table2 (emp_id,emp_address) values (112,'alabama'),
(116,'Burmuda'),
(117,'chicago'),
(118,'Danbury'),
(115,'Edinburg'),
(119,'florida')

use dbamonitor
go
select * from table1
select * from table2

selecting both the table will fetch below records.
SQL_JOINS_select_both_tables_Untitled
INNER JOIN syntax & example
i.e rows/data common in both tables will be displayed as shown in below screen-shot

use dbamonitor
go
select * from table1 A
INNER JOIN
table2 B
on
A.emp_id=B.emp_id

INNER_JOIN_Untitled

LEFT outer JOIN
–all rows in TABLE1 will be displayed along with the matching rows from table2

select * from table1 A
LEFT OUTER JOIN
table2 B
on A.emp_id=B.emp_id

LEFT_OUTER_JOIN_Untitled

RIGHT outer JOIN
All rows in TABLE2 will be displayed along with the matching rows from table1

select * from table1 A
RIGHT OUTER JOIN
table2 B
on A.emp_id=B.emp_id

RIGHT_OUTER_JOIN_Untitled


FULL OUTER JOIN

-- i.e irrespective of wheather there is any matching rows on either table, all the rows of both table will be displayed.

select * from table1 A
FULL OUTER JOIN
table2 B
on A.emp_id=B.emp_id
FULL_OUTER_JOIN_Untitled











-- CROSS JOINS (each single row in TABLE1 will join all rows in TABLE@)

select * from table1 A
 CROSS JOIN 
table2 B
CROSS_JOIN_Untitled
Posted in t-sql learnings | Tagged | Leave a comment

T-SQL to check what access user has on db & its objects

T-SQL to check what access user has on db & its objects

————————————————————————-

http://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/

Posted in Uncategorized | Leave a comment