Sql Comparing two tables: access

Showing posts with label access. Show all posts

Thursday, March 29, 2012

Concatenate Lname and Fname Columns

I'm using Access 2002 and Sql 2000.
I have a Lname and Fname columns that I'm try to concatenate. I'm trying to
use Lname + ', ' + Fname in a View. But Lname is the things that displays.
What am I doing wrong?
Thanks for the help,
Paulpjscott wrote:
> I'm using Access 2002 and Sql 2000.
> I have a Lname and Fname columns that I'm try to concatenate. I'm
> trying to use Lname + ', ' + Fname in a View. But Lname is the things
> that displays.
> What am I doing wrong?
> Thanks for the help,
> Paul
Are you using fixed-length character columns? If so, you'll need to trim
the data. For example:
create table #Names (
LName1 CHAR(20),
LName2 VARCHAR(20),
FName1 CHAR(20),
FName2 VARCHAR(20) )
go
Insert Into #Names Values (
'Gugick', 'Gugick','David','David')
Insert Into #Names Values (
'Smith', 'Smith','Dan','Dan')
go
Select
LName1 + ', ' + FName1 as "Fixed-Length Name",
LName2 + ', ' + FName2 as "Variable-Length Name",
RTRIM(LName1) + ', ' + RTRIM(FName1) as "Fixed-Length Name - Trimmed"
from #Names
Go
Drop Table #Names
go
David Gugick - SQL Server MVP
Quest Software

Sunday, March 25, 2012

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server? I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/Administration/ListConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?
I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>
|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server? I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/Administration/ListConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB
sql sql

computer name access sql server

Is there a way to determine which workstation is accessing the SQL server? I
know in SQL Profile you can see the userid but can you see what actualy
workstation is accessing the server?I found it
sp_who, tells me everything I need to know,
thanks,
"Mike" <Mike@.community.nospam.com> wrote in message
news:u$gQtjPtHHA.4952@.TK2MSFTNGP04.phx.gbl...
> Is there a way to determine which workstation is accessing the SQL server?
> I know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
>|||On Jun 22, 11:12 am, "Mike" <M...@.community.nospam.com> wrote:
> Is there a way to determine which workstation is accessing the SQL server?
I
> know in SQL Profile you can see the userid but can you see what actualy
> workstation is accessing the server?
select login_time as 'Login',
last_batch as 'Batch',
cast(status as varchar(12)) as 'Status',
cast(hostname as varchar(18)) as 'Host',
cast(program_name as varchar(32)) as 'Program',
cast(cmd as varchar(24)) as 'Command',
cast(nt_username as varchar(18)) as 'User',
cast(loginame as varchar(24)) as 'Login'
from sysprocesses
WHERE hostname<>''
order by nt_username;
go
http://www.sqlhacks.com/index.php/A...tConnectedUsers
for examples and explanations
New this week:
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to get how many records are in all the tables of a Microsoft SQL
Server database
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP
How to get the 3rd highest salary with MS SQL Server
How to number rows without using cursors with MS SQL Server 2005
How to rank rows by grouping without using cursors with SQL Server
with dense_rank
How to know who is connected to your server with SQL Server?
How to manually delete duplicate rows with MS SQL Server
How to delete duplicate rows in bulk with MS SQL Server
How to move TEMPDB to another drive in Microsoft SQL Server
Improvements to SQL Server indexes
How to increase the size of TEMPDB

Thursday, March 22, 2012

Computed Column Formulas

