DEV Community: KateYoung

Tutorial on How to connect to a Linked Server

KateYoung — Sat, 14 Aug 2021 18:20:00 +0000

Linked Servers are a way that a SQL Server can talk to another ODBC database, such as another SQL Server instance or an Oracle database, with a direct T-SQL query.

In this article, I'm going to show you how to set up a linked server with simple steps!

Enviorments:

OS: windows
Database management system: SQL Server 2019 & SQLBase 12.2

What I am doing:

Connect SQL Server with Gupta SqlBase,so that you can view tables and data on sql server.

Step one:

Create ODBC connection @ODBC DATA SOURCE Administrator.

Open ODBC data source administrator panel by go to the Start menu under Windows Administrative Tools, choose ODBC Data Sources:

Step Two:

On the panel, choose System DSN over User DSN.

Why?

User DSN tab will show data source only for a currently logged user on this computer. While creating data sources under the System DSN, it will be available to any user that is logged on to this computer.

This will open the Create New Data Source dialog, from the list, choose a driver for which you want to set up a data source. In my case, it will be Gupta SQL Base for SQL Server:

Data Source Name: give a name to this connection
Database Name: the db name that will be retrieved
Server Name:the targeted db server
User Name and password: your choice of password

NOTES: Test connectivity on created DSN.

Step Three:

Navigate to SQL SERVER 2019, Generate a new script to create a new Link server.
The script is written as below:

Replace [Database Source Name] field with created data source name from step two.

USE [master]
GO
/****** Object:  LinkedServer [Database Source Name]    Script Date: 8/11/2021 1:07:38 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Database Source Name', @srvproduct=N'Database Source Name', @provider=N'MSDASQL', @datasrc=N'Database Source Name'
 /* For security reasons the linked server remote logins password is changed with password */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Database Source Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'SYSADM',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Step Four:

Refresh Server Object by navigate to Linked Servers under Server Object tab.
Check if the connection is appeared and you can expand to see all the tables from source database.

Step Five:

Verify if connection is successfully stored in sql server.

Option One:

Run below scripts:

sp_linkedservers;

This will show a list of linked servers, which means SQL Server recognized this connection and

Option Two: Using select statement on Linked server:

Script:

select * from OPENQUERY(LinkedServer ,'select * from desired_database.schema.table_name' )  Go

Step Seven:

Data Transfer:
Script:

