SQL Server with Rick Martinez


October 19, 2010  3:52 PM

SoCal Code Camp October 2010!

Rick Martinez Rick Martinez Profile: Rick Martinez

I will be presenting at the SoCal Code Camp in October 2010 at USC. There will be two sessions SQL Server T-SQL Recipes, and SQL Server for Beginners. See you there!

July 14, 2010  12:33 PM

Inland Empire .NET User’s Group

Rick Martinez Rick Martinez Profile: Rick Martinez

The IE .NET Users Group was awesome last night! Llwellyn Falco on “Forensic Development” was one of the best presentations I have attended, great job Llwellyn that is a presentation I will not forget! I got Most Valuable Member, First Runner Up award and lots of great prices with a very nice dinner thanks to James Johnson. I really recommend joining the group. Inland Empire .NET User’s Group


April 9, 2010  9:55 PM

SoCal Code Camp June 2010

Rick Martinez Rick Martinez Profile: Rick Martinez

I got a couple of sessions up at the SoCal Code Camp site! I am very excited about this one! Thank you for all your support.

SQL Server 2005 – Designing a Unit Test Plan for a Database

SQL Server 2005 – Developing Applications for Notification Services


April 2, 2010  10:04 AM

SQL Server Shortcut Keys

Rick Martinez Rick Martinez Profile: Rick Martinez

To access your shortcut keys within SQL Server Management Studio click Tools, Options, Environment, Keyboard. There you can customize your shortcut keys or create your own. A shortcut key I created was Ctrl + F1 (select top 100 * from ). When you highlight a table name and hit Ctrl + F1 it will select the top 100 records.

SQL Keyboard Shortcut Example


April 2, 2010  9:45 AM

SQL Server Login SID

Rick Martinez Rick Martinez Profile: Rick Martinez

If you restore a database on a second sql server and you need to be changing the sql login every time you restore that database you can use CREATE LOGIN. You can use the following sql to create the account on the second server using the same sid from server 1. BOL

--check existing users on SERVER
select name, sid from dbo.syslogins

--create the account with the same sid from server 1
create login rickmartinez
with
	password = 'pa$$word',
	sid = 0x02D76C40D8B6BD41BB809DA2DA58590D,
	default_database = yourdatabasename,
	default_language = English


April 2, 2010  9:17 AM

SQL Server Study Group

Rick Martinez Rick Martinez Profile: Rick Martinez

There will be a course on “Beginning Database Design” Class runs from Saturday, April 03, 2010 through Saturday, June 05, 2010 its FREE! For more information and to register please visit the site. Seats are limited! Study Group or IE .Net User’s Group


February 5, 2010  11:21 AM

What To Do When The Computer Name on your SQL Server is Changed

Rick Martinez Rick Martinez Profile: Rick Martinez

Let me tell you a little story about why I am writing this. We have many SQL Servers running in our environment. We changed the computer name on one of those SQL Servers from Server1 to ServerA. Well from Server3 we could not configure log shipping because SQL Server on Server3 did not know the computer name was changed on Server1 to ServerA.
SQL Server Setup sets the server name to the computer name during installation.
1. Check the server name using SSMS in a new query window where the computer name was changed using the following code. @@SERVERNAME returns the name of the local server that is running SQL SERVER.
a. SELECT @@SERVERNAME AS ‘Server Name’
2. If @@SERVERNAME returns the old computer name follow the steps below.
a. sp_dropserver ‘OldServerName’
b. sp_addserver ‘NewServerName’, local
c. Restart SQL Server Services
Sp_dropserver removes a server from the list of know remote and linked servers on the local instance of SQL Server.
SP_addserver defines the name of the local instance of SQL Server. This stored procedure also defines a remote server.

--current computer name + instance
select @@servername as 'Server Name'

--drop old server name
sp_dropserver 'oldservername'

--add new server name
sp_addserver 'newservername', local

--restart services


February 2, 2010  9:42 PM

SQL SERVER 2005 Striping Backup Sets

