SQL Yoga

SQL SERVER: Develop Reports with RDLC (Asp.Net)

2011-11-21T13:45:00.001+05:30

As, I mentioned in my post, Load Reports without SQL SERVER Reporting Service with Asp.Net, we can develop a report without SQL SERVER Reporting Service. That is very simple to do with Microsoft Visual Studio. Please follow the following steps to generate simple report which can be load in browser without SQL SERVER Reporting Service.We need to follow following steps to generate simple report

SQL SERVER: Backup the database and upload it on FTP and on Network

2011-09-21T12:21:00.000+05:30

Recently one of my friend called me and asked me that his MS SQL SERVER Database server is crashed and the problem is, he has the database backups on the same server. So now as the server is crashed, and he cannot have any backup to restore the database. I suggested him, first get data recovery tool to get the data from crashed server and then restore it to make it work, so application will

SQL SERVER: Load Reports without SQL SERVER Reporting Service with Asp.Net

2011-09-13T14:48:00.000+05:30

Recently, we have weekly meetings with my team and one of developer is come up with question that, "Can we use Reports without having SQL SERVER Report Services?". That is very good question that came up by him, as he says client does not want to have SQL SERVER Reporting Services. Client SQL SERVER is hosted at Shared Hosting and does not want to extra pay to use SQL SERVER Reporting Service.

Community TechDays at Ahmedabad, 2011

2011-06-15T14:42:00.000+05:30

We had an excellent Community TechDays on 11th June 2011 at Ahmedabad. We have huge crowd to attend the sessions. Please find presentations and demos which speakers have presented during the session. Harish Vaidyanathan [twitter] Topic: HTML 5Download Presentation and Demo Files Jacob Sebastian [twitter | blog] Topic: SQL SERVER Worst practices Download Presentation and Demo

Microsoft Community TechDays at Ahmedabad

2011-06-04T18:43:00.002+05:30

Great News, Community TechDays event is back on 11th June, 2011 at Ahmedabad. This time I got a chance to have a session in this Community TechDays. There are attractive sessions as follows: 1. HTML5 - Future of the Web, Harish Vaidyanathan, Evangelist Lead, Microsoft2. SQL Server Performance Tuning, Pinalkumar Dave, Evangelist, Microsoft3. T-SQL Worst Practices, Jacob Sebastian, Microsoft MVP4.

SSRS: Configure User Authentication with ReportCredentials

2011-04-25T13:50:00.003+05:30

Many times I found that the Reporting service is configured to access as "Anonymous Access", so any user having URL can access reports from external world. Ideally it should be access by only users/application. For that SQL SERVER Reporting service should not be accessed by "Anonymous Access". It also prevents Report server from unauthorized access. NOTE: In shared hosting, Hosting provider

SSRS: Introduction to SQL SERVER Reporting Services

2011-04-19T17:55:00.001+05:30

Report is a better presentation of data. From report user/Management can easily analyze the information. Report is easy to explain such information like Sales graph, profitability etc.. SQL SERVER provides rich kind of report that can be easily generated from SQL SERVER and send it to user/management in different formats like Excel, word, HTML etc..SSRS (SQL SERVER Reporting Services) also

SQL SERVER: Display Row count of all the tables

2010-08-23T11:38:00.014+05:30

Recently we have performance issue in SQL SERVER and we decided to move some large tables to different file group, so we can improve SQL SERVER performance, as it uses other disks I/O. Now challenge is to identify large tables from many tables (approx 700 tables).There are few ways to display row count of all the tables:Use Procedure: sp_msForEachTable (Simplest way)Read information from system

SQL SERVER Session at Dotnetchaps - Developer Conference 2010

2010-08-08T16:00:00.008+05:30

Dotnetchaps is a group of people focused on Microsoft technologies .Net and SQL Server. The User Group organizes technical sessions for sharing knowledge on .Net and SQL Server. The motto of this group is to Learn, Share and Grow. DotNetChaps user group is also associated with www.sqlpass.org community and officially opened the SqlPass chapter at Anand. The chapter name is Anand India Sql

SQL SERVER: Find executing queries

2010-08-01T21:56:00.004+05:30

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.SELECT OBJECT_NAME(ObjectID) as ObjectName, st.Text, DB_NAME(database_ID) as dbname, Blocking_session_ID as BlockingSessionID, *FROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text

My First article on SQL SERVER Central about Access variables values from Trigger

2010-06-09T04:51:00.002+05:30

