Responsive Ads Here

Saturday, November 18, 2017

How to create a Linked Server to an Azure SQL database

5:16 AM Azure , Azure SQL database 1 Comments
Hi friends, today I will explain how to create and configure a linked server to retrieve data from an Azure SQL database.

Few points are describe in this article for configure link server with SSMS

  1. How to configure link server step by step in SSMS
  2. Solve some common Problem/Issue  when create link server
  3. Using the Query/Script base to create link server
How to configure link server step by step in SSMS

        To create link server via SSMS, go to Object Explorer under the Server Objects folder, right click on the Linked Servers folder and from the context menu choose the New Linked Server Command:

Fig 1.1

The New Lined Server window will be opened:

Fig 1.1

Put your Linked Server Name, then
Select data source: Microsoft OLE DB Provider for SQL Server
Then click on security tab on left side

Under the Security tab, select the Be made using this security context radio button and enter user credentials this exist on Azure server
Fig 1.2

Under the Security tab, use the same setting that we used in the previous example and press the OK button.
This will create a linked server to an Azure SQL Database(Azure Database). And when the plus(+) sign next to the table folder is pressed, the Tables folder will expand and show all tables for the selected Azure database:

Fig 1.3
To retrieve data from the SQL Azure table (e.g [dbo].[Actions]), type the following code:
SELECT Top 10 * FROM [LinkedServerName].[AzureDataBaseName].[dbo].[TableName]

Solve some common Problem/Issue  when create link server

When we create linked server we facing some common issue like Linked server is showing created but we can’t able to access the linked database. It’s given error which is mention below image:

Fig 2.1


Above mention error, Fail to retrive data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

          ADDITIONAL INFORMATION:
                         An exception occurred while executing a Transact-SQL statement or batch.  (Microsoft SQL Server, Error: 40515)

Using the Query/Script base to create link server

How to create linked server for SQL Database on Azure from local SQL Server
Step by step Azure SQL Database Linked Server
Step 1: Go to SQL Server Management Studio
Step 2: Run the below scripts. This script using a stored procedure "SP_addlinkedserver".
EXEC sp_addlinkedserver
@server='Azure', -–Provide Linked Server Name
@srvproduct='',    
@provider='sqlncli',
@datasrc='AzureDataServerName', -–provide Azure SQl Server name
@location='',
@provstr='',
@catalog='AzureDatabaseName' -–Provide azure database name




Step 3: Run the below script to provide the SQL Server login for the above created linked server.


Exec sp_addlinkedsrvlogin 'Azure', 'FALSE', NULL, '[azure sql server login]', '[sql server password]';




Step 4: Verify the created linked server, under the Databases -> Server Objects -> Linked Servers -> Your Linked Server Name





Step 5: Access the azure SQL database table. You need to use 4 part query. [linked server name].[database name].[schema name].[table name]




SELECT Top 10 * 
FROM [linked server name].[database name].[schema name].[table name]























By



at







1 comment:
  



















Labels:
,
,
,
,
,
,

















          

        

          

        

Wednesday, November 1, 2017


          

        

























SSIS FOR BEGINNERS











2:12 AM


basic about ssis
,
Janak Goti

0
 Comments



























SSIS FOR BEGINNERS















Hi friend's, today we are going to understand about SSIS. i collect many information about SSIS blob and try to understand how to use SSIS. i hope it is useful for you.












Now first try to understand about SSIS.








    

  1. What IS SSIS?
    2. 

  2. Why SSIS?
    3. 

  3. SSIS Variable.
    4. 

  4. SSIS Connection Manager.
    5. 

  5. SSIS ToolBox.
    6. 

  6. Data Flow Task.
    7. 





1. What Is SSIS?




    

  • It is a powerfull ETL tool, which is used for building enrterprise level data transformation, and dara integration solutions.
    • 





2. Why SSIS?






    

  • Extract, Transform, and Load (ETL) data from SQL Server to a file  anf also from file to SQL.
    • 

  • Sending and email.
    • 

  • Download the File from FTP.
    • 

  • Rename,Delete,Move File from Defined Path.
    • 

  • It allows you to join tables from different database (SQL, Oracle, ect..) and from potentially different servers.
    • 




Please refer Install SQL Server Data Tools article to understand the steps involved in installing the SQL Server Data Tools or Business Intelligence Development Studio(BIDS).









We will see following contents in this article.




    

  • Variables
    • 

  • Connection Manager 
    • 

  • SSIS Toolbox
    • 

      

    • Contaniner
      • 

    • Tasks
      • 

    

  • Data Flow Task
    • 




Variable:







Variables store values that a SSIS package and its containers, task, and event handlers can use at runtime.







