Access to SQL Server

[John]

Hi

I am upsizing my Access app to SQL Server. What is the SQL Server equivalent
of following from Access;

1. Format([My Date Field],"yyyy/MM/dd"). Is it possible to write a format
function in sql server code?

2. DISTINCTROW keyword in Access sql. Apparently DISTINCTROW is not allowed
in SQL Server sql.

3. A function to get windows user name in SQL Server.

Many Thanks

Regards

 

[Uri Dimant]

John
1) Take a look at CONVERT system function in the BOL that has third
parameter named style.

2) DISTINCT

3) SELECT HOST_NAME()

 

[Tibor Karaszi]

I think a closer mapping for 3 would be SYSTE;:USER or SUSER_SNAME().
Assumes a Windows login, of course.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

John
1) Take a look at CONVERT system function in the BOL that has third
parameter named style.

2) DISTINCT

3) SELECT HOST_NAME()

Hi

I am upsizing my Access app to SQL Server. What is the SQL Server
equivalent of following from Access;

1. Format([My Date Field],"yyyy/MM/dd"). Is it possible to write a
format function in sql server code?

2. DISTINCTROW keyword in Access sql. Apparently DISTINCTROW is not
allowed in SQL Server sql.

3. A function to get windows user name in SQL Server.

Many Thanks

Regards

 

[Uri Dimant]

Ahh, my mistake,( was thinking about the computer name) John, see Tibor's
respond

I think a closer mapping for 3 would be SYSTE;:USER or SUSER_SNAME().
Assumes a Windows login, of course.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

John
1) Take a look at CONVERT system function in the BOL that has third
parameter named style.

2) DISTINCT

3) SELECT HOST_NAME()

Hi

I am upsizing my Access app to SQL Server. What is the SQL Server
equivalent of following from Access;

1. Format([My Date Field],"yyyy/MM/dd"). Is it possible to write a
format function in sql server code?

2. DISTINCTROW keyword in Access sql. Apparently DISTINCTROW is not
allowed in SQL Server sql.

3. A function to get windows user name in SQL Server.

Many Thanks

Regards

 

[Erland Sommarskog]

A small request first. Please don't cross post your questions - it is
tedious for regulars in each list. Target one and if you don't get a
reasonable response, try another but in any event, this is the best one
for your questions.

I beg disagree. John is working with two products, and it may not be
obvious for him where to ask. Even his question is about SQL Server,
he may get better help from the Access crowd, because he is dressing
his questions in Access terms, and I bet that the Access newsgroups
has lot of people that has been on the same path as him.

Overall, I think cross-posting in general is OK, as long as it is not
excessive. What I is *not* OK is when people post the same to multiple
newsgroups independently. That is a waste of people's time, because you
might spend time on answering something that has been answered well
elsewhere.

Select suser_name() will give you want you want.

Unless he is on SQL 2000 where it returns NULL. SYSTEM_USER is better.

However, if John is using SQL Server authentication, the Windows
user name is not attainable.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[John]

Hi Uri

Many thanks. Wonder why access upsizing wizard could not have done this
minor change from DISTINCTROW to DISTINCT.

Thanks again

Regards

John
1) Take a look at CONVERT system function in the BOL that has third
parameter named style.

2) DISTINCT

3) SELECT HOST_NAME()

Hi

I am upsizing my Access app to SQL Server. What is the SQL Server
equivalent of following from Access;

1. Format([My Date Field],"yyyy/MM/dd"). Is it possible to write a format
function in sql server code?

2. DISTINCTROW keyword in Access sql. Apparently DISTINCTROW is not
allowed in SQL Server sql.

3. A function to get windows user name in SQL Server.

Many Thanks

Regards

 

[John]

Which bring me to my original question what is the sql server equivalent
of DISTINCTROW?

Many Thanks

Regards

hi,

Many thanks. Wonder why access upsizing wizard could not have done this
minor change from DISTINCTROW to DISTINCT.

distinctrow is not equal to distinct!

=> http://office.microsoft.com/en-us/access/HA012313511033.aspx


kind regards,
Josef


[fup'2 microsoft.public.access]

 

[John]

Got the answer, thanks.

"There is not an equivalent keyword.

But the workaround is to include the field names in your select distinct
statement that you want uniqueness to be defined on."

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_10229618.html

Which bring me to my original question what is the sql server
equivalent of DISTINCTROW?

Many Thanks

Regards

hi,

Many thanks. Wonder why access upsizing wizard could not have done this
minor change from DISTINCTROW to DISTINCT.

distinctrow is not equal to distinct!

=> http://office.microsoft.com/en-us/access/HA012313511033.aspx


kind regards,
Josef


[fup'2 microsoft.public.access]

 

[Paul Shapiro]

DistinctRow is an Access-specific SQL extension. It's not part of SQL
Server, and it's never been included in any version of the SQL standard.
It's basically a hack attempting to rescue people who don't understand sql
from their own mistakes.

Example:
Select C.companyName
From Company as C Join ContactPerson as P on C.companyID=P.companyID

This would return as many copies of the company name as there are rows in
ContactPerson for that company.

Select Distinct would return only the unique values from the output
expression list, in this case the unique values of companyName.

Select DistinctRow would return as many rows as exist in table Company and
have at least 1 match in ContactPerson. The difference is that if you had 4
companies with the same name, select distinct would return only 1 copy of
that name, while DistinctRow would return 4 copies.

So if you understand that, you can see that MOST of the time, assuming you
wrote your sql meaningfully, you can remove the DistinctRow from the Select
statement. If you really wanted unique values, replace DistinctRow with
Distinct. There is almost never a need for DistinctRow, and in the very rare
case that there is a need, there are other, clearer, ways to write the sql
statement.

 

[Aaron Bertrand [SQL Server MVP]]

You may also find some of the other advice here useful:


http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-betw
een-access-and-sql-server.html

Or in short form:

http://aspfaq.com/2214