I am working on migrating an existing Access DB to SQL Server Express.
The DB is for rental tracking. I am creating it as an ADP project and have
created all the tables, forms etc. from scratch.
I am using Access 2007 as a front end for the DB.
I have fields RentalDays, StartDate and EndDate in a table.
In the existing Access DB I have a query that checks for NULL of the EndDate
field. If it is NULL I calculate the RentalDays from the StartDate to NOW.
If the EndDate is populated I calculate the difference between the StartDate
and EndDate. This gives me the number of accrued days a product has been in
rental or if it is a finished rental it gives the number of days the product
was rented for.
IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
AS RentDays
I've been unable to figure out the syntax for the expression entered as a
formula in the table with the RentalDays column in SQL Server Express. I am
able to make a datediff formula for the RentalDays column using the formula:
(datediff,(day,[StartDate],[EndDate]))
This works fine but only populates the RentalDays field with the number of
days for a rental with both StartDate and EndDate entries. I would like to
have the computed column, (RentalsDays) expression return the number of days
for ongoing rentals (no EndDate) as well as finished rentals.
My goal is to have the RentalDays calculated on the back end and use the
data in reports (open and finished rentals) as well as for day-to-day entry
forms for check in and out of rented products. Those forms are already
created but I am doing the RentalDays caculation on the form. Actually, I am
not sure which is the technically correct or preferred method for doing this
(on the form or via a formula for the RentalDays column in the table). This
all works wonderfully in Access 2007 at present but the number of users and
the amount of data is anticipated to increase and will present a problem in
the longer term. Which is why I am working to change over to SQL Server
Express. I'm pretty fair with Access but making a full blown ADP project and
using SQL Server Express are new to me.
Any help with this is appreciated.
John K.> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
> AS RentDays
Try:
DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>I am working on migrating an existing Access DB to SQL Server Express.
> The DB is for rental tracking. I am creating it as an ADP project and have
> created all the tables, forms etc. from scratch.
> I am using Access 2007 as a front end for the DB.
> I have fields RentalDays, StartDate and EndDate in a table.
> In the existing Access DB I have a query that checks for NULL of the
> EndDate field. If it is NULL I calculate the RentalDays from the StartDate
> to NOW. If the EndDate is populated I calculate the difference between the
> StartDate and EndDate. This gives me the number of accrued days a product
> has been in rental or if it is a finished rental it gives the number of
> days the product was rented for.
> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
> AS RentDays
> I've been unable to figure out the syntax for the expression entered as a
> formula in the table with the RentalDays column in SQL Server Express. I
> am able to make a datediff formula for the RentalDays column using the
> formula:
> (datediff,(day,[StartDate],[EndDate]))
> This works fine but only populates the RentalDays field with the number of
> days for a rental with both StartDate and EndDate entries. I would like to
> have the computed column, (RentalsDays) expression return the number of
> days for ongoing rentals (no EndDate) as well as finished rentals.
> My goal is to have the RentalDays calculated on the back end and use the
> data in reports (open and finished rentals) as well as for day-to-day
> entry forms for check in and out of rented products. Those forms are
> already created but I am doing the RentalDays caculation on the form.
> Actually, I am not sure which is the technically correct or preferred
> method for doing this (on the form or via a formula for the RentalDays
> column in the table). This all works wonderfully in Access 2007 at present
> but the number of users and the amount of data is anticipated to increase
> and will present a problem in the longer term. Which is why I am working
> to change over to SQL Server Express. I'm pretty fair with Access but
> making a full blown ADP project and using SQL Server Express are new to
> me.
> Any help with this is appreciated.
> John K.|||Dan,
That worked perfectly. I have to go lookup the COALESCE function to see
what is really going on there. I looked around and didn't see anybody offer
such a simple solution. I did find out though that I couldn't do a
conditional statement in a formula field.
Thanks for the quick response.
John K
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> AS RentDays
> Try:
> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Kraus" <jkraus3@.twcny.rr.com> wrote in message
> news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>>I am working on migrating an existing Access DB to SQL Server Express.
>> The DB is for rental tracking. I am creating it as an ADP project and
>> have created all the tables, forms etc. from scratch.
>> I am using Access 2007 as a front end for the DB.
>> I have fields RentalDays, StartDate and EndDate in a table.
>> In the existing Access DB I have a query that checks for NULL of the
>> EndDate field. If it is NULL I calculate the RentalDays from the
>> StartDate to NOW. If the EndDate is populated I calculate the difference
>> between the StartDate and EndDate. This gives me the number of accrued
>> days a product has been in rental or if it is a finished rental it gives
>> the number of days the product was rented for.
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> AS RentDays
>> I've been unable to figure out the syntax for the expression entered as a
>> formula in the table with the RentalDays column in SQL Server Express. I
>> am able to make a datediff formula for the RentalDays column using the
>> formula:
>> (datediff,(day,[StartDate],[EndDate]))
>> This works fine but only populates the RentalDays field with the number
>> of days for a rental with both StartDate and EndDate entries. I would
>> like to have the computed column, (RentalsDays) expression return the
>> number of days for ongoing rentals (no EndDate) as well as finished
>> rentals.
>> My goal is to have the RentalDays calculated on the back end and use the
>> data in reports (open and finished rentals) as well as for day-to-day
>> entry forms for check in and out of rented products. Those forms are
>> already created but I am doing the RentalDays caculation on the form.
>> Actually, I am not sure which is the technically correct or preferred
>> method for doing this (on the form or via a formula for the RentalDays
>> column in the table). This all works wonderfully in Access 2007 at
>> present but the number of users and the amount of data is anticipated to
>> increase and will present a problem in the longer term. Which is why I am
>> working to change over to SQL Server Express. I'm pretty fair with Access
>> but making a full blown ADP project and using SQL Server Express are new
>> to me.
>> Any help with this is appreciated.
>> John K.
>|||> such a simple solution. I did find out though that I couldn't do a
> conditional statement in a formula field.
You can specify a conditional expression with CASE like the example below.
I think a COALESCE function is better choice in you case, though.
CASE
WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
ELSE DATEDIFF(day, [StartDate], [EndDate])
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
> Dan,
> That worked perfectly. I have to go lookup the COALESCE function to see
> what is really going on there. I looked around and didn't see anybody
> offer such a simple solution. I did find out though that I couldn't do a
> conditional statement in a formula field.
> Thanks for the quick response.
> John K
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> AS RentDays
>> Try:
>> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "John Kraus" <jkraus3@.twcny.rr.com> wrote in message
>> news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>>I am working on migrating an existing Access DB to SQL Server Express.
>> The DB is for rental tracking. I am creating it as an ADP project and
>> have created all the tables, forms etc. from scratch.
>> I am using Access 2007 as a front end for the DB.
>> I have fields RentalDays, StartDate and EndDate in a table.
>> In the existing Access DB I have a query that checks for NULL of the
>> EndDate field. If it is NULL I calculate the RentalDays from the
>> StartDate to NOW. If the EndDate is populated I calculate the difference
>> between the StartDate and EndDate. This gives me the number of accrued
>> days a product has been in rental or if it is a finished rental it gives
>> the number of days the product was rented for.
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> AS RentDays
>> I've been unable to figure out the syntax for the expression entered as
>> a formula in the table with the RentalDays column in SQL Server Express.
>> I am able to make a datediff formula for the RentalDays column using the
>> formula:
>> (datediff,(day,[StartDate],[EndDate]))
>> This works fine but only populates the RentalDays field with the number
>> of days for a rental with both StartDate and EndDate entries. I would
>> like to have the computed column, (RentalsDays) expression return the
>> number of days for ongoing rentals (no EndDate) as well as finished
>> rentals.
>> My goal is to have the RentalDays calculated on the back end and use the
>> data in reports (open and finished rentals) as well as for day-to-day
>> entry forms for check in and out of rented products. Those forms are
>> already created but I am doing the RentalDays caculation on the form.
>> Actually, I am not sure which is the technically correct or preferred
>> method for doing this (on the form or via a formula for the RentalDays
>> column in the table). This all works wonderfully in Access 2007 at
>> present but the number of users and the amount of data is anticipated to
>> increase and will present a problem in the longer term. Which is why I
>> am working to change over to SQL Server Express. I'm pretty fair with
>> Access but making a full blown ADP project and using SQL Server Express
>> are new to me.
>> Any help with this is appreciated.
>> John K.
>|||On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > such a simple solution. I did find out though that I couldn't do a
> > conditional statement in a formula field.
> You can specify a conditional expression with CASE like the example below.
> I think a COALESCE function is better choice in you case, though.
> CASE
> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
> ELSE DATEDIFF(day, [StartDate], [EndDate])
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
> news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
>
> > Dan,
> > That worked perfectly. I have to go lookup the COALESCE function to see
> > what is really going on there. I looked around and didn't see anybody
> > offer such a simple solution. I did find out though that I couldn't do a
> > conditional statement in a formula field.
> > Thanks for the quick response.
> > John K
> > "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> wrote in message
> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
> >> AS RentDays
> >> Try:
> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
> >>news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
> >>I am working on migrating an existing Access DB to SQL Server Express.
> >> The DB is for rental tracking. I am creating it as an ADP project and
> >> have created all the tables, forms etc. from scratch.
> >> I am using Access 2007 as a front end for the DB.
> >> I have fields RentalDays, StartDate and EndDate in a table.
> >> In the existing Access DB I have a query that checks for NULL of the
> >> EndDate field. If it is NULL I calculate the RentalDays from the
> >> StartDate to NOW. If the EndDate is populated I calculate the difference
> >> between the StartDate and EndDate. This gives me the number of accrued
> >> days a product has been in rental or if it is a finished rental it gives
> >> the number of days the product was rented for.
> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
> >> AS RentDays
> >> I've been unable to figure out the syntax for the expression entered as
> >> a formula in the table with the RentalDays column in SQL Server Express.
> >> I am able to make a datediff formula for the RentalDays column using the
> >> formula:
> >> (datediff,(day,[StartDate],[EndDate]))
> >> This works fine but only populates the RentalDays field with the number
> >> of days for a rental with both StartDate and EndDate entries. I would
> >> like to have the computed column, (RentalsDays) expression return the
> >> number of days for ongoing rentals (no EndDate) as well as finished
> >> rentals.
> >> My goal is to have the RentalDays calculated on the back end and use the
> >> data in reports (open and finished rentals) as well as for day-to-day
> >> entry forms for check in and out of rented products. Those forms are
> >> already created but I am doing the RentalDays caculation on the form.
> >> Actually, I am not sure which is the technically correct or preferred
> >> method for doing this (on the form or via a formula for the RentalDays
> >> column in the table). This all works wonderfully in Access 2007 at
> >> present but the number of users and the amount of data is anticipated to
> >> increase and will present a problem in the longer term. Which is why I
> >> am working to change over to SQL Server Express. I'm pretty fair with
> >> Access but making a full blown ADP project and using SQL Server Express
> >> are new to me.
> >> Any help with this is appreciated.
> >> John K.- Hide quoted text -
> - Show quoted text -
or
DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))|||> or
> DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))
I agree that is is the best approach for John's specific situation and is
why I suggested it in my initial response. I added the CASE example in
response to John's statement that he couldn't find a "conditional
statement". CASE can be used for other situations that involve conditions
other than NULL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
> On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > such a simple solution. I did find out though that I couldn't do a
>> > conditional statement in a formula field.
>> You can specify a conditional expression with CASE like the example
>> below.
>> I think a COALESCE function is better choice in you case, though.
>> CASE
>> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
>> ELSE DATEDIFF(day, [StartDate], [EndDate])
>> END
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
>>
>> > Dan,
>> > That worked perfectly. I have to go lookup the COALESCE function to
>> > see
>> > what is really going on there. I looked around and didn't see anybody
>> > offer such a simple solution. I did find out though that I couldn't do
>> > a
>> > conditional statement in a formula field.
>> > Thanks for the quick response.
>> > John K
>> > "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> wrote in message
>> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
>> >> AS RentDays
>> >> Try:
>> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> >>news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>> >>I am working on migrating an existing Access DB to SQL Server Express.
>> >> The DB is for rental tracking. I am creating it as an ADP project and
>> >> have created all the tables, forms etc. from scratch.
>> >> I am using Access 2007 as a front end for the DB.
>> >> I have fields RentalDays, StartDate and EndDate in a table.
>> >> In the existing Access DB I have a query that checks for NULL of the
>> >> EndDate field. If it is NULL I calculate the RentalDays from the
>> >> StartDate to NOW. If the EndDate is populated I calculate the
>> >> difference
>> >> between the StartDate and EndDate. This gives me the number of
>> >> accrued
>> >> days a product has been in rental or if it is a finished rental it
>> >> gives
>> >> the number of days the product was rented for.
>> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
>> >> AS RentDays
>> >> I've been unable to figure out the syntax for the expression entered
>> >> as
>> >> a formula in the table with the RentalDays column in SQL Server
>> >> Express.
>> >> I am able to make a datediff formula for the RentalDays column using
>> >> the
>> >> formula:
>> >> (datediff,(day,[StartDate],[EndDate]))
>> >> This works fine but only populates the RentalDays field with the
>> >> number
>> >> of days for a rental with both StartDate and EndDate entries. I would
>> >> like to have the computed column, (RentalsDays) expression return the
>> >> number of days for ongoing rentals (no EndDate) as well as finished
>> >> rentals.
>> >> My goal is to have the RentalDays calculated on the back end and use
>> >> the
>> >> data in reports (open and finished rentals) as well as for day-to-day
>> >> entry forms for check in and out of rented products. Those forms are
>> >> already created but I am doing the RentalDays caculation on the form.
>> >> Actually, I am not sure which is the technically correct or preferred
>> >> method for doing this (on the form or via a formula for the
>> >> RentalDays
>> >> column in the table). This all works wonderfully in Access 2007 at
>> >> present but the number of users and the amount of data is anticipated
>> >> to
>> >> increase and will present a problem in the longer term. Which is why
>> >> I
>> >> am working to change over to SQL Server Express. I'm pretty fair with
>> >> Access but making a full blown ADP project and using SQL Server
>> >> Express
>> >> are new to me.
>> >> Any help with this is appreciated.
>> >> John K.- Hide quoted text -
>> - Show quoted text -
> or
> DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))|||On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > or
> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))
> I agree that is is the best approach for John's specific situation and is
> why I suggested it in my initial response. I added the CASE example in
> response to John's statement that he couldn't find a "conditional
> statement". CASE can be used for other situations that involve conditions
> other than NULL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Madhivanan" <madhivanan2...@.gmail.com> wrote in message
> news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
>
> > On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
> > online.sbcglobal.net> wrote:
> >> > such a simple solution. I did find out though that I couldn't do a
> >> > conditional statement in a formula field.
> >> You can specify a conditional expression with CASE like the example
> >> below.
> >> I think a COALESCE function is better choice in you case, though.
> >> CASE
> >> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
> >> ELSE DATEDIFF(day, [StartDate], [EndDate])
> >> END
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
> >>news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
> >> > Dan,
> >> > That worked perfectly. I have to go lookup the COALESCE function to
> >> > see
> >> > what is really going on there. I looked around and didn't see anybody
> >> > offer such a simple solution. I did find out though that I couldn't do
> >> > a
> >> > conditional statement in a formula field.
> >> > Thanks for the quick response.
> >> > John K
> >> > "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> wrote in message
> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
> >> >> AS RentDays
> >> >> Try:
> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> >> >> --
> >> >> Hope this helps.
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
> >> >>news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
> >> >>I am working on migrating an existing Access DB to SQL Server Express.
> >> >> The DB is for rental tracking. I am creating it as an ADP project and
> >> >> have created all the tables, forms etc. from scratch.
> >> >> I am using Access 2007 as a front end for the DB.
> >> >> I have fields RentalDays, StartDate and EndDate in a table.
> >> >> In the existing Access DB I have a query that checks for NULL of the
> >> >> EndDate field. If it is NULL I calculate the RentalDays from the
> >> >> StartDate to NOW. If the EndDate is populated I calculate the
> >> >> difference
> >> >> between the StartDate and EndDate. This gives me the number of
> >> >> accrued
> >> >> days a product has been in rental or if it is a finished rental it
> >> >> gives
> >> >> the number of days the product was rented for.
> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
> >> >> AS RentDays
> >> >> I've been unable to figure out the syntax for the expression entered
> >> >> as
> >> >> a formula in the table with the RentalDays column in SQL Server
> >> >> Express.
> >> >> I am able to make a datediff formula for the RentalDays column using
> >> >> the
> >> >> formula:
> >> >> (datediff,(day,[StartDate],[EndDate]))
> >> >> This works fine but only populates the RentalDays field with the
> >> >> number
> >> >> of days for a rental with both StartDate and EndDate entries. I would
> >> >> like to have the computed column, (RentalsDays) expression return the
> >> >> number of days for ongoing rentals (no EndDate) as well as finished
> >> >> rentals.
> >> >> My goal is to have the RentalDays calculated on the back end and use
> >> >> the
> >> >> data in reports (open and finished rentals) as well as for day-to-day
> >> >> entry forms for check in and out of rented products. Those forms are
> >> >> already created but I am doing the RentalDays caculation on the form.
> >> >> Actually, I am not sure which is the technically correct or preferred
> >> >> method for doing this (on the form or via a formula for the
> >> >> RentalDays
> >> >> column in the table). This all works wonderfully in Access 2007 at
> >> >> present but the number of users and the amount of data is anticipated
> >> >> to
> >> >> increase and will present a problem in the longer term. Which is why
> >> >> I
> >> >> am working to change over to SQL Server Express. I'm pretty fair with
> >> >> Access but making a full blown ADP project and using SQL Server
> >> >> Express
> >> >> are new to me.
> >> >> Any help with this is appreciated.
> >> >> John K.- Hide quoted text -
> >> - Show quoted text -
> > or
> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted text -
> - Show quoted text -
Hi Dan
I think I forgot that you already replied the same answer :)|||Dan,
I has seen the case example and wasn't sure it was the best approach and
that is when I decided to post here to see if there was a better solution.
I'm confident that for my usage the COALESCE function was the cleanest.
Thanks again,
John K
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
> On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))
>> I agree that is is the best approach for John's specific situation and is
>> why I suggested it in my initial response. I added the CASE example in
>> response to John's statement that he couldn't find a "conditional
>> statement". CASE can be used for other situations that involve
>> conditions
>> other than NULL.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Madhivanan" <madhivanan2...@.gmail.com> wrote in message
>> news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
>>
>> > On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
>> > online.sbcglobal.net> wrote:
>> >> > such a simple solution. I did find out though that I couldn't do a
>> >> > conditional statement in a formula field.
>> >> You can specify a conditional expression with CASE like the example
>> >> below.
>> >> I think a COALESCE function is better choice in you case, though.
>> >> CASE
>> >> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
>> >> ELSE DATEDIFF(day, [StartDate], [EndDate])
>> >> END
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> >>news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
>> >> > Dan,
>> >> > That worked perfectly. I have to go lookup the COALESCE function
>> >> > to
>> >> > see
>> >> > what is really going on there. I looked around and didn't see
>> >> > anybody
>> >> > offer such a simple solution. I did find out though that I couldn't
>> >> > do
>> >> > a
>> >> > conditional statement in a formula field.
>> >> > Thanks for the quick response.
>> >> > John K
>> >> > "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> wrote in message
>> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> Try:
>> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> >> --
>> >> >> Hope this helps.
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> >> >>news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>> >> >>I am working on migrating an existing Access DB to SQL Server
>> >> >>Express.
>> >> >> The DB is for rental tracking. I am creating it as an ADP project
>> >> >> and
>> >> >> have created all the tables, forms etc. from scratch.
>> >> >> I am using Access 2007 as a front end for the DB.
>> >> >> I have fields RentalDays, StartDate and EndDate in a table.
>> >> >> In the existing Access DB I have a query that checks for NULL of
>> >> >> the
>> >> >> EndDate field. If it is NULL I calculate the RentalDays from the
>> >> >> StartDate to NOW. If the EndDate is populated I calculate the
>> >> >> difference
>> >> >> between the StartDate and EndDate. This gives me the number of
>> >> >> accrued
>> >> >> days a product has been in rental or if it is a finished rental it
>> >> >> gives
>> >> >> the number of days the product was rented for.
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> I've been unable to figure out the syntax for the expression
>> >> >> entered
>> >> >> as
>> >> >> a formula in the table with the RentalDays column in SQL Server
>> >> >> Express.
>> >> >> I am able to make a datediff formula for the RentalDays column
>> >> >> using
>> >> >> the
>> >> >> formula:
>> >> >> (datediff,(day,[StartDate],[EndDate]))
>> >> >> This works fine but only populates the RentalDays field with the
>> >> >> number
>> >> >> of days for a rental with both StartDate and EndDate entries. I
>> >> >> would
>> >> >> like to have the computed column, (RentalsDays) expression return
>> >> >> the
>> >> >> number of days for ongoing rentals (no EndDate) as well as
>> >> >> finished
>> >> >> rentals.
>> >> >> My goal is to have the RentalDays calculated on the back end and
>> >> >> use
>> >> >> the
>> >> >> data in reports (open and finished rentals) as well as for
>> >> >> day-to-day
>> >> >> entry forms for check in and out of rented products. Those forms
>> >> >> are
>> >> >> already created but I am doing the RentalDays caculation on the
>> >> >> form.
>> >> >> Actually, I am not sure which is the technically correct or
>> >> >> preferred
>> >> >> method for doing this (on the form or via a formula for the
>> >> >> RentalDays
>> >> >> column in the table). This all works wonderfully in Access 2007 at
>> >> >> present but the number of users and the amount of data is
>> >> >> anticipated
>> >> >> to
>> >> >> increase and will present a problem in the longer term. Which is
>> >> >> why
>> >> >> I
>> >> >> am working to change over to SQL Server Express. I'm pretty fair
>> >> >> with
>> >> >> Access but making a full blown ADP project and using SQL Server
>> >> >> Express
>> >> >> are new to me.
>> >> >> Any help with this is appreciated.
>> >> >> John K.- Hide quoted text -
>> >> - Show quoted text -
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted
>> > text -
>> - Show quoted text -
> Hi Dan
> I think I forgot that you already replied the same answer :)|||> I'm confident that for my usage the COALESCE function was the cleanest.
I agree. As I mentioned to Madhivanan, I provided the CASE example as an
alternative approach that might come in handy for other conditional
expressions you might have in the future.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:18B271B1-AEF3-45A2-A065-7E43BD734A48@.microsoft.com...
> Dan,
> I has seen the case example and wasn't sure it was the best approach and
> that is when I decided to post here to see if there was a better solution.
> I'm confident that for my usage the COALESCE function was the cleanest.
> Thanks again,
> John K
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
>> On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
>> online.sbcglobal.net> wrote:
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))
>> I agree that is is the best approach for John's specific situation and
>> is
>> why I suggested it in my initial response. I added the CASE example in
>> response to John's statement that he couldn't find a "conditional
>> statement". CASE can be used for other situations that involve
>> conditions
>> other than NULL.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Madhivanan" <madhivanan2...@.gmail.com> wrote in message
>> news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
>>
>> > On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
>> > online.sbcglobal.net> wrote:
>> >> > such a simple solution. I did find out though that I couldn't do a
>> >> > conditional statement in a formula field.
>> >> You can specify a conditional expression with CASE like the example
>> >> below.
>> >> I think a COALESCE function is better choice in you case, though.
>> >> CASE
>> >> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
>> >> ELSE DATEDIFF(day, [StartDate], [EndDate])
>> >> END
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> >>news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
>> >> > Dan,
>> >> > That worked perfectly. I have to go lookup the COALESCE function
>> >> > to
>> >> > see
>> >> > what is really going on there. I looked around and didn't see
>> >> > anybody
>> >> > offer such a simple solution. I did find out though that I couldn't
>> >> > do
>> >> > a
>> >> > conditional statement in a formula field.
>> >> > Thanks for the quick response.
>> >> > John K
>> >> > "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> wrote in
>> >> > message
>> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> Try:
>> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> >> --
>> >> >> Hope this helps.
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
>> >> >>news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>> >> >>I am working on migrating an existing Access DB to SQL Server
>> >> >>Express.
>> >> >> The DB is for rental tracking. I am creating it as an ADP project
>> >> >> and
>> >> >> have created all the tables, forms etc. from scratch.
>> >> >> I am using Access 2007 as a front end for the DB.
>> >> >> I have fields RentalDays, StartDate and EndDate in a table.
>> >> >> In the existing Access DB I have a query that checks for NULL of
>> >> >> the
>> >> >> EndDate field. If it is NULL I calculate the RentalDays from the
>> >> >> StartDate to NOW. If the EndDate is populated I calculate the
>> >> >> difference
>> >> >> between the StartDate and EndDate. This gives me the number of
>> >> >> accrued
>> >> >> days a product has been in rental or if it is a finished rental
>> >> >> it
>> >> >> gives
>> >> >> the number of days the product was rented for.
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> I've been unable to figure out the syntax for the expression
>> >> >> entered
>> >> >> as
>> >> >> a formula in the table with the RentalDays column in SQL Server
>> >> >> Express.
>> >> >> I am able to make a datediff formula for the RentalDays column
>> >> >> using
>> >> >> the
>> >> >> formula:
>> >> >> (datediff,(day,[StartDate],[EndDate]))
>> >> >> This works fine but only populates the RentalDays field with the
>> >> >> number
>> >> >> of days for a rental with both StartDate and EndDate entries. I
>> >> >> would
>> >> >> like to have the computed column, (RentalsDays) expression return
>> >> >> the
>> >> >> number of days for ongoing rentals (no EndDate) as well as
>> >> >> finished
>> >> >> rentals.
>> >> >> My goal is to have the RentalDays calculated on the back end and
>> >> >> use
>> >> >> the
>> >> >> data in reports (open and finished rentals) as well as for
>> >> >> day-to-day
>> >> >> entry forms for check in and out of rented products. Those forms
>> >> >> are
>> >> >> already created but I am doing the RentalDays caculation on the
>> >> >> form.
>> >> >> Actually, I am not sure which is the technically correct or
>> >> >> preferred
>> >> >> method for doing this (on the form or via a formula for the
>> >> >> RentalDays
>> >> >> column in the table). This all works wonderfully in Access 2007
>> >> >> at
>> >> >> present but the number of users and the amount of data is
>> >> >> anticipated
>> >> >> to
>> >> >> increase and will present a problem in the longer term. Which is
>> >> >> why
>> >> >> I
>> >> >> am working to change over to SQL Server Express. I'm pretty fair
>> >> >> with
>> >> >> Access but making a full blown ADP project and using SQL Server
>> >> >> Express
>> >> >> are new to me.
>> >> >> Any help with this is appreciated.
>> >> >> John K.- Hide quoted text -
>> >> - Show quoted text -
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted
>> > text -
>> - Show quoted text -
>> Hi Dan
>> I think I forgot that you already replied the same answer :)
>

