Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work

Displaying NULLS but not Blanks

Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the blan
k
value?
--
pmudTry:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND BRAND <> ''
ORDER BY BRAND ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:AC2C98C0-9DCD-430C-BA97-A50C60FEEE6C@.microsoft.com...
Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the
blank
value?
--
pmud|||SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T' AND BRAND <> ''
ORDER BY BRAND ASC
"pmud" wrote:

> Hi,
> I am using the followng query:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> ORDER BY BRAND ASC
> In all the data displayed by this query, there is also a Null value and a
> Blank value. Is there any way, I can display the null value but not the bl
ank
> value?
> --
> pmud|||Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the fiel
d
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Please post your DDL with INSERT statements of the sample data and desired
output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9D434774-65A1-4C29-8415-23309F77BF08@.microsoft.com...
Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the
field
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Try
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND COALESCE(BRAND,'this value is null') <> ''
ORDER BY BRAND ASC
HTH,
Stu|||Try this also
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND ltrim(rtrim(BRAND)) <> ''
ORDER BY BRAND ASC|||Hi Stu,
This works. :)) can you please explain how it works...
Thanks
--
pmud
"Stu" wrote:

> Try
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> AND COALESCE(BRAND,'this value is null') <> ''
> ORDER BY BRAND ASC
> HTH,
> Stu
>|||Sure!
COALESCE looks at a given list of values or columns, and returns the
first NON-null expression, so for every row it looks at BRAND to
determine if it's null, ad if it's not then it returns the absolute
expression 'this value is null'. You could have put any absolute
expression in there (e.g., 'whats up Doc?'), and it would have returned
that value when it encountered a NULL in the BRAND column. This
allowed SQL Server to compare a replacement value ('this value is
null') with an empty string, instead of comparing a NULL value to an
empty string, eg.
NULL <> '' = NULL; not included in the result set
'this value is null' <> '' = TRUE; included in the result set
Since the COALESCE was in the WHERE clause, and not the SELECT clause,
the SELECT returned the value where the second expression in the
COALESCE statement was used (NULL).
Boy, I read that 5 times, and I don't think I can make it any clearer.
Perhaps someone else can explain it better than I; I just know how to
make it work :)
Stu|||Hi Stu,
Thanks for taking the time to explain it. it was helpful. Combined with what
you explained, I read on Coalsce and I understand it now. :)) .. I am sure
had I read it without u explaining it, I would have never understod.
Thanks
--
pmud
"Stu" wrote:

> Sure!
> COALESCE looks at a given list of values or columns, and returns the
> first NON-null expression, so for every row it looks at BRAND to
> determine if it's null, ad if it's not then it returns the absolute
> expression 'this value is null'. You could have put any absolute
> expression in there (e.g., 'whats up Doc?'), and it would have returned
> that value when it encountered a NULL in the BRAND column. This
> allowed SQL Server to compare a replacement value ('this value is
> null') with an empty string, instead of comparing a NULL value to an
> empty string, eg.
> NULL <> '' = NULL; not included in the result set
> 'this value is null' <> '' = TRUE; included in the result set
> Since the COALESCE was in the WHERE clause, and not the SELECT clause,
> the SELECT returned the value where the second expression in the
> COALESCE statement was used (NULL).
> Boy, I read that 5 times, and I don't think I can make it any clearer.
> Perhaps someone else can explain it better than I; I just know how to
> make it work :)
> Stu
>

display on duplicate records