I am glad to inform you all that, my article on "Accessing variable from Trigger", which I wrote a few days back, have been published by "SQL SERVER CENTRAL" site.I would recommend all SQL lovers, to subscribe to the news letter of SQL SERVER CENTRAL, which consists of tips and tricks, useful in real world applications.Please feel free to contact me at tejasnshah.it@gmail.com for any MS SQL

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

2010-04-06T07:00:00.000+05:30

As I explained earlier about For Each Loop Container, which process each file from selected folder.There is a requirement to save File name along with record, so later on we can identify which record comes from which file.Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.1. Setup Flat File Connection with CSV file, as mentioned Basic

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

2010-03-07T21:04:00.001+05:30

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted. SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the

SQL SERVER: SSIS - Derived Column Data Flow Transformation

2010-01-10T10:55:00.000+05:30

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue.Let me share that problem with all readers.With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ?This is very practical problem that we need to fix. To solve this, I come

SQL SERVER: Presentation at Ahmedabad User Group Meeting

2009-12-20T17:56:00.000+05:30

Last Saturday, 19th December 2009, I presented session on "Write CROSS TAB Query with PIVOT".This was a regular User Group meeting held every third Saturday of month.This user group is running by Jacob Sebastian, President of PASS regional committee for the Asia and Middle East region.Last Saturday, I get a chance to present a session with Jacob Sebastian (SQL SERVER MVP) and Pinalkumar Dave

SQL SERVER: SSIS - Transfer Jobs Task

2009-12-07T10:00:00.000+05:30

The Transfer Jobs task can be configured to transfer all jobs, or only specified jobs. You can also indicate whether the transferred jobs are enabled at the destination. The jobs to be transferred may already exist on the destination. The Transfer Jobs task can be configured to handle existing jobs in the following ways: Overwrite existing jobs. Fail the task when duplicate jobs exist. Skip

SQL SERVER: How to Read Excel file by TSQL

2009-12-03T10:00:00.000+05:30

Many times developers asked that, they want to import data from Excel file.We can do this by many ways with SQL SERVER.1. We can use SSIS package2. Import/Export Wizard3. T-SQLToday, I am going to explain, How to import data from Excel file by TSQL. To import Excel file by TSQL, we need to do following: 1. Put Excel file on server, means we need to put files on server, if we are accessing it

SQL SERVER: How to remove cursor

2009-12-02T10:00:00.000+05:30

Many times developer ask me that How can they remove Cursor?They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same.Please find this code to remove cursor with Table variable:--declare table to keep records to be processedDECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))--

SQL SERVER SSIS: How to assign Connection from variable

2009-11-28T05:04:00.000+05:30

Last Article, SSIS - Foreach Loop Container, We need to assign dynamic connection to file connection, so SSIS For each loop Task can take each file from folder. Lets configure File connection from variable for SSIS - Foreach Loop Container. What we need to do is, we need to process each file from folder, so we need to assign value from variable to File connection, so SSIS Task will read that

SQL SERVER: SSIS - Foreach Loop Container

2009-11-09T08:53:00.002+05:30

Today, I am going to explain SSIS For Each Loop Task. I am sure that it will be easy to configure SSIS Package with Foreach Loop task, after you read this article. The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The

SQL SERVER SSIS - For Loop Container

2009-11-02T01:15:00.000+05:30

Today, I am going to explain SQL SERVER SSIS, For Loop Container. The For Loop container defines a repeating control flow in a package. The loop implementation is the same concept of the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False. The For Loop container

SQL SERVER: SSIS - ActiveX Task

2009-10-31T00:01:00.000+05:30

Today I am going to explain SSIS - ActiveX Task.Here I am just explain that how to write VB Script in this task, which will execute at run time. This ActiveX Task is mostly used when we move SQL 2000 DTS package to SSIS. As I found that many developers had used ActiveX with DTS packages in SQL SERVER 2000.The ActiveX Script task provides a way to continue to use custom code that was developed

SQL SERVER SSIS: BULK INSERT Task

2009-10-29T00:17:00.000+05:30

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK.The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/

SQL SERVER SSIS: Basic Example Of Data Flow Task

2009-10-24T00:00:00.000+05:30

Today I am going to give Basic example of Data Flow Task in SSIS, import CSV file to SQL SERVER DatabaseMany times user asked to import their data from their files. They might have data in Microsoft Access, Microsoft Excel, CSV File, Text Tile etc..Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That

SQL SERVER: What is SSIS?

2009-10-22T00:00:00.000+05:30

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server SSIS (SQL Server Integration Services) is introduced in SQL SERVER 2005. It is totally new transformation of data. Previously we have DTS package in SQL SERVER 2000. Now they introduced SSIS with many features. SSIS is an extensive data