Computed Column Formulas

|||Dan,
I has seen the case example and wasn't sure it was the best approach and
that is when I decided to post here to see if there was a better solution.
I'm confident that for my usage the COALESCE function was the cleanest.
Thanks again,
John K
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
> On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Hi Dan
> I think I forgot that you already replied the same answer

|||> I'm confident that for my usage the COALESCE function was the cleanest.
I agree. As I mentioned to Madhivanan, I provided the CASE example as an
alternative approach that might come in handy for other conditional
expressions you might have in the future.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:18B271B1-AEF3-45A2-A065-7E43BD734A48@.microsoft.com...
> Dan,
> I has seen the case example and wasn't sure it was the best approach and
> that is when I decided to post here to see if there was a better solution.
> I'm confident that for my usage the COALESCE function was the cleanest.
> Thanks again,
> John K
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
>
sql sql

Computed Column Formulas

computed column

Hello,
I've a problem with inserting a record through a form in Access. The
recordsource is a view from sql server.
The view has a computed column. The problem occurs when there is an
insert/update on that view. The errors indicates that arithabort needs
to be set on, etc.
In my ado-connection i can fix that, but how do i do it when the view is
linked through odbc? Maybe a 'instead of trigger'would do it, but i
don't know how.Jason
Lookup SET ARITHABORT command in the BOL along with good examples
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u09uNxe5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I've a problem with inserting a record through a form in Access. The
> recordsource is a view from sql server.
> The view has a computed column. The problem occurs when there is an
> insert/update on that view. The errors indicates that arithabort needs to
> be set on, etc.
> In my ado-connection i can fix that, but how do i do it when the view is
> linked through odbc? Maybe a 'instead of trigger'would do it, but i don't
> know how.|||Uri Dimant wrote:
> Jason
> Lookup SET ARITHABORT command in the BOL along with good examples
>
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:u09uNxe5FHA.1420@.TK2MSFTNGP09.phx.gbl...
>
>
>
Hi Uri,
I already looked at bol. In a ado-connection i used myconnection.execute
"set arithabort on"
But how to do it when the view is a linked accesstable? I use a filedsn.|||Jason
Probably you'll have to create a view in QA with this setting like
SET ARITHABORT ON
GO
CREATE VIEW myView AS
....
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eAjl94e5FHA.1032@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant wrote:
> Hi Uri,
> I already looked at bol. In a ado-connection i used myconnection.execute
> "set arithabort on"
> But how to do it when the view is a linked accesstable? I use a filedsn.|||Uri Dimant wrote:
> Jason
> Probably you'll have to create a view in QA with this setting like
> SET ARITHABORT ON
> GO
> CREATE VIEW myView AS
> ....
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eAjl94e5FHA.1032@.TK2MSFTNGP11.phx.gbl...
>
>
>
Hi Uri,
That doesn't work any other ideas?|||Try setting the database option arith_abort. Lookup sp_dboption in Books
Online.
ML

