Home > IT Blogs > SQL Server with Rick Martinez

SQL Server with Rick Martinez

PREV 12 NEXT
Oct 19 2010   3:52PM GMT

SoCal Code Camp October 2010!



Posted by: Rick Martinez
Code Camp, SQL Server

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!

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Jul 14 2010   12:33PM GMT

Inland Empire .NET User’s Group



Posted by: Rick Martinez
User Groups

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

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Apr 9 2010   9:55PM GMT

SoCal Code Camp June 2010



Posted by: 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

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Apr 2 2010   10:04AM GMT

SQL Server Shortcut Keys



Posted by: 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

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Apr 2 2010   9:45AM GMT

SQL Server Login SID



Posted by: Rick Martinez
SQL, SQL Security, SQL Login, T-SQL, SQL Server

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

PLAIN TEXT
SQL:
  1. –check existing users on SERVER
  2. SELECT name, sid FROM dbo.syslogins
  3.  
  4. –create the account with the same sid from server 1
  5. CREATE login rickmartinez
  6. WITH
  7.     password = ‘pa$$word’,
  8.     sid = 0×02D76C40D8B6BD41BB809DA2DA58590D,
  9.     default_database = yourdatabasename,
  10.     default_language = English

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Apr 2 2010   9:17AM GMT

SQL Server Study Group



Posted by: Rick Martinez
SQL, SQL Server, T-SQL

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

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Feb 5 2010   11:21AM GMT

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



Posted by: Rick Martinez
SQL Server, SQL SERVER 2005, SQL 2005, T-SQL, Computer Name, SERVER NAME

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.

PLAIN TEXT
SQL:
  1. –current computer name + instance
  2. SELECT @@servername AS ‘Server Name’
  3.  
  4. –drop old server name
  5. sp_dropserver ‘oldservername’
  6.  
  7. –add new server name
  8. sp_addserver ‘newservername’, LOCAL
  9.  
  10. –restart services

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Feb 2 2010   9:42PM GMT

SQL SERVER 2005 Striping Backup Sets



Posted by: Rick Martinez
SQL, SQL SERVER Backup, SQL 2005, SQL SERVER 2005, T-SQL, BACKUP, STRIPING

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:

PLAIN TEXT
SQL:
  1. BACKUP DATABASE AdventureWorks
  2. TO DISK = ‘C:\Backup\AdventureWorks_Stripe1.bak’,
  3. DISK = ‘D:\Backup\AdventureWorks_Stripe2.bak’,
  4. 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.

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Jan 30 2010   10:53PM GMT

SQL SERVER 2005 For Beginners Code Camp January 2010



Posted by: Rick Martinez
Code Camp, SQL SERVER for Beginners, SQL for Beginners, SQL Server, T-SQL, SQL 2005, SQL SERVER 2005

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.

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

Jan 25 2010   10:01PM GMT

How To Create a DML Trigger



Posted by: Rick Martinez
SQL Server, SQL SERVER 2005, T-SQL, DML Trigger, Trigger, SQL Trigger

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.

PLAIN TEXT
SQL:
  1. USE AdventureWorks;
  2. go
  3. /***************************************************************************
  4. Step 1
  5. create table to track all insertes, updates, and deletes
  6. ***************************************************************************/
  7. CREATE TABLE Production.ProductInventoryAudit
  8. (
  9.     ProductID int NOT NULL,
  10.     LocationID smallint NOT NULL,
  11.     Shelf nvarchar(10) NOT NULL,
  12.     Bin tinyint NOT NULL,
  13.     Quantity smallint NOT NULL,
  14.     rowguid uniqueidentifier NOT NULL,
  15.     ModifiedDate datetime NOT NULL,
  16.     InsOrUPD char(1) NOT NULL
  17. );
  18. go
  19. /***************************************************************************
  20. Step 2
  21. create the trigger to populate production.productinventoryaudit table
  22. ***************************************************************************/
  23. CREATE TRIGGER Production.trg_uid_ProductInventoryAudit
  24. ON Production.ProductInventory
  25. after INSERT, DELETE
  26. AS
  27. SET nocount ON;
  28.  
  29. –virtual table inserted rows
  30. INSERT Production.ProductInventoryAudit
  31. (ProductID, LocationID, Shelf, Bin, Quantity,
  32. rowguid, ModifiedDate, InsOrUPD)
  33. SELECT DISTINCT i.ProductID, i.LocationID, i.Shelf, i.Bin, i.Quantity,
  34. i.rowguid, GETDATE(), ‘I’
  35. FROM inserted i;
  36.  
  37. –virtual table Deleted rows
  38. INSERT Production.ProductInventoryAudit
  39. (ProductID, LocationID, Shelf, Bin, Quantity,
  40. rowguid, ModifiedDate, InsOrUPD)
  41. SELECT d.ProductID, d.LocationID, d.Shelf, d.Bin, d.Quantity,
  42. d.rowguid, GETDATE(), ‘D’
  43. FROM deleted d;
  44. go
  45. /***************************************************************************
  46. Step 5
  47. Insert a new row
  48. ***************************************************************************/
  49. INSERT Production.ProductInventory
  50. (ProductID, LocationID, Shelf, Bin, Quantity)
  51. VALUES (316, 6, ‘A’, 4, 22)
  52. /***************************************************************************
  53. Step 6
  54. Delete a row
  55. ***************************************************************************/
  56. DELETE Production.ProductInventory
  57. WHERE ProductID = 316 AND
  58. LocationID = 6
  59. /***************************************************************************
  60. Step 7
  61. Check the audit table
  62. ***************************************************************************/
  63. SELECT ProductID, LocationID, InsOrUpd
  64. FROM Production.ProductInventoryAudit

  Bookmark and Share     0 Comments     RSS Feed     Email a friend

PREV 12 NEXT