SQL Server - Set Default Time stamp in date

set default time, add time in date, default timestamp, time stamp

Another story :)
some time we need to add timestamp along with the date, or we need to replace existing timestamp with custom timestamp
Yes this is doable and it is very easy.

Here i am going to show a example from AdventuresWorks database, object will be user person.person and fieldname is createdDate.

1. I am converting first datetime to date only
2. Append date with default timestamp

This will work on both either on date field or on datetime field

select cast(createddate as date) ,DATEADD(day, DATEDIFF(day, 0, cast(createddate as date) ), '11:00:00.000') from [Person].[Person]

How to execute SQL query on CMD using text file and log result in text file

SQLcmd is small and easy to use tool is us with from last few decades. For starters SQL CMD is good and for some developers they are use to on cmd screen. They love to do query upon the screen, all is about the taste of doing work.

SQLCMD is command line can be used with in the cmd itself. You can execute the sql some Tsqls and any type of query using script. So this article is about the same that how to execute sql from script file using a sqlcmd.

At the same time we can generate the output of the script which is being executed in sqlcmd using "-o" parameter.

SQL script can be call using "-i" parameter.

sqlcmd -S IP_ADDRESS -d Database_Name -U Username -P Password -i C:\Users\***\Desktop\test.sql -o C:\Users\***\Desktop\test1.log

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.

Error
C:\Users\*******>sqlcmd -S ServerName -d master  -U sa
Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Solution
I am using "sqlcmd -S ServerName -d master  -U sa" command to connect cmd with SQL Server. I observed that the command was correct but spell mistake occur in Server Name, So when ever you guys get error like this, check your server name once in command

Msg 102, Level 15, State 1, Server *********, Line 1

This error is received when changing database using "use" command on sqlcmd window. I have verified that database name is exists and there is no spell mistake but still error is received. Doing google search nothing getting from articles. After relaxation of some time i have noticed the error carefully it is saying syntax error near '2019'

That mean something fishy in database name itself.


1> use 2019_database
2> go
Msg 102, Level 15, State 1, Server *********, Line 1
Incorrect syntax near '2019'.
1> use [2019_database]
2> go
Changed database context to '2019_database'.
1>

Solution:
use solid bracket around the database backup name

Do/Donts

• Best practice to keep database name starting from alphabet a-z
• If Database name start from Numeric then donot forget to use solid brackets around the database name
• Always use "GO" on sqlcmd to execute query

How to execute query on CMD

Yes title is correct, you can execute queries on CMD for SQL Server. See below syntax showing how to connect with  CMD and enter in SQL Prompt to execute queries.

Step 1 - Open  CMD
Step 2 - Connect
C:\Users\*****>SQLCMD -S IP_Address\Instance_Name -U sa -P SQL_Server_Password
1>
2> select * from master.test
3> Go

SQL Server - Set Default Time stamp in date

SQL Server - Set Default Time stamp in date

Another story :)
some time we need to add timestamp along with the date, or we need to replace existing timestamp with custom timestamp
Yes this is doable and it is very easy.

Here i am going to show a example from AdventuresWorks database, object will be user person.person and fieldname is createdDate.

1. I am converting first datetime to date only
2. Append date with default timestamp

This will work on both either on date field or on datetime field

 select cast(createddate as date) ,DATEADD(day, DATEDIFF(day, 0, cast(createddate as date) ), '11:00:00.000') from [Person].[Person]

Invalid byte 1 of 1-byte UTF-8 sequence

Error:
Sorry, an error occured loading the new transformation from the repository:

Error reading information from input stream
Invalid byte 1 of 1-byte UTF-8 sequence

Why:
I am modifying transformation XML file and instead of writing sql in "table input" step i directly replace query in xml file with in xml SQL tags.

As you know guys if we paste sql from UI in "Table input" step then kettle automatically convert special characters in SQL query to some readable form, already mentioned in other post read below link

Link 1 
Replace Special Char from string for "table input step" kettle transformation
https://j4info.blogspot.com/2016/02/replace-special-char-from-string-for.html