Monday, March 19, 2012

component services, DTC network access

I am trying to get an XP machine to insert data into a linked Windows
2003 SBS Premium machine. I have done it w/ XP to 2003 but the SBS seems
to have a different Security Configuration on the MSDTC tab in the
COmponent Services. There are no options for inbound or outbound, only:
Network DTC access (checked)
Network Administration (not checked)
Network Transactions (not checked)
Network Clients (not checked)
Transaction Internet Protocol (TIP) Transactions (not checked)
XA Transactions (not checked)
I have done the Application Server option for Network DTC Access.
Any ideas why the screen is different and what I can do to fix?
The server properties say:
Microsoft Windows Server 2003
for Small Business Server
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Hi
I assume that distributed transactions are not working? You may want to ask
in the SBS newsgroup why this is different microsoft.public.windows.server.sbs
John
"Darin" wrote:

> I am trying to get an XP machine to insert data into a linked Windows
> 2003 SBS Premium machine. I have done it w/ XP to 2003 but the SBS seems
> to have a different Security Configuration on the MSDTC tab in the
> COmponent Services. There are no options for inbound or outbound, only:
> Network DTC access (checked)
> Network Administration (not checked)
> Network Transactions (not checked)
> Network Clients (not checked)
> Transaction Internet Protocol (TIP) Transactions (not checked)
> XA Transactions (not checked)
> I have done the Application Server option for Network DTC Access.
> Any ideas why the screen is different and what I can do to fix?
> The server properties say:
> Microsoft Windows Server 2003
> for Small Business Server
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>

