Showing posts with label SQLYoga. Show all posts
Showing posts with label SQLYoga. Show all posts

August 14, 2015

SQL Server: Use of Recursive Query with example

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them.
 
Recursive query is the efficient way to display hierarchy setup (Parent child relation ship in the same table). e.g. Grand Parent, Parent, Child. This is common requirement. To display the parent child relationship in efficient manner, we can use Recursive CTE. Let me explain it by example.
Create Temporary Table:
DECLARE @Images TABLE (
 [ResourceID] [int] NOT NULL,
 [ChildOf] [int] NOT NULL,
 [ResourceName] [varchar](250) NOT NULL
 )
Lets populate some data into this table:
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (5, 0, N'Mammal')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (6, 5, N'Carnivore')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (14, 6, N'Cannine')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (15, 14, N'Dog')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (16, 15, N'Dog1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (17, 14, N'Monkey')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (18, 17, N'Monkey1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (19, 16, N'dog1.01')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (21, 18, N'monkey1.02')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (23, 17, N'test')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (25, 17, N'Monkey 2')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (31, 0, N'Fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (32, 31, N'cartilaginous fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (33, 32, N'dogfish')
That is how data is stored in the table:
 
 
This is not the way that we can understand the hierarchy. Recursive query will help us to display result like this:
This is much efficient way to list out, as we understand that how the hierarchy is being maintained. Recursive CTE helps us to generate this kind efficient output. Let me share the t-sql script which generates the output which we are looking for:
;WITH images
AS (
 SELECT *
  ,ROW_NUMBER() OVER (
   ORDER BY ResourceID
   ) AS RowID
 FROM @images
 )
 ,cte
AS (
 SELECT 1 AS [Level]
  ,[ResourceID]
  ,[ResourceName]
  ,ChildOf
  ,cast(cast(RowID AS VARBINARY(4)) AS VARBINARY(max)) AS sort
 FROM images
 WHERE ChildOf = 0
 --where [ResourceID] = 5
 
 UNION ALL
 
 SELECT p.[Level] + 1
  ,c.[ResourceID]
  ,c.[ResourceName]
  ,c.ChildOf
  ,cast(p.sort + cast(c.RowID AS VARBINARY(4)) AS VARBINARY(max))
 FROM images c
 INNER JOIN cte p ON p.[ResourceID] = c.ChildOf
  --WHERE c.ChildOF=5
 )
SELECT ResourceID
 ,ChildOf
 ,REPLICATE('.', ([Level] - 1) * 4) + [ResourceName] AS menu
FROM cte
ORDER BY sort
 
That's it. Here, I have used first CTE, to assign the Unique numbers. Then just iterate thru each record and find their related children records. Hope this helps you.
Reference : Tejas Shah http://www.SQLYoga.com

September 4, 2014

SQL SERVER: SSIS - Rename and move files from source folder to destination folder

With SSIS, most of the time we need to process all the files from the folder. Once file has been processed, we need to move the file to archive folder, so we should know that file has been processed and we have the file in archive folder.

Here, We are going to process the files and then will move the file from source folder to archive folder by appending date and time to the filename, so we can use it for future reference. SSIS will do both of these things, Rename a file and move a file, with File System Task with operation “Rename file”. Let’s review how it works:

1.
Add Variables:To configure Source folder and Archive Folder by variable
Name Scope Data Type Value
FileName [Package Scope] string
SourceFolder [Package Scope] string [Source Folder Path]
(example :- c:\Source_Folder)
SourcePath [Package Scope] string
TargetFolder [Package Scope] string [Target Folder Path]
(example :- c:\Archive_Folder)
ArchivePath [Package Scope] string
 2. Set variable’s properties expression:Go to Variable->properties and set EvaluateAsExpression and Expression, so we will have Source Path and Archive Path.

Here, we are adding Current Date time to the filename by expression as mentioned below.

Variable Name EvaluateAs
Expression
Expression
SourcePath true @[User::SourceFolder]+"\\"+ @[User::FileName]
ArchivePath true @[User::TargetFolder] +"\\"+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),FINDSTRING(REVERSE(@[User::FileName] ),".",1)+1,LEN(@[User::FileName])- FINDSTRING(REVERSE(@[User::FileName] ),".",1)))
+"_"+ Right("0" +(DT_STR,4,1252) datepart("yyyy", getdate()),2) 
+ Right("0" +(DT_STR,2,1252) datepart("mm", getdate()) ,2)
+ Right("0" +(DT_STR,2,1252) datepart("dd", getdate()),2)+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2)
+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2)
+ Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2)+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),1,FINDSTRING(REVERSE(@[User::FileName] ),".",1)))

