Category Archives: SSMS

SQL Server: Where You Can Find Saved Import/Export SSIS Packages

Using SQL Server Import Export Utility, have you ever saved this SSIS package to SQL Server ?

Recently, a friend of mine asked that after saving Import/Export process as SSIS package to SQL Server, Where he can find this saved package to re-execute it.

Once you have saved your SSIS package,open SQL Server Management studio and then on object explorer panel click on connect and select Integration Services. Use valid user name and password to connect.

Once connect, expend Stored Packages node and then expend MSDB node. Here you can find your saved package. Right click on it and select “Run Package” to re-execute your saved import/export steps.

Advertisements

SQL Serve Videos: Installing SQL Server and Creating Your First Database

If it’s your first day with SQL Server and you need someone who can help you in installation and creation of your first database in SQL Server 2008, then following videos at youtube are uploaded for you.
Length. 3:03
Length. 6:20
Installation of SQL Server 2005 is bit different as compared to new versions. If you are interested in old version, like SQL Server 2005 then, click on following link to get proper help.
Length 9:55
As 90% functions of SQL Server Management Studio are same in 2005 and later versions, following video (which is based on SQL Server 2005) will be helpful for users who are facing SQL Server Management Studio very first time. 
Length 10:47
Once you have installed SQL Server and successfully connected to your first database then, next thing you must learn is T-SQL Queries
Length 8:57

SQL Server: Shortcuts for TSQL Code in SSMS

For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.
If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.

It has shortcuts like:

                                    SSC        =       SELECT  COUNT(*) FROM
                                    SSF         =       SELECT * FROM
                                    UPD       =       UPDATE <>
 SET    <>
 FROM   <>

>SQL Server: Query Template, a Time Saving Feature

>

While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time saving facility. You can save your script as template, like following is a simple query script, which I like to execute for different databases and with different order by clause.
USE AdventureWorks
GO
SELECT     s.SalesPersonID, c.Title, c.FirstName, 
c.MiddleName, c.LastName, c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD
FROM        Sales.SalesPerson AS s INNER JOIN
HumanResources.Employee AS e 
ON e.EmployeeID = s.SalesPersonID
LEFT OUTER JOIN
Sales.SalesTerritory AS st 
ON st.TerritoryID = s.TerritoryID 
INNER JOIN
Person.Contact AS c 
ON c.ContactID = e.ContactID 
ORDER BY c.FirstName
One solution to avoid any labor work to change above script manually according to my requirements, is to create a dynamic query. But, in SQL Server 2008 I would prefer Query Template, by saving above query in following Template.
USE 
GO
SELECT     s.SalesPersonID, c.Title, c.FirstName, 
c.MiddleName, c.LastName,
c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD
FROM        Sales.SalesPerson AS s INNER JOIN
HumanResources.Employee AS e 
ON e.EmployeeID = s.SalesPersonID
LEFT OUTER JOIN
Sales.SalesTerritory AS st 
ON st.TerritoryID = s.TerritoryID 
INNER JOIN
Person.Contact AS c 
ON c.ContactID = e.ContactID 
ORDER BY 

Next time when I need to execute this script, I will just open the template and press Ctlr+Shift+M or click on replace button, and it will ask me  for template parameters value and on pressing OK button it will replace template parameters with my values on whole script.
Defining a template needs three parameters separated with comma and enclosed with < and > signs.
<TemplateParameter, ParameterDataType, DefaultValue>
Replace Button

>SQL Server: Keeping Log of Each Query Executed Through SSMS

>

During a normal working day, a DBA or Developer executes countless queries using SQL Server Management Studio. Some of these queries, which are thought important, are saved and roughly 80% of query windows are closed without pressing save button. But after few minutes, hours or even days, most DBAs and Developers like me want their quires back, which they have executed but can’t save.
SQL Server Management Studio has no such feature through which we can get our unsaved queries back. If you need to keep log of each query you have executed in SSMS then you must install free tool SSMS Tools Pack 1.9” by Mladen Prajdić, which contains “Query Execution History” and much more. 

>SQL Server: What I have Learned in April 2011

>

Dedicated to Unbeaten Hero of SQL Server Community Jacob Sebastian
TSQL Tips & Tricks
SQL Server Management:
Performance Tuning:
SQL Server Community:

>SQL Server: NULL As Nothing vs NULL As String Value in SSMS

>