Link 2
Function to process each character of String [SQL server]
http://j4info.blogspot.com/2016/02/function-to-process-each-character-of.html

Solution :
On other hand if you would like to modify XML file from transformation then you have you replace special character in some format (see above link in the same article).

In my case there was same space like character in sql query (not space in actual,select char(160)).Space can be identify in notepad plus plus shown as small orange dot in place of space if clicked on "Show all Character" button.

But my space is showing space only means it is some special kind of character and we need to replace it " " so that kettle can read it.

py0019 : Color code in idel Python Shell

When you write a code in IDLE python shell, you may observe 4 different colors, yes these colors have some meaning:

• Purple: It shoe that you have typed a command in python shell
• Green: Some content sent to command
• Blue: Any output from command will be BLUE in color
• Black: not a command

What are commands: Print/quit are commands in python. Command generally written with round parenthesis i.e. print(), quit()

Function to Find Valid Invalid Date Format Oracle

Some times we need to check data inside the column if it is in proper shape or proper format. Like some of database saving date data in side of varchar field. which is wrong, if could have invalid date format or can have bad data in terms of date, month, year.

Here i am going to provide you the example of function to check date is properly saved in varchar column, regardless of any date format. Function will check for all combination of date type format and will return valid or invalid i.e. atleast one format is correctly saved in column.