INSERT INTO targeted_database.dbo.table_name 
SELECT * FROM OPENQUERY (LinkedServer, 'SELECT * FROM desired_database.schema.table_name)
Go

There you go!

How to add class file to web application in ASP.NET

KateYoung — Thu, 25 Mar 2021 13:39:28 +0000

IDE
Visual Studio 2019

Scenario
I was trying to create a public class and add it to a webform.

Solution
After researched a bunch of materials, I finally figured the simple way to do it.

In Class file

Create an ASP.NET folder"App_Code" in your project.
Create a class file in the folder "App_Code".
Declared the class as a public class and type your methods there.
Right-click the class file to properties
Look for "Build Action", change it to "Compile"

In webform

Go back to the webform that you wanted to add a class file and type "using webForms.App_Code;" at the top of the page.
There you go! Now you can reference your public class file anywhere you want!

Customer first

KateYoung — Tue, 23 Mar 2021 02:48:08 +0000

First, understand the problem your customer has
And then you solve that problem;

Second, you understand the data. What data goes from where to where, how does it look, how is it stored;

Third, you create the API; how does the information look that is exchanged between customer and UI, and how is it between UI and backend;

Fourth
You don't need every UI detail, but you need to know which data needs to be shown how fast
Your whole solution changes depending on whether how they want to display on the interface.

Really
what does it mean to put the customer first?
put yourself in their shoes!
what's the data the user sees?
What does he input?
Where does the data need to go?
How fresh does it need to be?

Mar,18,2021

KateYoung — Thu, 18 Mar 2021 19:34:02 +0000

Today is the day I begin to learn ASP.NET, as someone who just started her professional career. I would like to document my debug journey.

IDE
Visual Studio 2015

Scenario
I tried to create a webform and get stuck at one bug for 2hours.
and how I troubleshoot.

Investigation

I make sure that each element has assigned a unique ID so that each textbox is related exactly to each label.
I make sure that each button has assigned a unique ID so that it associates with its function block.
I eliminated unlikely causes and narrow them down to sensible cause: unlikely it's caused by HTML tags, and since it should return values when you clicked the button, so it could be an interactive behavioral bug.

Hypothesis
Onclick was not appear on aspx page.

Test
button.Onclick doesn't response as it should be.

Conclusion
Go back to the source code page, check for buttons and add "OnClick" at the end of the line, assigned it to the associated button ID. since there are two buttons.

Takeaway
when dealing with errors, try to form a road map in your head, first, ask what exactly doesn't work? try to focus on one little piece at a time, with the enormous information on the internet, you can easily lose focus and waste your time.

Be sensible to think through the code lines, is it a behavioral bug? is it something else?

Be patient, patient, patie, pati, pat, pa, p.ʕ•́ᴥ•̀ʔっ

Advice for a fresh starter

KateYoung — Fri, 19 Feb 2021 04:13:20 +0000

With almighty God's blessing, I recently got an offer as junior programmer, so I asked my mentor for advice on how to better mentally prepared in a workplace.

From my mentor:

Right Mindset
Realizing you're going to mess up in the beginning, you're going to be terrible, you're going to do a lot of stupid stuff and that's OK, you're there to learn, so learn. don't stop learning. don't stop learning once you get the answer, learn more.

You'll need to figure out what works for you. Sometimes it's more efficient when you have a concrete goal.

Ask
Nobody expects you to know everything or anything really.. but they need you to tell them when you're stuck.
So if you work on a task, try to figure it out on your own. If you can't make progress for a day, go ask for help.

Never end up in a situation where you wasted 3 days trying to do someting without asking for help or telling someone you're stuck.

The best format is "I'm trying to do X because I need to do Y. I tried to do Z and T but it didn't work. I looked on the web and read through the documentation but I don't know where to go from here".

Tell them what you want, tell them why you want it, tell them what you tried and show them that you did some work to figure it out already.

I'd add one: confront the dragon,slay it!ʕ•́ᴥ•̀ʔっ

Codewars-Binary addition

KateYoung — Sat, 13 Feb 2021 16:48:19 +0000

This challenge has the following instructions:

Adds two numbers together and returns their sum in binary.
The binary number returned should be a string.

The first step is simply adding numbers to a sum,
The second step here intoducing toString() method, what it does is passing in an value and convert it based on numeral system.

Let's look at some basic number systems:
1.Decimal number system (Base- 10)
2.Binary number system (Base- 2)
3.Octal number system (Base-8)
4.Hexadecimal number system (Base- 16)

In this case, the solution will be:

Take it to the next level, using arrow function expression:

One thing to keep in mind is that, you can only pass in from range 2 through 36, otherwise [Range error] is thrown.

Happy coding!

Bonus challenge: How to convert your name into binary representation? ʕ•́ᴥ•̀ʔっ

Writing is hard

KateYoung — Sat, 13 Feb 2021 04:45:21 +0000

Writing is hard, especially when you're first start, but its rewards is beyond what you can image. Just pick up one bloody problem you want to write about, engage with the material until you find something that grips you that you desire to investigate it.

Writing is a process of investigating, sorting, summarizing, formulating, integrating in your own personality at the highest and most abstract level of organization.~~(borrowed from JORDAN PETERSON)~~
It's difficult but it can be done by daily practice just like learning to do anything.

Therefore, here I am.
Happy learning! ʕ•́ᴥ•̀ʔっ