While loading data from Flat Files, I have found that few column values are inserted as ‘NULL’ and client’s application crashed. WHY ?.  Because ‘NULL’ as nothing and NULL as character value are two different things. I found one more blessing of SQL Server 2008. That in SQL Server Management Studio 2008, it shows NULL values in result set with different color. How it helps us in real life, let’s find it with a simple query which we will execute on both SQL Server 2008 and SQL Server 2005.
USE AdventureWorks
GO
SELECT  DocumentID,
Title,
DocumentSummary
FROM    Production.Document
Here is result set from SQL Server 2008
And following result set is from SQL Serve 2005.
In SQL Server 2005, from result set, you can’t say which rows contains NULL (as nothing) values and which contains ‘NULL’ (as character). Lets update one of row in above table on both versions.
USE AdventureWorks
GO
UPDATE Production.Document
SET DocumentSummary = 'NULL'
WHERE DocumentID = 9
Execute select query on both sides and have a look on result-set. In SQL Server 2005, you can’t differentiate which NULL indicates nothing and which NULL are character values, but in SQL Server 2008 you can easily identify such data.

I have learned a lesson from this incident and now,to avoid such discrepancies, I like to execute following script to avoid both, NULL as character value problem and extra spaces problem, once I have loaded data in my target tables.
DECLARE @SqlText VARCHAR(1000)
DECLARE MY_CURSOR Cursor FAST_FORWARD
FOR SELECT  '[' + sc.name + '].[' + tb.name + ']' AS TableName,
cl.name AS ColumnName
FROM    sys.columns cl
INNER JOIN sys.tables tb ON cl.object_id = tb.object_id
INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
WHERE   tb.type = 'U'
AND cl.system_type_id IN ( 167, 175, 231, 239 )
AND cl.is_computed = 0
AND sc.principal_id = 1
ORDER BY TableName
/*
varchar = 167
nvarchar= 231
char = 175
nchar = 239
*/
OPEN My_Cursor
DECLARE @TableName VARCHAR(500),
@ColumnName VARCHAR(500)
Fetch NEXT FROM MY_Cursor INTO @TableName, @ColumnName
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT  @SqlText = 'UPDATE ' + @TableName + ' SET [' + @ColumnName
+ '] = CASE LTRIM(RTRIM([' + @ColumnName
+ ']))
WHEN ''NULL'' THEN NULL ELSE LTRIM(RTRIM(['
+ @ColumnName + '])) END'
EXEC ( @SqlText )                                                                      
FETCH NEXT FROM MY_CURSOR INTO @TableName, @ColumnName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

http://rcm.amazon.com/e/cm?t=co04b8-20&o=1&p=8&l=bpl&asins=B004NSUYXI&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=FFFFFF&bg1=FFFFFF&f=ifrhttp://rcm.amazon.com/e/cm?t=co04b8-20&o=1&p=8&l=bpl&asins=B000071HPQ&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=FFFFFF&bg1=FFFFFF&f=ifr

SQL Server: Understanding Output of SET STATISTICS TIME ON

In one of my early post Which is More Important for Query Optimization, CPU or Logical Reads? We have discussed about importance of logical page read values, that when we need to compare performance of two different versions of same query. If fewer pages are being accessed by one of our query, then it would be considered efficient as compare to other one.
But how much time a query is taking can be best measured by setting STATISTICS TIME on by using statement SET STATISTICS TIME ON.
--DBCC FREEPROCCACHE
SET STATISTICS TIME ON
SELECT TOP (10) ProductID,[Name],Color,ListPrice,[Size]
FROM Production.Product
SET STATISTICS TIME  OFF
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 6 ms.
(10 row(s) affected)
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
This output can be grouped as
  • Parse Time (in milliseconds) 
  • Compile Time (in milliseconds) 
  • Execution Time (in milliseconds)
Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree
Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.
And execution time is total time spent during execution of compiled plan.
All above three outputs are further divided to two subparts each i.e. CPU time and Elapsed time. CPU time is time used by CPU resources to complete a task (parse, compile or execute), while elapsed time is the total time took by a task from start to its end.
For example execution CPU time is just the time for which the CPU was busy executing the task and Elapsed time is the amount of time it took for the query to execute from start to completion. Most of the time elapsed time larger then CPU time because it also includes time spent during I/O operations required by query.
Elapsed time can be different for same query syntax on same server during different execution times because it depends upon other resources availability. Hence CPU time for execution of a query is important during performance comparison process.
Keep in mind that, ZERO parse and compile time is an indication that optimizer has reused the existing plan and hence made no effort and took no time for these processes.
DBCC FREEPROCCACHE is used to clear out the cache BUT DON’T use it on production servers.

>SQL Server: Multiple Objects Script but Save Each to Separate Files

>

Recently a developer asked for a tool or script which can generate script for given objects (tables, views, stored procedures etc) but results should be saved in separate files.
I think they were not aware that SQL Server 2008 already added this facility in script Wizard. Once you have provided all your required options and reached on last page of script wizard. This page provides options that you can save output to a single file or separate files, one for each object.

>SQL Server: What I Have Learned In February 2011

>

Dedicated to Tara Kizer., who is on this planet, just to help others.
February was a tough month as I switched my company and also because of my final term exams. Here is a list of topics which I have learned and shared in February 2011.   
Data Migration
TSQL Tips and Tricks
Performance Tuning
Database Design
SQL Server Management Studio