I have the following query:
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
ORDER BY ProductCode, BillingCustID, BillingPeriod
which returns the following columns:
BillingPeriod CustomerID ProductCode BillingCustID
-- -- -- --
200502 205022338 APX 79110
200503 205022338 APX 79110
200504 205022338 BRW 79110
200505 205022338 BRW 79110
200506 205027355 APX 79110
200506 205022338 APX 79110
200507 205027355 BRW 79110
200507 205022338 BRW 79110
As you can see from the result, there are duplicates under the billingperiod
column for the same billingcustID. Essentially, there should only ever be on
e
CustomerID associated with the same ProductCode and BillingCustID and
BillingPeriod.
How can I rewrite my query to only display only the duplicates?
Thanks for your help in advance.Try this...
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress A
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
AND EXISTS (
SELECT 1 FROM dbo.cbt_BillingAddress B
WHERE A.BillingCustID = B.BillingCustID
AND A.ProductCode = B.ProductCode
AND A.BillingPeriod = B.BillingPeriod
AND A.CustomerID <> B.CustomerID)
ORDER BY ProductCode, BillingCustID, BillingPeriod
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> I have the following query:
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> which returns the following columns:
> BillingPeriod CustomerID ProductCode BillingCustID
> -- -- -- --
> 200502 205022338 APX 79110
> 200503 205022338 APX 79110
> 200504 205022338 BRW 79110
> 200505 205022338 BRW 79110
> 200506 205027355 APX 79110
> 200506 205022338 APX 79110
> 200507 205027355 BRW 79110
> 200507 205022338 BRW 79110
> As you can see from the result, there are duplicates under the
billingperiod
> column for the same billingcustID. Essentially, there should only ever be
one
> CustomerID associated with the same ProductCode and BillingCustID and
> BillingPeriod.
> How can I rewrite my query to only display only the duplicates?
> Thanks for your help in advance.|||Awesome. Thanks for your help. One last question: what is the significance o
f
the statement:
SELECT 1...
What does this do and where can I get more info on its usage. Thanks again.
"Jim Underwood" wrote:

> Try this...
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress A
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> AND EXISTS (
> SELECT 1 FROM dbo.cbt_BillingAddress B
> WHERE A.BillingCustID = B.BillingCustID
> AND A.ProductCode = B.ProductCode
> AND A.BillingPeriod = B.BillingPeriod
> AND A.CustomerID <> B.CustomerID)
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> billingperiod
> one
>
>|||Honestly, I am not sure if this serves a purpose in SQL Server, but I use it
out of habit from my Oracle 7/8 experience.
where exists (Select 1 from table1 where column1 = 'MyData')
is functionally the same as
where exists (Select column1 from table1 where column1 = 'MyData')
It simply verifies that a row exists in either case. I use the literal 1
for performance reasons.
In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
than selecting a value from a table. You could select a character ('x' for
example) but I was always told using a number was more efficient than a
character. Essentially, you don't need to retrieve a data value from disk
or memory, save what you use in your where clause. The SQL engine can
evaluate the where clause without returning any data in the process.
SQL server may very well ignore the select columns in an exist clause, I
really don't know. Maybe someone with more experience can validate or
correct me on this point.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:0155F28A-3A76-4159-8157-EFC6E2A45C0E@.microsoft.com...
> Awesome. Thanks for your help. One last question: what is the significance
of
> the statement:
> SELECT 1...
> What does this do and where can I get more info on its usage. Thanks
again.
>
> "Jim Underwood" wrote:
>
be|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uCH2N0pJGHA.984@.tk2msftngp13.phx.gbl...
> Honestly, I am not sure if this serves a purpose in SQL Server, but I use
> it
> out of habit from my Oracle 7/8 experience.
> where exists (Select 1 from table1 where column1 = 'MyData')
> is functionally the same as
> where exists (Select column1 from table1 where column1 = 'MyData')
> It simply verifies that a row exists in either case. I use the literal 1
> for performance reasons.
> In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
> than selecting a value from a table. You could select a character ('x'
> for
> example) but I was always told using a number was more efficient than a
> character. Essentially, you don't need to retrieve a data value from disk
> or memory, save what you use in your where clause. The SQL engine can
> evaluate the where clause without returning any data in the process.
> SQL server may very well ignore the select columns in an exist clause, I
> really don't know. Maybe someone with more experience can validate or
> correct me on this point.
I'm sure that I don't have more experience but from what I've seen in this
newsgroup,
select *
is the norm in Exists clauses.