System variables: Defined by Integration Services








    

  • SSIS provides a set of system variables that store information about the running package and its objects. These variables can be used in expressions and property expressions to customize packages, containers, tasks, and event handlers.
    • 






You can go Through this link for more details about System variable (https://docs.microsoft.com/en-us/sql/integration-services/system-variables)























User-Defined variables: Defined by Package Developers













How to create user define variable that shown in below image.












Fig.1









How to set expression for variable that shown in below image.











Fig.2







Connection Manager:







SSIS provides different types of connection managers that enable packages to connect to a variety of data source and servers:






    

  • There are built-in connection managers that setup installs when toy install Integration services.
    • 

  • There are connection managers that are available for download from the Microsoft website.
    • 

  • You can create your own custom connection manager if the existing connection managers do not meet your needs.
    • 






Let's see, step by step how we can add Connection Manager.








    

  1. Solution Explorer > Connection Managers > New Connection Manager.
    2. 




You can see the list of connection managers for different type of connections.













Fig.3














    

  •  Add connection manager.
    • 








Fig.4










    

  • After adding your connection. you can see the all connection here.
    • 








Fig.5











SSIS Toolbox:







Steps: Menu bar > SSIS > Select SSIS Toolbox.







Now,you can see SSIS toolbox on the left side.







SSIS toolbox have list of tasks and containers that you can perform.







List of Containers:




    

  • For each Loop Container:  Runs a control flow repeatedly by using an enumerator.
    • 

  • For Loop Container:  Runs a control flow repeatedly by testing a condition.
    • 

  • Sequence Container: Groups tasks and containers into control flows that are subsets of the package control flow.
    • 




List of Tasks:




    

  • Data Flow Task
    • 

      

    • The tasks runs data flows to extract data, apply column level transformations, and load data.
      • 

    

  • Data Preparation Task
    • 

      

    • These tasks do the following processes: copy file and directories, download files and data, run web methods, apply oprations to XML documents, and profile data for cleansing. 
      • 

    

  • Work Flow Tasks
    • 

      

    • The tasks that communications with other processes to run package, run program or batch file, send and receive messages between packages,send e-mail messages, read Windows Management Instrumentation(WMI) data, and watch for WMI events.
      • 

    

  • SQL Sever Tasks
    • 

      

    • The tasks that access, copy, insert, delete, and modify SQL Server objects and data.
      • 

    

  • Scripting Tasks
    • 

      

    • The tasks that extend package functionality by using scripts.
      • 

    

  • Analysis Services Tasks
    • 

      

    • The tasks crreate, modify, delete and process Analysis Services objects.
      • 

    

  • Maintenance Tasks
    • 

      

    • The tasks that perform administrative functions such as backing up and shrinking SQL Server databases, rebuilding and reorganizing indexes, and running SQL Server Agent jobs.
      • 

    




you can add task/container by drag the task/Container from SSIS toolbox to design area.







Data Flow Task:







Drag the Data Flow task from SSIS Toolbox to design area and double click on it.



you are now in Data flow tab.



now you can see that SSIS Toolbox has different components.







Type:








    

  • Source: From where you want your data.
    • 

  • Destination: It is where you want to move your data.
    • 

  • Transformation: It is operation that perform ETL(Extract, Transform, Load)
    • 




So, that's all guys i hope These will you to understand basic SSIS.


































By



at







No comments:
  



















Labels:
,
,

















          

        

          

        

Friday, October 6, 2017


          

        

























DataTable To Xml Genrate











11:16 PM


c#
,
data table to XML convert

0
 Comments









DataTable To Xml Genrate






















Data Table to XML Genrate



 /// 










      /// Write out XML to a DataTable to a file in a controlled manner.

      /// Use a XmlWriterSettings to control the output formatting.

      /// 

      public static string DataTableToXML(DataTable table)

      {

         XmlWriterSettings settings = new XmlWriterSettings();

         settings.CheckCharacters = true;

         settings.CloseOutput = true;

         settings.ConformanceLevel = ConformanceLevel.Document;

         settings.Encoding = Encoding.UTF8;

         settings.Indent = false;

         settings.NewLineHandling = NewLineHandling.Replace;

         settings.NewLineOnAttributes = true;

         settings.OmitXmlDeclaration = true;



         using (var sw = new StringWriter())

         {

            using (var writer = XmlWriter.Create(sw))

            {

               // Build Xml with xw.

               writer.WriteStartDocument(true);

               WriteTable(writer, table);

               writer.WriteEndDocument();

            }

            return sw.ToString();

         }

      }



      private static void WriteTable(XmlWriter writer, DataTable table)

      {

         //     

         writer.WriteStartElement("DocumentElement");



         foreach (DataRow row in table.Rows)

         {

            // 



            writer.WriteStartElement(table.TableName);



            foreach (DataColumn column in table.Columns)

            {

               writer.WriteStartElement(column.ColumnName);

               writer.WriteValue(row[column].ToString());

               writer.WriteEndElement();

            }

            // 

            writer.WriteEndElement();



         }



         // 

             writer.WriteEndElement();

      }


















By



at







No comments:
  



















Labels:
,
,
,
,

















          

        

          

        

Tuesday, September 26, 2017


          

        

























Generic Code For XML Generate c#











6:27 AM


Generic Code For XML Generate in .net
,
Generic Code For XML Generate in c#

0
 Comments



























XML Helper for convert class object/List to xml and xml to class object/List conversion









Hi friend's, today we are going to understand ubout XMl Helper for convert class object/List to xml and xml to class object/List convertion.









let's start with example,









Create class for EmployeeDetail













public class EmployeeDetails



    {



        [XmlAttribute]



        public int EmployeeID { get; set; }



        [XmlAttribute]



        public string EmployeeName { get; set; }



        [XmlAttribute]



        public string EmailID { get; set; }



        [XmlAttribute]



        public List lstEmployeeDetails { get; set; }



    }



Now, we have one class XMLHandler that contains two methods that make our work easy and clean.




    

  • Method ConvertToXML for Generic code to convert Xml to class object or list.
    • 


  • Method Convert for Generic code to convert class object or list to Xml.
    • 






public static class XMLHandler



    {



        public static T Convert(string inputString)



        {



              XmlSerializer serializer = new XmlSerializer(typeof(T), string.Empty);



MemoryStream memStream = new MemoryStream(Encoding.UTF8.GetBytes(inputString));



              T resultingMessage = (T)(serializer.Deserialize(memStream));



              return resultingMessage;



        }



        public static string ConvertToXML(T obj)



        {



            if (obj == null)



            {



                return string.Empty;



            }



            XmlSerializer serializer = new XmlSerializer(typeof(T));



            StringWriter sww = new StringWriter();



            using (XmlWriter writer = XmlWriter.Create(sww))



            {



                serializer.Serialize(writer, obj);



                return sww.ToString().Replace("", "").Replace(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"", ""); // Your XML



            }



        }



    }













To convert List to xml:



Now we will create instance for EmployeeDetails



EmployeeDetails objEmployeeDetails = new EmployeeDetails();



string xml = XMLHandler>.ConvertToXML(objEmployeeDetails. lstEmployeeDetails);



It will return xml as below Figure,











So, that's all guys i hope these will help you to create xml using object/class.

please share this blog if this helpful you. https://dotnetupdatedtechnology.blogspot.com/




























By



at







No comments:
  



















Labels:
,
,
,
,

















          

        

          

        

Thursday, September 21, 2017


          

        

























Improve SQL Query Performance











3:37 AM


Improve SQL Query Performance
,
Janak Goti

0
 Comments









Improve SQL Query Performance




















Improve SQL Query Performance



Hi friend's, today we are going to understand step to improve t-sql query perfomance step by step mention below.








    

  1. Use EXISTS instead of IN to check existence of data.
    2. 

  2. Avoid * in SELECT statement. Give the name of columns which you require.
    3. 

  3. Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters).
    4. 

  4. Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
    5. 

  5. Avoid NULL in fixed-length field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.
    6. 

  6. Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
    7. 

  7. Create Clustered and Non-Clustered Indexes.
    8. 

  8. Keep clustered index small since the fields used in clustered index may also used in non-clustered index.
    9. 

  9. Most selective columns should be placed leftmost in the key of a non-clustered index.
    10. 

  10. Drop unused Indexes.
    11. 

  11. Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
    12. 

  12. Use joins instead of sub-queries.
    13. 

  13. Use WHERE expressions to limit the size of result tables that are created with joins.
    14. 

  14. Use TABLOCKX while inserting into a table and TABLOCK while merging.
    15. 

  15. Use WITH (NOLOCK) while querying the data from any table.
    16. 

  16. Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
    17. 

  17. Avoid Cursors since cursor are very slow in performance.
    18. 

  18. Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
    19. 

  19. Use UNION ALL in place of UNION if possible.
    20. 

  20. Use Schema name before SQL objects name.
    21. 

  21. Use Stored Procedure for frequently used data and more complex queries.
    22. 

  22. Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
    23. 

  23. Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
    24. 

  24. Avoid use of Non-correlated Scalar Sub Query. Use this query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch.
    25. 

  25. Avoid Multi-statement Table Valued Functions (TVFs). Multi-statement TVFs are more costly than inline TVFs.
    26. 




