Archive

Archive for the ‘SQL Server basics’ Category

“Cannot generate SSPI context” error, when connecting to SQL Server from other machine using SSMS

December 9, 2011 Leave a comment

Friends, Some days back when I tried to connect to a SQL server instance using SSMS form one of the machine in same domain I got error ”Cannot generate SSPI context” , after struggling a lot and going through this support article not found a solution to make it work.

Finally solution found, the issue is because of different time set on both server and the machine where I was running SSMS. After setting same time on both the issue get resolved.

Happy coding.

Advertisements

How to see the code or text of a SQL trigger

October 14, 2011 Leave a comment

Hi Friends,

To see the code or text of a SQL trigger use following query

exec sp_helptext ‘{Name of trigger}’

#temp table or a @table variable

February 21, 2010 Leave a comment

In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure.

1. Local Temporary Tables(# table)

CREATE TABLE #employee
(
empid INT,
empname VARCHAR(100)
)

  • A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
  • The data in this #temp table is visible only to the current scope usually a stored procedure, or a set of nested stored procedures.
  • The table gets cleared up automatically when the current procedure goes out of scope but try to delete before exiting stored procedure.
  • Local temp tables name is limited to 116 characters.

Use DROP TABLE statment like following.

DROP TABLE #people

2.Table Variables(@ table)

DECLARE @employee TABLE
(
empid INT,
empname VARCHAR(100)
)

  • A table variable is created in memory, and so performs slightly better than #temp tables.
  • Table variables are automatically cleared when the procedure or function goes out of scope, so you don’t have to remember to drop or clear the data.
  • You cannot truncate a table variable.
  • Table variables cannot be altered after they have been declared.
  • Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.