3. Add For each Loop Container and set properties :Now, lets loop thru the folder and process each file from the folder by “For Each loop”. Here, we can setup the folder by Expressions->Directory use Expression @[User::SourceFolder]. We should also specify which type of files we are going to process, like “txt”, “csv” etc..by specifying the same in “Files”. We are fetching the file name with extension so that option needs to be selected as displayed in the following screenshot.SQLYoga.com 
We need to assign each file name to the variable, by Variable Mappings->set variable [User::FileName] and Index as 0

SQLYoga.com
4. Add File System Task:Add “File System Task” inside “For each Loop Container”SQLYoga.com
5. Set properties for File System Task:This is the place where we need to setup the operation, which will do our job.
A. Configure SourceVariable
B. Configure DestinationVariable
C. Select operation: “Rename File”, which will rename the file and move it to the Archive Folder as we have specified in variable.
SQLYoga.com 
6. Run package and checkSQLYoga.com
With SSIS, it is much simple to process multiple files as mentioned above.
Reference: Tejas Shah (www.SQLYoga.com)

September 1, 2014

SQL SERVER: SSIS - Conditional Split Data Flow Transformation Task

Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups.

1. Create sample text file:
This text file is piped delimited and last row  in text Total Row count

EmployeeNumber|Employeename
242|Lorem ipsum
239|dolor sit
225|amet consectetur
242|adipisicing elit
142|seddo eiusmod
222|tempor incididunt
142|ut labore
143|dolore magna
144|Ut enim
Total Row count|9


