Coding on Linux

Not that Coding in Linux is new for me. But overwhelmed by aerching for eveything on my favourite llm. I have found that I am forgetting the basoc usage of a lot of common things both in SQL and Linux. Thought of putting some important things that I am using and putting it one of my blogs. Starting with Jetson Orin today. Got teh SD card but for some reason it was behaving really slow in teh GUI. So thought of getting into terminal after a really long time. So getting all the cards that are mounted. df -h now to format the disk in ExFAT format diskutil eraseDisk ExFAT SDCARD GPTFormat /dev/disk4 I think I am getting very weak in remembering stuff on trminal and would like to thank our LLMS for it. Not to demean the the boon that we have. Yes I call it a boon in our era where we are solving problems which will take decades to fix. Broing work like documentation noting point writing out MOMs it is a wonderful tool. But then it cannot write a good story the creativity humans have it not present in the LLM. It is good for writing out flowery wordswhich mean not much generally. I have found thagt I can write the similar sentiment in a couple of lines which it will take pages to write down to be read by human. Does that make sense ? As in think about it you are trying to impress someone who already understands this is written by an LLM and may be using another LLM to ready and filter your profile. Coming back to the blog in question. Will continue to blog on this one. Thanks, Tushar

SQL Azure User Permissions

 New Demand to find out the user permissions in Azure SQL Database. 

SELECT    roles.principal_id                            AS RolePrincipalID

  ,    roles.name                                    AS RolePrincipalName

  ,    database_role_members.member_principal_id    AS MemberPrincipalID

  ,    members.name                                AS MemberPrincipalName

FROM sys.database_role_members AS database_role_members  

JOIN sys.database_principals AS roles  

  ON database_role_members.role_principal_id = roles.principal_id  

JOIN sys.database_principals AS members  

  ON database_role_members.member_principal_id = members.principal_id;  

GO

Reference-> SQLShack

https://www.sqlshack.com/database-level-roles-in-azure-sql-database/#:~:text=Expand%20the%20Azure%20SQL%20DB,%3E%20Roles%20%2D%3E%20Database%20Roles.

Find the Jobs that failed

 Well need to send out notifications for the job failures in the SQL Server. 

Well first of all we need to look into the jobhistory to get the last completed instance of the job run completed in last 24hours. 

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name

Now that we have the instance id of the jobhistory per job that we are interested in, we can now filter the other columns that required for the reporting. 

SELECT j.name,h.message,msdb.dbo.agent_datetime(h.run_date,h.run_time) AS starttime FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

INNER JOIN 

(

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name) b ON b.name = j.name AND b.instance_id = h.instance_id

WHERE j.enabled=1

AND h.run_status =0

Hope you enjoyed the query. 

Thanks,

Tushar 

Interested in Jobs missing Schedules

Hi Peeps, 

This will be a running post.. 

I will keep updating the posts as I create the query.. 

so let's begin 

 Question-> We need to find the jobs which are missing the run in the schedule. 

 Now what do we need to get this into action. 

 a. Job is currently running flag 

b. Job Start Time 

c. Next Schedule Date/Time 

d. Job End Time 

e. If Job End Time> Next Schedule Date/Time || If Schedule Date/Time is passed and Job Start Date Time is not equal to Schedule Date/Time 

 a. Job is curently running flag

 This can be extracted from sysjobactivity and sysjobs 

 SELECT sj.Name, 

CASE

WHEN sja.start_execution_date IS NULL THEN 'Not running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'

END AS 'RunStatus',sja.job_id,sja.start_execution_date,sja.stop_execution_date

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobactivity sja

ON sj.job_id = sja.job_id

