Ms Sql Server Technology Discussions

[deals2buy]

[quote name='ravi87' timestamp='1356127394' post='1302997652']


Edo timepassing posting aa bro...edo serious prob ani .. ekkada recchipoi answering.. hehe .... ok byee........ tc.................
[/quote]
timepass endi? nijamagane doubt vachi adigina..

[ravi87]

[quote name='Kaarthikeya' timestamp='1356129036' post='1302997876']
timepass endi? nijamagane doubt vachi adigina..
[/quote]


code pampu babu ante.. monday anesari.. ne seriousness chusi ala bro.. anthe. .. (copy n paste ki ala ante ). chill

[deals2buy]

[quote name='ravi87' timestamp='1356129968' post='1302997989']


code pampu babu ante.. monday anesari.. ne seriousness chusi ala bro.. anthe. .. (copy n paste ki ala ante ). chill
[/quote]
nenu intiki vachesina... [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img] no laptop bringing home [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img] endukante 5 hrs pani chestene naa brain pani cheyyadu [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img] alaantidi weekends ante..how ya how [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

[deals2buy]

[b] T-SQL Script to Find Details About TempDB[/b]


[CODE]
SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth >
AND is_percent_growth =
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
[/CODE]

Above script will return following result set.

[b]FileName FileSizeinMB AutogrowthStatus GrowthValue GrowthIncrement[/b]
tempdev 8 Autogrowth is on. 10 Growth value is a percentage.
templog 0.5 Autogrowth is on. 10 Growth value is a percentage.

[deals2buy]

[b] Enabling Clustered and Non-Clustered Indexes – Interesting Fact[/b]


[i]When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However, when clustered index is enabled, it does not automatically enable nonclustered index. All the nonclustered indexes needs to be enabled individually. I wondered if there is any short cut to enable all the indexes together. Index rebuilding came to my mind instantly. I ran T-SQL command of rebuilding all the indexes and it enabled all the indexes on table.[/i]
This was very intriguing to me, as I never faced this kind of situation before. Everyday there is something new in SQL Server and that what makes life of DBA refreshing.

[b]T-SQL to rebuild all the indexes on table.[/b]
[CODE]
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
[/CODE]

[b]Rebuild All Indexes using SQL Server Management Studio.[/b]
Expand AdventureWorks Database, Expand Production.Product Database, Expand Index Group
[img]http://www.pinaldave.com/bimg/indexrebuild1.gif[/img]
Right click on Indexes and select [i][b]Rebuild All[/b][/i]
[img]http://www.pinaldave.com/bimg/indexrebuild2.gif[/img]

[deals2buy]

[b] Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script[/b]

[b]Index Rebuild : [/b]This process drops the existing Index and Recreates the index.
[CODE]
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
[/CODE]

[b]Index Reorganize : [/b]This process physically reorganizes the leaf nodes of the index.
[CODE]
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
[/CODE]
[b]Recommendation:[/b] Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

[deals2buy]

[b] Find Collation of Database and Table Column Using T-SQL[/b]

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

[CODE]
/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO
/* Find Collation of SQL Server Database Table Column */
USE AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND name = 'City'
[/CODE]

[img]http://www.pinaldave.com/bimg/findcollation.jpg[/img]

[deals2buy]

[b] Change Collation of Database Column – T-SQL Script[/b]


[CODE]
USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO
[/CODE]


[color=black]When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.[/color]


[img]http://www.pinaldave.com/bimg/colcollation.jpg[/img]

[deals2buy]

[b] Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.[/b]


To fix the error which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database.[i]
[color=#ff0000][b]Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.[/b][/color][/i]
[b]Reason:[/b]
SQL Server 2000 supports multi-instances, the originating_server field contains the instance name in the format ‘server\instance’. Even for the default instance of the server, the actual server name is used instead of ‘(local)’. Therefore, after the Windows server is renamed, these jobs still reference the original server name and may not be updated or deleted by the process from the new server name. It’s a known problem with SQL2000 SP3.
[b]Fix/Workaround/Solution:[/b]
In order to solve the problem you should perform the following steps:

From the Query Analyzer run following steps in order:
[CODE]
SELECT @@servername
[/CODE]
and verify if it shows the correct SQL server name.
a) If not, run:[color=darkred][CODE]
sp_dropserver <'name_returned'>[/color]
[color=darkred]and then:
sp_addserver <'correct_servername'>, 'local'
[/CODE][/color]

to change the SQL server name.

Please restart SQL server service to let the new configuration takes effect.
If yes,

Please check the originating_server column in msdb..sysjobs by running:

[CODE]
SELECT *
FROM msdb..sysjobs
[/CODE]

and verify if all jobs have the correct server name for originating_server.

If not, update this value with the correct server name by running following script

[CODE]
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY('ServerName')AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = '<wrong_servername>'
[/CODE]

[deals2buy]

[b] Take Database Backup using SSMS[/b]


http://www.youtube.com/watch?feature=player_embedded&v=iu-jzJyJ4G8

[deals2buy]

[b] Introduction to SQL Server Replication[/b]


http://www.sqlservercentral.com/articles/Stairway+Series/72274/

[deals2buy]

[b] How to create and use Temp tables in SSIS[/b]


[b] Problem[/b]

I'm trying to use a temp table in an SSIS package. It seems like everything is working correctly until I try to query the temp table. What am I doing wrong?
[b] Solution[/b]

Creating temp tables in SSIS seems like a straight-forward process using the Execute SQL Task, however there are a couple of properties that must be changed. In this tip, we’ll walk through creating a simple temp table in SSIS.
[b] Creating Sample SSIS Package[/b]

First, I will drag an Execute SQL Task into my Design view and rename it Create Temp Table:
[img]http://www.mssqltips.com/tipimages2/2826_CreateTempTable.png[/img]

Next, I will right click and edit and choose my connection and SQLStatement:

[img]http://www.mssqltips.com/tipimages2/2826_SQLTaskEditor.png[/img]

(The SQL statement used in this example is below)

[CODE]
IF OBJECT_ID('tempdb..##tmpTeams') IS NOT NULL
DROP TABLE ##tmpTeams
CREATE TABLE ##tmpTeams
(
Team VARCHAR(255),
Mascot VARCHAR(255),
State VARCHAR (2)
)
INSERT INTO ##tmpTeams VALUES
('Auburn', 'Tigers', 'AL'),
('Alabama', 'Crimson Tide', 'AL'),
('Mississippi', 'Rebels', 'MS'),
('Louisiana State', 'Tigers', 'LA'),
('Mississippi State', 'Bulldogs', 'MS'),
('Arkansas', 'Razorbacks', 'AR'),
('Texas A&M', 'Aggies', 'TX'),
('Georgia', 'Bulldogs', 'GA'),
('Tennessee', 'Volunteers', 'TN'),
('Vanderbilt', 'Commodores', 'TN'),
('Florida', 'Gators', 'FL'),
('South Carolina', 'Gamecocks', 'SC'),
('Missouri', 'Tigers', 'MO')
[/CODE]


Next, I will drag a Data Flow task into my Design view, rename it Query and drag the precedence constraint between the two:
[img]http://www.mssqltips.com/tipimages2/2826_CreateTempTable-Query.png[/img]

For the Data Flow task we are going to query the temp table and export the results to a database table. Right click the Data Flow task and choose Edit. Drag a OLE DB Source and a OLE DB Destination task into the Design view.
[img]http://www.mssqltips.com/tipimages2/2826_OLEDBSource-OLEDBDest.png[/img]

To avoid errors when configuring the OLE DB Source we need to create the temp table first using SSMS. In SSMS run the following statement to create a global temp table:

[CODE]
CREATE TABLE ##tmpTeams
(
Team VARCHAR(255),
Mascot VARCHAR(255),
State VARCHAR (2)
)
[/CODE]

Once the table has been created, let’s go back into our SSIS package. Right click OLE DB Source and choose Edit. Choose your data source and choose SQL command for the Data access mode dropdown. In the SQL command text we will need to create our SQL statement:

[img]http://www.mssqltips.com/tipimages2/2826_OLEDBSourceEditor.png[/img]

Hit OK on the OLE DB Source window and right click OLE DB Destination and choose Edit. Choose your datasource and choose "Table or View – fast load" from the Data access mode dropdown. I already created a table called AlabamaTeams that I will use to display the data and will use this name in the dropdown for the Name of the table or the view.
[img]http://www.mssqltips.com/tipimages2/2826_OLEDBDestEditor.png[/img]

Once finished configuring the OLE DB Destination hit OK. We are now ready to execute the package. Hit F5 to begin or choose Debug, Start Debugging from the Menu bar:
[img]http://www.mssqltips.com/tipimages2/2826_Debug.png[/img]

After executing the package, an error will occur on the Query task:
[img]http://www.mssqltips.com/tipimages2/2826_CreateTempTable-Query-OLEDB.png[/img]

If we look at the Progress tab we can see that the reason this error occurs is because after the temp table is created in the “Create Temp Table” task it is deleted. The “Query” task searches for the table but can’t find it.
[img]http://www.mssqltips.com/tipimages2/2826_Error.png[/img]

[b] Fixing the Issue[/b]

To fix this issue, we will need to change a few properties on the tasks and connection manager. The first property is a connection manager property. If you right click the OLEDB Connection Manager and choose properties you will see a property called RetainSameConnection. This must be set to “True”. RetainSameConnection means that the temp table will not be deleted when the task is completed.

[img]http://www.mssqltips.com/tipimages2/2826_RetainSameConnection.png[/img]

The second property we must change is the Execute SQL Task property. If you right click on each task (Create Temp Table and Query) and choose properties you will see a property called DelayValidation. This must be set to “True”. DelayValidation means that the task will not check if the table exists upon creation.
[img]http://www.mssqltips.com/tipimages2/2826_DelayValidation.png[/img]

Once we have set both properties to “True”, execute the package again. The package should be successful this time:
[img]http://www.mssqltips.com/tipimages2/2826_Success.png[/img]

If we switch back over to SSMS and query the table, AlabamaTeams, we should return a result set:
[img]http://www.mssqltips.com/tipimages2/2826_QueryResults.png[/img]

**Note: I’m using global temp tables (##tmpTeams) instead of local temp tables (#tmpTeams) because when I create the table using SSMS the first time it uses a different session (SPID) therefore when I try to configure my OLE DB Source it cannot find the temp table and gives the following error:
[img]http://www.mssqltips.com/tipimages2/2826_OLEDBSourceError.png[/img]

With that said, only global temp tables should be used. Also, make sure when you are creating your global temp tables that the table name does not interfere with any other global temp tables that may be created using other processes.

[deals2buy]

[b] [url="http://www.sqlservercentral.com/blogs/katie-and-emil/2012/12/18/ssis-precedence-constraint-tutorial/"]SSIS Precedence Constraint Tutorial[/url][/b]


[b] What is Precedence Constraint?[/b]

Precedence Constraint allows us to "control the flow" by executing tasks that meet certain condition.

Video 1: Success, Failure, Completion

In this first video I discuss the basics and cover the most frequent use of Precedence Constraint which is "constraint" with three values: Success, Completion and Failure

[media=]http://www.youtube.com/watch?feature=player_embedded&v=4QRnEC--SZ8[/media]


NOTE: The videos have been created using SSIS 2012 but should work with SSIS 2005 and 2008.

[b] What is Precedence Constraint?[/b]

Precedence Constraint allows us to "control the flow" by executing tasks that meet certain condition.

Video 1: Success, Failure, Completion

In this first video I discuss the basics and cover the most frequent use of Precedence Constraint which is "constraint" with three values: Success, Completion and Failure

[media=]http://www.youtube.com/watch?feature=player_embedded&v=4QRnEC--SZ8[/media]

Video 2: Expressions

The second video shows how we can use expressions and SSIS variables to control which tasks should be executed. I also cover the basic operators used with expressions like equal, not equal, AND, OR.

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


Video 3: Expression AndOr Constraint

SSIS allows us to use more complex option that combines both constraint and expression in two different ways using AND or OR.


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

[deals2buy]

Video 4: Multiple Constraints

Occasionally we will have tasks that receive information from multiple tasks so we have to decide if that task should be executed when all conditions are true OR if at least one of them is true and this is what I discuss in this video.


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



Video 5: Show Annotation

With more complex packages it is important to ensure it is easy to read the logic and this is where Precedence Constraint "Show Annotation" property can help us.


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


Video 6: Containers

The final video show examples how containers can be used to either make the package more readable or handle logic that would not be that easy to achieve without containers.


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

[chelsea]