create function datetype1(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype2(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype3(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MM-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype4(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM-DD-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype5(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY-MM-DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype6(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD/MM/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype7(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM/DD/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype8(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY/MM/DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/



CREATE OR REPLACE Function DateFieldValidInvalid
   ( field_v in varchar2)
   RETURN varchar2
IS
   cnumber varchar2(2000);
v_query1 varchar2(4000);

BEGIN
v_query1 := 'select a from (select datetype1(''' || field_v||''') a from dual union ' ||
'select datetype2(''' || field_v||''') from dual union ' ||
'select datetype3(''' || field_v||''') from dual union ' ||
'select datetype4(''' || field_v||''') from dual union ' ||
'select datetype5(''' || field_v||''') from dual union ' ||
'select datetype6(''' || field_v||''') from dual union ' ||
'select datetype7(''' || field_v||''') from dual union ' ||
'select datetype8(''' || field_v||''') from dual) where a = ''Valid''' ;
 
execute immediate v_query1 into cnumber;

RETURN cnumber;
END;


select DateFieldValidInvalid(created_ON) from t_contact ;

Row Count of Each Table

Is it easier way to find row count of each table in SQLSERVER 

Yes, earlier i have added post which is custom dynamic tsql to get row count of each table in sqlserver. Even SQLServer system tables itself store the result of each row count of all tables of all databases.

It can be easily identify using sys.tables and partition table. This is not a innovative but it is just a smartness at your work

SELECT 

SCHEMA_NAME(schema_id),

t.name AS Table_Name,

SUM(p.[rows]) AS [TotalRowCount]

FROM 

sys.tables t

JOIN sys.partitions p ON t.object_id = p.object_id

AND p.index_id IN ( 0, 1 )

GROUP BY SCHEMA_NAME(schema_id), t.name

having 

SUM(p.[rows]) > 1

to get all tables remove having clause from above query you will be able to see the result of each table. Right now it is returning the only those tables whose result is more than 1.

print row count in begin try on delete

I was executing below section of code on sqlserver

SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN

select count(*) from my_table

delete from my_table where key = 1

COMMIT
PRINT 'Success!'
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'Fail'+ ERROR_MESSAGE()
END CATCH
SET NOCOUNT OFF

Here my code is able to show the count of Select query but not able to show the result of delete query in result window.

Requirement:
To show the result of delete in query window.

Solution :
Remove SET NOCOUNT ON and SET NOCOUNT OFF from your block, you will be able to see the delete result.

Count of Each Table Oracle

small and simple plsql script is created to find count of each table in oracle database for one schema.
This PLSQL can execute either on SQL Developer or SQLPlus editor. It's a small plsql used cursor and for loop and concept of dynamic query execution in Oracle.

See below code

set serveroutput on
declare
    
    cursor c2 is select * from user_tables;
    v_sql1 varchar(4000);
    v_count number;
begin
   for ii in c2 loop
    v_sql1 := 'select count(*) from '|| ii.table_name;
    
    execute immediate v_sql1 into v_count;
    dbms_output.put_line(ii.table_name ||'-'|| v_count);
   end loop;
end;
/

Simple Cursor and For Loop in Oracle

in oracle we can open cursor and need to start loop (while similar as SQL Server) so that we can process data what ever is coming in cursor.

In this post i am going to show you simplest code or plsql to do cursor and for loop of cursor.
See below syntax

set serveroutput on
declare
    cursor c2 is select * from user_tables;
begin
   for ii in c2 loop
    dbms_output.put_line(ii.table_name);
   end loop;
end;
/

In this cursor Oracle will automatically open and close the cursor before start and end of the program. so do not worry about the having 'open cursor' and 'close cursor' in your code.

py0018 : Change color of button on hover and borderless button

while working in python we might need to create border less button or change the color of button on hover.
for this we can add parameter in method (def) of button, activebackground='green', borderwidth=0. Activebackground will be responsible of change color of button when mouse cursor hover on the the button
and borderWidth will remover the border from the button

import tkinter as tk

class HoverButton(tk.Button):
    def __init__(self, master, **kw):
        tk.Button.__init__(self,master=master,**kw)
        self.defaultBackground = self["background"]
        self.bind("Add**less**than**Enter**Add**greater**than", self.on_enter)
        self.bind("Add**less**than**Leave**Add**greater**than", self.on_leave)

    def on_enter(self, e):
        self['background'] = self['activebackground']

    def on_leave(self, e):
        self['background'] = self.defaultBackground

root = tk.Tk()

classButton = HoverButton(root, text="Classy Button", activebackground='green', borderwidth=0)
classButton.grid()

root.mainloop()

Note* Add**less**than** *** **Add**greater**than
please modify the code before execution, add less than and greater than sign

py0017 : Parameter 'bg' value is not used less... (ctrl+F1)

Error:
Parameter 'bg' value is not used less... (ctrl+F1)
Inspection infoL This inspection highlights local variablem parapmeters or local function unused in scope

Requirement:
How to set default color of button default to white

Solution:
Used "bg" paramter to set color value

   def numrcbtn(self, val, write=True, width=12, height=4, padx=1, pady=1) :
   return HoverButton(root, text=val, command=lambda: self.click(val, write), width=width, height=height, padx=padx, pady=pady, activebackground='Gainsboro' , bg='white')
   
   

Traceonly option is currently not supported

Problem :

set autotrace traceonly;

Execute above query on sql developer.

If you are looking for traceonly option in sqldeveloper then answer is no , they query you executed on oracle CMD will not work on SQL Developer.

Instead you can write the query on SQL Developer whose xplain plan your trying to get.

It is more simple in SQL Developer , see below how

1. Write a SQL query on SQL Developer
2. Select query
3. Click on third icon (right to "Execute statement") "Explain Plan) or Press F10
4. On Pressing third icon click on , it will generate the query and 
5. Execute it

Deleted Offline Database? No Issue

Yes you read right, no worry if you deleted Offline database it is easy to recover.

Very first thing all must aware that if you delete any Offline database, SQL Server will not delete mdf and ldf files from filesystem. Still there are chances of recover database directly using old mdf files you can create new database.

Benefits :

• Easy to Restore
• Easy to Recover
• No loss of tables and data

Use below query to create new database.

Here is mdf file of offline database which is still exists on filesystem after deleting of offline database

CREATE DATABASE offlinedbname ON 

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\offlinedbname.mdf' )

FOR ATTACH

GO

Delete Offline Database and Space Recovery

This is very recent post i have read SQL Server will not delete mdf and ldf files if database is Offline state.

This Problem has been observed in case of recovering database and action performed to delete database which are Offline. So after deleting off line database one have to manually delete the mdf and ldf file from filesystem in order to recover the space.

Is it safe to delete orphan mdf , ldf filesfrom file system if no database exists.

Answer is Yes it is safe to delete files.

SQL Server Audit Table Total Count Total Nulls Len of Data

Hello engineers, are you guys working on SQL Server. Are you guys working on TSQL. If yes i have written a TSQL program which right example of

• Execute dynamic SQL
• In Parameters/variables
• Out parameters/variables
• IN/Out in dynamic SQL
• Cursor in TSQL
• Audit of all tables data
• How many records in table
• How many distinct records in each field
• How many not null records
• Maximum length of data in each field

create table all_counts (id int identity , table_name  nvarchar(200), column_name  nvarchar(200), data_type nvarchar(200) , total_count int, distinct_count int , notnull_count  int, data_len int)

Declare 

@table_name varchar(50),

@column_name nvarchar(100),

@data_Type nvarchar(100),

@total_count integer,

@distinct_count integer,

@notnull_count integer,

@data_len integer,

@query_str nvarchar(4000),

@query_str2 nvarchar(4000) ,

@query_str3 nvarchar(4000) ,

@query_str4 nvarchar(4000) ;

declare cur_col Cursor for 

select  

t.TABLE_NAME

,column_name

,data_type 

from 

INFORMATION_SCHEMA.COLUMNS c 

join INFORMATION_SCHEMA.tables t on c.TABLE_NAME = t.TABLE_NAME 

where 

t.TABLE_TYPE ='BASE TABLE' 

open cur_col

fetch next from cur_col into @table_name,@column_name,@data_Type

while @@FETCH_STATUS=0

begin

Set @query_str='Select @total_count= count(@column_name1) from '+ @table_name;

exec sp_executesql @query_str, N'@column_name1 nvarchar(500),@total_count int output' ,@column_name1=@column_name,@total_count=@total_count output

Set @query_str2='Select  @distinct_count= count( distinct ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str2, N'@distinct_count int output' ,@distinct_count=@distinct_count output

Set @query_str3='Select  @notnull_count= count(  ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str3, N'@notnull_count int output' ,@notnull_count=@notnull_count output

Set @query_str4='Select  @data_len= len(max(  ' + @column_name + ')) from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str4, N'@data_len int output' ,@data_len=@data_len output

insert into all_counts select @table_name, @column_name, @data_Type, @total_count, @distinct_count, @notnull_count, @data_len

fetch next from cur_col into @table_name,@column_name,@data_Type 

end

close cur_col

deallocate cur_col

I hope this program is helping many engineers in different ways. Rest of usage is depend upon the thinking and capability of an engineer. He/she can modify this program to any extent since this is generic TSQL.

If any faced error while executing above TSQL cause of Table Name or Column Name have special character which was not supported by Dynamic SQL.

Error :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'tablename'

Solution:

We need to update table_name and column name variables from Cursor as below

['+ @table_name +']';

Foreign Key Reference Tables

To know about the database it is good for one if he/she looking for all references for a object

• whats tables are dependent on MAIN table (Child Objects)
• On which tables MAIN table is dependent (Parent Objects)

Suppose i have 3 tables and in parenthesis show its fields

Dept (dept_id, Dept_name )

Emp (emp_id, Emp_first_name, emp_last_name , dept_id)

Sal(sal_id, year, emp_id)

Here EMP is Main table and Dept is Parent table (for Main table) and Sal is Child table (for Main table).

So one can easily understand the database if he has knowledge of both kind of references

Below is the script to identify the same.

SELECT 

f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM 

sys.foreign_keys AS f

JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

where 

OBJECT_NAME(f.parent_object_id)='MAIN_TABLE'

or OBJECT_NAME (f.referenced_object_id) = 'MAIN_TABLE'

SQL Server Database Restore Audit

Do you want to know who restore the database, when restore the database, which database backup has been used.

Yes you can audit the database restore, see my previous post

How to Find Latest Database restored on Database and which backup file used

How to Find Latest Database restored on Database and which backup file used

SELECT 
   resh.destination_database_name, 
   resh.restore_date, 
   bckst.backup_start_date, 
   bckst.backup_finish_date, 
   bckst.database_name as sourceDBName, 
   bmf.physical_device_name as backupFileUsed,
   bckst.*
FROM msdb..restorehistory resh
INNER JOIN msdb..backupset bckst ON resh.backup_set_id = bckst.backup_set_id
INNER JOIN msdb..backupmediafamily bmf ON bckst.media_set_id = bmf.media_set_id
WHERE resh.destination_database_name = '**Database Name**'
ORDER BY resh.restore_date DESC

Most of time i was asked which database bacup has been restore on particular database, who restore the database, what is machine name, which backup file used, where the backup has been placed.
Donot worry about this sqlserver store all of this information as a metadata so above query is capable of give answers of what/when/where 3W/5W

Execute above query on any of database query window, add filder of database name on which you want to find all these questions. make sure user have the rights of executing query in cross databases.

py0016 : ModuleNotFoundError: No module named 'Tkinter'

Traceback (most recent call last):
  File "C:/Users/*****/PycharmProjects/Cal1/_main_.py", line 2, in
    from Tkinter import *
ModuleNotFoundError: No module named 'Tkinter'

While import from tkinter i was getting error as "ModuleNotFoundError: No module named 'Tkinter'". As further investigation i found tkinter is the part of python software that we have installed in actual. So no need to run anyother
pip install command for "tkinter".

i have recheck my code as i written "from Tkinter import *", so the error was here i used "T" capital T instead of "t"

so if you get the error i advise check your spelling before doing anything it should all in lowercase

py0015: NameError: name 'Select' is not defined

Error 
select = Select(driver.find_element_by_id('id****')) 
NameError: name 'Select' is not defined

This error i received when i tried to select value from drop down menu while doing automation in python selenium

How ever i came to know that i was using correct code but some import class is missing from header of file. I need to add "from selenium.webdriver.support.select import Select" in main file or same file so that code will work fine.

Solution:
from selenium.webdriver.support.select import Select

Correct code for click on drop down:
select = Select(driver.find_element_by_id('**id**'))
select.select_by_visible_text('String')

py0014: table_name1 = table_name.replace(',','') AttributeError: 'pyodbc.Row' object has no attribute 'replace'

Problem :
below is my sample code, where i am getting error table_name1 = table_name.replace(',','') AttributeError: 'pyodbc.Row' object has no attribute 'replace' and in same example you can cover how to use replace in python
    cursor.execute('select table_name from information_schema.tables')
    for table_name in cursor:
        table_name1 = table_name.replace(',','')
        print (table_name1)

I was trying to loop the list of all table names which are in schema using query "select table_name from information_schema.tables". The intention was to do some action based on the table exists or not.

Why i used replace:
suppose after for loop when i use command "print (table_name)", it gives output enclosed in single quotes and postfix by comma, so i used replace function to replace comma and it starts throwing above error.

Solution:
if you notice code above i gave "table_name" in for loop and for replace function we have to write it as str.replace(old,new). But in my case replace was not able to get any string in "table_name" because "table_name" is just act as "i" here in for loop and it is not a attribute in this situation.

So i have to use "table_name.table_name.replace(',','')"

Correct code as below:
    cursor.execute('select table_name from intermediate_table')
    for tb in cursor:

        table_name1 = tb.table_name.replace(',','')
        print (table_name1)

Here i have replace table_name in for loop with "tb" for better visibilty and better understanding.

py0013 : use function from another file in main file python

To make code convient and readable form it is neccessary to break code in multiple files, where you can write your code and write function can be later call on any where in the project. In python its easy to do.

Below is the sample code, i am taking the example from my previous post

py0012 : try catch in Python/ DB connection in try catch

which is having file name "file1" and t funcation made which will return something on db connection pass or fail and that can be used in mail file as per below code example. First we need to import file, then use funcation name directly "t()" without any file reference.


from file1 import *
if t() == "DBConnectionError":
    # your code will stop here, dependent file will not throw any error
exit(0)


if t() == 1:
# if database connection pass it will move further as per the written commands, this also show that how we can handle error on other files call
    driver = selenium.webdriver.Firefox(executable_path=FirefoxDriver, firefox_binary=binary, firefox_profile=profile)

py0012 : try catch in Python / DB connection in try catch

try catch is preliminary to add in your code, other wise if it break in between it looks ugly when error come to end user. To make code clean and show user friendly error you can use try catch, see one of the example below when database connection.

Earlier when database connection fails, it show red color unexpected error and since this file is being in use further so need to use try catch which will return something so that further can be used as if else for any type of condition

import os
import pyodbc
from configuration import *

try:
    crsrconn = pyodbc .connect("Driver={SQL Server Native Client 11.0};"
                                "Server="+ServerName+";"
                                "Database="+DatabaseName+";"
                                "Trusted_Connection=yes;"
                                "pwd="+Password+";")
    cursor = crsrconn.cursor()
    cursor.execute('SELECT count(*) rn FROM *****')
    for row in cursor:

        if row != 0:
            def t(): return 1
            print ('1')
        #else:
         #   def t(): return 0
          #  print ('0')

except pyodbc.Error as err:
    print("DBConnectionError")
    def t(): return ("DBConnectionError")

py0011 : Learnt Something impressive in Python

Code writing techniques

Today i tried to write some code in python like i used for , if , else, try, catch, return. By using this came to know python has some limitation, no we cant say limitation its syntax.

• For "if" we can not keep the code below on same starting column point, you have to give "tab" space in front of them for any hierarchy .
• write "return" like this, def t() return 1 or def t() return "true"

py0010 : Firefox not opening Websites which are in Tunnel/Proxy

If your system has implemented with proxy or tunnel and you want to open a web link (which are in tunnel or in proxy)
note* generally these kind of websites not worked on internet, but the firefox leaunched by Selenium/python using Geckodriver is open with internet without any proxy setting given in your system. So you have add some lines of code in your .py file before Geckodriverr called so that it will open firefox with system proxy settings.

#Add proxy IP Address
proxy = "***.***.***.***"
#Add proxy port
port = int("****")

cap = DesiredCapabilities().FIREFOX
cap["marionette"] = True

profile = webdriver.FirefoxProfile()
profile.set_preference("network.proxy.type", 1)
profile.set_preference("network.proxy.http", proxy)
profile.set_preference("network.proxy.http_port", port)
profile.set_preference("network.proxy.ssl", proxy)
profile.set_preference("network.proxy.ssl_port", port)
profile.update_preferences()

Related/Helpful Links:
py0005 : selenium.common.exceptions.SessionNotCreatedException: Message: Unable to find a matching set of capabilities
py0004 : Begginner error, Open Firefox

py0009 : How to Declare Variable and Use variable in same file of Other file

Assume that i have two files in my project first is "__init__.py" another one is "configuration.py" and i am using config file for different type of parameters which needs to change timely. The case is declared variable in config file i have to access them in same file or different file in all project.
See below how this can be done.

Configuration.py :
FirefoxPath = "C:/Users/*****/AppData/Local/Mozilla Firefox/firefox.exe"
FirefoxDriver = "C:\\Users\\*****\\Desktop\\python\\geckodriver-v0.24.0-win64\\geckodriver.exe"


__init__.py :
import os
from Configuration import *
binary = FirefoxBinary(FirefoxPath)
driver = selenium.webdriver.Firefox(executable_path=FirefoxDriver, firefox_binary=binary, firefox_profile=profile)

py0008 : connection with sqlserver database

First of all you need to install pyodbc package if not you might face this error "unknown command pyodbc"

Open cmd and use below command to install :
pip install pyodbc

Helpfull Link :

py0003 : pip install selenium SyntaxError: invalid syntax, while install selenium

install above package. for me it is very easy step, to install this package connect with database without any error in single shot.

I used below code to connect with database.

import pyodbc
crsrconnection = pyodbc .connect("Driver={SQL Server Native Client 11.0};"
                      "Server=IPAddress Or Server Name\Instance;"
                      "Database=****;"
                      "Trusted_Connection=yes;pwd=****")


cursor = crsrconnection.cursor()
cursor.execute('SELECT count(*) rn FROM sametable')

for row in cursor:
    print(row)

py0007 : Click on div ul li span class "Display Name" Selenium Python

Being a startup its difficult to work around, but struggle of 2 days i was able to click on element having flow like div/ul/li/span/class.

Searched lot on google most of examples are with ID, but in my case id was not defined so i tried many time with class name and Display name with possible probability. At last able to Click.

Solution:
Add below lines in your code
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait as wait

wait(driver, 10).until(EC.element_to_be_clickable((By.XPATH, "//span[text()='DisplayName']"))).click()

py0006 : selenium.common.exceptions.WebDriverException: Message: Failed to find firefox binary. You can set it by specifying the path to 'firefox_binary':

I have reinstalled the Firefox earlier it was working fine, suddenly it started giving this error. If you read error carefully it is going to search firefox.exe (binary file) which is unable to find.

Locate your firefox.exe file and set in a variable and add below lines in your code

from selenium.webdriver.firefox.firefox_binary import FirefoxBinary
binary = FirefoxBinary('C:/Users/****/AppData/Local/Mozilla Firefox/firefox.exe')
driver = selenium.webdriver.Firefox( firefox_binary=binary)

py0005 : selenium.common.exceptions.SessionNotCreatedException: Message: Unable to find a matching set of capabilities

Error : selenium.common.exceptions.SessionNotCreatedException: Message: Unable to find a matching set of capabilities

I am using latest Selenium 3.8.0 and GeckoDriver and Firefox version 64.0.2.
Below Firefox version 64.0.2 you might have set capability marionette to False using DesiredCapabilities

Solution
Add below code
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities
cap = DesiredCapabilities().FIREFOX
cap["marionette"] = False

py0004 : Begginner error, Open Firefox

I have written the code and getting two type error from 3 rows of code that is ridiculous:
First error belongs to the Firefox driver which was not found and gives error like  "File "C:/Users/*****/Desktop/python/example/0001.py", line 3, in "

Second type of error is "NameError: name 'selenium' is not defined" as name showcase something not found related to selenium

from selenium import webdriver
from selenium.webdriver.common.keys import Keys
driver = selenium.webdriver.Firefox()

Error on execution of code 
C:\Users\*****\AppData\Local\Programs\Python\Python37-32\python.exe C:/Users/*****/Desktop/python/example/0001.py
Traceback (most recent call last):
  File "C:/Users/*****/Desktop/python/example/0001.py", line 3, in
    driver = selenium.webdriver.Firefox()
NameError: name 'selenium' is not defined

Process finished with exit code 1


SOLUTION:
Add path for Firefox driver that you have downloaded from internet and add "Import selenium" at first line

import selenium
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
driver = selenium.webdriver.Firefox(executable_path= r'C:\\Users\\*****\\Desktop\\python\\geckodriver-v0.24.0-win64\\geckodriver.exe')

Run the Code, Firefox will open automatically

py0003 : pip install selenium SyntaxError: invalid syntax, while install selenium

I was trying to install python-selenium binding, somebody told me to execute below command but i was not aware where to execute, so while executing below command i was getting this error

>>> pip install selenium
  File "", line 1
    pip install selenium
              ^
SyntaxError: invalid syntax
>>> pip
Traceback (most recent call last):
  File "", line 1, in
NameError: name 'pip' is not defined

However i tried to search pip jar, but not found however able to find pip.exe file in installation folder of PYTHON. It was found at "C:\Users\*****\AppData\Local\Programs\Python\Python37-32\Scripts"

So now open CMD, move to above directory ( using cd batch command ) upto script (* it could be different in your case) and execute below command:


C:\Users\*****\AppData\Local\Programs\Python\Python37-32\Scripts>pip install selenium
Collecting selenium
  Downloading https://files.pythonhosted.org/packages/80/d6/4294f0b4bce4de0abf13e17190289f9d0613b0a44e5dd6a7f5ca98459853/selenium-3.141.0-py2.py3-none-any.whl (904kB)
    100% |████████████████████████████████| 911kB 3.9MB/s
Collecting urllib3 (from selenium)
  Downloading https://files.pythonhosted.org/packages/62/00/ee1d7de624db8ba7090d1226aebefab96a2c71cd5cfa7629d6ad3f61b79e/urllib3-1.24.1-py2.py3-none-any.whl (118kB)
    100% |████████████████████████████████| 122kB 3.6MB/s
Installing collected packages: urllib3, selenium
Successfully installed selenium-3.141.0 urllib3-1.24.1
You are using pip version 18.1, however version 19.0.1 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.

py0002 : No Python interpreter is selected

Error is too simple to understand.

This error generally come when you install some of the Python interface like Pycharm or Anaconda only and Python not have installed on your system.

Information for Starters - Python is command based language you need to open cmd and enter in python prompt.

But if you want to work more beautifully there are several interpreter in market to work on UI.

Solution for this problem - install Python.exe on your machine, use default installation process and any latest version of python, in my case i have installed "python-3.7.2.exe"

and launch PyCharm > after click on new project > Select interpreter and select "python.exe"

py0001 : could not find main class com/intellij/idea/main

Have you installed PyCharm on your system?

New to Python?

Probably there are chances of getting this error when trying to launch "JetBrains PyCharm Community Edition **** " from your desktop or from your start menu. I have search on google to reach out the solution every blog giving different solution related to Java_Home path, Java Version Issue ..etc..

But i simply uninstalled the application and installed as per below selection (see below screenshot)

While installation setup will ask for few Check boxes like 

• 32-bit launcher
• 64-bit launcher
• Add lanchers dir to the PATH
• Add "Open Folder as Project"
• .py
• Download and install JRE x64 by JetBrains

Earlier i have installed without "Download and install JRE x64 by JetBrains" selection so i was getting this error.

later i have uninstalled and re-install with all check-box selected with "32-bit launcher" and worked successfully.

Don't forgot to leave your comments if this is helpful.

dba_data_files data file not appears but dbf file in exists on FileSystem

Do NOT  Confuse

If you have executed command "DROP TABLESPACE *** INCLUDING CONTENTS AND DATAFILES;" on oracle database it would be noticeable that table-space and data-files has been deleted from server metadata i.e. no entry can be seen in dba_tablespaces and dba_data_files object.

But if you move to data-file physical path on your machine (in which oracle is installed) you may able to see old data-files (do not worry)!!.

Now you are thinking that i have deleted table-space why these files are here or you would try to delete this manually.

Is it safe to do? , can you delete this manually?

Yes you can delete this manually
1. Stop the server
2. Restart the Server
3. Delete unwanted datafiles from physical location

Error ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

While creating table-space you might get this error. This error generally comes up when you are giving large size to a data-file of table-space than allowed.
For creating large data-file table-space we need to create bigfile table-spaces.

But i would like to suggest not to create bigfile table-space for regular database restore create instead we can create simple table-space with multiple data-files.

Lets reproduce the issue, suppose i am trying to create table-space with data-file of size 40GB

CREATE TABLESPACE tablsapce1 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_01.DBF' SIZE 40G AUTOEXTEND ON NEXT 40M;

Error will appear on the screen

Error ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

So it is better to create one table-space and add two data-files of 20Gb each, see below how to do it:

CREATE TABLESPACE tablsapce1 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_01.DBF' SIZE 20G AUTOEXTEND ON NEXT 40M;

alter tablespace tablsapce1 add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\tablespace01_02.DBF' SIZE 20G AUTOEXTEND ON NEXT 10M;

In this way you can avoid this particular error

On Other hand the concept behind this is "db block size", it depends upon the your Oracle db block size that how bigger you can create your table-space/data-file.

For 2kb Block size data-file size should be below and equal to 8GB and for 4kb (16GB), 8kb (32GB ), 16kb (64GB), 32kb (128GB)