use master go declare @table table(logdate datetime,processinfo varchar(max),text varchar(max)) insert into @table exec xp_readerrorlog select * from @table where processinfo like ‘spid%’ order by 1
Category: Database
Spark in python
Just a simple example to get started. #./pyspark –jars /tmp/mysql-connector-java-5.1.45-bin.jar Welcome to ____ __ / __/__ ___ _____/ /__ _\ \/ _ \/ _ `/ __/ ‘_/ /__ / .__/\_,_/_/ /_/\_\ version 1.5.2 /_/ Using Python version 2.6.6 (r266:84292, Jan…
Getting SQL Server table size with Powershell
$conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = “Server=********\NCRWO;database=NCRWO_TransactionLog;Integrated Security=true;MultipleActiveResultSets=true” $conn.Open() $cmd = New-Object System.Data.SqlClient.SqlCommand $cmdInner = New-Object System.Data.SqlClient.SqlCommand $cmd.Connection = $conn $cmdInner.Connection = $conn $cmd.CommandText = “SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE'” $swReader = $cmd.ExecuteReader() while ($swReader.Read()) { $cmdInner.CommandText =…
Slow inserts due to…the recycle bin??
We had an ODI job that was dropping and creating 296 tables (and indexes) every two minutes. The drops were not issued with a “purge” directive appended, so we created, dropped, and pushed to the recycle bin almost 8 million…
Loading snowflake from Oracle
Single class to illustrate loading data… import java.sql.*; import java.util.*; public class loadSnowflake { public static void main(String[] args) throws Exception { Class.forName(“oracle.jdbc.driver.OracleDriver”); Class.forName(“com.snowflake.client.jdbc.SnowflakeDriver”); Properties properties = new Properties(); properties.put(“user”, “showard”); properties.put(“password”, “*****”); properties.put(“account”, “rcaccount#”); properties.put(“schema”, “PUBLIC”); Connection connection =…
Improving batch inserts into MySQL
We had a custom class to copy data from Oracle to MySQL at AWS. It was sending each statement individually, even though we batched them. import java.sql.*; public class test { public static void main(String args[]) throws Exception { Class.forName(“com.mysql.jdbc.Driver”);…
Running admin commands on secondary mongodb server
You have to let your session know that reading this information from a non-primary server is OK (literally)… -bash-4.1$ mongo rs1:SECONDARY> show dbs 2018-02-01T10:37:58.457-0500 E QUERY [thread1] Error: listDatabases failed:{ “operationTime” : Timestamp(1517499476, 1), “ok” : 0, “errmsg” : “not…
Peoplesoft – Printing Balance sheet tree
with mytree as (select distinct tnode.tree_name, tnode.parent_node_num, tnode.tree_node_num, tnode.tree_node, tlvl.tree_level, glacct.account, glacct.account_type, tnode.parent_node_name, glacct.descr from sysadm.pstreenode tnode, sysadm.pstreeleaf tleaf, sysadm.ps_gl_account_tbl glacct, sysadm.pstreelevel tlvl where tnode.setid=tleaf.setid and tnode.tree_name=tleaf.tree_name and tnode.effdt=tleaf.effdt and tleaf.tree_node_num between tnode.tree_node_num and tnode.tree_node_num_end and glacct.account between tleaf.range_from and…
Splunk – Transaction to calculate start and end time of component
host=cmhlpecomecm* EOMReservationService AND (started OR ended) | eval tmp=split(_raw,” “) | eval thread=mvindex(tmp,5) | transaction thread startswith=”started” endswith=”ended” | timechart span=1h avg(duration) median(duration) The query above results in the following output on the Events tab of the UI… …and the…
C# – Write file on client and read same file from inside the database
We had a job that an enterprise software vendor was running that failed on permissions denied. They asked us to open the file share to anyone without a password. Our awesome SQL Server DBA pushed back, thankfully. We came up…