Ms Sql Server Technology Discussions

[cool_dude]

[quote name='chelsea' timestamp='1355255983' post='1302933338']
Connection string correct ichavu chudu ba Mostly BIDS lo run aithe ikkada koda run avvali nuvu shared datasource use chesava
[/quote]

Ledhu bhaiyya..Shared use cheyyaledhu. Naku datasources folder lo access ledhu in Report Manager.Nenu datasources issues ani anukuntunna..But ala ayina kuda kanisam okka sari kuda un kavaddhu kadha..But prob here is its running after couple of refreshments.

[deals2buy]

[b] Departures from Origins and Arrivals at Destinations[/b]


http://www.sqlservercentral.com/articles/T-SQL/95033/

example with SQL Server

[deals2buy]

SQL Server Backup Questions We Were Too Shy to Ask

http://www.simple-talk.com/sql/database-administration/sql-server-backup-questions-we-were-too-shy-to-ask/

One more post

[deals2buy]

[b] [url="http://www.sqlservercentral.com/blogs/sql_awesomesauce/"]SQL Awesomesauce[/url][/b]

[deals2buy]

[b] Reporting Services Disaster Recovery[/b]


http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69699/

[lolliman]

[quote name='deals2buy' timestamp='1355323905' post='1302937798']
[b] Departures from Origins and Arrivals at Destinations[/b]


[url="http://www.sqlservercentral.com/articles/T-SQL/95033/"]http://www.sqlserver...es/T-SQL/95033/[/url]

example with SQL Server
[/quote]
[quote name='deals2buy' timestamp='1355323966' post='1302937807']
SQL Server Backup Questions We Were Too Shy to Ask

[url="http://www.simple-talk.com/sql/database-administration/sql-server-backup-questions-we-were-too-shy-to-ask/"]http://www.simple-ta...too-shy-to-ask/[/url]

One more post
[/quote]
[quote name='deals2buy' timestamp='1355324084' post='1302937820']
[b] [url="http://www.sqlservercentral.com/blogs/sql_awesomesauce/"]SQL Awesomesauce[/url][/b]
[/quote]
[quote name='deals2buy' timestamp='1355324094' post='1302937822']
[b] Reporting Services Disaster Recovery[/b]


[url="http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69699/"]http://www.sqlserver...s (SSRS)/69699/[/url]
[/quote]

Gp baaa... ivanni choostunna kaani... chadavadaaniki time dorakatlaaa

[deals2buy]

[quote name='lolliman' timestamp='1355324777' post='1302937913']
Gp baaa... ivanni choostunna kaani... chadavadaaniki time dorakatlaaa
[/quote]
online lo ethukkoni chadavatam elago cheyyam...so oka daggara padesi unchaam anuko eppudo okappudu aina chaduvochu

[Desamudhuru]

where condition lo ninnati date petadam ela bhayya ?? Roju night report run avuthadi.

[mtkr]

[quote name='Desamudhuru' timestamp='1355350589' post='1302941323']
where condition lo ninnati date petadam ela bhayya ?? Roju night report run avuthadi.
[/quote]


select convert(varchar,getdate()-1,101)

[deals2buy]

[quote name='Desamudhuru' timestamp='1355350589' post='1302941323']
where condition lo ninnati date petadam ela bhayya ?? Roju night report run avuthadi.
[/quote]
SELECT * FROM Table
WHERE Date = Convert(Varchar(10), GetDate()-1, 101)

Nee Data format ni batti aa number 101 chage avutundi

[deals2buy]

[b] Select and Delete Duplicate Records[/b]


Developers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.

In this video we are demonstrating two scripts – 1) selects duplicate records 2) deletes duplicate records.

We are assuming that the table has a unique incremental id. Additionally, we are assuming that in the case of the duplicate records we would like to keep the latest record. If there is really a business need to keep unique records, one should consider to create a unique index on the column. Unique index will prevent users entering duplicate data into the table from the beginning. This should be the best solution. However, deleting duplicate data is also a very valid request. If user realizes that they need to keep only unique records in the column and if they are willing to create unique constraint, the very first requirement of creating a unique constraint is to delete the duplicate records.
Let us see how to connect the values in Sixty Seconds:

[media=]http://www.youtube.com/watch?feature=player_embedded&v=ioDJ0xVOHDY[/media]


« [url="http://blog.sqlauthority.com/2012/12/18/sql-server-select-the-most-optimal-backup-methods-for-server/"]SQL SERVER – Select the Most Optimal Backup Methods for Server[/url]

[b] SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video[/b]

