Tag Archive : sql server 2005

How to Save Column Names with Results : SQL Server 2005

Posted on June 11, 2008 in: Database, SQLServer 2005|8 Comments

2
Digg me

Whether you are copying query output from SQL Server Management Studio or Saving result as .csv file, if you need to save the column name or column header along with result.. here is simple option you have to enable.

Goto Query > Query Options or Press CTRL + Shift + O

Goto Option Grid Under Results

Check the box which says “Include column headers when copying or saving the results”

Click OK.

Run the query and save result as .csv or copy paste to excel with column headers. :)

SQL Server 2005 : Access Tables / Entities across Servers.

Posted on February 18, 2008 in: Database, Tips and Tricks|1 Comment

0
Digg me

Accessing objects (tables..) across servers (SQL Server) is very simple.

Step 1: Create a link between two servers

Goto Query Window

[sourcecode language='sql']
EXEC sp_addlinkedserver
   ‘PRODSVR’,
   N’SQL Server’
GO[/sourcecode]
‘PRODSVR’  is the name of the Server.Execute it.

Step 2: Setup Permission between Servers.In Management StudioGotoServer Object >> Linked Servers >>

PRODSVR (See Image)

Right Click on PRODSVR goto Properties and make changes as given below.

 

Step 3: To access the tables in other server

Use the following  hirearchy

PRODSVR.dbname.dbo.tablename

To access Person.Address table from AdventureWorks database

[sourcecode language='sql']

Select * From PRODSVR.AdventureWorks.Person.Address

[/sourcecode]
Advantages :

1. Update Production server with data from development server. (SSIS is alternate way)

2. Allow users to access production database without multiple logins. (windows authentication or SQL authentication) 

Organize your SQL Server 2005 tables in a better way !!

Posted on February 6, 2008 in: Database, Tips and Tricks|Post Comment

0
Digg me

When you create tables in SQLServer (2005) by default its all get created under default schema. (dbo.)

If you have too many tables then the table list could be confusing / clumsy.

Here enters Schema

(see image)

To organize your tables into meaningful groups (or namespaces)

First create a schema
CREATE SCHEMA Person
GO
Then Alter your existing table
ALTER SCHEMA Lookup
TRANSFER dbo.Contact
GO

So now that you have grouped your tables into different schemas.

How to use them ? The usual way but with schema name added to it.
SELECT * FROM Person.Contact
What is Schema ? (from MSDN)Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a

database user.  Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any

user, and schema ownership is transferable.

In previous versions of SQL Server, database users and schemas were conceptually the same object. Beginning in SQL Server 2005, users and schemas

are separate, and schemas serve as containers of objects.

SQL Server 2005 : Verify table already exists

Posted on December 4, 2007 in: Database|Post Comment

0
Digg me

Verifying that an object exists

If the table exists, it is deleted. If the table does not exist, the DROP TABLE statement is not executed.

IF OBJECT_ID (N’dbo.AWBuildVersion’, N’U') IS NOT NULL
 DROP TABLE dbo.AWBuildVersion;
GO

Syntax :

OBJECT_ID (‘object_name’, ['object_type'])

Object Type is optional, and possible values it can hold are.. (Items in bold are frequently used)

Object type:
AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

P = SQL stored procedure

PC = Assembly (CLR) stored procedure

FN = SQL scalar function

FS = Assembly (CLR) scalar function

FT = Assembly (CLR) table-valued function

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TR = SQL DML trigger

IF = SQL inlined table-valued function

TF = SQL table-valued-function

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

IT = Internal table