I am developing a Smart Device project for my Symbol MC9000 Windows Mobile
handheld. I am trying to make a simple connection to a SQL server. My proble
m
is my Data Adapter object does not have a .FILL method as it always has in
normal C# desktop apps.
I also noticed I cannot do a dataadapter object.dispose() either (i.e da.
Dispose(); )
Am I missing something or is this development environment for mobile just
that limited?
The intellisense only shows the following methods/properties for the da
object.
.DeleteCommand, .InsertCommand, .RowUpdated, .RowUpdating, .SelectCommand,
and .UpdateCommand.
That all it lists. Do I need to install something maybe?
I have never done a mobile app so this is all new to me.
Any help would be greatly appreciated.
Below is some of the code I am developing just to make the connection.
David
// Connection String for MSSQL Server
string Connection_String = "Initial Catalog=WINSTSDB;Data
Source=DB2ML350;workstation id=Symbol_MC9000w;packet size=4096;integrated
security=SSPI";
// Setup SQL Connection object
SqlConnection dbConn = new SqlConnection( Connection_String );
// Create Data Adapter object with SQL statement.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM RackTemp WHERE SID =
'" + strSidcode + "'",dbConn);
// Define a Data Table object
System.Data.DataTable WinstisTable = new DataTable();
try
{
// Pull in data into data table object named WinstisTable
conn.Open();
da.Fill( WinstisTable );
}
catch(Exception exc)
{
MessageBox.Show(exc.Message.ToString(),"Error",MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
finally
{
// Close connection
conn.Close();
}
Message posted via http://www.webservertalk.comDavid C via webservertalk.com wrote:
> I am developing a Smart Device project for my Symbol MC9000 Windows
> Mobile handheld. I am trying to make a simple connection to a SQL
> server. My problem is my Data Adapter object does not have a .FILL
> method as it always has in normal C# desktop apps.
>
You might be better off posting this to a csharp or adonet newgroup. It's
somewhat offtopic here, and you are less likely to get a response than you
would in a relevant dotnet group (all the .Net groups have the word "dotnet"
in their names).
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||I guess because it is about SQL Connectivity that it might apply here. I wil
l
post on DotNetForums.com to see if some may help me.
David
Bob Barrows [MVP] wrote:
>You might be better off posting this to a csharp or adonet newgroup. It's
>somewhat offtopic here, and you are less likely to get a response than you
>would in a relevant dotnet group (all the .Net groups have the word "dotnet
"
>in their names).
Message posted via http://www.webservertalk.com
Showing posts with label developing. Show all posts
Showing posts with label developing. Show all posts
Tuesday, March 20, 2012
Nightmare Query
It's probably a piece of cake for those in the know but it's proving hard
work for me...
Scenario: I developing a report for a system that managed a filter exchange
business. Basically filters are sent to/from customers to our business. I'm
trying to produce a management report which summarises for each customer
depot, which filter types (PartNo) have we sent them and which have they
sent back.
The desired report is probably best explained by example:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
[Yeah, it looks a bit crap on usenet, but it looks better if you pop it in
Excel]
As you can see not all PartNos may be listed - some may not have been sent
or received within the reporting time period. I dont want to show these
PartNos in such a case.
I have developed a query that lists the totals for each Depot for each
PartNo, eg.
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
etc
SQL:
Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTotal
from
(Select L.LocationID, S.PartNo, Count(*) as NumSent
from StockMovements S
inner join Locations L on L.LocationID = S.LocationTo
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Sent
join
(Select L.LocationID, S.PartNo, Count(*) as NumRecd
from StockMovements S
inner join Locations L on L.LocationID = S.LocationFrom
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Recd
on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
[Note: a 'Location' is a Customer Depot]
I'm not sure how to take this query on further to provide the end result.
I'm not sure if some quasi-temporary tables are needed to store some
intermediate data, or whether it can be acheived with other means (ie a
View).
Any suggestions?
Thanks in advance.
CJM
Trimmed DDL:
CREATE TABLE [dbo].[Locations] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[SerialNo] [int] NOT NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PartMaster] (
[PartID] [int] IDENTITY (1, 1) NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartType] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartDesc] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
[Note: In case it's not obvious, PartMaster contains details on all
available PartNos]
If anything else is missing let me know...
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]First problem i am having in looking at this that no PartNo in
[StockMovements] so you query should be failing based on what you told
us your table creates are becuase you are pulling PartNo from
[StockMovements]
What is the field that holds the part number in [StockMovements]? Could
you change [StockMovements] or is it used buy other items?|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138296034.089998.119680@.g43g2000cwa.googlegroups.com...
> First problem i am having in looking at this that no PartNo in
> [StockMovements] so you query should be failing based on what you told
> us your table creates are becuase you are pulling PartNo from
> [StockMovements]
> What is the field that holds the part number in [StockMovements]? Could
> you change [StockMovements] or is it used buy other items?
>
Sorry - Trimmed a bit too enthusiastically... PartNo *IS* in StockMovements
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[Ref] [int] NULL ,
[SerialNo] [int] NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL ,
) ON [PRIMARY]
GO|||In the two examples that you gave:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
and
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
are different.
what are the heading in the report?
Is this how you want the data back. IE: Location ID, Part Number, Parts
Sent Out, Part Received Back, Total|||On Thu, 26 Jan 2006 16:32:21 -0000, CJM wrote:
(snip)
>I have developed a query that lists the totals for each Depot for each
>PartNo, eg.
>C1D1, PartNo1, 1
>C1D1, PartNo28, -1
>C1D2, PartNo7, -10
>etc
>SQL:
>Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTota
l
> from
> (Select L.LocationID, S.PartNo, Count(*) as NumSent
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationTo
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Sent
> join
> (Select L.LocationID, S.PartNo, Count(*) as NumRecd
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationFrom
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Recd
> on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
>[Note: a 'Location' is a Customer Depot]
>I'm not sure how to take this query on further to provide the end result.
Hi CJM,
First, I note that this query will only include parts that are in both
the Sent and the Recd derived table for the same LocationID. If there is
no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
'123' and LocationFrom = 14 will be excluded from the results. If that's
not what you intended, you'll have to replace the JOIN with a FULL OUTER
JOIN and add a bunch of COALESCE functions on the first SELECT line.
Second, I wonder why the PartNo column is called a number but declared
as character, why it is not a key, and why it is used to reference parts
instead of the PartID column that IS a key.
Third, I recommend you to rewrite the query above to the (slightly)
simpler query below:
SELECT L.LocationID, P.PartNo,
COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID, P.PartNo
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Now you also wanted the part numbers to be columns instead of rows. That
is usually called a crosstab or pivot - and the best way to do it is to
transform the table on the client side.
If you have to do it on the server side, AND you're on SQL Server 2005,
you can also check out the new PIVOT operator. For SQL Server 2000,
google this group for "CROSSTAB" to find some common kludges. Note
however that all these crosstabl techniques require you to know the
columns in advance - and if I understand your question correctly, you
want to be able to dynamically include or exclude columns, based on the
query results.
I know of no supported way to create a dynamic crosstab on the server.
You might want to try your luck with dynamic SQL - but ungh!! You might
also want to investigate if there are third-party products that can help
you out. Rac4SQL is often recommended in this group - though admittedly
always by the same poster, who probably works for the company that sells
Rac4SQL. I have no personal experience with either Rac4SQL or any other
third party product for crosstabbing.
Hugo Kornelis, SQL Server MVP|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138297710.003578.172650@.g14g2000cwa.googlegroups.com...
> In the two examples that you gave:
> Customer, Depot, PartNo1, PartNo7, PartNo28, Total
> Cust1, C1D1, 1, null, -1, 0
> Cust1, C1D2, null, -10, null, -10
> Cust2, C2D1, 1, -2, -3, -4
> Cust3 C3D1, null, null, 1, 1
> Total, null, 2, -12, -3, -13
>
> and
> C1D1, PartNo1, 1
> C1D1, PartNo28, -1
> C1D2, PartNo7, -10
>
> are different.
The first example is the output I want, the second is the data that results
in that output. The columns for that data are: DepotID, PartNo,
'NetTotalSent' (ie Total sent to that depot - Total received from that
depot)
You can see that for Depot C1D1, they have received 1 x PartNo1 and returned
1 x PartNo28. The net total is therefore 0. [See line 1 of the example
output]
> what are the heading in the report?
The headings are on the first line: Customer, Depot, PartNo1, PartNo7,
PartNo28, Total. Depending on when you run the report you may have more or
less columns - it depends on which parts have been shipped/returned in that
time period.
> Is this how you want the data back. IE: Location ID, Part Number, Parts
> Sent Out, Part Received Back, Total
>
No - see above.
I realise that this is quite confusing; and I'm trying to trade-off between
swamping you with too much superfluous detail, and not providing enough to
mek it clear. I hope this somewhat clairifies it.
Thanks|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
> Hi CJM,
> First, I note that this query will only include parts that are in both
> the Sent and the Recd derived table for the same LocationID. If there is
> no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
> '123' and LocationFrom = 14 will be excluded from the results. If that's
> not what you intended, you'll have to replace the JOIN with a FULL OUTER
> JOIN and add a bunch of COALESCE functions on the first SELECT line.
>
TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
COALESCE functions that are needed? (And why?)
> Second, I wonder why the PartNo column is called a number but declared
> as character, why it is not a key, and why it is used to reference parts
> instead of the PartID column that IS a key.
Historical reasons. Many of these PartNos pre-date all our computer systems,
and I suspect some pre-date civilisation itself (as do some of the people in
charge here!)
Some PartNo's are indeed integers but most are alpha numerical. The PartID
was introduced to be a numerical key, but is under-used. In retrospect, I
would have stuck with PartNo as the key.
> Third, I recommend you to rewrite the query above to the (slightly)
> simpler query below:
>
It *is* simpler, and a lot neater too. I'll digest it (and probably use it
too - thanks)
Update: I've tried it and it works a treat (and the results are more like
what I was expecting)
> SELECT L.LocationID, P.PartNo,
> COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
> FROM Locations AS L
> CROSS JOIN PartMaster AS P
> LEFT JOIN StockMovements AS Sent
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationTo = L.LocationID
> LEFT JOIN StockMovements AS Recd
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationFrom = L.LocationID
> WHERE Sent.PartNo IS NOT NULL
> OR Recd.PartNo IS NOT NULL
> GROUP BY L.LocationID, P.PartNo
> (untested - see www.aspfaq.com/5006 if you prefer a tested solution)
> Now you also wanted the part numbers to be columns instead of rows. That
> is usually called a crosstab or pivot - and the best way to do it is to
> transform the table on the client side.
> If you have to do it on the server side, AND you're on SQL Server 2005,
> you can also check out the new PIVOT operator. For SQL Server 2000,
> google this group for "CROSSTAB" to find some common kludges. Note
> however that all these crosstabl techniques require you to know the
> columns in advance - and if I understand your question correctly, you
> want to be able to dynamically include or exclude columns, based on the
> query results.
>
Yeah, I've done cross-tabs in SQL before, but as you say, the columns need
to be fixed (defined in advance). But the specification for my report *does*
ask for dynamically included and excluded columns... hmmmm...
> I know of no supported way to create a dynamic crosstab on the server.
> You might want to try your luck with dynamic SQL - but ungh!! You might
> also want to investigate if there are third-party products that can help
> you out. Rac4SQL is often recommended in this group - though admittedly
> always by the same poster, who probably works for the company that sells
> Rac4SQL. I have no personal experience with either Rac4SQL or any other
> third party product for crosstabbing.
>
I've heard of these dynamic SQL techniques, but never actually used them.
I've always found a way around them (ie not using them), but I thought one
of you guys might have a really simple and effective technique (wishful
thinking, it appears).
Again I've heard about Rac4SQL, but have no experience either.
If I could get ALL PartNos listed for all depots, it would be a big
resultset with lots of zeros/nulls, but it would probably do as a starting
point. I've tried adapting my query, linking it in to the PartMaster table,
but I can't get it to returns results for all part regardless of whether
they have been used in the reporting period. Any suggestions?
> --
> Hugo Kornelis, SQL Server MVP
Thanks Hugo, you've been a great help.
Chris|||On Fri, 27 Jan 2006 10:08:37 -0000, CJM wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
> news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
>
>TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
>COALESCE functions that are needed? (And why?)
Hi CJM,
Replace
Select Sent.LocationID, Sent.PartNo, (...)
with
Select COALESCE(Sent.LocationID, Recd.LocationID) AS LocationID,
COALESCE(Sent.PartNo, Recd.PartNo) AS PartNo,
(...)
This is needed because the FULL OUTER JOIN can cause either one (but not
both) of Sent.LocationID and Recd.LocationID to be NULL.
(snip)
>If I could get ALL PartNos listed for all depots, it would be a big
>resultset with lots of zeros/nulls, but it would probably do as a starting
>point. I've tried adapting my query, linking it in to the PartMaster table,
>but I can't get it to returns results for all part regardless of whether
>they have been used in the reporting period. Any suggestions?
For a static crosstab, you create a CASE expression inside an aggregate
for each part. You'll have to change the query each time a part is added
to your catalog.
Something like this:
SELECT L.LocationID,
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) AS Part1,
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) AS Part2,
..
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) AS Part9999
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID
(untested, since you didn't post a repro script)
SQL Server 2005 introduces new techniques for crosstabbing, but I can't
offer any code as I haven't yet had a chance to play with them. If you
have SQL Server 2005, check PIVOT in Books Online.
Hugo Kornelis, SQL Server MVP
work for me...
Scenario: I developing a report for a system that managed a filter exchange
business. Basically filters are sent to/from customers to our business. I'm
trying to produce a management report which summarises for each customer
depot, which filter types (PartNo) have we sent them and which have they
sent back.
The desired report is probably best explained by example:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
[Yeah, it looks a bit crap on usenet, but it looks better if you pop it in
Excel]
As you can see not all PartNos may be listed - some may not have been sent
or received within the reporting time period. I dont want to show these
PartNos in such a case.
I have developed a query that lists the totals for each Depot for each
PartNo, eg.
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
etc
SQL:
Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTotal
from
(Select L.LocationID, S.PartNo, Count(*) as NumSent
from StockMovements S
inner join Locations L on L.LocationID = S.LocationTo
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Sent
join
(Select L.LocationID, S.PartNo, Count(*) as NumRecd
from StockMovements S
inner join Locations L on L.LocationID = S.LocationFrom
Where S.PartNo is not Null
and PartNo <> ''
Group By S.PartNo, L.LocationID) as Recd
on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
[Note: a 'Location' is a Customer Depot]
I'm not sure how to take this query on further to provide the end result.
I'm not sure if some quasi-temporary tables are needed to store some
intermediate data, or whether it can be acheived with other means (ie a
View).
Any suggestions?
Thanks in advance.
CJM
Trimmed DDL:
CREATE TABLE [dbo].[Locations] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[SerialNo] [int] NOT NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PartMaster] (
[PartID] [int] IDENTITY (1, 1) NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartType] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartDesc] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
[Note: In case it's not obvious, PartMaster contains details on all
available PartNos]
If anything else is missing let me know...
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]First problem i am having in looking at this that no PartNo in
[StockMovements] so you query should be failing based on what you told
us your table creates are becuase you are pulling PartNo from
[StockMovements]
What is the field that holds the part number in [StockMovements]? Could
you change [StockMovements] or is it used buy other items?|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138296034.089998.119680@.g43g2000cwa.googlegroups.com...
> First problem i am having in looking at this that no PartNo in
> [StockMovements] so you query should be failing based on what you told
> us your table creates are becuase you are pulling PartNo from
> [StockMovements]
> What is the field that holds the part number in [StockMovements]? Could
> you change [StockMovements] or is it used buy other items?
>
Sorry - Trimmed a bit too enthusiastically... PartNo *IS* in StockMovements
CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[Ref] [int] NULL ,
[SerialNo] [int] NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL ,
) ON [PRIMARY]
GO|||In the two examples that you gave:
Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13
and
C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
are different.
what are the heading in the report?
Is this how you want the data back. IE: Location ID, Part Number, Parts
Sent Out, Part Received Back, Total|||On Thu, 26 Jan 2006 16:32:21 -0000, CJM wrote:
(snip)
>I have developed a query that lists the totals for each Depot for each
>PartNo, eg.
>C1D1, PartNo1, 1
>C1D1, PartNo28, -1
>C1D2, PartNo7, -10
>etc
>SQL:
>Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTota
l
> from
> (Select L.LocationID, S.PartNo, Count(*) as NumSent
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationTo
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Sent
> join
> (Select L.LocationID, S.PartNo, Count(*) as NumRecd
> from StockMovements S
> inner join Locations L on L.LocationID = S.LocationFrom
> Where S.PartNo is not Null
> and PartNo <> ''
> Group By S.PartNo, L.LocationID) as Recd
> on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo
>[Note: a 'Location' is a Customer Depot]
>I'm not sure how to take this query on further to provide the end result.
Hi CJM,
First, I note that this query will only include parts that are in both
the Sent and the Recd derived table for the same LocationID. If there is
no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
'123' and LocationFrom = 14 will be excluded from the results. If that's
not what you intended, you'll have to replace the JOIN with a FULL OUTER
JOIN and add a bunch of COALESCE functions on the first SELECT line.
Second, I wonder why the PartNo column is called a number but declared
as character, why it is not a key, and why it is used to reference parts
instead of the PartID column that IS a key.
Third, I recommend you to rewrite the query above to the (slightly)
simpler query below:
SELECT L.LocationID, P.PartNo,
COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID, P.PartNo
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Now you also wanted the part numbers to be columns instead of rows. That
is usually called a crosstab or pivot - and the best way to do it is to
transform the table on the client side.
If you have to do it on the server side, AND you're on SQL Server 2005,
you can also check out the new PIVOT operator. For SQL Server 2000,
google this group for "CROSSTAB" to find some common kludges. Note
however that all these crosstabl techniques require you to know the
columns in advance - and if I understand your question correctly, you
want to be able to dynamically include or exclude columns, based on the
query results.
I know of no supported way to create a dynamic crosstab on the server.
You might want to try your luck with dynamic SQL - but ungh!! You might
also want to investigate if there are third-party products that can help
you out. Rac4SQL is often recommended in this group - though admittedly
always by the same poster, who probably works for the company that sells
Rac4SQL. I have no personal experience with either Rac4SQL or any other
third party product for crosstabbing.
Hugo Kornelis, SQL Server MVP|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138297710.003578.172650@.g14g2000cwa.googlegroups.com...
> In the two examples that you gave:
> Customer, Depot, PartNo1, PartNo7, PartNo28, Total
> Cust1, C1D1, 1, null, -1, 0
> Cust1, C1D2, null, -10, null, -10
> Cust2, C2D1, 1, -2, -3, -4
> Cust3 C3D1, null, null, 1, 1
> Total, null, 2, -12, -3, -13
>
> and
> C1D1, PartNo1, 1
> C1D1, PartNo28, -1
> C1D2, PartNo7, -10
>
> are different.
The first example is the output I want, the second is the data that results
in that output. The columns for that data are: DepotID, PartNo,
'NetTotalSent' (ie Total sent to that depot - Total received from that
depot)
You can see that for Depot C1D1, they have received 1 x PartNo1 and returned
1 x PartNo28. The net total is therefore 0. [See line 1 of the example
output]
> what are the heading in the report?
The headings are on the first line: Customer, Depot, PartNo1, PartNo7,
PartNo28, Total. Depending on when you run the report you may have more or
less columns - it depends on which parts have been shipped/returned in that
time period.
> Is this how you want the data back. IE: Location ID, Part Number, Parts
> Sent Out, Part Received Back, Total
>
No - see above.
I realise that this is quite confusing; and I'm trying to trade-off between
swamping you with too much superfluous detail, and not providing enough to
mek it clear. I hope this somewhat clairifies it.
Thanks|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
> Hi CJM,
> First, I note that this query will only include parts that are in both
> the Sent and the Recd derived table for the same LocationID. If there is
> no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
> '123' and LocationFrom = 14 will be excluded from the results. If that's
> not what you intended, you'll have to replace the JOIN with a FULL OUTER
> JOIN and add a bunch of COALESCE functions on the first SELECT line.
>
TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
COALESCE functions that are needed? (And why?)
> Second, I wonder why the PartNo column is called a number but declared
> as character, why it is not a key, and why it is used to reference parts
> instead of the PartID column that IS a key.
Historical reasons. Many of these PartNos pre-date all our computer systems,
and I suspect some pre-date civilisation itself (as do some of the people in
charge here!)
Some PartNo's are indeed integers but most are alpha numerical. The PartID
was introduced to be a numerical key, but is under-used. In retrospect, I
would have stuck with PartNo as the key.
> Third, I recommend you to rewrite the query above to the (slightly)
> simpler query below:
>
It *is* simpler, and a lot neater too. I'll digest it (and probably use it
too - thanks)
Update: I've tried it and it works a treat (and the results are more like
what I was expecting)
> SELECT L.LocationID, P.PartNo,
> COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
> FROM Locations AS L
> CROSS JOIN PartMaster AS P
> LEFT JOIN StockMovements AS Sent
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationTo = L.LocationID
> LEFT JOIN StockMovements AS Recd
> ON Sent.PartNo = P.PartNo
> AND Sent.LocationFrom = L.LocationID
> WHERE Sent.PartNo IS NOT NULL
> OR Recd.PartNo IS NOT NULL
> GROUP BY L.LocationID, P.PartNo
> (untested - see www.aspfaq.com/5006 if you prefer a tested solution)
> Now you also wanted the part numbers to be columns instead of rows. That
> is usually called a crosstab or pivot - and the best way to do it is to
> transform the table on the client side.
> If you have to do it on the server side, AND you're on SQL Server 2005,
> you can also check out the new PIVOT operator. For SQL Server 2000,
> google this group for "CROSSTAB" to find some common kludges. Note
> however that all these crosstabl techniques require you to know the
> columns in advance - and if I understand your question correctly, you
> want to be able to dynamically include or exclude columns, based on the
> query results.
>
Yeah, I've done cross-tabs in SQL before, but as you say, the columns need
to be fixed (defined in advance). But the specification for my report *does*
ask for dynamically included and excluded columns... hmmmm...
> I know of no supported way to create a dynamic crosstab on the server.
> You might want to try your luck with dynamic SQL - but ungh!! You might
> also want to investigate if there are third-party products that can help
> you out. Rac4SQL is often recommended in this group - though admittedly
> always by the same poster, who probably works for the company that sells
> Rac4SQL. I have no personal experience with either Rac4SQL or any other
> third party product for crosstabbing.
>
I've heard of these dynamic SQL techniques, but never actually used them.
I've always found a way around them (ie not using them), but I thought one
of you guys might have a really simple and effective technique (wishful
thinking, it appears).
Again I've heard about Rac4SQL, but have no experience either.
If I could get ALL PartNos listed for all depots, it would be a big
resultset with lots of zeros/nulls, but it would probably do as a starting
point. I've tried adapting my query, linking it in to the PartMaster table,
but I can't get it to returns results for all part regardless of whether
they have been used in the reporting period. Any suggestions?
> --
> Hugo Kornelis, SQL Server MVP
Thanks Hugo, you've been a great help.
Chris|||On Fri, 27 Jan 2006 10:08:37 -0000, CJM wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
> news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
>
>TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
>COALESCE functions that are needed? (And why?)
Hi CJM,
Replace
Select Sent.LocationID, Sent.PartNo, (...)
with
Select COALESCE(Sent.LocationID, Recd.LocationID) AS LocationID,
COALESCE(Sent.PartNo, Recd.PartNo) AS PartNo,
(...)
This is needed because the FULL OUTER JOIN can cause either one (but not
both) of Sent.LocationID and Recd.LocationID to be NULL.
(snip)
>If I could get ALL PartNos listed for all depots, it would be a big
>resultset with lots of zeros/nulls, but it would probably do as a starting
>point. I've tried adapting my query, linking it in to the PartMaster table,
>but I can't get it to returns results for all part regardless of whether
>they have been used in the reporting period. Any suggestions?
For a static crosstab, you create a CASE expression inside an aggregate
for each part. You'll have to change the query each time a part is added
to your catalog.
Something like this:
SELECT L.LocationID,
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) AS Part1,
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) AS Part2,
..
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) -
COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) AS Part9999
FROM Locations AS L
CROSS JOIN PartMaster AS P
LEFT JOIN StockMovements AS Sent
ON Sent.PartNo = P.PartNo
AND Sent.LocationTo = L.LocationID
LEFT JOIN StockMovements AS Recd
ON Sent.PartNo = P.PartNo
AND Sent.LocationFrom = L.LocationID
WHERE Sent.PartNo IS NOT NULL
OR Recd.PartNo IS NOT NULL
GROUP BY L.LocationID
(untested, since you didn't post a repro script)
SQL Server 2005 introduces new techniques for crosstabbing, but I can't
offer any code as I haven't yet had a chance to play with them. If you
have SQL Server 2005, check PIVOT in Books Online.
Hugo Kornelis, SQL Server MVP
Saturday, February 25, 2012
Newbie: Use XMLA to connect to SQL Server
Hi,
I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.
When I query the datasources via XMLA, I get a response like:
DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated
(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:
1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?
For reference: My current XMLA query to discover the datasources is:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlns
OAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.
Regards,
Philipp
|||
Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.
Chris Harrington has the best collection of blog posts I have seen on the subject here
http://www.activeinterface.com/thinolap.html
Particularly this one: http://www.activeinterface.com/b2005_11_21.html
Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.
How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?
|||Hi Darren,
thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.
As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?
So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||
The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.
oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)
So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code
Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");
and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).
Anyway, thanks for your help!
Philipp
Newbie: Use XMLA to connect to SQL Server
Hi,
I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.
When I query the datasources via XMLA, I get a response like:
DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated
(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:
1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?
For reference: My current XMLA query to discover the datasources is:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.
Regards,
Philipp
|||
Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.
Chris Harrington has the best collection of blog posts I have seen on the subject here
http://www.activeinterface.com/thinolap.html
Particularly this one: http://www.activeinterface.com/b2005_11_21.html
Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.
How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?
|||Hi Darren,thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.
As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?
So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||
The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.
oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)
So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code
Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");
and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).
Anyway, thanks for your help!
Philipp
Newbie: Use XMLA to connect to SQL Server
Hi,
I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.
When I query the datasources via XMLA, I get a response like:
DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated
(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:
1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?
For reference: My current XMLA query to discover the datasources is:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.
Regards,
Philipp
|||
Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.
Chris Harrington has the best collection of blog posts I have seen on the subject here
http://www.activeinterface.com/thinolap.html
Particularly this one: http://www.activeinterface.com/b2005_11_21.html
Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.
How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?
|||Hi Darren,
thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.
As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?
So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||
The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.
oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)
So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code
Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");
and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).
Anyway, thanks for your help!
Philipp
Subscribe to:
Posts (Atom)