December 19, 2012 by [url="http://blog.sqlauthority.com/author/pinaldave/"]pinaldave[/url]
[img]http://www.pinaldave.com/bimg/60x60.jpg[/img]Developers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.
In this video we are demonstrating two scripts – 1) selects duplicate records 2) deletes duplicate records.
We are assuming that the table has a unique incremental id. Additionally, we are assuming that in the case of the duplicate records we would like to keep the latest record. If there is really a business need to keep unique records, one should consider to create a unique index on the column. Unique index will prevent users entering duplicate data into the table from the beginning. This should be the best solution. However, deleting duplicate data is also a very valid request. If user realizes that they need to keep only unique records in the column and if they are willing to create unique constraint, the very first requirement of creating a unique constraint is to delete the duplicate records.
Let us see how to connect the values in Sixty Seconds:

Here is the script which is used in the video.

[CODE]
USE tempdb
GO
CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
GO
INSERT INTO TestTable (ID, NameCol)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Second'
UNION ALL
SELECT 4, 'Second'
UNION ALL
SELECT 5, 'Second'
UNION ALL
SELECT 6, 'Third'
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
-- Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
FROM TestTable
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO
-- Deleting Duplicate
DELETE
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
DROP TABLE TestTable
GO
[/CODE]

[deals2buy]

[b] Select the Most Optimal Backup Methods for Server[/b]


Backup and Restore are very interesting concepts and one should be very much with the concept if you are dealing with production database. One never knows when a natural disaster or user error will surface and the first thing everybody wants is to get back on point in time when things were all fine. Well, in this article I have attempted to answer a few of the common questions related to Backup methodology.
[b] How to Select a SQL Server Backup Type[/b]

In order to select a proper SQL Server backup type, a SQL Server administrator needs to understand the difference between the major backup types clearly. Since a picture is worth a thousand words, let me offer it to you below.
[img]http://www.pinaldave.com/bimg/ftpbackup1%20%281%29.png[/img]
[b] Select a Recovery Model First[/b]

The very first question that you should ask yourself is: Can I afford to lose at least a little (15 min, 1 hour, 1 day) worth of data? Resist the temptation to save it all as it comes with the overhead – majority of businesses outside finances can actually afford to lose a bit of data.
If your answer is YES, I can afford to lose some data – select a SIMPLE (default) recovery model in the properties of your database, otherwise you need to select a FULL recovery model.
The additional advantage of the Full recovery model is that it allows you to restore the data to a specific point in time vs to only last backup time in the Simple recovery model, but it exceeds the scope of this article
[b] Backups in SIMPLE Recovery Model[/b]

In SIMPLE recovery model you can select to do just Full backups or Full + Differential.
[b] Full Backup[/b]

This is the simplest type of backup that contains all information needed to restore the database and should be your first choice. It is often sufficient for small databases, but note that it makes a big impact on the performance of your database
[b] Full + Differential Backup[/b]

After Full, Differential backup picks up all of the changes since the last Full backup. This means if you made Full, Diff, Diff backup – the last Diff backup contains all of the changes and you don’t need the previous Differential backup. Differential backup is obviously smaller and carries less performance overhead
[b] Backups in FULL Recovery Model[/b]

In FULL recovery model you can select Full + Transaction Log or Full + Differential + Transaction Log backup. You have to create Transaction Log backup, because at that time the log is being truncated. Otherwise your Transaction Log will grow uncontrollably.
[b] Full + Transaction Log Backup[/b]

You would always need to perform a Full backup first. Then a series of Transaction log backup. Note that (in contrast to Differential) you need ALL transactions to log since the last Full of Diff backup to properly restore. Transaction log backups have the smallest performance overhead and can be performed often.
[b] Full + Differential + Transaction Log Backup[/b]

If you want to ease the performance overhead on your server, you can replace some of the Full backup in the previous scenario with Differential. You restore scenario would start from Full, then the Last Differential, then all of the remaining transactions log backups
[b] Typical backup Scenarios[/b]

You may say “Well, it is all nice – give me the examples now”. As you may[b] [url="http://blog.sqlauthority.com/2012/11/26/sql-server-sends-backups-to-a-network-folder-ftp-server-dropbox-google-drive-or-amazon-s3/"]already know[/url][/b], my [b][url="http://sqlbackupandftp.com/?ref=242"]favorite SQL backup software[/url][/b] is [b][url="http://sqlbackupandftp.com/?ref=242"]SQLBackupAndFTP[/url][/b]. If you go to Advanced Backup Schedule form in this program and click “Load a typical backup plan…” link, it will give you these scenarios that I think are quite common – see the image below.
[img]http://www.pinaldave.com/bimg/ftpbackup1%20%282%29.png[/img]
[b] The Simplest Way to Schedule SQL Backups[/b]

