Wednesday, June 23, 2010

Create a random number

This will create a 10-digits random number



public static string CreateRandomNumber()
{
return GenerateRandomPhoneNumber(3);
}

private static string GenerateRandomPhoneNumber(int seed)
{
string phoneNumber = String.Empty;

Random random = new Random((int)(DateTime.Now.Ticks % 5000000000 + seed));

phoneNumber += random.Next(1, 10).ToString();

for (int i = 0; i < 9; i++)
{
phoneNumber += random.Next(0, 10).ToString();
}

return phoneNumber;
}

Tuesday, June 22, 2010

Specify instance in Select statement

Something like:

SELECT * FROM [WINXP\CORP].[Northwind].[dbo].Employees

cannot be done. So what we can do is create something called a "linked server" like this:

select * from sys.servers

EXEC sp_addlinkedserver @server='CORP'
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='WINXP\CORP'

select * from sys.servers

and now we can perform a select:

SELECT * FROM CORP.Northwind.dbo.Eployees

Note that the CORP in @datasrc is the instance name in the WINXP server. If you want to access the default instance you only need to specify the server name.

To delete the linked server use:

exec sp_dropserver 'CORP'

Here are another examples:


EXEC sp_addlinkedserver @server='SQLV02' -- the "friendly name will use in our queries"
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='SQL-V02' -- the actual name of the server

EXEC sp_addlinkedserver @server='INC' -- same as above
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='SQL-V01\Inc' -- the server name and the db instance (Inc in this example)


Monday, June 7, 2010

Unique Index


-- To create a unique index:

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ClientsTable_ClientID_Name')

BEGIN

-- ClientID and Name will be unique per row

CREATE UNIQUE INDEX IX_ClientsTable_ClientID_Name

ON ClientsTable (ClientID, [Name])

END

GO