Wednesday, September 04, 2013

Missing Send to items in windows 7

Recently I lost few of my icons went missing from Send to folder when we right click on any item or folder. I frequently use Mail Recipient in this. Here is how we can restore this back from default profile

1. Go to following path for default profile setting to capture

"C:\Users\Default\AppData\Roaming\Microsoft\Windows\SendTo" or any other profiles which you have access to from below locations
"C:\Windows\ServiceProfiles\LocalService\AppData\Roaming\Microsoft\Windows\SendTo"
"C:\Windows\ServiceProfiles\NetworkService\AppData\Roaming\Microsoft\Windows\SendTo"

2. Now go to run command  or type in start-search box and type shell:sendto to access your own profile.

3. Copy missing files from your default profile to your own profile. If you want you can add  your own short cuts for quick access.

You are good to go now. Hope this helps!!

Tuesday, August 20, 2013

TSQL: DateTime in different formats

Here is how you can convert get date in different formats

SELECT
GETDATE() AS GetDate,
CONVERT(VARCHAR,GETDATE(),0) AS '0',
CONVERT(VARCHAR,GETDATE(),100) AS '100',
CONVERT(VARCHAR,GETDATE(),1) AS '1',
CONVERT(VARCHAR,GETDATE(),101) AS '101',
CONVERT(VARCHAR,GETDATE(),7) AS '7',
CONVERT(VARCHAR,GETDATE(),107) AS '107',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),10) AS '10',
CONVERT(VARCHAR,GETDATE(),110) AS '110',
CONVERT(VARCHAR,GETDATE(),12) AS '12',
CONVERT(VARCHAR,GETDATE(),112) AS '112',
CONVERT(VARCHAR,GETDATE(),14) AS '14',
CONVERT(VARCHAR,GETDATE(),114) AS '114'


You can refer this URL for other formatting options or follow below URL


Find more help  http://www.sql-server-helper.com/tips/date-formats.aspx

Thursday, July 25, 2013

Find all Stored Procedures depending on a Table in Database

We can use sp_depends, sp_depends displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure

But some times sp_depends does not always return accurate results.

Here is how you can get accurate results by using this query’s below

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%TableName%'


Save results with headers in SQL Server Management Studio

I am working a lot with excel and CSVs for my SSIS packages these days. so I need to some input files as CSV files from SQL Server. Here is what I did in SQL Server Management Studio to get CSV files with Headers
  1. Go to Tools in SQL Server Management Studio and click options.
  2. Now click on Query Results and select SQL Server.
  3. Now go to Results to Grid.
  4. Select “Include column headers when copying or saving the results. (shown in below image)
  5. Click OK
  6. Restart SQL Server Management Studio and you are good to go now.
 image Now open your query window and write you query and execute. On the results window right click and select  "save results as" and then save to CSV file which I can open in excel. 


Getting all Triggers in a Database

Some times you might want to know what are the triggers available across your database. Here is how you can know by using the below query.

-- Displays all Triggers in a Database 
SELECT
[Table] = OBJECT_NAME(o.parent_obj),
[Trigger] = o.[name],
[Type] = CASE WHEN
(
SELECT cmptlevel
FROM master.dbo.sysdatabases
WHERE [name] = DB_NAME()
) = 80 THEN
CASE WHEN
OBJECTPROPERTY(o.[id],
'ExecIsInsteadOfTrigger') = 1 THEN
'Instead Of'
ELSE
'After'
END
ELSE
'After'
END,
[Insert] = CASE WHEN
OBJECTPROPERTY(o.[id],
'ExecIsInsertTrigger') = 1 THEN
'Yes'
ELSE
'No'
END,
[Update] = CASE WHEN
OBJECTPROPERTY(o.[id],
'ExecIsUpdateTrigger') = 1 THEN
'Yes'
ELSE
'No'
END,
[Delete] = CASE WHEN
OBJECTPROPERTY(o.[id],
'ExecIsDeleteTrigger') = 1 THEN
'Yes'
ELSE
'No'
END,
[Enabled?] = CASE WHEN
OBJECTPROPERTY(o.[id],
'ExecIsTriggerDisabled') = 0 THEN
'Enabled'
ELSE
'Disabled'
END
FROM sysobjects o
WHERE OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
-- Comment out the following clause if you need to
-- include system triggers, e.g. those in MSDB
AND OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0
ORDER BY 1,2