Code By Experience

Database Command Types DDL Data Definition Language  (DDL) statements are used to define the database structure or schema.  -           CREATE - to create objects in the database -           ALTER - alters the structure of the database -           DROP - delete objects from the database -           TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed -           COMMENT - add comments to the data dictionary -           RENAME - rename an object DML Data Manipulation Language  (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to d...

J ob is a set of one or more management tasks. The SQL Server Agent service is managing SQL server jobs. SQL Server Agent is a windows service. This topic will help to beginners to create SQL Server Jobs in a easy steps. Install Sql Server Management Studio and Go SQL Server Agent. Purpose: Some time customer want to complete a specific action in periodically like “Daily”, “Weekly”, and “Monthly”. DBA can Backup database Log repor Data Synchronization (bad practice). Monitoring  For this tutorial, have created a store procedure that basically calculate “Total Sales Amount”, “Total Discount Amount”, “Total Product” for daily sales and insert this record into a table. For beginners, using “Northwind” database download from codeplex or Microsoft Create table DailySalesRecords to hold date wise total sale records . USE [NORTHWIND] GO CREATE TABLE [dbo].[DailySalesRecords]( [OrderDate] [datetime] NOT NULL , [TotalAmount] [ dec...

S tored Procedure is m ost popular database  object .   A stored procedure in SQL Server is a group of one or more Transact-SQL statements    . Procedures resemble constructs in other programming languages because they can: ü   Allow input parameters and return multiple values in the form of output parameters to the calling program. ü   Contain programming statements that perform operations in the database. Can calling other procedure, functions ü   Return a status value to a calling program to indicate success or failure (and the reason for failure). Benefits of Using Stored Procedures:          ü   Reduced server/client network traffic: A procedure contain a batch of sql statement, even execute other procedure, This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. ü   Stronger sec...

I am working several remote server those are connected with DB link. I need create a DB link (remote server oracle db) with my SQL Server, but don't know the required server is connected or not.  at this moment used SQL server default procedure   EXEC SP_LINKEDSERVERS Found below results: all link server name, provider name, DataSource IP those are connected with DBLINK MSDN Column name Data type Description SRV_NAME sysname Name of the linked server. SRV_PROVIDERNAME nvarchar( 128 ) Friendly name of the OLE DB provider managing access to the specified linked server. SRV_PRODUCT nvarchar( 128 ) Product name of the linked server. SRV_DATASOURCE nvarchar( 4000 ) OLE DB data source property corresponding to the specified linked server. SRV_PROVIDERSTRING nvarchar( 4000 ) OLE DB provider string property corresponding to the linked server. ...

When I try to run DBCC repair I get the below the errors .Database needs to DBCC CHECKTABLE( 'Person.Address' , REPAIR_REBUILD ) Msg 7919, Level 16, State 3, Line 1 Repair statement not processed. Database needs to be in single user mode. The correct way to run a DBCC CHECKDB or DBCC CHECKTABLE statement with valid REPAIR options is to start SQL Server normally and then explicitly set the database in single user mode. You can do this from either the Enterprise Manager or the Query Analyzer. When single user mode set to true only one user can connect to the server. From Enterprise Manager: Right-click the database name, and then click  Properties . In the  Properties  dialog box, click  Options . Select the  single user  option, and then click  OK .        From Query Analyzer: SP_DBOPTION 'AdventureWorks' , SINGLE, TRUE Run DBCC command CHECKDB: DBCC CHECKTABLE( 'Pers...