component services, DTC network access

I am trying to get an XP machine to insert data into a linked Windows
2003 SBS Premium machine. I have done it w/ XP to 2003 but the SBS seems
to have a different Security Configuration on the MSDTC tab in the
COmponent Services. There are no options for inbound or outbound, only:
Network DTC access (checked)
Network Administration (not checked)
Network Transactions (not checked)
Network Clients (not checked)
Transaction Internet Protocol (TIP) Transactions (not checked)
XA Transactions (not checked)
I have done the Application Server option for Network DTC Access.
Any ideas why the screen is different and what I can do to fix?
The server properties say:
Microsoft Windows Server 2003
for Small Business Server
Darin
*** Sent via Developersdex http://www.codecomments.com ***Hi
I assume that distributed transactions are not working? You may want to ask
in the SBS newsgroup why this is different microsoft.public.windows.server.s
bs
John
"Darin" wrote:

> I am trying to get an XP machine to insert data into a linked Windows
> 2003 SBS Premium machine. I have done it w/ XP to 2003 but the SBS seems
> to have a different Security Configuration on the MSDTC tab in the
> COmponent Services. There are no options for inbound or outbound, only:
> Network DTC access (checked)
> Network Administration (not checked)
> Network Transactions (not checked)
> Network Clients (not checked)
> Transaction Internet Protocol (TIP) Transactions (not checked)
> XA Transactions (not checked)
> I have done the Application Server option for Network DTC Access.
> Any ideas why the screen is different and what I can do to fix?
> The server properties say:
> Microsoft Windows Server 2003
> for Small Business Server
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>

