Posts

Showing posts from July, 2008

OPENQUERY

Executes the specified pass-through query on the specified linked server.This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one. For Eg: SELECT TOP 1* FROM OPENQUERY(TAW,'SELECT Column1,Column2 FROM DatabaseName.dbo.TableName') Before running this query linked server has to becreated in the source server.Here 'TAW' is linkedserver name. And most important thing is if we want to execute the stored procedure which is in other server we need to use OPENQUERY compulsorily.

To know the permissions on the procedure.

---To know the permissions on the procedure. SELECT PERM.[Type],PERM.Permission_name FROM SYS.DATABASE_PERMISSIONS as PERM JOIN SYS.OBJECTS AS SO ON PERM.MAJOR_ID=SO.[OBJECT_ID] WHERE SO.[Name] like 'ProcedureName'

Build List Which Decides Service Packs.

Source From : http://www.sqlservercentral.com/articles/Administration/2960/

How to Read SQL Server 2005 Version(How to Know Which Service Pack Installed)

----In SQL Server 2005 SELECT SERVERPROPERTY('productversion') as Version, SERVERPROPERTY ('productlevel') AS ServicePack, SERVERPROPERTY ('edition') as Edition GO The results are:• The product version (for example, "9.00.1399.06"). • The product level (for example, "RTM"). • The edition (for example, "Enterprise Edition"). GO SELECT @@VERSION GO USE master GO XP_MSVER ---Another way knowing service pack is by build number: Microsoft SQL Server 2005 - 9.00.3042.00 Here '3042' is bulid number.And this is having SP2. Another example is Microsoft SQL Server 2005 - 9.00.3215.00 Here build number is '3215'.This is greater than 3042 build number so this is service pack 2. For Build details we can go see : http://support.microsoft.com/default.aspx?scid=kb;en-q321185

Changing The Name of the Server.

-----Changing the Name of the Server.But it wont Drop the Server. SP_DROPSERVER 'BHI-207\SQLEXPRESS'--Present serverName GO SP_ADDSERVER 'RAMESH MAMILLAPALLI',LOCAL--After running the Query Stop and Restart the Services. GO SELECT @@SERVERNAME

SP_ADDEXTENDEDPROPERTY

----We can give definitions for tables. EXEC SP_ADDEXTENDEDPROPERTY @name = N'MS_Description', @value = N'This Table is Telling about Emplyee Information.And also Employer', @level0type = N'SCHEMA', @level0name = dbo, @level1type = N'TABLE', @level1name = EmployeeTable; And also we can do the same thing in Table->RightClick->Properties->Extended Properties.