SQL Server to Tally

Professional Edition

udiMagic Professional edition supports import of data from SQL Server database to Tally Prime. It supports :

Getting Started

How do I transfer data from SQL Server database into Tally ?
Before we proceed, let's have look at the steps involved in Excel to Tally vs SQL Server to Tally.

Steps Import from Excel to Tally Import from SQL Server to Tally Remarks
Step 1 Start udiMagic Software Start udiMagic Software
Step 2 Select option Excel to Tally Select option Advanced Usage
Step 3 Select option Import data into Tally Select option MS-SQL Server [SQL Server to Tally]
TODO : Specify the Configuration string to connect to the SQL Server database
Step 4 Select the Excel file Select the Query file [Excel to tally]
Select any Standard Excel template provided with udiMagic Software.
Example: Vouchers-Sales-Purchase-Basic.xls

[SQL Server to Tally]
TODO : Write SQL SELECT statement to retrieve data from your database tables. Your SELECT query should return the same columns as given in the Standard Excel template.
Step 5 Select the XML file
[Optional - XML file is automatically selected]
Select the XML file
Each Standard Excel template provided with udiMagic has a corresponding XML file.

Here's an example:
Excel file: Vouchers-Sales-Purchase-Basic.xls
XML file : Vouchers-Sales-Purchase-Basic-xml-tags.xml
Step 6 Click the Start button Click the Start button

As it can be seen, the above task to import from SQL Server to Tally involves two main things:-

  1. To write Connection String to connect to SQL Server
  2. To write SQL-SELECT statements to fetch data from the SQL Server database

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

Writing Connection String
Follow these steps :-

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.

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;

a) You need to replace the highlighted text with actual values.
b) For more information on connection-strings, please refer this link http://www.connectionstrings.com

Writing SQL-SELECT statements

As database structures vary from case to case, you must write SQL statements (queries) to fetch data from SQL Server.

Sample SQL SELECT query

<SQL>SELECT vchno as id,basevtype,vchtype,vchno,vchdate,refno,refdate,partyname,partygstin,statename,itemname,hsncode,uom,qty,rate,discount,amount,igstrate,igstamt,cgstrate,cgstamt,sgstrate,sgstamt,salepurcledger,igstledger,cgstledger,sgstledger,narration FROM gstdata</SQL>

a) You must write SQL SELECT to select appropriate fields from your database tables.
b) Save the SQL Query in a file with extension qry.
c) If required, please consult your system administrator to write SQL SELECT statement.

Additional notes:
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. 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.

Importing data from SQL Server to Tally
  1. Start udiMagic Software
  2. Select the option Advanced Usage
  3. Select the option SQL Server
  4. Click the Browse button and select the "SQL Query file"
  5. Click the Browse button and select the "XML file"
  6. Click the Start button

Using Named Parameters in SQL-Select query

You can use named parameters :FromDate and :ToDate in your SQL Query to provide input for the fields "From Date" and "To Date" in udiMagic for uploading data from SQL Server to Tally Prime.

How to use parameters in SQL query?

Here's a sample SQL SELECT query that shows how to use the :FromDate and :ToDate named parameters

SELECT field1,field2,fldDate FROM table WHERE table.fldDate>=:FromDate AND table.fldDate<=:ToDate

Instead of hard-coding the period (from and to dates) in SQL-query, we have used 2 named parameters viz :FromDate and :ToDate. The values for these parameters are assigned at runtime based on the period you enter in the udiMagic screen as shown here.