Ms Sql Server Technology Discussions

[deals2buy]

[b] Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies[/b]


[i][b]How do I find all the tables used in a particular stored procedure?[/b][/i]
[i][b]How do I know which stored procedures are using a particular table?[/b][/i]

Here is the sample stored procedure.
[color=blue]CREATE PROCEDURE [/color][color=black]mySP[/color]
[color=blue]AS
SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]Sales.Customer
GO[/color]
Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.
Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

[img]http://www.pinaldave.com/bimg/references.jpg[/img]
Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.
[b] Find Referecing Objects of a particular object[/b]

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).
[color=blue]USE [/color][color=black]AdventureWorks
GO[/color]
[color=blue]SELECT[/color]
[color=black]referencing_schema_name [/color][color=blue]= [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.SCHEMA_ID[/color][color=gray]),[/color]
[color=black]referencing_object_name [/color][color=blue]= [/color][color=black]o.name[/color][color=gray],[/color]
[color=black]referencing_object_type_desc [/color][color=blue]= [/color][color=black]o.type_desc[/color][color=gray],[/color]
[color=black]referenced_schema_name[/color][color=gray],[/color]
[color=black]referenced_object_name [/color][color=blue]= [/color][color=black]referenced_entity_name[/color][color=gray],[/color]
[color=black]referenced_object_type_desc [/color][color=blue]= [/color][color=black]o1.type_desc[/color][color=gray],[/color]
[color=black]referenced_server_name[/color][color=gray], [/color][color=black]referenced_database_name[/color]
[color=green]--,sed.* -- Uncomment for all the columns[/color]
[color=blue]FROM[/color]
[color=black]sys.sql_expression_dependencies sed[/color]
[color=blue]INNER JOIN[/color]
[color=black]sys.objects o [/color][color=blue]ON [/color][color=black]sed.referencing_id [/color][color=blue]= [/color][color=black]o.[object_id][/color]
[color=magenta]LEFT [/color][color=gray]OUTER [/color][color=blue]JOIN[/color]
[color=black]sys.objects o1 [/color][color=blue]ON [/color][color=black]sed.referenced_id [/color][color=blue]= [/color][color=black]o1.[object_id][/color]
[color=blue]WHERE[/color]
[color=black]referenced_entity_name [/color][color=blue]= [/color][color=red]'Customer'[/color]
[url="http://www.pinaldave.com/bimg/references1.jpg"][img]http://www.pinaldave.com/bimg/references1.jpg[/img][/url]
The above query will return all the objects which are referencing the table Customer.
[b] Find Referenced Objects of a particular object[/b]

Here we are finding all the objects which are used in the view table vIndividualCustomer.
[color=blue]USE [/color][color=black]AdventureWorks
GO[/color]
[color=blue]SELECT[/color]
[color=black]referencing_schema_name [/color][color=blue]= [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.SCHEMA_ID[/color][color=gray]),[/color]
[color=black]referencing_object_name [/color][color=blue]= [/color][color=black]o.name[/color][color=gray],[/color]
[color=black]referencing_object_type_desc [/color][color=blue]= [/color][color=black]o.type_desc[/color][color=gray],[/color]
[color=black]referenced_schema_name[/color][color=gray],[/color]
[color=black]referenced_object_name [/color][color=blue]= [/color][color=black]referenced_entity_name[/color][color=gray],[/color]
[color=black]referenced_object_type_desc [/color][color=blue]= [/color][color=black]o1.type_desc[/color][color=gray],[/color]
[color=black]referenced_server_name[/color][color=gray], [/color][color=black]referenced_database_name[/color]
[color=green]--,sed.* -- Uncomment for all the columns[/color]
[color=blue]FROM[/color]
[color=black]sys.sql_expression_dependencies sed[/color]
[color=blue]INNER JOIN[/color]
[color=black]sys.objects o [/color][color=blue]ON [/color][color=black]sed.referencing_id [/color][color=blue]= [/color][color=black]o.[object_id][/color]
[color=magenta]LEFT [/color][color=gray]OUTER [/color][color=blue]JOIN[/color]
[color=black]sys.objects o1 [/color][color=blue]ON [/color][color=black]sed.referenced_id [/color][color=blue]= [/color][color=black]o1.[object_id][/color]
[color=blue]WHERE[/color]
[color=black]o.name [/color][color=blue]= [/color][color=red]'vIndividualCustomer'[/color]
[url="http://www.pinaldave.com/bimg/references2.jpg"][img]http://www.pinaldave.com/bimg/references1.jpg[/img][/url]
The above query will return all the objects which are referencing the table Customer.