Thursday, March 8, 2012

Complicated Connection Problem between ADP and SQL Server

About 3 years ago, I was told to create a SQL Server 2000 database for a
client of ours, and to set up an Access XP/2002 project (adp) front end that
would be used on their network to interact with the data. This database
needs to be updated every six months. Since there has been some "feature
creep" in the front end, I have almost always needed to install a new adp
front end on the 5-8 workstations where the users happen to sit.
I'm not a network guy by any means. This client has thousands of
workstations in at least two different building, so they provided all the
expertise for getting the user workstations talking to the SQL Server. Most
of the time, I just walk in with a back-up of the new database, restore it
onto their server, slap a new front end on the 5-8 workstations that will
need to work with the database, and off I go.
The very first time that we ever did this installation, there were some
problems getting the workstations to talk to the server. The guy I was
working with did some magic on the workstations and the connections worked
just fine after that. Then he moved onto a new job, and they assigned
someone else to work with me during my twice-yearly visits. All of the
workstations that had been used before would work just fine, the connections
would go thorugh without any effort on our part, and the installation was
easy. But whenever one of the workers had been given a new computer, the
connection would fail and the adp wouldn't be able to talk to the server.
This wasn't a problem, though, since the guy I was working with also seemed
to know what to do. He would get on the workstation, do his magic, the
connection would be established, and that workstation would never again
cause us any problems on any future vists.
This week I made my latest visit to the client's offices. The guy I had
been working with us died unexpectedly a few months ago, and nobody there
seems to have any idea what he did to make these virgin machines -- the ones
that had never been used to work with this database in the past -- talk to
the SQL Server. And of course, two of the five users that need to work
with the database have recently received new computers.
Since I don't have anyone there who can fix the problem on their end, and
since it was probably sloppy of me to require them to alter their
workstation setup in the first place, I was wondering whether the problem
might be in my ADO connection string. Maybe I could use a different string
that would connect just fine, without any requirement to change any settings
on the workstation.
The SQL Server at the client uses mixed-mode security. My project opens up
with a custom login form to get the username and password. Then it calls
the following function to establish the connection for the project. Note
that I first open up a generic ADO connection (cnnTest) to the database to
make sure that it works and to trap any errors that might come up. Then, if
that test connection works, I go ahead and call
CurrentProject.OpenConnection using the same string.
-- BEGIN VBA CODE --
Public Function EstablishConnection(ByVal user As String, _
ByVal password As String, _
Optional ByVal displayWarnings As
Boolean = True, _
Optional ByVal finalTry As Boolean =
False) As Boolean
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim cnnTest As ADODB.Connection
Call DoCmd.Hourglass(True)
strServer = SERVER_NAME
strDatabase = "PbcPrimary"
strConnect = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial
Catalog=" & strDatabase & _
";Persist Security Info=FALSE"
Set cnnTest = New ADODB.Connection
On Error GoTo LoginFailure
Call cnnTest.Open(strConnect, user, password)
On Error GoTo 0
Set cnnTest = Nothing
'If things have progressed to this point, then cnnTest has been successfully
established. Switch the
'CurrentProject's connection to this new connection.
Call CurrentProject.OpenConnection(strConnect, user, password)
EstablishConnection = True
Call DoCmd.Hourglass(False)
Exit Function
' - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - -
LoginFailure:
Call DoCmd.Hourglass(False)
If displayWarnings Then
If Err.Number = -2147217843 Then
If Not finalTry Then
Call MsgBox("The user name and password that you entered are not
valid. Please " & _
"check your entries and try again.", vbExclamation, "Login Failed")
Else
Call Fatal("The user names and passwords that you have entered were
not valid. If " & _
"you have forgotten your user name or password, please see the
local JRC site manager.", _
"Shutting Down")
End If
ElseIf Err.Number = -2147467259 And LTest(Err.Description, "Cannot open
database") Then
'Note that you get this error for at least two conditions. First,
this error occurs if the server is found,
'but the database cannot be. Secondly, it occurs if the user's login
exists on the server, but the login
'has not been given permission to access the database. Since the
first situation seems more likely, that is
'the problem that is being described in the MsgBox.
Call Fatal("The reconciliation database could not be found on the
database server.", _
"Database Missing")
ElseIf Err.Number = -2147467259 Then
Call Fatal("The " & strServer & " database server could not be located
on the " & _
"network.", "Server Not Found")
Else
Call Unexpected("frmLogin, cmdOK_Click", Err.Number, Err.Description,
"trying to " & _
"connect to the reconciliation database", "Connection Failed")
End If
End If
EstablishConnection = False
End Function
-- END VBA CODE --
As I said, this code works just fine on any of the "experienced" machines,
but the new ones don't like it. On the "virgin" workstations, I get an
error -2147467259, [DBNMPNTW]ConnectionOpen (CreateFile()). Because of my
error-handling routine, this gets reported to the user as "The database
server could not be located on the network".
I did some research on this error. It has something to do with "named
pipes", and I'm sure many of you actually understand what it means. But I'm
not a network guy. Moreover, this isn't my server. I can't just go into
the server and change the settings to allow the use of named pipes. So I
thought that I should instead change my connection string to something that
the server and the network would accept.
I created a brand new adp on one of the virgin workstations, binded it to
the target database on the SQL server, and found that I was indeed able to
connect to the server and the database without any problem at all from
within this new project. When I checked the connection string of this bound
project, I found the following:
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=ClientServerName;User ID=MyUserName;Initial Catalog=PbcPrimary;Data
Provider=SQLOLEDB.1
This string has me puzzled a bit. I don't understant why what I thought was
the "Provider" has now become the "Data Provider", and I really don't
undertand why the provider is now something that is native to Access iteslf
instead of the SQL Server OLEDB provider. But what was even stranger was
what I found when I checked the BaseConnectionString property of the bound
project:
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL
CATALOG=PbcPrimary;DATA SOURCE=ClientServerName;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstationName
Even after reading the on-line help file, I still don't really understand
what the difference is between CurrentProject.BaseConnectionString and
CurrentProject.Connection.ConnectionString. Maybe someone here can
enlighten me.
But here is where things really get weird. When I copied the working
connection string from the bound project into my own project, it still
didn't work. When I checked all of the connection properties (under the
File menu) of the working bound project, I found that it was using a
different network library. I can't remember which one specifially, but it
definitely wasn't "DBNMPNTW" and I think it was probably "DBMSSOCN".
And now things get really strange. I added in the command "Network
Library=DBMSSOCN" into my connection string. When I did that, my inital
test ADO connection (cnnTest) worked just fine. Success! But when the
program then tried to use the exact same connection string in the
CurrentProject.OpenConnection method, I got an entirely new error. Since
I'm no longer at the client site, I don't have the exact text of the error,
but it was something along the lines of "-2147467259 (8004005) Client unable
to establish connection".
Why would a regular ADO connection work, but the project connection fail?
Does anyone know of some connection string settings that might work here?
Alternately, maybe somebody knows or can guess what kind of workstation
magic was done to allow the connection on the "experienced" computers. We
did find one thing. All of the older machines have a DSN which points
directly at the SQL Server. But the newer machines have no such DSN. So I
think it has something to do with the pressence of this DSN on the older
machines. But we couldn't create a DSN on the newer machines. We got the
same "Client unable to establish connection" error when we tried. So whle
the DSN might be necessary, it's also the case that something else has to be
done before the DSN can be created. Then again, the connection string I'm
using is DSN-less, so maybe the DSNs were just there because the guy who
fixed the workstations for me used DSNs to make sure his fix was working.
First of all, have you checked out the name resolution for the server
names you're using? If named pipes aren't doing the trick (and I don't
know much about them) then perhaps making sure the name can resolve to
an IP address could help. Also, for testing purposes, use the IP
address instead of the server name and see if that helps.
Second, I'm not sure if this will help, but... here are some connection
strings that work for me with an ADP connection to a SQL 2000 database.
?currentproject.BaseConnectionString
PROVIDER=SQLOLEDB.1;DATA SOURCE=TheServerName;USER
ID=MyUserName;PASSWORD=MyPassword;INITIAL CATALOG=TheDatabase
?currentproject.Connection.ConnectionString
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=TheServerName;User ID=MyUserName;Password=MyPassword;Initial
Catalog=TheDtabase;Data Provider=SQLOLEDB.1
In this case TheServerName is a name that can be found by pinging or
doing a Start->Run "\\MyServerName".
I also have a SQL Server Client Network tool which lets me use any
stinking name I want... or you can host->IP into the "hosts." file
(commonly found at windows\system32\etc\drivers\hosts or something.).
I hope this helps.
Oh, another thing that might help. In your ADP, go to
Tools->Options->Pages and click "Use Default Connection File" then
Browse. Start with the New Source button before you start messing with
existing odc or udl files. Perhaps the wizard will help you come up
with something.
And, I'm sure you've also gone to File->Connection and played with the
settings there?
Once you have a connection that works, you can go to the immediate pane
in the VBE IDE and print out the connection strings. That will help you
learn what you need to build your own connection strings.
Erik
ESquared
ESquared's Profile: http://www.dbtalk.net/m3
View this thread: http://www.dbtalk.net/t285429