WHERE session_id = (

SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

b. Job Start Time can also be pulled from SubQuery1 

c. Next Schedule Date/Time 

This we can pull from sysjobschedules 

SubQuery2 

SELECT j.job_id,convert(NVARCHAR(24),(msdb.dbo.agent_datetime(s.next_run_date,s.next_run_time)),121) NextSchedule

   FROM msdb.dbo.sysjobs j

   INNER JOIN msdb.dbo.sysjobschedules s 

   ON s.job_id = j.job_id

d. Job End time from 

SubQuery1 

 to be continued..

wonder what is the difference between import math and from math

Hey you people, Hope you are having a lot of fun with SQL as ever. Well I have been trying my hands at python at last. Well wanted to since a decade now even before this bubble of AI and ML but yea end justifies the means. So I looked into some of the codes in the header with importing libraries just like #include in c. There were some confusing statement some stated with import and other stated with from so I went ahead and verifiied. It is not very unlike how we import libraries in C# or andy Other Lnaguage. Where you get the option of importing the whole library or the specific function. Like import math # will iclude all the function built into the class or the library of math form math import sqrt #will only import the sqrt funtion from the math class. I guess this will make the code lighter and stop and confusion when multiple classes imported have the same library. Thanks, Tushar

SQL Replication

-- to get what are the errors that are in the server
select  * from distribution..MSrepl_errors with(nolock) where time > '20120221' order by time desc
select  top 10 * from distribution..MSrepl_errors with(nolock)  order by time desc
---------------------------------------------------------------------------------

-- to get what was the command that was cauing the issue. From here we get the hint what is the table that is involved.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x0001492D0000299B000600000000', @xact_seqno_end='0x0001492D0000299B000600000000'

{CALL [sp_MSdel_dbot_server_status] (53966)}

msdb table  which has replication issues
-------
select * from sysreplicationalerts
select distinct subscriber,article from sysreplicationalerts
select distinct publisher,publisher_db,article from sysreplicationalerts
select distinct 'select count(*) '+''''+article+''''+'from ' +publisher_db + '..'+ article + ' with (nolock)' from sysreplicationalerts



-- to run a job from command line.
EXEC msdb.dbo.sp_start_job N'FESCOVAWSA04W3-Aarow_acp-aarow_acp-11' ;
GO

--Adding transactional article in an existing publication. Here a lot of parameters are environment specific and t_aaflc_fund_fmly_lkup
--is the table that is being added to the existing publishing.
use [Aarow_acp]
exec sp_addarticle @publication = N'aarow_acp',
@article = N't_aaflc_fund_fmly_lkup',
@source_owner = N'dbo',
@source_object = N't_aaflc_fund_fmly_lkup',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N't_aaflc_fund_fmly_lkup',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dbot_aaflc_fund_fmly_lkup]',
@del_cmd = N'CALL [sp_MSdel_dbot_aaflc_fund_fmly_lkup]',
@upd_cmd = N'SCALL [sp_MSupd_dbot_aaflc_fund_fmly_lkup]'
GO


-- to compare data on row basis. using -c to compare table data on column basis.
tablediff -sourceserver PWIBOSSQLA10V3 -sourcedatabase RSOWDistribution2 -sourcetable t_server_status -destinationserver PWIBOSSQLA10V3 -destinationdatabase RSOWDistribution1 -destinationtable t_server_status -q -o


select log_reuse_wait_stats,name from sys.databases --- check if replication appears in the description

distribution.dbo.msrepl_commands

----------------------------
to find the ditributor/subscriber/publisher detail

select article,publisher_db from distribution..msarticles
select * from distribution..MSpublisher_databases
select * from distribution..MSsync_states
select * from distribution..MSsubscriber_info

IHsubscriptions
MSsubscriber_info

http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html


Thanks,
Tushar Kanti

Identify the Database Server which is used to mount the database in Sharepoint 2010

Hi Guys,
Presently working in 2nd largest Sharepoint Environment globally. I have been working with a lot of Sharepoint Enironments with different levels of complexities. Well Today I will talk about the a couple of important tables in the Config database related to Sharepoint.
As you know the config database houses a lot of important tables. These tables have a lot of information which can be very crucial in understanding the links between a lot of internals inside sharepoint. Today we will take a peek into the Objects, Class and the SiteMap tables in the COnfig database. I definitely do miss the documentation on these tables. However there were little bread crums over the internet which I could follow and reach to this juncture.
The article is divided into two parts in the first part we will look  into the very basics of queries and in the next section we will look into the complex query merging a couple of the basic queries.

PART 1

a.SiteMap Table will have most of the information required for the Sharepoint to maintain a SiteCollection. ApplicationId,DatabaseId,Status,Version,HostHeaderIsSiteName, SubscriptionId,etc colums are there which are very useful in SiteMap table.

b.Objects Table will have the entries for most the objects that are related to config may be the Server Names,Databases,etc information.  Id,ClassId, ParentId , Name ,Status ,Version etc are colmns which are very useful in Objects table.

c.CLasses Table will have the Id,BaseClassId and FullName columns which will be useful to get basic information.

To Identify The Databases mounted in the Farm:

Query1 -> Select distinct DatabaseID from SiteMap

This Query1 will give us the databaseid's mounted for the whole farm sharing the config database.

Query2 -> Select distinct Name as DBName , DatabaseId,ParentId

from SiteMap s join Objects o on s.DatabaseID=o.ID

This Query2 will give us  the database names for the Farm that are presently mounted.

These two queries are good to start with the inquition of database information inside Sharepoint Config database.

PART2

Now lets move to the next section of this post where we will discuss other details about the database servers that are being used in Sharepoint to Mount the databases.

Query 3 -> select distinct O.Name As DatabaseName, servers.Name as ServerName

from SiteMap s

inner join Objects o on o.id= s.databaseid

inner join Objects Parent on Parent.Id= o.ParentId

inner join Objects servers on servers.Id= Parent.ParentId

This query will hunt for the parentid to which the database is belonging and then hunt for the child for the Servers and joining them we identify the Databse Servers for our Databases. Here we are self joining the Objects table to get all the information.

Thanks,

Tushar Kanti