Rick Martinez Rick Martinez Profile: Rick Martinez

Striping backups involves using more than one device (disk or tape) for a single backup set operation. When performing a database backup, you can use up to 64 devices (disk or backup) in your backup operation. This is particularly useful for very large databases, because you can enhance backup performance by striping the backup files across seperate drives. Striping the backup files means each file is written simultaneously. Striped backups use parallel write operations, and can significantly speed up backup operations.
Here is a sample:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_Stripe1.bak',
DISK = 'D:\Backup\AdventureWorks_Stripe2.bak',
DISK = 'E:\Backup\AdventureWorks_Stripe3.bak'

This creates three files which are each used to store one third of the backup information needed to restore the database.


January 30, 2010  10:53 PM

SQL SERVER 2005 For Beginners Code Camp January 2010

Rick Martinez Rick Martinez Profile: Rick Martinez

Hello Everyone, if you attended my session on SQL SERVER 2005 for Begginers you can download the slide presentation and the t-sql code here. Thank you all for your support.

Download SQL SERVER 2008 Express from Microsoft.
Download only SQL SERVER Management Studio Express from Microsoft.


January 25, 2010  10:01 PM

How To Create a DML Trigger

Rick Martinez Rick Martinez Profile: Rick Martinez

A DML trigger contains t-sql code that is used to respond to an INSERT, UPDATE, or DELETE operation against a table or view. When a data modification event occurs, the trigger performs a set of actions defined within the trigger. There are two types of DML triggers: AFTER and INSTEAD OF. AFTER triggers are only allowed for tables, and they execute after the data modification has been completed against the table. INSTEAD OF triggers execute instead of the original data modification and can be created for both tables and views. Before proceeding it is important to note that SQL Server creates two “virtual” tables that are available specifically for triggers, called the deleted and inserted tables. These two tables capture the before and after pictures of the modified rows. You can also download the sql here.

Let us begin by using the AdventureWorks database. Please follow the steps to create and test DML Triggers.

use AdventureWorks;
go
/***************************************************************************
Step 1
create table to track all insertes, updates, and deletes
***************************************************************************/
create table Production.ProductInventoryAudit
(
	ProductID int not null,
	LocationID smallint not null,
	Shelf nvarchar(10) not null,
	Bin tinyint not null,
	Quantity smallint not null,
	rowguid uniqueidentifier not null,
	ModifiedDate datetime not null,
	InsOrUPD char(1) not null
);
go
/***************************************************************************
Step 2
create the trigger to populate production.productinventoryaudit table
***************************************************************************/
create trigger Production.trg_uid_ProductInventoryAudit
on Production.ProductInventory
after insert, delete
as
set nocount on;

--virtual table inserted rows
INSERT Production.ProductInventoryAudit
(ProductID, LocationID, Shelf, Bin, Quantity,
rowguid, ModifiedDate, InsOrUPD)
SELECT DISTINCT i.ProductID, i.LocationID, i.Shelf, i.Bin, i.Quantity,
i.rowguid, GETDATE(), 'I'
FROM inserted i;

--virtual table Deleted rows
INSERT Production.ProductInventoryAudit
(ProductID, LocationID, Shelf, Bin, Quantity,
rowguid, ModifiedDate, InsOrUPD)
SELECT d.ProductID, d.LocationID, d.Shelf, d.Bin, d.Quantity,
d.rowguid, GETDATE(), 'D'
FROM deleted d;
go
/***************************************************************************
Step 5
Insert a new row
***************************************************************************/
INSERT Production.ProductInventory
(ProductID, LocationID, Shelf, Bin, Quantity)
VALUES (316, 6, 'A', 4, 22)
/***************************************************************************
Step 6
Delete a row
***************************************************************************/
DELETE Production.ProductInventory
WHERE ProductID = 316 AND
LocationID = 6
/***************************************************************************
Step 7
Check the audit table
***************************************************************************/
SELECT ProductID, LocationID, InsOrUpd
FROM Production.ProductInventoryAudit


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: