Archive for the ‘Database’ Category
SQL Server 2005 : Access Tables / Entities across Servers.
Accessing objects (tables..) across servers (SQL Server) is very simple.
Step 1: Create a link between two servers
Goto Query Window
EXEC sp_addlinkedserver 'PRODSVR', N'SQL Server' GO
‘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
Select * From PRODSVR.AdventureWorks.Person.Address
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)
SQL Server, Clean your Database Records and reset Identity Columns, The Shortest Path
(Copied from Moses’s Blog for my reference)
Well, I had a small issue regarding writing a script to clean a database we have and reset its identity columns in all tables. Although the database wasn’t huge one (less than 100 tables) I had to trace relations to be able to delete child table’s records before parent’s ones because of the foreign key constraints. The solution is disable the foreign keys and delete records with no fear of any errors then enables the constraints again.
Well, I found a solution to disable all constraints without the need to go on each table and disable it manually. and I was happy to know that I can use this solution in deleting all records from all tables. Not only this I was able to use the same solution to reset identity columns in all tables.
The solution was to use this built in stored procedure sp_MSforeachtable. For help about this proc search for it in Books online or use this sp_helptext sp_MSForeachtable.
Now back to my 6 lines, bellow is how I re-zeroed my Database:
1: /*Disable Constraints & Triggers*/
2: exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
3: exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
4:
5: /*Perform delete operation on all table for cleanup*/
6: exec sp_MSforeachtable 'DELETE ?'
7:
8: /*Enable Constraints & Triggers again*/
9: exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
10: exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
11:
12: /*Reset Identity on tables with identity column*/
13: exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
Organize your SQL Server 2005 tables in a better way !!
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.
Asp.Net Filter Datatable by Column
Filter Datatable by Rows
If you need to get subset of a big dataset into datatable Rowwise …
Dim dtEmp as DataTable Dim dsEmployee as New DataSet dsEmployee = someobject.getdata() 'dataset can be populated in many ways which is not explained here. Dim sExpr as String Dim drRows() as DataRow, drSingleRow as DataRow sExpr = "EmpID > 100" 'Condition drRows = dsEmployee.Tables(0).Select(sExpr) 'If you need to add Sort Order it can be added to sExpr Dim sSortOrder as String sSortOrder = "EmpName DESC" drRows = dsEmployee.Tables(0).Select(sExpr,sSortOrder) For Each drSingleRow in drRows dtEmp.ImportRow(drRows) Next ' Datatable dtEmp has filtered records
Filter Datatable by Column
If you need to get subset of big dataset into datatable columnwise…
Dim dtEmp as DataTable
Dim dsEmployee as New DataSet
dsEmployoee = someobject.getdata() 'dataset can be populated in many ways which is not explained here.
'This copies the structure and data
dtEmp = dsEmployee.Tables(0).Copy
dtEmp.Columns.Remove("Unwanted Column 1")
dtEmp.Columns.Remove("Unwanted Column 2")
dtEmp.Columns.Remove("Unwanted Column 3")
SQL Server 2005 : Verify table already exists
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
Retrieving the First N Records from a SQL Query
3 Techniques
1. Simple Fixed ‘N’
SELECT TOP 10 NewsURL, ArticleTitle, ArticleAuthor
FROM NewsStoriesTable
ORDER BY PublicationDate DESC
2. Dynamic ‘N’ Records
CREATE PROCEDURE getRecentArticles
(
@ResultCount int
)
AS
Declare @sSQL
Set @sSQL = “Select TOP ” + @ResultCount + ” NewsURL, ArticleTitle, ArticleAuthor
FROM NewsStoriesTable ORDER BY PublicationDate DESC”
sp_executesql @sSQL
3. Dynamic ‘N’ Records (My Preferred way)
CREATE PROCEDURE getRecentArticles
(
@ResultCount int
)
AS
SET ROWCOUNT @ResultCount — KEY LINE WHICH DOES THE MAGIC
SELECT NewsURL, ArticleTitle, ArticleAuthor
FROM NewsStoriesTable
ORDER BY PublicationDate DESC
SQL2000 : Drop Database Contents from Query Analyzer
U : Table
P : SP
PK : Primary Key
UQ : Unique Constraint
F : Foreign Key
FN : Functions
D : Default
To see the list of User Create Items
— SELECT * FROM sysObjects WHERE xType in (‘U’,’P’,’PK’,’UQ’,’F’,’FN’,’D’)
— Declare @sql NVarChar(4000)– Select @sql= isnull(@sql,”) +’Drop Table ‘ + Name + ‘;’ From sysObjects Where xType=’U’– Exec sp_executesql @sql
— Declare @sql NVarChar(4000)– Select @sql= isnull(@sql,”) +’Drop Proc ‘ + Name + ‘;’ From sysObjects Where xType=’P’– Exec sp_executesql @sql
— Declare @sql NVarChar(4000)– Select @sql= isnull(@sql,”) +’Drop Function ‘ + Name + ‘;’ From sysObjects Where xType=’FN’– Exec sp_executesql @sql
Deleting LOG Files in SQL 2000
How to effectively delete Log files in SQL 2000 ?
* Backup DB (just in case something goes wrong)
* Enterprise manager >> DBName [Rightclick] >> Options >> Single User Mode
(If it complains others are using the DB, open Query Analyzer and sp_who and kill those Process ids)
* Enterprise manager >> DBName [Rightclick] >> Detatch Database
* Goto Physical Location (dbname_log.ldf) rename that file
* Enterprise manager (top level) >> Databases >> Attach Database
(specify the MDF name and DB name and owner – sa)
* Make sure DB works now.
* Delete the renamed .ldf file.
Script(s) 