[deals2buy]

[b] Microsoft OLE DB Provider for SQL Server error ’80040e07′ or Microsoft SQL Native Client error ’80040e07′[/b]


[color=#ff0000]Microsoft OLE DB Provider for SQL Server error ’80040e07′[/color]
[color=#ff0000]Syntax error converting datetime from character string.[/color]

[b]OR[/b]

[color=#ff0000]Microsoft SQL Native Client error ’80040e07′[/color]
[color=#ff0000]Syntax error converting datetime from character string.[/color]

If you have ever faced above error – I have a very simple solution for you.

The solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.
INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-[color=#ff0000]Septeber[/color]-2013')
The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.
SELECT CONVERT (datetime, '01-[color=#ff0000]Septeber[/color]-2013', 112)
The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.

[color=#ff0000]Msg 241, Level 16, State 1, Line 2[/color]
[color=#ff0000]Conversion failed when converting date and/or time from character string.[/color]

Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.

[Desamudhuru]

[quote name='deals2buy' timestamp='1354418035' post='1302883926']
[b] Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies[/b]


[i][b]How do I find all the tables used in a particular stored procedure?[/b][/i]
[i][b]How do I know which stored procedures are using a particular table?[/b][/i]

Here is the sample stored procedure.
[color=blue]CREATE PROCEDURE [/color][color=black]mySP[/color]
[color=blue]AS
SELECT [/color][color=gray]*[/color]
[color=blue]FROM [/color][color=black]Sales.Customer
GO[/color]
Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.
Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

[img]http://www.pinaldave.com/bimg/references.jpg[/img]
Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.
[b] Find Referecing Objects of a particular object[/b]

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).
[color=blue]USE [/color][color=black]AdventureWorks
GO[/color]
[color=blue]SELECT[/color]
[color=black]referencing_schema_name [/color][color=blue]= [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.SCHEMA_ID[/color][color=gray]),[/color]
[color=black]referencing_object_name [/color][color=blue]= [/color][color=black]o.name[/color][color=gray],[/color]
[color=black]referencing_object_type_desc [/color][color=blue]= [/color][color=black]o.type_desc[/color][color=gray],[/color]
[color=black]referenced_schema_name[/color][color=gray],[/color]
[color=black]referenced_object_name [/color][color=blue]= [/color][color=black]referenced_entity_name[/color][color=gray],[/color]
[color=black]referenced_object_type_desc [/color][color=blue]= [/color][color=black]o1.type_desc[/color][color=gray],[/color]
[color=black]referenced_server_name[/color][color=gray], [/color][color=black]referenced_database_name[/color]
[color=green]--,sed.* -- Uncomment for all the columns[/color]
[color=blue]FROM[/color]
[color=black]sys.sql_expression_dependencies sed[/color]
[color=blue]INNER JOIN[/color]
[color=black]sys.objects o [/color][color=blue]ON [/color][color=black]sed.referencing_id [/color][color=blue]= [/color][color=black]o.[object_id][/color]
[color=magenta]LEFT [/color][color=gray]OUTER [/color][color=blue]JOIN[/color]
[color=black]sys.objects o1 [/color][color=blue]ON [/color][color=black]sed.referenced_id [/color][color=blue]= [/color][color=black]o1.[object_id][/color]
[color=blue]WHERE[/color]
[color=black]referenced_entity_name [/color][color=blue]= [/color][color=red]'Customer'[/color]
[url="http://www.pinaldave.com/bimg/references1.jpg"][img]http://www.pinaldave.com/bimg/references1.jpg[/img][/url]
The above query will return all the objects which are referencing the table Customer.
[b] Find Referenced Objects of a particular object[/b]

Here we are finding all the objects which are used in the view table vIndividualCustomer.
[color=blue]USE [/color][color=black]AdventureWorks
GO[/color]
[color=blue]SELECT[/color]
[color=black]referencing_schema_name [/color][color=blue]= [/color][color=black]SCHEMA_NAME[/color][color=gray]([/color][color=black]o.SCHEMA_ID[/color][color=gray]),[/color]
[color=black]referencing_object_name [/color][color=blue]= [/color][color=black]o.name[/color][color=gray],[/color]
[color=black]referencing_object_type_desc [/color][color=blue]= [/color][color=black]o.type_desc[/color][color=gray],[/color]
[color=black]referenced_schema_name[/color][color=gray],[/color]
[color=black]referenced_object_name [/color][color=blue]= [/color][color=black]referenced_entity_name[/color][color=gray],[/color]
[color=black]referenced_object_type_desc [/color][color=blue]= [/color][color=black]o1.type_desc[/color][color=gray],[/color]
[color=black]referenced_server_name[/color][color=gray], [/color][color=black]referenced_database_name[/color]
[color=green]--,sed.* -- Uncomment for all the columns[/color]
[color=blue]FROM[/color]
[color=black]sys.sql_expression_dependencies sed[/color]
[color=blue]INNER JOIN[/color]
[color=black]sys.objects o [/color][color=blue]ON [/color][color=black]sed.referencing_id [/color][color=blue]= [/color][color=black]o.[object_id][/color]
[color=magenta]LEFT [/color][color=gray]OUTER [/color][color=blue]JOIN[/color]
[color=black]sys.objects o1 [/color][color=blue]ON [/color][color=black]sed.referenced_id [/color][color=blue]= [/color][color=black]o1.[object_id][/color]
[color=blue]WHERE[/color]
[color=black]o.name [/color][color=blue]= [/color][color=red]'vIndividualCustomer'[/color]
[url="http://www.pinaldave.com/bimg/references2.jpg"][img]http://www.pinaldave.com/bimg/references1.jpg[/img][/url]
The above query will return all the objects which are referencing the table Customer.
[/quote]
Dini gurinchi telusukovali anukuna baa... thanks chepavu.

[Kakateeyudu]

http://www.andhrafriends.com/topic/364344-calling-sql-developers-admins-or-sql-telsina-vallu-help-please/ Help required pleasee

[deals2buy]

[b] SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator[/b]


Some errors are very simple to understand but the solution of the same is not easy to figure out. Here is one of the similar errors where it clearly suggests where the problem is but does not tell what is the solution. Additionally, there are multiple solutions so developers often get confused with which one is correct and which one is not correct.

Let us first recreate scenario and understand where the problem is. Let us run following
[color=blue]USE [/color][color=black]Tempdb
GO[/color]
[color=blue]CREATE TABLE [/color][color=black]TestTable [/color][color=gray]([/color][color=black]ID [/color][color=blue]INT[/color][color=gray], [/color][color=black]MyText [/color][color=blue]NTEXT[/color][color=gray])[/color]
[color=black]GO[/color]
[color=blue]SELECT [/color][color=black]ID[/color][color=gray], [/color][color=black]MyText[/color]
[color=blue]FROM [/color][color=black]TestTable[/color]
[color=blue]WHERE [/color][color=black]MyText [/color][color=blue]= [/color][color=red]'AnyText'[/color]
[color=black]GO[/color]
[color=blue]DROP TABLE [/color][color=black]TestTable
GO[/color]


When you run above script it will give you following error.
[color=#ff0000]Msg 402, Level 16, State 1, Line 1[/color]
[color=#ff0000]The data types ntext and varchar are incompatible in the equal to operator.[/color]

One of the questions I often receive is that voucher is for sure compatible to equal to operator, then why does this error show up. Well, the answer is much simpler I think we have not understood the error message properly. Please see the image below. The next and varchar are not compatible when compared with each other using equal sign.
[img]http://www.pinaldave.com/bimg/402error.jpg[/img]

Now let us change the data type on the right side of the string to nvarchar from varchar. To do that we will put N’ before the string.


[color=blue]USE [/color][color=black]Tempdb
GO[/color]
[color=blue]CREATE TABLE [/color][color=black]TestTable [/color][color=gray]([/color][color=black]ID [/color][color=blue]INT[/color][color=gray], [/color][color=black]MyText [/color][color=blue]NTEXT[/color][color=gray])[/color]
[color=black]GO[/color]
[color=blue]SELECT [/color][color=black]ID[/color][color=gray], [/color][color=black]MyText[/color]
[color=blue]FROM [/color][color=black]TestTable[/color]
[color=blue]WHERE [/color][color=black]MyText [/color][color=blue]= [/color][color=red]N'AnyText'[/color]
[color=black]GO[/color]
[color=blue]DROP TABLE [/color][color=black]TestTable
GO[/color]


When you run above script it will give following error.
[color=#ff0000]Msg 402, Level 16, State 1, Line 1[/color]
[color=#ff0000]The data types ntext and nvarchar are incompatible in the equal to operator.[/color]


You can see that error message also suggests that now we are comparing next to nvarchar. Now as we have understood the error properly, let us see various solutions to the above problem.


[b]Solution 1: Convert the data types to match with each other using CONVERT function.[/b]
Change the datatype of the MyText to nvarchar.

[color=blue]SELECT [/color][color=black]ID[/color][color=gray], [/color][color=black]MyText[/color]
[color=blue]FROM [/color][color=black]TestTable[/color]
[color=blue]WHERE [/color][color=magenta]CONVERT[/color][color=gray]([/color][color=blue]NVARCHAR[/color][color=gray]([/color][color=magenta]MAX[/color][color=gray]), [/color][color=black]MyText[/color][color=gray]) [/color][color=blue]= [/color][color=red]N'AnyText'[/color]
[color=black]GO[/color]


[b]Solution 2: Convert the data type of columns from NTEXT to NVARCHAR(MAX) (TEXT to VARCHAR(MAX)[/b]

[color=blue]ALTER TABLE [/color][color=black]TestTable[/color]
[color=blue]ALTER COLUMN [/color][color=black]MyText [/color][color=blue]NVARCHAR[/color][color=gray]([/color][color=magenta]MAX[/color][color=gray])[/color]
[color=black]GO[/color]

Now you can run the original query again and it will work fine.


[b]Solution 3: Using LIKE command instead of Equal to command.[/b]
[color=blue]SELECT [/color][color=black]ID[/color][color=gray], [/color][color=black]MyText[/color]
[color=blue]FROM [/color][color=black]TestTable[/color]
[color=blue]WHERE [/color][color=black]MyText [/color][color=gray]LIKE [/color][color=red]'AnyText'[/color]
[color=black]GO[/color]

Well, any of the three of the solutions will work. Here is my suggestion if you can change the column data type from ntext or text to nvarchar or varchar, you should follow that path as text and ntext datatypes are marked as deprecated. All developers any way to change the deprecated data types in future, it will be a good idea to change them right early.
If due to any reason you can not convert the original column use Solution 1 for temporary fix. Solution 3 is the not the best solution and use it as a last option.


[b][u]Other Possible Solutions[/u]:[/b]
[list=1]
[*]

SELECT ID, MyText
FROM TestTable
WHERE cast(mytext AS varchar(max))= ‘AnyText’
[list=1]
[*]

SELECT ID, MyText
FROM TestTable
WHERE SUBSTRING(MyText,0,DATALENGTH(MyText))=N’AnyText’
SELECT ID, MyText
FROM TestTable
WHERE PATINDEX(N’AnyText’, MyText) =1
SELECT ID, MyText
FROM TestTable
WHERE CHARINDEX(N’AnyText’, MyText) =1 AND DATALENGTH(N’AnyText’)=DATALENGTH(MyText)
These solutions may not work in some criteria : like leading spaces, trailing spaces, etc…
However, As mentioned, solution 2 will be most preferable. ntext type will be removed in future versions ([url="http://msdn.microsoft.com/en-us/library/ms187993.aspx"]http://msdn.microsoft.com/en-us/library/ms187993.aspx[/url]), .
[/list]
[/list]

[deals2buy]

[b] Using SSIS to export data from a 64 bit server to Excel 2007[/b]


Recently I updated an old SSIS package due to an upgrade. This SSIS package exports data from SQL Server to Excel on a monthly basis. The Accounting and Finance departments need stand alone copies of data in Excel spreadsheets to do what-if analysis after the month is closed out. The challenge is Excel 2007 is typically a 32 bit program designed for workstations, and a 64 bit SQL Server doesn’t export data to a 32 bit file without some workarounds. After reading numerous articles on the Internet and 3 tries with Microsoft Support, I found the correct setup for exporting data. I have documented the steps as outlined below.
[b] Prerequisite: Microsoft Access Database Engine 2010 Redistributable[/b]

Install the Microsoft Access Database engine 2010 for connectivity to the SQL Server. The install is available at: [url="http://www.microsoft.com/en-us/download/details.aspx?id=13255"][u]http://www.microsoft.com/en-us/download/details.aspx?id=13255[/u][/url]
While one would think you would need the 64 bit version, the x86 install is actually needed due to trying to get a 64 bit OS to work with a 32 bit Excel file. The x86 install will need to be installed on the SQL Server that the SSIS package is connecting to for its source data.
[b] Creation and Editing of the SSIS package on your workstation[/b]

The SSIS package can be built from your workstation, but will need to be flipped between the 64BitRuntime for any debugging. Open the SSIS package in SQL Server Business Intelligence Development Studio on your workstation. In Solution Explorer set the properties by right clicking on the solution name, select Debugging and set Run64BitRuntime to false.

[i][img]http://www.sqlservercentral.com/Images/16745.jpg[/img][/i]


[b] Creating the Excel Target Data Connection String[/b]

In the Connection Manager right click and select New OLE DB Connection. Click the New button, and select from the drop down list of Providers Microsoft Office 12.0 Access Database Engine OLE DB Provider. Fill in the filepath to the xlsx file that will be used.

[img]http://www.sqlservercentral.com/Images/16746.jpg[/img]

Click the Data Links button, and select the All tab. For the Extended Properties fill in the value as Excel 12.0. Click on all the OK’s, and now the Excel data destination is ready.

[img]http://www.sqlservercentral.com/Images/16747.jpg[/img]

[b] Finish Setup of the SSIS Package[/b]

Continue with setting up your source data in the Connection Manager as SQLServer as a New OLE DB Connection, using the SQL Server Native Client.

[img]http://www.sqlservercentral.com/Images/16748.jpg[/img]

Set up Data Flow Tasks with Data Conversions as needed. I found that text data usually required a data conversion to Unicode string [DT_WSTR] to allow the data to be transferred properly.

[img]http://www.sqlservercentral.com/Images/16749.jpg[/img]

When done creating and testing the SSIS package on your workstation, be sure to change in Solution Explorer the Debugging properties back to Run64BitRuntime to true prior to running or scheduling it to run from a 64 bit server.

[img]http://www.sqlservercentral.com/Images/16750.jpg[/img]

The SSIS package is now ready to be scheduled in a SQL Server Agent in a job, or used in a dtexec cmd line, or however you choose to run it.

[img]http://www.sqlservercentral.com/Images/16751.jpg[/img]

[deals2buy]

Report Building 3.0: Adding Maps to Your Reports

http://www.simple-talk.com/sql/reporting-services/report-building-3.0-adding-maps-to-your-reports/

[deals2buy]

[b]
[url="http://www.sqlservercentral.com/blogs/sqlrnnr/2012/11/30/backup-history/"]Backup History[/url][/b]

[deals2buy]

I have the following set options
[color="#0000ff"]SET[/color] NUMERIC_ROUNDABORT [color="#0000ff"]OFF[/color][color="#808080"];
[/color][color="#0000ff"]SET[/color] ANSI_PADDING[color="#808080"],[/color] ANSI_WARNINGS[color="#808080"],[/color] CONCAT_NULL_YIELDS_NULL[color="#808080"],[/color] ARITHABORT[color="#808080"],[/color]QUOTED_IDENTIFIER[color="#808080"],[/color] ANSI_NULLS [color="#0000ff"]ON[/color][color="#808080"];[/color]
I then execute the following T-SQL statement
CREATE TABLE "SELECT" ("TABLE" int)
GO;
Is the table (Select) created ?

[deals2buy]

[b] Tip: Resetting Identity Fields[/b]


I have been cleaning up some existing code scripts and making them into standardized stored procedures. Two of my scripts deal with backing out ETL data from raw and temp tables that was transformed incorrectly during an ETL routine. Part of the code I use will reseed the identity field for the raw and temp tables to eliminate discontinuities in the identity field. The original code was using a construct such as:
Declare @MaxIdentityValue BigInt

Set @MaxIdentityValue = (Select Top 1 IdentityField
From MyTable
Order By IdentityField Desc
)

DBCC CHECKIDENT(MyTable, RESEED, @MaxIdentityValue)
This could have been better served by
Declare @MaxIdentityValue BigInt

Set @MaxIdentityValue = (Select Max(IdentityField)
From MyTable
)

DBCC CHECKIDENT(MyTable, RESEED, @MaxIdentityValue)
But I took some time and dove into what BOL had to say about DBCC CHECKIDENT and discovered this passage:
[i]When the current identity value is larger than the maximum value in the table, then:[/i]
[i]Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command.[/i]
[i]or[/i]
[i]Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.[/i]
Using this tip, I ran the following test:
If Exists(Select Name
From sys.objects
Where name = 'TestReseed'
And type = 'U'
)
Drop Table TestReseed
GO
Create Table TestReseed(Fld1 Int Identity(1,1), Fld2 Int)
GO

Declare @ReseedValue Int = 0

While @ReseedValue < 10
BEGIN
Set @ReseedValue = @ReseedValue + 1
Insert Into TestReseed(Fld2)
Values(@ReseedValue)
END

Select * From TestReseed

/* Results
Fld1 Fld2
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
*/
The identity field matches our control field. Now let's delete 5 rows:
Delete From TestReseed Where Fld1 > 5

Select * From TestReseed

/* Results
Fld1 Fld2
1 1
2 2
3 3
4 4
5 5
*/
Now let's add in 5 rows again:
Declare @ReseedValue Int = 0

While @ReseedValue < 5
BEGIN
Set @ReseedValue = @ReseedValue + 1
Insert Into TestReseed(Fld2)
Values(@ReseedValue)
END

/* Results
Fld1 Fld2
1 1
2 2
3 3
4 4
5 5
11 1
12 2
13 3
14 4
15 5
*/
Notice that the identity field now has a discontinuity in it between 5 and 11. Now take out the top 5 rows again.
Delete From TestReseed Where Fld1 > 5
At this point we can reset the identity column to where we want it with the following:

DBCC CHECKIDENT("TestReseed",RESEED,1)
DBCC CHECKIDENT("TestReseed",RESEED)

/* Results

Checking identity information: current identity value '20', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '1', current column value '5'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
Now we add back in our 5 rows:
Declare @ReseedValue Int = 0
While @ReseedValue < 5
BEGIN
Set @ReseedValue = @ReseedValue + 1
Insert Into TestReseed(Fld2)
Values(@ReseedValue)
END

Select * From TestReseed

/* Results
1 1
2 2
3 3
4 4
5 5
6 1
7 2
8 3
9 4
10 5
*/
We see that with two simple lines of code, the identity value is set to the next contiguous value without having to determine the reseed value in a parameter.

[deals2buy]

[b] How to Identify and Backup the Latest SQL Server Database in a Series[/b]


[b] Problem[/b]

I have to support a third party application that periodically creates a new database on the fly. This obviously causes issues with our [url="http://www.mssqltips.com/sql-server-tip-category/8/backup-and-recovery/"]backup mechanisms[/url]. The databases have a particular pattern for naming, so I can identify the set of databases, however, I need to make sure I'm always backing up the newest one. Read this tip to ensure you are backing up your latest database in a series.
[b] Solution[/b]

If you know the database naming pattern, identifying the proper database is fairly easy to do. For instance, I've created a set of test databases to simulate the situation:
[img]http://www.mssqltips.com/tipimages2/2807_kbk_BackupNewestDB_1.png[/img]
All three start with Test_ or simply just Test. Therefore, if I want to find the newest database, I simply need to query sys.databases for the name and the creation date. We accomplish that with the following query:

SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
ORDER BY create_date DESC
However, if we're going to build a [url="http://www.mssqltips.com/sqlservertutorial/1/sql-server-backup-options-and-commands/"]backup process[/url] around identifying the name, we need to get the name out of the query and into a variable. If we put this together for a script, here's what we're looking at:

SET NOCOUNT ON;
DECLARE @NewestDB sysname;

-- Find the newest database based on a pattern search
SET @NewestDB = (
SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
ORDER BY create_date DESC);

-- Routine to back up newest DB
-- Enter your code to backup the newest database here

PRINT 'Newest DB [' + @NewestDB + '] backup complete.';
And with the name captured into a variable, it's a simple matter of building a [url="http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/"]dynamic SQL statement[/url] to backup the database and then executing the built statement.
[b] Ensuring the Previous SQL Server Database is Backed Up[/b]

If the application is rolling over databases, then it is likely that when the new database is created, you won't have a backup after the latest changes to the just previous database. If we want to make sure it gets backed up, then we need to query to see when the last backup of that database was taken. With that and the creation date of the new database, we can determine if a final backup of the previous database is needed. We'll need a couple of more variables a couple of additional queries, but none of it is too difficult. In the end we're left with the following:

SET NOCOUNT ON;
DECLARE @NewestDB sysname;
DECLARE @NextDB sysname;
DECLARE @LastBackupDate DATETIME;
DECLARE @NewestDBCreationDate DATETIME;

-- Find the newest database based on a pattern search
SET @NewestDB = (
SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
ORDER BY create_date DESC);

-- Find when it was created
SET @NewestDBCreationDate = (
SELECT create_date
FROM sys.databases
WHERE [name] = @NewestDB);

-- Find the next oldest database
SET @NextDB = (
SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
AND NOT [name] = @NewestDB
ORDER BY create_date DESC);

-- Determine the last backup date for the next oldest
SET @LastBackupDate = (
SELECT
BU.backup_finish_date
FROM msdb.dbo.backupset AS BU
WHERE BU.[type] = 'D'
AND BU.database_name = @NextDB);

IF (@LastBackupDate IS NULL) OR (@LastBackupDate < @NewestDBCreationDate)
BEGIN

-- Routine to back up older DB
-- Enter your code to backup the oldest database here

PRINT 'Older DB [' + @NextDB + '] backup complete.';

END;

-- Routine to back up newest DB
-- Enter your code to backup the newest database here

PRINT 'Newest DB [' + @NewestDB + '] backup complete.';

The reason we check to see if @LastBackupDate is null is in the rare case that we don't have a backup record for that particular database. This could mean that there was never a backup taken or that it was taken long enough ago that it has been deleted from the table. In either case, we want to initiate the backup.

[gundugadu]

vayyas naku oka help kavali


oka query in tune cheyali

dani exectuion plan chusthe oka ktable meeda key lookup chesthundi and adi taking lot of time in the whole execution plan.


eeee keylookup elaa theseyalo cheppandi

i suspect this deals with predicate index lo lookup chesthunna column in add cheyali


flzzzzzzzzzzzzzzzzzz help if you know how remove the keylooup from the execution plan

[Desamudhuru]

[quote name='gundugadu' timestamp='1354721661' post='1302899644']
vayyas naku oka help kavali


oka query in tune cheyali

dani exectuion plan chusthe oka ktable meeda key lookup chesthundi and adi taking lot of time in the whole execution plan.


eeee keylookup elaa theseyalo cheppandi

i suspect this deals with predicate index lo lookup chesthunna column in add cheyali


flzzzzzzzzzzzzzzzzzz help if you know how remove the keylooup from the execution plan
[/quote]
http://blog.sqlauthority.com/2009/11/09/sql-server-removing-key-lookup-seek-predicate-predicate-an-interesting-observation-related-to-datatypes/

oka sari chudu emaina help ithadi emo. Nenu inka emaina dorukuthayi emo chusta

[gundugadu]

[quote name='Desamudhuru' timestamp='1354721820' post='1302899662']
[url="http://blog.sqlauthority.com/2009/11/09/sql-server-removing-key-lookup-seek-predicate-predicate-an-interesting-observation-related-to-datatypes/"]http://blog.sqlautho...d-to-datatypes/[/url]

oka sari chudu emaina help ithadi emo. Nenu inka emaina dorukuthayi emo chusta
[/quote]

edi chusa baaaaaaaaa but didn't worked out well

i know naaaa scenario ki exact ga solution online lo kanukkovadam kastam but
meery yemina suggestions estaremo ani asking

practical ga meeru implement chesindi online lo vundaka povachu andukani

[gundugadu]

muduru baaaaaaaaaa thanks fr the concern ye

looks like nenu execute chesthunna server lone lack of resources valla slow avuthundi


and aaa key lookup ni execution plan lo dani predicate index lo denithonithe lookup chesthundo aaaa column in Include chesa index lo

key lookup kastha non clustered index seek ayyindi

thanks