Complicated Connection Problem between ADP and SQL Server

About 3 years ago, I was told to create a SQL Server 2000 database for a
client of ours, and to set up an Access XP/2002 project (adp) front end that
would be used on their network to interact with the data. This database
needs to be updated every six months. Since there has been some "feature
creep" in the front end, I have almost always needed to install a new adp
front end on the 5-8 workstations where the users happen to sit.
I'm not a network guy by any means. This client has thousands of
workstations in at least two different building, so they provided all the
expertise for getting the user workstations talking to the SQL Server. Most
of the time, I just walk in with a back-up of the new database, restore it
onto their server, slap a new front end on the 5-8 workstations that will
need to work with the database, and off I go.
The very first time that we ever did this installation, there were some
problems getting the workstations to talk to the server. The guy I was
working with did some magic on the workstations and the connections worked
just fine after that. Then he moved onto a new job, and they assigned
someone else to work with me during my twice-yearly visits. All of the
workstations that had been used before would work just fine, the connections
would go thorugh without any effort on our part, and the installation was
easy. But whenever one of the workers had been given a new computer, the
connection would fail and the adp wouldn't be able to talk to the server.
This wasn't a problem, though, since the guy I was working with also seemed
to know what to do. He would get on the workstation, do his magic, the
connection would be established, and that workstation would never again
cause us any problems on any future vists.
This week I made my latest visit to the client's offices. The guy I had
been working with us died unexpectedly a few months ago, and nobody there
seems to have any idea what he did to make these virgin machines -- the ones
that had never been used to work with this database in the past -- talk to
the SQL Server. And of course, two of the five users that need to work
with the database have recently received new computers.
Since I don't have anyone there who can fix the problem on their end, and
since it was probably sloppy of me to require them to alter their
workstation setup in the first place, I was wondering whether the problem
might be in my ADO connection string. Maybe I could use a different string
that would connect just fine, without any requirement to change any settings
on the workstation.
The SQL Server at the client uses mixed-mode security. My project opens up
with a custom login form to get the username and password. Then it calls
the following function to establish the connection for the project. Note
that I first open up a generic ADO connection (cnnTest) to the database to
make sure that it works and to trap any errors that might come up. Then, if
that test connection works, I go ahead and call
CurrentProject.OpenConnection using the same string.
-- BEGIN VBA CODE --
Public Function EstablishConnection(ByVal user As String, _
ByVal password As String, _
Optional ByVal displayWarnings As
Boolean = True, _
Optional ByVal finalTry As Boolean =
False) As Boolean
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim cnnTest As ADODB.Connection
Call DoCmd.Hourglass(True)
strServer = SERVER_NAME
strDatabase = "PbcPrimary"
strConnect = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial
Catalog=" & strDatabase & _
";Persist Security Info=FALSE"
Set cnnTest = New ADODB.Connection
On Error GoTo LoginFailure
Call cnnTest.Open(strConnect, user, password)
On Error GoTo 0
Set cnnTest = Nothing
'If things have progressed to this point, then cnnTest has been successfully
established. Switch the
'CurrentProject's connection to this new connection.
Call CurrentProject.OpenConnection(strConnect, user, password)
EstablishConnection = True
Call DoCmd.Hourglass(False)
Exit Function
' - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - -
LoginFailure:
Call DoCmd.Hourglass(False)
If displayWarnings Then
If Err.Number = -2147217843 Then
If Not finalTry Then
Call MsgBox("The user name and password that you entered are not
valid. Please " & _
"check your entries and try again.", vbExclamation, "Login Failed")
Else
Call Fatal("The user names and passwords that you have entered were
not valid. If " & _
"you have forgotten your user name or password, please see the
local JRC site manager.", _
"Shutting Down")
End If
ElseIf Err.Number = -2147467259 And LTest(Err.Description, "Cannot open
database") Then
'Note that you get this error for at least two conditions. First,
this error occurs if the server is found,
'but the database cannot be. Secondly, it occurs if the user's login
exists on the server, but the login
'has not been given permission to access the database. Since the
first situation seems more likely, that is
'the problem that is being described in the MsgBox.
Call Fatal("The reconciliation database could not be found on the
database server.", _
"Database Missing")
ElseIf Err.Number = -2147467259 Then
Call Fatal("The " & strServer & " database server could not be located
on the " & _
"network.", "Server Not Found")
Else
Call Unexpected("frmLogin, cmdOK_Click", Err.Number, Err.Description,
"trying to " & _
"connect to the reconciliation database", "Connection Failed")
End If
End If
EstablishConnection = False
End Function
-- END VBA CODE --
As I said, this code works just fine on any of the "experienced" machines,
but the new ones don't like it. On the "virgin" workstations, I get an
error -2147467259, [DBNMPNTW]ConnectionOpen (CreateFile()). Because of
my
error-handling routine, this gets reported to the user as "The database
server could not be located on the network".
I did some research on this error. It has something to do with "named
pipes", and I'm sure many of you actually understand what it means. But I'm
not a network guy. Moreover, this isn't my server. I can't just go into
the server and change the settings to allow the use of named pipes. So I
thought that I should instead change my connection string to something that
the server and the network would accept.
I created a brand new adp on one of the virgin workstations, binded it to
the target database on the SQL server, and found that I was indeed able to
connect to the server and the database without any problem at all from
within this new project. When I checked the connection string of this bound
project, I found the following:
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=ClientServerName;User ID=MyUserName;Initial Catalog=PbcPrimary;Data
Provider=SQLOLEDB.1
This string has me puzzled a bit. I don't understant why what I thought was
the "Provider" has now become the "Data Provider", and I really don't
undertand why the provider is now something that is native to Access iteslf
instead of the SQL Server OLEDB provider. But what was even stranger was
what I found when I checked the BaseConnectionString property of the bound
project:
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL
CATALOG=PbcPrimary;DATA SOURCE=ClientServerName;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=MyWorkstationName
Even after reading the on-line help file, I still don't really understand
what the difference is between CurrentProject.BaseConnectionString and
CurrentProject.Connection.ConnectionString. Maybe someone here can
enlighten me.
But here is where things really get weird. When I copied the working
connection string from the bound project into my own project, it still
didn't work. When I checked all of the connection properties (under the
File menu) of the working bound project, I found that it was using a
different network library. I can't remember which one specifially, but it
definitely wasn't "DBNMPNTW" and I think it was probably "DBMSSOCN".
And now things get really strange. I added in the command "Network
Library=DBMSSOCN" into my connection string. When I did that, my inital
test ADO connection (cnnTest) worked just fine. Success! But when the
program then tried to use the exact same connection string in the
CurrentProject.OpenConnection method, I got an entirely new error. Since
I'm no longer at the client site, I don't have the exact text of the error,
but it was something along the lines of "-2147467259 (8004005) Client unable
to establish connection".
Why would a regular ADO connection work, but the project connection fail?
Does anyone know of some connection string settings that might work here?
Alternately, maybe somebody knows or can guess what kind of workstation
magic was done to allow the connection on the "experienced" computers. We
did find one thing. All of the older machines have a DSN which points
directly at the SQL Server. But the newer machines have no such DSN. So I
think it has something to do with the pressence of this DSN on the older
machines. But we couldn't create a DSN on the newer machines. We got the
same "Client unable to establish connection" error when we tried. So whle
the DSN might be necessary, it's also the case that something else has to be
done before the DSN can be created. Then again, the connection string I'm
using is DSN-less, so maybe the DSNs were just there because the guy who
fixed the workstations for me used DSNs to make sure his fix was working.First of all, have you checked out the name resolution for the server
names you're using? If named pipes aren't doing the trick (and I don't
know much about them) then perhaps making sure the name can resolve to
an IP address could help. Also, for testing purposes, use the IP
address instead of the server name and see if that helps.
Second, I'm not sure if this will help, but... here are some connection
strings that work for me with an ADP connection to a SQL 2000 database.
?currentproject.BaseConnectionString
PROVIDER=SQLOLEDB.1;DATA SOURCE=TheServerName;USER
ID=MyUserName;PASSWORD=MyPassword;INITIA
L CATALOG=TheDatabase
?currentproject.Connection.ConnectionString
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=TheServerName;User ID=MyUserName;Password=MyPassword;Initia
l
Catalog=TheDtabase;Data Provider=SQLOLEDB.1
In this case TheServerName is a name that can be found by pinging or
doing a Start->Run "\\MyServerName".
I also have a SQL Server Client Network tool which lets me use any
stinking name I want... or you can host->IP into the "hosts." file
(commonly found at windows\system32\etc\drivers\hosts or something.).
I hope this helps.
Oh, another thing that might help. In your ADP, go to
Tools->Options->Pages and click "Use Default Connection File" then
Browse. Start with the New Source button before you start messing with
existing odc or udl files. Perhaps the wizard will help you come up
with something.
And, I'm sure you've also gone to File->Connection and played with the
settings there?
Once you have a connection that works, you can go to the immediate pane
in the VBE IDE and print out the connection strings. That will help you
learn what you need to build your own connection strings.
Erik
ESquared
---
ESquared's Profile: http://www.dbtalk.net/m3
View this thread: http://www.dbtalk.net/t285429

Thursday, March 29, 2012

Concatenate Lname and Fname Columns

Sunday, March 25, 2012

computer name access sql server

computer name access sql server

computer name access sql server

Thursday, March 22, 2012

Computed Column Formulas

Computed Column Formulas

Computed Column Formulas

computed column

Monday, March 19, 2012

component services, DTC network access

component services, DTC network access

Thursday, March 8, 2012

Complicated Connection Problem between ADP and SQL Server

Complicated Connection Problem between ADP and SQL Server

Sql Comparing two tables

Blog Archive

About Me