2. Create sample table:
Create sample destination table in Test database
  1: CREATE TABLE [dbo].[Employee](
  2:  [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
  3:  [EmployeeNumber] [int] NULL,
  4:  [EmployeeName] [varchar](50) NULL,
  5:  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
  6:  (
  7:   [EmployeeId] ASC
  8:  )
  9: )
 10: GO

3. Add Data Flow Task:
Add data flow task in Package for transfer data source text file “Employee.txt” to sql server table “dbo.Employee”
SQLYoga.com 
4. Add new Flat File Connection:
Right click on Connection Manager and add new Flat File connection
SQLYoga.com 
5. Set Flat file source properties:
Flat file source properties set Connection manager Name, File Name (Source full filepath), column names in the first data row as a true
SQLYoga.com
6. Add Flat file source:
Add “Flat File Source” in “Data flow Task” and set properties Flat file connection manager and retain null values from the source as null values in the data flow as true and click button on preview and see in this file extra row include in text file
SQLYoga.com
7. Add Conditional Split Transformation in Data Flow Task:
Add Conditional Split Transformation in data flow task for split row
SQLYoga.com
8. Set Condition Split Properties:
Set properties Condition split where EmployeeNumber equal to “Total Row count” then this rows not use and other row use to process.
SQLYoga.com
9. Add new OLE DB Connection:
Right click on Connection Managers and add new OLE DB Connection
SQLYoga.com 
10. Set OLE DB Connection properties
OLE DB Connection properties set Server Name, Connection to a database and click on ok button
SQLYoga.com 
11. Add OLE DB Destination:
Add “OLE DB Destination” in “Data flow Task” and set properties OLE DB Connection Manager and Name of the table or the view
SQLYoga.com
12. Set Input output selection :
Set Input output selection between Condition spit and OLE DB Destination
SQLYoga.com
SQLYoga.com
13. Run package and Check :
SQLYoga.com
SQLYoga.com

In this way we get split the information and use as per the requirement,

Reference: Tejas Shah (www.SQLYoga.com)

August 6, 2014

SQL SERVER: Clone SSIS Package

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.

Please find following steps to achieve the same:

1. Existing package:
 
SQLYoga.com

2. Copy Package:
Right click on existing package and click on copy option and after right click on SSIS Packages folder and Paste on the location

SQLYoga.comSQLYoga.com

3. Rename Package Name for newly Pasted file:
To rename package name as your mind and When message box open then click yes button

 SQLYoga.com

SQLYoga.com

4. Open package in Notepad Editor:

Go to folder where package is exist and open with package in notepad editor

SQLYoga.com

5. Replace package Name in Notepad file:

Replace old package name to new package name with Replace option

SQLYoga.com SQLYoga.com

6. Replace more text

Replace more text if you know to Change (Example: “Activity” text replace to “Job”)

SQLYoga.com

7. Check Replace name

Check all task name, SQL task and Data Flow task in replace with new text and Annotation text will no be changed, so it needs to be change manually

SQLYoga.com

8. Need to change manually

If SSIS package has Package SQL server Configuration then we need to change that manually too.

SQLYoga.com

March 28, 2014

T-SQL to find out Space by File Group

T-SQL script to find out SQL SERVER FileGroup space details:
SELECT
 fg.groupname AS 'File Group',
 Name, 
 CONVERT (Decimal(15,2),ROUND(f.Size/128.000,2)) 
  [Currently Allocated Space (MB)],
 CONVERT (Decimal(15,2),
 ROUND(FILEPROPERTY(f.Name,'SpaceUsed')/128.000,2))
  AS [Space Used (MB)],
 CONVERT (Decimal(15,2),
 ROUND((f.Size-FILEPROPERTY(f.Name,'SpaceUsed'))/128.000,2))
  AS [Available Space (MB)]
FROM dbo.sysfiles f (NOLOCK)
JOIN sysfilegroups fg (NOLOCK) ON f.groupid = fg.groupid
ORDER BY fg.groupname

September 4, 2013

SQL SERVER: T-SQL to read all attributes of the XML

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespace in this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.
 
Recently, my team assigned one requirement to import XML file. To read an XML file is achieved by following this POST but here, we have a requirement to read each Attribute name and Attribute Value to store that info in normalized table by comparing Attribute Name. Let me explain with example.
 
XML:


 
Expected Result:
SQLYoga Read All Attributes of the XML with T-SQL #1
 
Query to achieve the same:
DECLARE @XML AS XML

SELECT @XML = ''

SELECT CAST(x.v.query('local-name(.)') AS VARCHAR(100)) AS AttributeName
 ,x.v.value('.', 'VARCHAR(100)') AttributeValue
FROM @XML.nodes('//@*') x(v)
ORDER BY AttributeName
Here, We have used “@*”, which gives us way to read the attributes for the ROOT tag and “local-name” gives is the Attribute name. That’s it.
 
Reference: Tejas Shah ( www.SQLYoga.com )

July 8, 2013

SQL Yoga: Parse XML with namespace with SQL SERVER

Recently, we were working on XML having namespace and we need to read that XML to fetch the information from the XML.

Here, is the Sample XML which we are trying to parse:

DECLARE @x XML
SELECT @x = '
    <X
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">
        <info>Sample XML TEST</info>       
    </X>           
  '

If we need to fetch the “info” element with T-SQL we can write query as follows:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')
    SELECT  x.v.value('info[1]','VARCHAR(50)')
    FROM    @x.nodes('/X') x (v)

Here, we have to use “WITH XMLNAMESPACES”, so we can specify the namespace of the XML that we are reading, else it wont give us any result.

SQLYoa Parse XML
         

May 6, 2013

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server.

To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in
previous post.

Read XML Elements with T-SQL:

DECLARE @SQLYoga TABLE(
    ID INT IDENTITY,
    Data VARCHAR(50),
    CreatedDate DATETIME DEFAULT(GETDATE()),
    CreatedBy INT
    )
INSERT INTO @SQLYoga(Data)
SELECT 'SQLYoga'
UNION ALL
SELECT 'Tejas Shah'
UNION ALL
SELECT 'Generate XML'

DECLARE @xml XML
SELECT @xml = (
        SELECT    *
        FROM    @SQLYoga
        FOR XML PATH('Record'), ROOT('Records')
    )
SELECT @xml

SQLYoga Resultset of XML PATH

Now, please find query to read the query to read XML generated above:

SELECT

        x.v.value('ID[1]', 'INT') AS ID,
        x.v.value('Data[1]', 'VARCHAR(50)') As Data,
        x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate
FROM    @xml.nodes('/Records/Record') x(v)

This query generates the output as follows:

SQLYoga Read XML with T-SQL

That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this.

Reference: Tejas Shah (www.SQLYoga.com)

May 3, 2013

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database.

There are two ways to have an XML from query:
1. FOR XML RAW
2. FOR XML PATH

Please find following sample queries where both of the way are being used:

Lets create sample data

DECLARE @SQLYoga TABLE(
    ID INT IDENTITY,
    Data VARCHAR(50),
    CreatedDate DATETIME DEFAULT(GETDATE()),
    CreatedBy INT
    )
INSERT INTO @SQLYoga(Data)
SELECT 'SQLYoga'
UNION ALL
SELECT 'Tejas Shah'
UNION ALL
SELECT 'Generate XML'

Generate XML with XML ROW

SELECT *
FROM @SQLYoga
FOR XML RAW, ROOT('Records')

SQLYoga Resultset of XML RAW

XML RAW, returns the XML by each record with row element and all columns as attributes in the XML.

Generate XML with XML PATH

SELECT    *
FROM    @SQLYoga
FOR XML PATH('Record'), ROOT('Records')

SQLYoga Resultset of XML PATH

XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record.

That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this.

Reference: Tejas Shah (www.SQLYoga.com)

April 30, 2013

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

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 be up.

Then, to overcome this kind of problem in future, I suggested him to always keep the database backup on another server and it is best to have database back up on another location too. He agreed with me that, and asked me a good question that: "Do you know if there is any kind of software by which I can schedule this and get out of this problem?"

As he needs the solution quickly, I checked it out some Tools and I come with the very cool tool "SQL SERVER Backup And FTP". This is very simple to use. This tool works as:

  1. Connect SQL SERVER
  2. Select Database(s)
  3. Select "Network Path", "FTP connection"
  4. Enter Email Address, so software will notify the user when backup is done

We need to install this software on the server and we can take SQL SERVER Database backup on Network and FTP together.

First please install this software from SQL SERVER Backup And FTP and Let me explain it how it works:

  • Connect SQL SERVER: First connect to SQL SERVER Database, for which we need to take backup. We need to Enter SQL SERVER Name and credentials to connect to SQL SERVER

SQLYoga SQL Server Backup

  • Select Database: Once SQL SERVER is connected, all databases of that SQL server will be listed. We need to select all the databases for which we need to take backup as follows:

SQLYoga SQL Server Backup

Here I have done following:

  • Checked two databases, pubs and SQLYoga.
  • Checked "Network Folder" and select Network path
  • Checked "FTP SERVER" and specify FTP Server, to put backup on FTP Server
  • Checked "Email Notification", so I will be notified about success/failure of moving database backup.
  • Unchecked "Schedule this job", as I just need to run it once. If we need to run it on daily basis, we can specify schedule to run it on that time daily.

That's it and now we are out of stress to move database backup to another server or on FTP

Let's click on "Run now" to check it out.

After process is completed, Two zip files, pubs and SQLYoga is on Network path as well as on FTP.

Another cool feature is that, we can schedule Full Backup, Differential Backup, and Transaction Backup, so we can have very minimal amount of data loss.

SQLYoga SQL Server Backup

Here, I have configured this software as:

  • Take full backup of database at every 24 hours
  • Take differential backup at every 1 hour
  • Take Transaction backup at every 15 minutes

Here, we assume that it is ok that we have 15 minutes data loss. It is depends on your application to decide this time window.

Let's take one case study to get more understanding about to restore these backups:

e.g. Database server is crashed at 8:20 AM, How can I restore my database from the backups?

  1. Restore full backup database which was taken at mid night 3 AM.
  2. As we have configured to have differential back at every hour, we also have Differential backup of 8:00 AM, so restore differential database backup.
  3. We have also configured transaction backup for every 15 minutes, so we have one transaction log backup which is taken on 8:15 AM, so let's restore it. So, now we have data up to 8:15 AM
  4. As we have assume that Organization is good if we loss 15 minutes of data. Here in this case, as we have Transaction backup of 8:15 AM,  we are losing transactions which are made between 8:15 AM - 08:20 AM. So, we should proceed with 08:15 AM transaction backup and make database available

Another, option is also available, to remove old Differential backup and Transaction backup. Once new differential backup is generated, there is no need to keep old differential backup. We only need to restore recent differential backup, so we can remove old differential backups. We can also remove all transaction log backup which are generated before Differential Log. SO to remove old backups, this software is also providing an option for the same, we just need to checked this option and that's it.

This software is also allowed following settings (advanced):

SQLYoga SQL Server Backup

  • You can setup custom "temp" folder, by default its using "C:\Windows\Temp"
  • Compression Settings (Zip)
  • Encryption

I like this tool, I recommend this tool to take back up and move it on FTP. Try to use it and let me know your comments

November 5, 2012

SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.
 
PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:
 
ctrl_no id Amount OrderDate
1000029 100016 990 10/25/2012
1000029 100018 990 10/25/2012
1000029 100016 660 10/26/2012
1000029 100018 660 10/26/2012
1000029 206007 660 10/26/2012
1000029 206007 660 10/27/2012
This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:
id 10/25/2012 10/26/2012 10/27/2012
100016 990.00 660.00 NULL
100018 990.00 660.00 NULL
206007 NULL 660.00 660.00
Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.
CREATE TABLE #TestTable (
	ctrl_no INT
	,id INT
	,Amount NUMERIC(18, 2)
	,OrderDate DATE
	)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT id
	,[10/25/2012]
	,[10/26/2012]
	,[10/27/2012]
