Access SQL Server in ODBC in different domain

Hi,

Recently, in my company, we added a new domain with sql servers. Ofcourse, a trust was setup between existing and new domain.
There was a need to access these sql servers from prev domain.
The way you access it would be as:

<sqlservername>.domainName\instanceName

Example: If sqlserver name is: DevServer 
and domain is: company.internal.com 
and instance name is: PostTesting

Then I will access it as: devserver.company.internal.com\posttesting

For default instance, it would be just: devserver.company.internal.com

HTH

Encrypt / Decrypt password in Sql Server Database

I know this solution is not a recommended solution by many DBAs but it is available to use. Sometimes you have to get things going and implementing each step with detail can take weeks to finish but as a quick solution, this method has helped me to store password in the database with encryption and decryption methods.

I will not suggest this implementation but it is a fast way to store passwords in database.

CREATE TABLE LoginDetails (
 UserName VARCHAR(50)
      , [Password] VARBINARY(100)
)

INSERT INTO LoginDetails VALUES ('John', ENCRYPTBYPASSPHRASE('secretkey', 'johnPassword'))
INSERT INTO LoginDetails VALUES ('Shaw', ENCRYPTBYPASSPHRASE('secretkey', 'shawPassword'))
INSERT INTO LoginDetails VALUES ('Mike', ENCRYPTBYPASSPHRASE('secretkey', 'mikePassword'))

When I select from table, I see binary data in Password column


Now to read password, you need to decrypt it with the same key along convert function to read binary data as readable text.

SELECT username, CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('secretkey', [password])) AS Password FROM LoginDetails

Here is the result

How to find start date of month in SQL Server?

SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS StartOfMonth

0 in date functions means 1/1/1900.
The above function first find the difference of total months from 1/1/1900.
It then adds the difference in 0 (1/1/1900) again which will land to the beginning date of month.

Why I dont see Network drives inside Open Dialog box?

If you like me that u didn't see network drives then you have to right place.
You need to visit the drive by typing the drive letter in the FileName field.
Once you visit, next time you open dialog box, you will see the drive listed in the dialog box.

HTH

SET vs SELECT SQL Server

Many times we wonder how set and select are different and mostly we think that select is used to return data set back to user and set is used to set the value of variables.

This is exactly right but sometimes people also use to set variable value using select statement.

Lets try some statements and learn some behaviors as well:

-- create couple temp tables
create table #Test (BusinessDate datetime)
create table #TestNoRows (BusinessDate datetime)

-- insert null row in #test table

insert into #test values (null)

-- declare variable
declare @businessDate as datetime

select @businessDate = businessdate from #Test
select @businessDate

You will get Null.  A row was returned from table with Null. To make it more clear, we will now set some default value to variable so that variable has some value.

-- Now set some default value.
set @businessDate = '01/01/1970'

select @businessDate =  isnull(businessdate, '01/01/2000') from #Test
select @businessDate

You will get '01/01/2000'. Again a row was returned and isnull function was able to work on this value and replaced the default value.

Now we will test how select behave with empty table.

-- declare variable
declare @businessDateNoRow as datetime

select @businessDateNoRow = businessdate from #TestNoRows
select @businessDateNoRow

No row returned. You will get Null as default value of variable.

-- Now set some default value.
set @businessDateNoRow = '01/01/1970'
select @businessDateNoRow =  isnull(businessdate, '01/01/2000') from #TestNoRows
select @businessDateNoRow

You will get '01/01/1970' because no rows returned that would be checked for null or not null. So same default value stored in variable is returned.

Now we will test how set/select works.

--using set

set @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )

select @businessDateNoRow

You will get null.

--using select

select @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )
select @businessDateNoRow

You will get null too.

When no rows are returned and if set is used, you would write subquery to set value. Nothing to do with set or select.

Multiple valid OR conditions in select statement SQL Server

If you have a query that has OR condition,  you will not get multiple rows just because there are multiple conditions satisfied the result.
However many rows exist in table that many rows will be returned.

Also all the operators are evaluated before the query result is returned.
This can result in multiple rows to be returned.

Lets work with an example:

Create table #person (name varchar(50), age int)

-- insert one row

insert into #person values ('tom', 40)

-- select person whose name is either 'tom' or age is 40.

select * from #person 

where name = 'tom' or age = 40

One row will be returned.

-- insert second row 

insert into #person values ('monica', 41)

-- Now select with multiple conditions 

select * from #person 

where (name = 'tom' or age = 40 or age > 40)

2 rows will be returned which tells all the ORs are evaluated and based on filters, rows will be returned.

HTH

Using star * in SQL Server Views

Learned a lesson about using star * in views.

If you add new cols in the table that is referenced in the view as * (along with other cols using join), the view will only show same number of columns that it is originally compiled with. 
View definition doesn’t get update right if * is not used in the end.

It keeps the same number of columns and moves the data to wrong columns while trying to return same number of cols.

Never use *.

HTH

Return no result set with Min / Max

Hi,

Just in case if you wondering how not to return NULL rows when aggregate functions couldnt find value, this is how you can do it!

If Object_Id('tempdb..#myTable') Is Not Null 

Drop Table #myTable;

Go

Create Table #myTable(col1 Integer Null, col2 Integer Null)

Insert #myTable(col1) values (1)

;With t_max(tmax, tmin) As

       (Select Max(col2), Min(col2)

       From #myTable)

Select tmax, tmin

From t_max

Where tmax Is Not Null And tmin Is Not Null

HTH