So, that's all guys i hope these will help you to easy understand this post.






















By



at







No comments:
  



















Labels:
,

















          

        

          

        

Wednesday, September 13, 2017


          

        

























How to Make Visualization Report Using Power BI











4:42 AM


Make using Power BI Visualization Report.
,

1 
 Comments



























How
to make Visualization Report Using Power BI?



How
to use  Power BI Desktop Tool?




    

  • First
we need to Sign up/ Register on Power BI
    • 









Fig 1.0














    

  • For
make any Visualization we need some data for visualize through Power BI
    • 

      

    • To
get data  go through following step
      • 

        

      • Home
Tab --> Get Data
        • 

      • Select
Data source type which we want to add or use data for our visualization
        • 

      

    




As
shown in below screenshot









Fig 2.0



As
shown in above figure there are many types of data sources available.








    

  • In
our example we use SQL Server Database by clicking SQL
Server database from list and Click on Connect.
    • 

  • By
clicking Connect  it prompts a window for Server and database for
import data.
    • 

  • Enter
Server name and Database name and click OK.
    • 

  • After
Clicking OK, there are one more prompt window “Navigator” which
lists the  database objects
    • 

  • As
shown in above Fig 2.0, select  database objects which we want to use our
report, after choosing db objects click on Load.
    • 

  • we