FROM (
	SELECT id
		,Amount
		,OrderDate
	FROM #TestTable t
	) x
PIVOT(SUM(Amount) FOR OrderDate IN (
			[10/25/2012]
			,[10/26/2012]
			,[10/27/2012]
			)) p

DROP TABLE #TestTable
 
Let me know if you have any question/comment.
Reference: Tejas Shah (www.SQLYoga.com)

October 10, 2012

SQL SERVER: T-SQL to display Job history

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed.

We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query:

SELECT
    j.name As JobName,
    run_date As RunDate,
    RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,
    CASE h.run_Status   
        WHEN  0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN  'Retry'
        WHEN 3 THEN 'Cancelled'
    ELSE ''
    END AS JobStatus,
    RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j on h.job_id = j.job_id
    AND h.step_id = 0
ORDER BY j.Name, h.run_date

Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows:

SQL Yoga T-SQL to display job history


We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments

Reference: Tejas Shah (
http://www.SQLYoga.com)

September 29, 2012

SQL SERVER: SSMS, Allow to create Stored procedure though table doesn’t exists

We are working on to synchronize our new SQL instance, with existing database and need to make sure all SQL OBJECTS are created on the SQL server instance, so we can execute the same stored procedures from the new SQL instance.

Our assignment is to create same replica of existing database to new SQL instance. We were also pretty much sure because all our Stored Procedures are also created on the new instance. As soon as we try to execute the stored procedure, we are getting, very simple error like:

Invalid object name 'XXXXXX'.

When we debug that, we come to know that TABLES are not created on the new SQL instance because of one error. Our main question here is: How come SQL SERVER allows to create Stored procedures, if table doesn’t exists?

After review it, we come to know that SSMS 2012 has an option, that we can configure this restriction to allow or not to allow, Check for object existence

Check for object existence I SQLYoga Tejas Shah
By default, "Check for object existence” is configures as FALSE, so SQL Server allows user to create stored procedure without checking dependent object is exists or not. Lets see one simple demo:

--Checking, if table exists or not. If it is exists, delete the table
if object_id('SQLYoga') IS NOT NULL
    DROP TABLE SQLYoga

--Creating a Stored procdedure which using the object "SQLYoga" which we have just deleted
CREATE PROC StoredProcedureWithoutObject
As
BEGIN
    SELECT * FROM SQLYoga
END

This is very simple example that first we have checked that, if table called “SQLYoga” exists in the database then drop that table and create a stored procedure which uses this table. As Check for object existence configuration is setup as “to allow”, we are able to create the Stored procedure though table is not exists on the table. Stored procedure won’t be executed and will give an error at the time of execution, as table doesn’t exists. To enable this configuration, go to SSMS->Tools->Options->SQL Server Object Explorer and setup “Check for object existence” to TRUE.

Please let me know your comments

September 13, 2011

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

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.


Answer of his question is, YES. We can have reports without using SQL SERVER Reporting Service with same layout.


Microsoft Visual Studio is providing file called "RDLC" file, by which we can develop report in Microsoft Visual Studio and that Report doesn't need to deploy on Report server and will be called from Asp.Net application itself. By RDLC, it is very simple to load report in browser. We are out of issue to deploy reports on the Report Server and execute it from there. RDLC file remains there with project files.


In the Next post, I will explain in details to configure RDLC report with ASP.Net

June 15, 2011

Community TechDays at Ahmedabad, 2011

 
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

Harish Vaidyanathan [twitter]

Topic: HTML 5
Download Presentation and Demo Files

 
 jacob

Jacob Sebastian [twitter | blog]

Topic: SQL SERVER Worst practices

Download Presentation and Demo Files

 
pinal

Pinalkumar Dave [twitter | blog]

Topic: SQL SERVER Performance troubleshooting using Waits and Queues

 
tejasshah

Tejas Shah [twitter | blog]

Topic: Asp.Net Tips and Tricks

Download Presentation and Demo Files

 

We will upload this session photos soon