rtslink.com
Home / UDI-Magic / Professional / SQL-Server to Tally

SQL-Server to Tally


Looking for a software utility to transfer data from SQL Server into Tally software. Try out UDIMagic.


CONTENTS


Introduction

SQL-Server is a popular Database Management System (DBMS) which is used as a backend in many ERP packages. Until now, integrating an ERP package with Tally was a tedious task as it required modifications in the existing ERP package. Moreover, this could be done only by programmers.

UDIMagic professionl edition allows to fetch data from your SQL-Server and send it directly to Tally. And all this happens by merely writing few SQL-SELECT statements and XML-tags.

- Works with all versions of SQL Server
- Supports Tally 6.3 or higher versions
- SQL-Server to Tally feature is available in the UDIMagic professional-edition only (UDIMagic v3.0 onwards)


Getting Started


How do I transfer data from MS SQL-Server  into Tally ?

The above task involves three main things:-

1) SQL Server information
    (Specify the Connection string)

2) SQL-SELECT statements 
    (Writing SQL-SELECT statements to fetch data from the MS SQL-Server)

3) XML tags 
    (Writing XML tags required by UDIMagic to process the data and send it to Tally)

Though use of UDIMagic does not require any programming knowledge, you need to have basic understanding of SQL (Structured
query Language). And Of-course, you must have the rights / privileges to access the SQL Server.


SQL-Server Information

The first thing you need to do is to specify SQL-Server Infomation.

Follow the steps given underneath:-
a) Start UDIMagic.
b) Next, select "Advanced Usage" option (available in professional version).
c) Next, select the Database-type as "MS SQL-Server"
d) Next,  click on the "Settings" button and specify the CONNECTION-String for your SQL-Server.
e) Next, click on the "TEST SERVER CONNECTION" button to test-check the connection.

Remarks:-
a) UDIMagic uses the Connection string to connect to SQL SERVER. Hence, it is mandatory to specify the Connection-string. 

Connection Strings

What is Connection String ?

A Connection string is a string that contains information required to connect to a database or a data-file. Because there are
several providers and each provider may have multiple ways to make a connection, you can write a connection-string in
multiple ways.

* A connection-string consists of series of keyword-value pairs separated by semicolons (;).
* The equal-to sign (=) connects each keyword and its value.
* Example:  Key1=Value1; Key2=Value2; key3=Value3;

SAMPLE Connection strings for connecting to SQL-Server

Connecting to a SQL Server Instance
Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

Standard Security
Provider=sqloledb;Data Source=myServerAddress; Initial Catalog=myDataBase;UserId=myUsername;Password=myPassword;


Trusted Connection
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;


Remarks:-
You need to replace the highlighted text with actual values.

More about Connection-strings
For more information on connection-strings, please refer this link http://www.connectionstrings.com/default.aspx


Writing SQL-SELECT statements to fetch data from the SQL-SERVER

After you have test-checked the connection to your SQL Server, you need to write SQL statements (queries) to fetch data from SQL-Server. This process involves :-

a) Plan-out which DATA is to be transferred from SQL-Server to Tally
b) Identify the tables (Masters and Vouchers) that contain the required data
c) Write SQL-queries to fetch data

SQL-QUERIES
SQL-queries are generally written in a Text file (having .qry extension). You can use any TEXT-editor (like Notepad) to view/edit the SQL-query files. UDIMagic allows you to write multiple SQL-SELECT statements in a Query (qry) file.

Sample file:- MS SQL-Server Query1.qry

<SQL.LIST>
<SQL>.........your SQL-SELECT statement1..........</SQL>

<SQL>.........your SQL-SELECT statement2..........</SQL>
........
</SQL.LIST>

As you can see, each SQL-SELECT statement is to be enclosed in <SQL>....</SQL> tag. This allows you to have multiple queries in a single Query (txt) file.

UDIMagic processes these queries one-by-one in the order in which they appear in the query-file. It contructs a result-data-set which can be visualized as Columnar format comprising of Rows and Columns. The first field in the result data-set can be accessed using COLUMNREFERNCE A, the second field in the result data-set can be accessed using COLUMNREFERENCE B and so on. This has been discussed in the next section.


Writing XML tags required by UDIMagic to identify data retrieved from SQL-SERVER

In the previous section, we had seen how to fetch data from the MS SQL-Server. In this section, we shall focus on how to write XML tags to identify the Excel-sheet data and send it to Tally.

What are these XML tags and why are they required ?

To import data into Tally, you need to tell UDIMagic what data you have and where is it stored. This is done using XML tags. Let's take an example to understand it.

STEPS:-

Step 1: Create a database named "scdatabase" in SQL-Server 

Step 2: Create a table "Ledger" which contains 2 fields "Name" and "Parent"

Step 3: Add few records to the "Ledger" table

Step 4: Write a SQL-query to fetch data from Ledger table  (of "scdatabase") as given below:-

SQL-query

<SQL.LIST>
<SQL>
SELECT name AS name,parent FROM ledger </SQL>
</SQL.LIST>


The result of the above SQL-query can be visualised as follows:-

Result data-set

Name Parent
Shweta Softwares Sundry Debtors
Sale of Goods Sales Accounts
VAT Output 4% Duties & taxes


Step 5: Write XML tags to identify and send the data to Tally

XML tags

<XML.LIST>
  <XML>
    <MASTER TYPE="LEDGER">
      <NAME.LIST>
        <NAME COLUMNREFERENCE="A"/>
      </NAME.LIST>
      <PARENT COLUMNREFERENCE ="B"/>
    </MASTER>
  </XML>
</XML.LIST>


That's all. You can now run UDIMagic and import data into Tally Software.

Downloads 

UDI-Magic Demo version


Links 

UDI-Magic Home Page 
UDI-Magic Articles and sample Excel templates



Tags:-
UDIMagic,  SQL Server to Tally,  SQL SERVER, SQL SERVER 2005 Express edition, Excel to Tally
Tally is a registered trademark of Tally Solutions FZ LLC.

Valid HTML 4.01 Transitional