My Thoughts & Views

Archive for the ‘SQL Server’ Category

Problem :

In SSRS, when you export the report in CSV format, you will not get values. (Export Limitations in SSRS)

Solution :

The data renderers, CSV and XML, only show data and not any layout, means it display non-static data.

Data renderers don’t render static data by Default, they only render expressions.
So either change the table to render real data coming from data base or expressions, or change data output option for table cells (only cells which contain dynamic data, not the headers) from “Auto” to “Yes”.

Or
you can control the export behavior on a textbox-by-textbox basis with the DataElementOutput property.You have 3 options to select, Auto (default), Yes, No.

Right click on Report in Visual Studio solution explorer, select view code & then

add <DataElementOutput>Output</DataElementOutput>

After the name of the text boxes which will be filled by dynamic data.

Hope this helps, Let me know if you find any difficulty.


Problem: You have a table (table1) & values as above, and it contains the duplicate records as shown, how will you delete the duplicates?


Solution: Use following query, the records shown in the below image will be deleted

delete from table1 where empid not in
(select max(empid) from table1 group by empName,empEmail,empCity);

SQL Server has a SQL Profiler tool, which helps Security Administrators to have a complete view of users who have logged in or out of the server.This information can be used for legal purposes to document activity & for technical purposes to track security policy violations.

For more information please go through the documentation provided with your SQL Server.

Here is the sequence generator function written in SQL, which when passed starting and end numbers will generate the rest sequence automatically.

CREATE FUNCTION fn_p(    @exp  int)
RETURNS @result TABLE (id int)
AS
BEGIN
DECLARE @pow int
SET @pow = POWER(10, @exp)
INSERT @result SELECT 0 id UNION ALL SELECT 1 * @pow UNION ALL SELECT 2 * @pow UNION ALL SELECT 3 * @pow UNION ALL SELECT 4 * @pow UNION ALL SELECT 5 * @pow UNION ALL SELECT 6 * @pow UNION ALL SELECT 7 * @pow UNION ALL SELECT 8 * @pow UNION ALL SELECT 9 * @pow RETURNENDGO

CREATE FUNCTION fn_sequence
(
@start int,
@end int
)
RETURNS @result TABLE(id int)
AS
BEGIN
IF @start > @end
RETURN
IF @end < 10
INSERT @result SELECT id FROM ( SELECT a0.id id FROM fn_p(0) a0 ) d WHERE id BETWEEN @start AND @end ELSE
IF @end < 100 INSERT @result SELECT id FROM ( SELECT (a1.id + a0.id) id FROM fn_p(1) a1 CROSS JOIN fn_p(0) a0 ) d WHERE id BETWEEN @start AND @end ELSE
IF @end < 1000 INSERT @result SELECT id FROM ( SELECT (a2.id + a1.id + a0.id) id FROM fn_p(2) a2 CROSS JOIN fn_p(1) a1 CROSS JOIN fn_p(0) a0 ) d WHERE id BETWEEN @start AND @end ELSE
IF @end < 10000 INSERT @result SELECT id FROM ( SELECT (a3.id + a2.id + a1.id + a0.id) id FROM fn_p(3) a3 CROSS JOIN fn_p(2) a2 CROSS JOIN fn_p(1) a1 CROSS JOIN fn_p(0) a0 ) d WHERE id BETWEEN @start AND @end
RETURNENDGO

The following query gives 3rd highest records from SQL.

SELECT TOP(1) salary FROM ( SELECT TOP(3) salary FROM Emp ORDER BY salary DESC)

Keywords: getting nth highest record, nth highest record, TOP

1. Following Query displays all the table names.

Query : select name from sys.objects where type=‘u’ ;

2. Following Query displays all the columns in a table

Query : select Table_Name,Column_Name from INFORMATION_SCHEMA.COLUMNS ;

How To: Re index Entire database

Applies to : SQL Server 2000/ 2005

Recently i moved some database to new server and wanted to reindex entire Sql Server database, as people were complaining about slow response for our .NET WinForms application.

I thought it might save some body’s hours of time who is searching for the solution.
Problem :
You want to increase the performance of your database so you move Sql Server from one machine to another machine which has higher capacity than the older one. But after migration users start complaining that their application response is slow.

Soultion :
This is what i done to solve the above problem. I read somewhere that re indexing and reorganizing will help to improve some performance. So here is how to do it.

System Stored Provedure : sp_MSforeachtable

This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set.

EXEC [sp_MSforeachtable] @command1=”DBCC DBREINDEX(‘?’)”

To see progress messages while it is running, try this version:

EXEC [sp_MSforeachtable] @command1=”RAISERROR(‘DBCC DBREINDEX(”?”) …’,10,1) WITH NOWAIT DBCC DBREINDEX(‘?’)”.

You can Re index the Live Databases as well.Each table will be locked up for the duration of the reindexing. Users will be locked out if they are hitting that table.

If you know any other solution to such problems then please share it here so that it helps people to solve such problems.