find selected db objects at right side under  "Fields" pane as shown
in Fig 3.0.
    • 








Fig 3.0






    

  • There
are wide range of  visualizations available here.Using these Visualizations  we
can build reports as our need.
    • 




For
an example we are going to make report  of sales by category.







As
shown in below Fig 4.0, Take a Pie chart  from visualizations grid.











Fig 4.0








    

  • Now
just Drag and Drop the Column or Field from Fields section.
    • 

  • We
take "CategoryName"  into Legend  and  "ProductSales" into  Values field.
    • 




There are many other features are also provided there like






    

  • Tooltips
    • 

  • Filters
    • 

  • Details
    • 

  • Page
level filters
    • 

  • Report
level filters
    • 




There
are also Format tab of your report  from using that you can format  your report
as per your need.









Fig 5.0



There
are options to customize your report like




    

  • Legend
– set legend text, color size and position
    • 

  • Data
colors – customize data colors
    • 

  • Details
Labels
    • 

  • Title
    • 

  • Background 
    • 

  • Lock
aspect
    • 

  • General
– set position, width and height of report
    • 

  • Border 
    • 




You
can find the data by clicking Data  Tab  on
left side as shown in figure. Also column formatting, adding also available
here in data tab.







Fig 6.0



You
can also find and manage relationship among db objects or data objects 
under "Relationships Tab"  as shown in  below Fig 7.0.







Fig 7.0



Under Relationships
Tab, we can easily manage relationships among data objects. We
can edit, add or delete relationships  here as shown in above Fig 7.0.






    

  • After
making report  you can publish this report to Power BI
    • 

  • Now,
publish report to web using following steps
    • 

  • File
Menu   -->   Publish  --> Publish to Power BI
    • 

  • After
publishing successfully  go to your PowerBI account
    • 

  • You
can find the report under "Report Section" on left side menu
and Dataset under "Dataset Section".
    • 




Hope,
This will help you.

Thank You.
























By



at







1 comment:
  



















Labels:









































SSMS Formatter use with Microsoft SQL Server Management Studio 2016











2:26 AM


Janak Goti
,
SQL Formatter for SQL Server Management Studio Poor Man’s T-SQL Formatter is an open source plug-in for SQL Server Management Studio (SSMS).

0
 Comments



























SSMS Formatter use with Microsoft SQL Server Management Studio 2016



Hi friend's, today we are going to understand
that how to apply Format for your query in SSMS(Microsoft SQL Server Management
Studio 2016).



You just need to go through some steps that
mentioned below.






    

  1. First
start the SQL Server (SSMS 2016)
    2. 

  2. On
the top of the Menu --> Tools --> Options
    3. 














Fig 1.0









After open the Options pop up on left side shown the menu.



  3. Environment
--> Keyboard --> under the Show Commands containing.

















Fig 2.0









  4. SSMSBoost.Format
--> select Use new shortcut in: --> Text Editor --> Press shortcuts
keys: Ctrl + k, Ctrl + D --> Click On 'Assign' and then Click 'OK'.





  5. All
set to format your Query. Now just write query and apply you Short Cut key.







Hope,
This will help you.Thank
You.
























By



at







No comments:
  



















Labels:
,
,

















        

      




























{
  "@context": "http://schema.org",
  "@type": "Organization",
  "url": "http://c-sharpnets.blogspot.com/",
  "logo": "http://c-sharpnets.blogspot.com/images/logo.png"
}