How can I convert a date and an amount in my select statement

I need to convert a date like 08/1/2009 to 0809

I also need to show currency as 100.00 and not 100.0000

How can I do these in a select statement?

SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment

I appreciate any help!

hi Jackxxx,

can you try this

SELECT convert(varchar,datepart(dd,getdate()))+convert(varchar,datepart(yy,getdate())) AS ExpirationDate, convert(decimal(10,2), 2323.2422)

thanks,

Satish.

|||

I tried the expiration date and the date was 7/1/2009 and your statement returned 172007.

Also I goofed on the other the field name is AmountPaid that I need to show 100.00 for.

|||

hi Jackxxx,

what i gave was an example you need to modify your actuall query accordingly like i've put getdate() so you need to put your datetime field in there similary amount field also.

thanks,

satish,

|||

It's almost perfect, the date still shows all for digits of the year. Is there a way to only show the last two digits? Like 09 for 2009

I very much appreciate your help!

|||

hi jackxxx,

i tried alot but its giving 4 digits atlast i had to cheat, use

select right(datepart(yy,getdate()),2)

hope it works now.

regards,

satish.

How can I combine three fields together of dif datatypes

I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?

(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)

Select Item_Description&' '& Item_Cost&' '& Is_ActiveAsDescriptionFROM tblItemList

I tried Casting this but same incompatable message.

Select Item_Description&' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) AsDescriptionFROM tblItemList

What I'm hoping to end up with is:

Brake Pedal, $36.00, True

Did you try using the + operator after casting?

Select
Item_Description + ' ' + CAST(Item_Cost AS NVARCHAR) +' '+ CAST(Is_Active AS NVARCHAR) As Description
FROM
tblItemList

What are the datatypes of the above fields?

|||

Ok, Scott, I tried the + again and it did work, however, can you point me in the direction of how to format the money to $36.00 and the bit to True or False. I would like to end up with:

Brake Pedal, $36.00, True

|||

SELECT
Item_Description+', $'+Cast(Item_Costasnvarchar)+', '+ dbo.ufnGetBoolean(Is_Active)
FROM
tblItemList

The ufnGetBoolean function is:

CREATEFUNCTION [dbo].[ufnGetBoolean](@.BitValue [bit])
RETURNSvarchar(5)
AS
-- Returns a string value based on the bit value passed
BEGIN
DECLARE @.retvarchar(5);
SET @.ret='false'

IF(@.BitValue= 1)
SET @.ret='true'

RETURN @.ret

END;

Let me know if that works.

How can I combine 2 SELECT statements into 1 ?

Please consider the following SELECT statements; is it possible to combine
them into a sole SELECT statement returning 4 columns ? Thank you.
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
--
Gilbert.HI
Use UNION or UNION ALL
Cheers
vinu
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Sorry but, as far as I know, with UNION I will get more lines with always 3
columns. This is not why I need. I need the same number of lines
(approximatively) with 4 columns.
--
Gilbert.
"vinu" wrote:

> HI
> Use UNION or UNION ALL
> Cheers
> vinu
>
> "Gilbert" <gilbert@.nospam.nospam> wrote in message
> news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
>
>|||Please post DDL, sample data and expected results.
You might also consider joining the two queries as derived tables on common
columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fool
you into thinking I have any idea what you really want. Stop us guessing and
explain your goals.
ML
http://milambda.blogspot.com/|||I thought that giving the SELECT statements was enough details; sorry. I
intend to do monthly statistics on a table storing user requests. The first
query gives the number of requests per month. The second query gives the
average delay of the answer per month, for all requests that are closed (thi
s
is why there is a WHERE clause). If I execute 2 independent SELECT statement
s
I receive 2 tables with 3 columns, each line displaying the value for a
specific month. What I would like to do is to execute only 1 SELECT, giving
me 1 table of 4 columns (year, month, number of requests, and average delay)
,
each line displaying the values for a specific month. Thank you for your hel
p.
--
Gilbert.
"ML" wrote:

> Please post DDL, sample data and expected results.
> You might also consider joining the two queries as derived tables on commo
n
> columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fo
ol
> you into thinking I have any idea what you really want. Stop us guessing a
nd
> explain your goals.
>
> ML
> --
> http://milambda.blogspot.com/|||Ok, I see. But we still need to understand how the data in the two queries
can be joined to form one valid result-set.
That's why we need to see the DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||OK. Here is the DDL (simplified by hand to remove unuseful columns and
constraints) :
CREATE TABLE [InterventionDI] (
[NumIntervention] [int] IDENTITY (1, 1) NOT NULL ,
[DhEnregistrementIntervention] [datetime] NOT NULL CONSTRAINT
[DF_InterventionDI_DhEnregistrementInter
vention] DEFAULT (getdate()),
[DhClotureIntervention] [datetime] NULL ,
[CodEtatIntervention] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL,
CONSTRAINT [PK_InterventionDI] PRIMARY KEY NONCLUSTERED
(
[NumIntervention]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
Here is a set on 10 records (INSERT created by hand) :
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (18, '13/09/2002 09:52:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (19, '13/09/2002 09:53:18', '21/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (20, '14/09/2002 09:54:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (21, '14/09/2002 09:55:18', '15/09/2002 16:53:57', 'O')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (22, '14/09/2002 09:56:18', '15/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (23, '14/09/2002 09:57:18', '18/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (24, '13/10/2002 09:58:18', '20/09/2003 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (25, '13/10/2002 09:59:18', '20/09/2004 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (26, '13/01/2003 09:12:18', '20/09/2005 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (27, '13/01/2003 09:22:18', '20/09/2006 16:53:57', 'F')
Thank you.
--
Gilbert.
"ML" wrote:

> Ok, I see. But we still need to understand how the data in the two queries
> can be joined to form one valid result-set.
> That's why we need to see the DDL and sample data:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
> --
> http://milambda.blogspot.com/|||Based on what the two queries have in common this might give the correct
result:
select Demande.Anee
,Demande.Mois
,Demande.ItvQteDemande
,Duree.ItvDuree
from (
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Demande (Anee, Mois, ItvQteDemande)
inner join (
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Duree (Anee, Mois, ItvDuree)
on (Duree.Anee = Demande.Anee)
and (Duree.Mois = Demande.Mois)
order by Demande.Anee
,Demande.Mois
Please, validate this against a sufficient amount of cases before using it
in production.
ML
http://milambda.blogspot.com/|||UNTESTED
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
, (
case when CodEtatIntervention = 'F' then
ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0)
ELSE NULL END
)AS ItvDuree
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Wonderful ! Thank you for having teached me something new.
--
Gilbert.
"ML" wrote:

> Based on what the two queries have in common this might give the correct
> result:
> select Demande.Anee
> ,Demande.Mois
> ,Demande.ItvQteDemande
> ,Duree.ItvDuree
> from (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Demande (Anee, Mois, ItvQteDemande)
> inner join (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Duree (Anee, Mois, ItvDuree)
> on (Duree.Anee = Demande.Anee)
> and (Duree.Mois = Demande.Mois)
> order by Demande.Anee
> ,Demande.Mois
> Please, validate this against a sufficient amount of cases before using it
> in production.
>
> ML
> --
> http://milambda.blogspot.com/sql

How can I check for Null or Empty in an Insert/Select Statement - example in Access

The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql

How can I check for Null or Empty in an Insert/Select Statement

Is there a way in the following Query to check if the _vocon.COMPANY is
null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
statement 2) as each record is processed. I would like to use something like
IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id +
10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
If the company name is null
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
'01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003Hi
You don't give the version of SQLServer, it is also better to post DDL
(Create table statements etc...), example data (as Insert statements) to
avoid ambiguities.
The behaviour of concatenating with null can be set with the
CONCAT_NULL_YIELDS_NULL option see:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_set-set_2z8s.htm
To update your values you could use:
UPDATE _vocon
SET Company = Id + 10200000
WHERE Company IS NULL
OR LEN(RTRIM(Company)) = 0
John
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uqveIhNzDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Is there a way in the following Query to check if the _vocon.COMPANY is
> null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
> statement 2) as each record is processed. I would like to use something
like
> IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id
+
> 10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM
_vocon
> If the company name is null
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
> '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
> Regards
> Jeff
>
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003
>

How can I bind to an SqlDataSource from Code

My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this?

Me.SqlDataSourceSearchResult.ConnectionString ="ConnectStr"Me.SqlDataSourceSearchResult.SelectCommand ="SelectStatement"

gvSearchResult.DataSource =

Me.SqlDataSourceSearchResult

gvSearchResult.DataBind()

Hello Jackxxx,

If you are going to be doing this in code-behind, I would not bother with the SqlDataSource control. I would bind the data directly to the destination control as follows: -

Dim conn As New SqlConnection("YourConnectionString")
Dim command As New SqlCommand("SELECT * FROM YourTable", conn)
Dim da As New SqlDataAdapter(command)
Dim tblData As New DataTable
da.Fill(tblData)
conn.close()

gvSearchResult.DataSource = tblData
gvSearchResult.DataBind()

Also add the following 2 lines at the top of the code behind if they are not there already: -

Imports System.Data.SqlClient
Imports System.Data

Kind regards

Scotty

|||

Scotty,

I was trying to use the sqldatasource so that I could some how use its built in sort functionality.

|||

Hello Jackxxx,

I see now. In that case, do the following after populating tblData: -

Dim dvData as new DataView(tblData)

dvData.Sort = "MyField DESC" ' you can also apply filtering on a DataView

mygrid.DataSource = dvData ' binds to the DataView, not the DataTable

mygrid.DataBind()

Kind regards

Scotty

|||

I'm really hoping to find a way to bind to the SqlDataSource so that I can take advantage of sorting and paging for multiple fields without all the extra coding.

There must be a way.

|||

Hi Jack,

Suppose your SqlDataSource returns a DataSet, you can

mygrid.DataSource = sqlDatasource.Select()
mygrid.DataBind()

However, if you need to take advantage of sorting and paging, I suggest you bind with the designer.

HostName Function with Access2K front end

In an Insert Into statement I have used the Host_Name() function to
identify which user has suppied a record to a table that holds
temporary data.
I'm using an Access2K front end.

Code:
Alter procedure SPName
@.parameter1 int
AS
Set nocount On
Set xact_abort off

Declare @.myHost nvarchar(50)
Set @.myHost = Host_Name()

Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
myMachineName)
SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
FROM tblMyName
WHERE tblMyName.UniqueID = @.parameter1

I have two problems.
In some cases (and only on one or two of maybe about 250 client
workstations) Host_Name() returns the name of MY machine. I'm thinking
this is because I developed the app and distributed it and for some
reason it's retaining the info contained in my original connection
setup set in the MS-Access Connection window?

Also, on some occasions, I get blocking messages in my trace log
following this operation.

Any help on these two issues is appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404100634.3ab98872@.posting.google.c om...
> In an Insert Into statement I have used the Host_Name() function to
> identify which user has suppied a record to a table that holds
> temporary data.
> I'm using an Access2K front end.
> Code:
> Alter procedure SPName
> @.parameter1 int
> AS
> Set nocount On
> Set xact_abort off
> Declare @.myHost nvarchar(50)
> Set @.myHost = Host_Name()
> Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
> myMachineName)
> SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
> FROM tblMyName
> WHERE tblMyName.UniqueID = @.parameter1
> I have two problems.
> In some cases (and only on one or two of maybe about 250 client
> workstations) Host_Name() returns the name of MY machine. I'm thinking
> this is because I developed the app and distributed it and for some
> reason it's retaining the info contained in my original connection
> setup set in the MS-Access Connection window?
> Also, on some occasions, I get blocking messages in my trace log
> following this operation.
> Any help on these two issues is appreciated.
> lq

As a complete guess, there may be some network name resolution issue which
means that the server sometimes resolves workstation names incorrectly. You
should be able to investigate this with your network admin if you can pin it
down to just a couple of machines.

As for the blocking issue, it's hard to say without more information, such
as the text of the errors.

Simon