I hate to repeat myself, but backup scheduling in SQL agent leaves a lot to be desired. I do not know the simple way to schedule your SQL server backups than in[b] [url="http://sqlbackupandftp.com/?ref=242"]SQLBackupAndFTP[/url][/b] – see the image below. The whole backup scheduling with compression, encryption and upload to a Network Folder / HDD / NAS Drive / FTP / Dropbox / Google Drive / Amazon S3 takes just a few minutes – see my previous post for the [b][url="http://blog.sqlauthority.com/2012/11/26/sql-server-sends-backups-to-a-network-folder-ftp-server-dropbox-google-drive-or-amazon-s3/"]review[/url][/b].
[img]http://www.pinaldave.com/bimg/ftpbackup1%20%283%29.png[/img]
[b] Final Words[/b]

This post offered an explanation for major backup types only. For more complicated scenarios or to research other options as usually go to [url="http://msdn.microsoft.com/en-us/library/ms187048.aspx"]MSDN[/url].

[deals2buy]

[b] Checking SQL Server Services Owner[/b]


[b] T-SQL Script to Check the SQL Server Services Owner[/b]


Below is a script that calls xp_cmdshell and the sc.exe application to check the status for the SQL Server services:

[CODE]SET NOCOUNT ON

-- Temporary Tables
CREATE TABLE #tmpServices
(oOutput VARCHAR(1024))

CREATE TABLE #tmpServicesDetail
(oOutput VARCHAR(1024))

CREATE TABLE #tmpServicesFinal
(ServiceName VARCHAR(100),
ServiceOwner VARCHAR(100),
ServiceStartTp VARCHAR(100),
ServiceBinary VARCHAR(150))

-- sc query is used to query the entire service control manager and then filters
-- by anything with "SQL" in it's name. /I option ignores Case.
INSERT INTO #tmpServices EXEC xp_cmdshell 'sc query |find /I "sql"|find /I "service_name"'

-- Remove NULL records
DELETE FROM #tmpServices WHERE oOutput IS NULL

-- Cursor variables
DECLARE @curServNm VARCHAR(100)
DECLARE @cCMD VARCHAR(100)
DECLARE @cBinary VARCHAR(150)
DECLARE @cOwner VARCHAR(100)
DECLARE @cStartTp VARCHAR(100)

DECLARE cCursor CURSOR FOR
SELECT RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) )) AS ServiceName
FROM #tmpServices

OPEN cCursor
FETCH NEXT FROM cCursor INTO @curServNm

WHILE @@FETCH_STATUS = 0

BEGIN

-- You can use different Options to query SC. For Example, use sc queryex to pull PID
SET @cCMD = 'sc qc "#SERVICENAME#"'
SET @cCMD = REPLACE(@cCMD, '#SERVICENAME#', @curServNm)

INSERT INTO #tmpServicesDetail EXEC xp_cmdshell @cCMD

DELETE FROM #tmpServicesDetail WHERE oOutput IS NULL

-- To extract any other piece of data, you should modify/add variable:
-- For Example: If I use sc queryex to get PID, then I would make the following changes:
-- Then You can Insert it into Temp Table
-- SELECT @cPID = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
-- FROM #tmpServicesDetail
-- WHERE PATINDEX('%PID%', oOutPut) > 0

SELECT @cBinary = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
FROM #tmpServicesDetail
WHERE PATINDEX('%BINARY_PATH_NAME%', oOutPut) > 0

SELECT @cOwner = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
FROM #tmpServicesDetail
WHERE PATINDEX('%SERVICE_START_NAME%:%', oOutPut) > 0

SELECT @cStartTp = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
FROM #tmpServicesDetail
WHERE PATINDEX('%START_TYPE%:%', oOutPut) > 0

INSERT INTO #tmpServicesFinal (
ServiceName,
ServiceOwner,
ServiceStartTp,
ServiceBinary)
VALUES(
@curServNm,
@cOwner,
@cStartTp,
@cBinary)

FETCH NEXT FROM cCursor INTO @curServNm
END

CLOSE cCursor
DEALLOCATE cCursor

-- Final result set
SELECT * FROM #tmpServicesFinal

-- Clean-up objects
IF OBJECT_ID('TempDB.dbo.#tmpServices') IS NOT NULL
DROP TABLE #tmpServices

IF OBJECT_ID('TempDB.dbo.#tmpServicesDetail') IS NOT NULL
DROP TABLE #tmpServicesDetail

IF OBJECT_ID('TempDB.dbo.#tmpServicesFinal') IS NOT NULL
DROP TABLE #tmpServicesFinal[/CODE]

Here are some sample results from my test environment:

[img]http://www.mssqltips.com/tipImages2/2823_SCResults.jpg[/img]

[ghazni]

Backup chesinapudu default ga ekadiko velipotadi kada...... malli aa path nundi ela restore cheyadam...... chala rojulu ayindi oka sari backup chesanu... aapath andariki accessible undadu kada..........

[ghazni]

enta mandi sp_who2 use chestaru........ sp_who2 active use cheyandi so that DB mida enta access chestunaro telustundi.....