Metadata Services: February 2012

Saturday, February 25, 2012

Newbie:why osql doesn't let me logon ?

I need to move tempdb from c: drive to d:on the local server.
I try to go to osql to perform the deattach database command, but here is
what I get:
My user is an administrator on the local SQL server.
C:>osql -U myuser
Password:
Login failed for user 'myuser. Reason: not associated with a trusted SQL
Server connectionIf you are local administrator and you have mixed authentification activated
you can easily login with
OSQL -E
else you have to provide a password with
OSQL -UUsername -PPassword
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Does your SQL Server allow SQL server authentication, or only NT
authentication?
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>I need to move tempdb from c: drive to d:on the local server.
> I try to go to osql to perform the deattach database command, but here is
> what I get:
> My user is an administrator on the local SQL server.
> C:>osql -U myuser
> Password:
> Login failed for user 'myuser. Reason: not associated with a trusted SQL
> Server connection
>
>|||Only NT auth.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does your SQL Server allow SQL server authentication, or only NT
> authentication?
>
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
> >I need to move tempdb from c: drive to d:on the local server.
> > I try to go to osql to perform the deattach database command, but here
is
> > what I get:
> > My user is an administrator on the local SQL server.
> >
> > C:>osql -U myuser
> > Password:
> > Login failed for user 'myuser. Reason: not associated with a trusted SQL
> > Server connection
> >
> >
> >
> >
>|||Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use Integrated
authentification)
HTH, Jens Suessmeyer.
--
https://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Only NT auth.
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
>> Does your SQL Server allow SQL server authentication, or only NT
>> authentication?
>>
>> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
>> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>> >I need to move tempdb from c: drive to d:on the local server.
>> > I try to go to osql to perform the deattach database command, but here
> is
>> > what I get:
>> > My user is an administrator on the local SQL server.
>> >
>> > C:>osql -U myuser
>> > Password:
>> > Login failed for user 'myuser. Reason: not associated with a trusted
>> > SQL
>> > Server connection
>> >
>> >
>> >
>> >
>>
>|||Ok, so now I do osql -E
Then a prompt appears.
I go there and I do:
1>sp_detach_db tempdb
Nothing happens. I see that my tempdb continues where it is.
How can I move the tempdb to another drive then ?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use Integrated
> authentification)
> HTH, Jens Suessmeyer.
> --
> https://www.sqlserver2005.de
> --
> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> > Only NT auth.
> >
> > "Lisa Pearlson" <no@.spam.plz> wrote in message
> > news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
> >> Does your SQL Server allow SQL server authentication, or only NT
> >> authentication?
> >>
> >>
> >> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> >> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
> >> >I need to move tempdb from c: drive to d:on the local server.
> >> > I try to go to osql to perform the deattach database command, but
here
> > is
> >> > what I get:
> >> > My user is an administrator on the local SQL server.
> >> >
> >> > C:>osql -U myuser
> >> > Password:
> >> > Login failed for user 'myuser. Reason: not associated with a trusted
> >> > SQL
> >> > Server connection
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||And GO ! ;-)
> 1>sp_detach_db tempdb
> 2>GO
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
news:%23cS9YfbUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Ok, so now I do osql -E
> Then a prompt appears.
> I go there and I do:
> 1>sp_detach_db tempdb
> Nothing happens. I see that my tempdb continues where it is.
> How can I move the tempdb to another drive then ?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ezj%23gZbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Then OSQL -U won´t work. YOu hav to use OSQL -E instead (to use
>> Integrated
>> authentification)
>> HTH, Jens Suessmeyer.
>> --
>> https://www.sqlserver2005.de
>> --
>> "Magoo" <magoo-nospam@.hotmail.com> schrieb im Newsbeitrag
>> news:%23waUTUbUFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> > Only NT auth.
>> >
>> > "Lisa Pearlson" <no@.spam.plz> wrote in message
>> > news:OGeDv5aUFHA.228@.TK2MSFTNGP12.phx.gbl...
>> >> Does your SQL Server allow SQL server authentication, or only NT
>> >> authentication?
>> >>
>> >>
>> >> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
>> >> news:OgpRnrYUFHA.3244@.TK2MSFTNGP15.phx.gbl...
>> >> >I need to move tempdb from c: drive to d:on the local server.
>> >> > I try to go to osql to perform the deattach database command, but
> here
>> > is
>> >> > what I get:
>> >> > My user is an administrator on the local SQL server.
>> >> >
>> >> > C:>osql -U myuser
>> >> > Password:
>> >> > Login failed for user 'myuser. Reason: not associated with a trusted
>> >> > SQL
>> >> > Server connection
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Newbie:why osql doesn't let me logon ?

Newbie:UNION with SELECT TOP

I have been trying to query the Top 100 selling items for each category to b
e
one result set...all day. I thought that this should work...
SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
FROM dbo.Sales
WHERE categoryid ='01'
GROUP BY productid
ORDER BY sum(sales) DESC
UNION
SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
FROM dbo.Sales
WHERE categoryid ='02'
GROUP BY productid
ORDER BY sum(sales) DESC
UNION...
...and so on for our 14 different categories.
This is the first time that I have tried to use that UNION command, and I'm
wondering if it is the right way to go about this? Obviously I am muddling m
y
way through SQL Queries and appreciate any help.Chuck wrote:
> I have been trying to query the Top 100 selling items for each
> category to be one result set...all day. I thought that this should
> work...
> SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
> FROM dbo.Sales
> WHERE categoryid ='01'
> GROUP BY productid
> ORDER BY sum(sales) DESC
> UNION
> SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
> FROM dbo.Sales
> WHERE categoryid ='02'
> GROUP BY productid
> ORDER BY sum(sales) DESC
> UNION...
> ...and so on for our 14 different categories.
> This is the first time that I have tried to use that UNION command,
> and I'm wondering if it is the right way to go about this? Obviously
> I am muddling my way through SQL Queries and appreciate any help.
Apart from suggesting the use of UNION ALL instead of UNION*, I can make no
suggestions here. Is it not doing what you want it to do?
Bob Barrows
*Using the ALL keyword prevents the query engine from wasting time trying to
eliminate duplicate rows from the result. You know there will be no
duplicates, so use UNION ALL
--
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.|||> Apart from suggesting the use of UNION ALL instead of UNION*, I can make
> no
> suggestions here. Is it not doing what you want it to do?
Well, you can't have ORDER BY within each UNION, you can only order the
entire result.
Though I am not sure why this query all has to be in one resultset. My
three suggestions would be:
(a) get the top 100 across all categories:
SELECT TOP 100
productID, categoryID, SUM(sales)
FROM dbo.Sales
GROUP BY productID, categoryID
ORDER BY 3 DESC;
(b) an easy way to bypass the ORDER/UNION quandary is to use a #temp table
or @.table variable and insert each result there, then you can order on the
whole instead of its parts.
(c) have 14 results, or take category as a parameter and call the stored
procedure however many times is relevant (my guess is that, in general, we
are either looking at sales overall (in which case (a) is more prudent) or
we are looking at a specific category).
A|||create table #t(productid int, categoryid int, sumsales float)
insert into #t values(1,1,12.3)
insert into #t values(2,1,11.0)
insert into #t values(3,1,15.3)
insert into #t values(1,2,12.3)
insert into #t values(2,2,1.3)
insert into #t values(3,2,2.3)
insert into #t values(4,2,7.3)
insert into #t values(5,2,12.1)
select * from #t t1 where
(select count(*) from #t t2 where t1.categoryid = t2.categoryid
and t2.sumsales>t1.sumsales)<2
order by categoryid, sumsales desc
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
productid categoryid sumsales
-- --
----
3 1 15.300000000000001
1 1 12.300000000000001
1 2 12.300000000000001
5 2 12.1
(4 row(s) affected)
drop table #t|||oops I hit Post button too soon.
the query I posted will give you top 2 in each category, *assuming
there are no ties*
I added a tie:
insert into #t values(4,1,12.3)
and this works:
select * from #t t1 where sumsales in
(select top 2 with ties sumsales
from #t t2 where t1.categoryid = t2.categoryid
order by sumsales desc)
order by categoryid, sumsales desc
productid categoryid sumsales
-- --
3 1 15.300000000000001
4 1 12.300000000000001
1 1 12.300000000000001
1 2 12.300000000000001
5 2 12.1
(5 row(s) affected)|||Aaron Bertrand [SQL Server MVP] wrote:
> Well, you can't have ORDER BY within each UNION, you can only order
> the entire result.
Thanks, I missed that (obviously ... )
--
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.|||you can do it with derived tables and subqueries as well, and not have
to hard-code categoryid's
e.g.
select productid, categoryid, sales
from (
select productid, categoryid, sum(sales) as sales
from sales
group by productid, categoryid
) SumAll
where productid in (
select top 100 productid
from (
select productid, sum(sales) as sales
from sales
where categoryid=SumAll.categoryid
group by productid
) SumCategory
order by sales desc
)
order by categoryid asc, sales desc
Chuck wrote:
> I have been trying to query the Top 100 selling items for each category to
be
> one result set...all day. I thought that this should work...
> SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
> FROM dbo.Sales
> WHERE categoryid ='01'
> GROUP BY productid
> ORDER BY sum(sales) DESC
> UNION
> SELECT TOP 100 productid, categoryid, sum(sales) as [Sales]
> FROM dbo.Sales
> WHERE categoryid ='02'
> GROUP BY productid
> ORDER BY sum(sales) DESC
> UNION...
> ...and so on for our 14 different categories.
> This is the first time that I have tried to use that UNION command, and I'
m
> wondering if it is the right way to go about this? Obviously I am muddling
my
> way through SQL Queries and appreciate any help.|||
"Alexander Kuznetsov" wrote:

> oops I hit Post button too soon.
> the query I posted will give you top 2 in each category, *assuming
> there are no ties*
> I added a tie:
> insert into #t values(4,1,12.3)
> and this works:
> select * from #t t1 where sumsales in
> (select top 2 with ties sumsales
> from #t t2 where t1.categoryid = t2.categoryid
> order by sumsales desc)
> order by categoryid, sumsales desc
>
> productid categoryid sumsales
> -- --
> 3 1 15.300000000000001
> 4 1 12.300000000000001
> 1 1 12.300000000000001
> 1 2 12.300000000000001
> 5 2 12.1
> (5 row(s) affected)
>
Bob, Aaron, and Alexander,
Thank you for your quick replies. In answer to your questions...I have
thought of creating individual result sets, which would solve part of the
issue. The ultimate goal here is to create a VIEW which will join with an
inventory table to produce the percentage of Top 100 selling merchandise
"in-stock" by category. As a result, at some point it will be necessary to
rejoin the individual result sets, which I am trying to avoid.
However, I want to break down the project into bite sized pieces, focussing
for the moment on this problem.
I have tried Alexander's suggestion, but the sumsales column in it is not an
aggregate, while my query it is. That produced the error that I could not
use an aggregate as the value for an IN statement. I have altered it to
this, which has produced a timeout error.
SELECT ProductID, CategoryID, SUM(Sales) AS Expr1, SUM(QTY) AS Expr2
FROM cghastin.jvsProd_Performance t1
WHERE (ProductID IN
(SELECT TOP 2 productid
FROM cghastin.jvsprod_performance t2
WHERE t1.categoryid = t2.categoryid
GROUP BY productid
ORDER BY SUM(sales) DESC))
GROUP BY CategoryID, ProductID|||an index on (productID, category_id, sales, qty)
might speed it up
Also you may try this:
select productid, categoryid, sumsales, sumqty
from (
select productid, categoryid, sum(sales) as sumsales, sum(qty) as
sumqty
from sales
group by productid, categoryid
) t
where (select count(*) from (
select productid, categoryid, sum(sales) as sumsales, sum(qty) as
sumqty
from sales
group by productid, categoryid ) t1 where t.productid =
t1.productid
and t.categoryid = t1.categoryid and t.sumsales < t1.sumsales)<100
that is with ties|||an index on (productID, category_id, sales, qty)
might speed it up
Also you may try this:
select productid, categoryid, sumsales, sumqty
from (
select productid, categoryid, sum(sales) as sumsales, sum(qty) as
sumqty
from sales
group by productid, categoryid
) t
where (select count(*) from (
select productid, categoryid, sum(sales) as sumsales, sum(qty) as
sumqty
from sales
group by productid, categoryid ) t1 where t.productid =
t1.productid
and t.categoryid = t1.categoryid and t.sumsales < t1.sumsales)<100
that is with ties

Newbie:preparation to reindex SQL db

Hello,
A third-party vendor tells me a SQL db contains various errors and suggests
that I 'reindex' the db.
I see that I can go to SQL Query Analyzer, pick the db I need and run
DBCC DBREINDEX
Question is this, what's the implication of running this without specifying
a specific table ? Is that alright run this on the 'entire' database ?> Question is this, what's the implication of running this without
> specifying
> a specific table ? Is that alright run this on the 'entire' database ?
How large is the database? What is your tolerance for downtime?
I would make a backup of the database, restore it on another server, and
test out the reindex there. Not only will you get an idea of time/CPU/I/O
requirements, you will also be able to test how your application(s) will
work against it during the reindexing of the tables.
My guess is that in the long run you'll want to break up the operation into
smaller chunks (e.g. one or two tables at a time). But it's tough to say
without more information specific to *your* environment. I know what I
can't do here in mine. :-)|||Hi,
DBCC DBREINDEX will recreate the Index, But for identifying errors you need
to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
Once you identify the errors you need to execute DBCC CHECKDB with
REPAIR_REBUILD to clear the errors.
DBCC DBREINDEX can be used to remove the fragmentation in your table and
this command will lock your table. Due to this you can execute this command
only offline. So check your vendor whether to exeecute DBCC CHECKDB or
REINDEX.
Thanks
Hari
SQL Server MVP
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hello,
> A third-party vendor tells me a SQL db contains various errors and
suggests
> that I 'reindex' the db.
> I see that I can go to SQL Query Analyzer, pick the db I need and run
> DBCC DBREINDEX
> Question is this, what's the implication of running this without
specifying
> a specific table ? Is that alright run this on the 'entire' database ?
>|||What errors exactly did this vendor say you had in the database?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udIZZpRUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi,
> DBCC DBREINDEX will recreate the Index, But for identifying errors you
need
> to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
> Once you identify the errors you need to execute DBCC CHECKDB with
> REPAIR_REBUILD to clear the errors.
> DBCC DBREINDEX can be used to remove the fragmentation in your table and
> this command will lock your table. Due to this you can execute this
command
> only offline. So check your vendor whether to exeecute DBCC CHECKDB or
> REINDEX.
> Thanks
> Hari
> SQL Server MVP
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> suggests
> specifying
>

Newbie:preparation to reindex SQL db

Hello,
A third-party vendor tells me a SQL db contains various errors and suggests
that I 'reindex' the db.
I see that I can go to SQL Query Analyzer, pick the db I need and run
DBCC DBREINDEX
Question is this, what's the implication of running this without specifying
a specific table ? Is that alright run this on the 'entire' database ?> Question is this, what's the implication of running this without
> specifying
> a specific table ? Is that alright run this on the 'entire' database ?
How large is the database? What is your tolerance for downtime?
I would make a backup of the database, restore it on another server, and
test out the reindex there. Not only will you get an idea of time/CPU/I/O
requirements, you will also be able to test how your application(s) will
work against it during the reindexing of the tables.
My guess is that in the long run you'll want to break up the operation into
smaller chunks (e.g. one or two tables at a time). But it's tough to say
without more information specific to *your* environment. I know what I
can't do here in mine. :-)|||Hi,
DBCC DBREINDEX will recreate the Index, But for identifying errors you need
to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
Once you identify the errors you need to execute DBCC CHECKDB with
REPAIR_REBUILD to clear the errors.
DBCC DBREINDEX can be used to remove the fragmentation in your table and
this command will lock your table. Due to this you can execute this command
only offline. So check your vendor whether to exeecute DBCC CHECKDB or
REINDEX.
Thanks
Hari
SQL Server MVP
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hello,
> A third-party vendor tells me a SQL db contains various errors and
suggests
> that I 'reindex' the db.
> I see that I can go to SQL Query Analyzer, pick the db I need and run
> DBCC DBREINDEX
> Question is this, what's the implication of running this without
specifying
> a specific table ? Is that alright run this on the 'entire' database ?
>|||What errors exactly did this vendor say you had in the database?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udIZZpRUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi,
> DBCC DBREINDEX will recreate the Index, But for identifying errors you
need
> to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
> Once you identify the errors you need to execute DBCC CHECKDB with
> REPAIR_REBUILD to clear the errors.
> DBCC DBREINDEX can be used to remove the fragmentation in your table and
> this command will lock your table. Due to this you can execute this
command
> only offline. So check your vendor whether to exeecute DBCC CHECKDB or
> REINDEX.
> Thanks
> Hari
> SQL Server MVP
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > A third-party vendor tells me a SQL db contains various errors and
> suggests
> > that I 'reindex' the db.
> >
> > I see that I can go to SQL Query Analyzer, pick the db I need and run
> > DBCC DBREINDEX
> >
> > Question is this, what's the implication of running this without
> specifying
> > a specific table ? Is that alright run this on the 'entire' database ?
> >
> >
>

Newbie:preparation to reindex SQL db

Hello,
A third-party vendor tells me a SQL db contains various errors and suggests
that I 'reindex' the db.
I see that I can go to SQL Query Analyzer, pick the db I need and run
DBCC DBREINDEX
Question is this, what's the implication of running this without specifying
a specific table ? Is that alright run this on the 'entire' database ?
> Question is this, what's the implication of running this without
> specifying
> a specific table ? Is that alright run this on the 'entire' database ?
How large is the database? What is your tolerance for downtime?
I would make a backup of the database, restore it on another server, and
test out the reindex there. Not only will you get an idea of time/CPU/I/O
requirements, you will also be able to test how your application(s) will
work against it during the reindexing of the tables.
My guess is that in the long run you'll want to break up the operation into
smaller chunks (e.g. one or two tables at a time). But it's tough to say
without more information specific to *your* environment. I know what I
can't do here in mine. :-)
|||Hi,
DBCC DBREINDEX will recreate the Index, But for identifying errors you need
to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
Once you identify the errors you need to execute DBCC CHECKDB with
REPAIR_REBUILD to clear the errors.
DBCC DBREINDEX can be used to remove the fragmentation in your table and
this command will lock your table. Due to this you can execute this command
only offline. So check your vendor whether to exeecute DBCC CHECKDB or
REINDEX.
Thanks
Hari
SQL Server MVP
"Magoo" <magoo-nospam@.hotmail.com> wrote in message
news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hello,
> A third-party vendor tells me a SQL db contains various errors and
suggests
> that I 'reindex' the db.
> I see that I can go to SQL Query Analyzer, pick the db I need and run
> DBCC DBREINDEX
> Question is this, what's the implication of running this without
specifying
> a specific table ? Is that alright run this on the 'entire' database ?
>
|||What errors exactly did this vendor say you had in the database?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udIZZpRUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi,
> DBCC DBREINDEX will recreate the Index, But for identifying errors you
need
> to execute either DBCC CHECKDB or DBCC CHECKTABLE commands.
> Once you identify the errors you need to execute DBCC CHECKDB with
> REPAIR_REBUILD to clear the errors.
> DBCC DBREINDEX can be used to remove the fragmentation in your table and
> this command will lock your table. Due to this you can execute this
command
> only offline. So check your vendor whether to exeecute DBCC CHECKDB or
> REINDEX.
> Thanks
> Hari
> SQL Server MVP
> "Magoo" <magoo-nospam@.hotmail.com> wrote in message
> news:usGs49MUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> suggests
> specifying
>

Newbie:if query is successful, how to response.write on screen ?

I did this in the past using .asp, but with ASP.NET 2.0 perhaps things have
really changed, because I do response.write(TextBox1.Text) and doesn't work.
Here is what I am trying to:
Partial Class Default_aspx
Sub btnDefault_Click(ByVal sender As Object, ByVal e As
System.EventArgs)
Dim myString
myString = "SELECT Employee From Pubs " _
& "WHERE job_id = " & TxtBox1.Text
'If job_id = TxtBox1.Text is found Then
'I want to go a new page and displays on the screen 'OK
info matched'
'How can I jump to a new page from here ? (Pardon me if
this should be a question posted to .ASP newsgroup maybe ? )
Else
'Display TxtBox1.Text input did not match information in
the db'
Response.Write("Please click the 'back' button and try
to resubmit 'secret question' to reset password again.")
End If
End Sub
End ClassThis should be in an asp.net group. However, here are your answers:
First off, Response.Write's are generally discouraged, especially for what
you are doing. You should have a label and set the label's text to the
strings to display. Your sql should never ever under any circumstances be
created like that. Use stored procedures or at least parameterized queries.
Also, please google sql injection attacks to see why this is bad.
Judging from the code you posted it looks like you are doing authentication
of some kind. You might want to take a look at forms authentication. To
redirect someone to a new page you can do a
Response.Redirect("myOtherPage.html").

Newbie:help on maximum of a series of columns

Hi,
I have the following table:
tblMeasurements
Date | value1| value2| ...|value24|
I need to find (and display) the maximum per day, i.e. the maximum number
among the value1...value24 in the same row (date).
I tried MAX(value1,....,value24) to no avail.
The examples I see on the net seem to find the max for all the entries of a
*specific* column. I want the max of a specific row. Can it be done and am i
looking at the right function ?
I know its a simple question but i cant seem to find the answer.
TIA
-steveSteve
Why not to store like this
CREATE TABLE #Test
(
[Id] Int not null primary key,
[Date] datetime not null,
Value int not null
)
SELECT MAX(Value),Date FROM #Test
GROUP BY Date
"Steve" <try@.this.com> wrote in message
news:xOz0e.19063$ZC6.78184@.wagner.videotron.net...
> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>|||Try,
select
[Date]
max(value)
from
(
select
[date],
case n.colA
when 1 then value1
when 2 then value2
..
when 24 then value24
end as value
from
t
cross join
(
select 1
union all
select 2
union all
..
select 24
) as n(colA)
) as a
group by
[date]
go
AMB
"Steve" wrote:

> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>
>

Newbie: XML as datasource

Hi everyone,
I have a Web Form with data fields. What I want to do is to save these
fields into an XML file (dynamically), then hook this XML (as a data source)
to a SQL Report so that I don't have to save these data into a SQL Server
database. Is it possible? If it is then can someone show me how.
Any suggestion is greatly appreciated.
Many thanks in advance.There is currently no support for accessing an XML data source. You either
have to write your own custom XML data source extension or look on the market
for an XML OLEDB provider. Or search MSDN for sample code that implements an
XML data source extension. My current understanding is that it will be
included with the SQL Server 2005 release of Reporting Services.
HTH
Charles Kangai, MCT, MCDBA
"Calvin KD" wrote:
> Hi everyone,
> I have a Web Form with data fields. What I want to do is to save these
> fields into an XML file (dynamically), then hook this XML (as a data source)
> to a SQL Report so that I don't have to save these data into a SQL Server
> database. Is it possible? If it is then can someone show me how.
> Any suggestion is greatly appreciated.
> Many thanks in advance.|||Hi
I used the "Microsoft OLE DB Simple Provider"
1.) Create new Shared Data Source
General: "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6"
Credentials: "Use Windows Authentication (Integrated Security)"
2.) Create new Report using this Shared Data Source
Query: path to the xml-file "c:\temp\test.xml"
Kind Regards
Daniel
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4A0288C1-9C16-4CDF-981A-88B89475616C@.microsoft.com...
> There is currently no support for accessing an XML data source. You either
> have to write your own custom XML data source extension or look on the
> market
> for an XML OLEDB provider. Or search MSDN for sample code that implements
> an
> XML data source extension. My current understanding is that it will be
> included with the SQL Server 2005 release of Reporting Services.
> HTH
> Charles Kangai, MCT, MCDBA
> "Calvin KD" wrote:
>> Hi everyone,
>> I have a Web Form with data fields. What I want to do is to save these
>> fields into an XML file (dynamically), then hook this XML (as a data
>> source)
>> to a SQL Report so that I don't have to save these data into a SQL Server
>> database. Is it possible? If it is then can someone show me how.
>> Any suggestion is greatly appreciated.
>> Many thanks in advance.

Newbie: working with expressions on tables

I was reading the help, but I don't know if this is possible.
Dataset
first | last | Id
a | b | 1
c | d | 2
e | f | 3
For 1 cell I want to put:
Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
I just want to put specific fields in different spots. I didn't see this in
the help and looked around a bit. Is there a section in the help that
covers expressions like this?I think you want this:
=iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
Fields!ID.Value, Nothing)
Details on the IIF function are available on MSDN:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> I was reading the help, but I don't know if this is possible.
> Dataset
> first | last | Id
> a | b | 1
> c | d | 2
> e | f | 3
>
> For 1 cell I want to put:
> Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> I just want to put specific fields in different spots. I didn't see this
in
> the help and looked around a bit. Is there a section in the help that
> covers expressions like this?
>|||That works, but it only takes the 1st value. It doesn't loop through all
the rows if I do it cell by cell.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u4AUaamVEHA.2716@.tk2msftngp13.phx.gbl...
> I think you want this:
> =iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
> Fields!ID.Value, Nothing)
> Details on the IIF function are available on MSDN:
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> > I was reading the help, but I don't know if this is possible.
> >
> > Dataset
> >
> > first | last | Id
> > a | b | 1
> > c | d | 2
> > e | f | 3
> >
> >
> > For 1 cell I want to put:
> > Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> >
> > I just want to put specific fields in different spots. I didn't see
this
> in
> > the help and looked around a bit. Is there a section in the help that
> > covers expressions like this?
> >
> >
>

Newbie: which replication strategy should I use?

Hi all

My company is going to open new working centers on different locations of my region. One of the problems we are suffering is that at some locations, the network communications infrastructure is very very very poor. So, in that locations we work with low bandwidths, and connections usually break down.

Because of this, we are thinking in distributing our database. I have been doing some tests on replication, reading the docs, etc... But I am still not sure which replication strategy should we use, and how to organize our database tables to allow replication work properly.

Our offices are going to share some data (a product catalogue, for example) which could be updated an queried from any of the offices. But there is also data which is not going to be shared, as product stocks for each location.

We were thinking on using transactional peer-to-peer replication. But now we are having serious doubts about this since in a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525643&SiteID=1) we were told we can't store non-shared data (stock) in the same table as the shared data (products) due to how database behaves when a publication is restarted. We know it would not be usual to republish, but we were thinking on crash recovery.

It would be fantastic if somebody could help us to decide what should we do to organize our database to allow the use of a proper replication scheme.

Thanks in advance,

Rubn D. M.

Considering your low bandwidths, have you looked at merge replication? It works best in a disconnected environment and over unreliable links as it has retry capabilities built in. you can also run agents at scheduled intervals. This would work provide you don't have low-latency requirement.

As far as your scenario goes where you want to replicate a table but not track changes in certain columns at both ends, this really won't work. In its simplest form you can do vertical partitioning at the publisher side, but that means any columns you choose to not replicate will not exist at the subscriber.

|||Hi Greg,
thank you very much for your help.

I thought transactional replication has also retry capabilities... So, what happens in a "transactional replication environment with updatable queued subscriptions" if the connection between publisher and subscriber is lost? Would it be needed to "restart" the subscription, or it will continue working correctly when connection is stablished again?

Thanks again ;-)

Regards

|||It does, but it's not as robust as merge replication, which is designed for the scenario you described.

Newbie: what kind of "cube" is this?

1. In sql 2005 Analysis services how can I tell what kind of "cube" I'm viewing -- ROLAP, HOLAP, or MOLAP?

2. During cube creation where do I specify the type of "cube" I want to create

TIA,

Barkingdog

The cube wizard will not ask you for the storage settings that you want to use. After you have run the wizard and created the cube, go to the "Partitions" tab of the cube editor. You will then see information for your partitions. You can click on a partition and then click on the "Storage Settings..." link to specify MOLAP, HOLAP or ROLAP.

HTH,

- Steve

|||Cube and dimensions will initially be MOLAP when creating cubes and databases with Cube or Dimension wizards.

Newbie: what kind of "cube" is this?

1. In sql 2005 Analysis services how can I tell what kind of "cube" I'm viewing -- ROLAP, HOLAP, or MOLAP?

2. During cube creation where do I specify the type of "cube" I want to create

TIA,

Barkingdog

HTH,

- Steve

|||Cube and dimensions will initially be MOLAP when creating cubes and databases with Cube or Dimension wizards.

Newbie: Viewing KPIs thru MOSS

I'm a newbie with SSAS so please bare with me.

I've create a data cube and subsequent KPIs. I now want to be able to publish these KPIs from within MOSS. All of the literature up until now seemed to indicate that I could easily attached to the KPIs from within MOSS, but now I finding that I need to publish them to Excel then to MOSS. Is this correct?

If so, I do not have Excel 2007, so is there a work around to this? Also, similiar to how Reporting Services when deployed provides the ability to render the reports via the web browser...does this functionality exist within SSAS and KPIs?

Thanks for your time and assistance.

SArnwine wrote:

..

but now I finding that I need to publish them to Excel then to MOSS. Is this correct?
...

If you follow the blog entry below, you should be able to create a KPI list, add items to this list for SSAS KPI's, and display the KPI's in the list using the MOSS built-in Key Performance Indicators Web Part:

KPI's in Microsoft Office SharePoint Server (MOSS) 2007

|||thanks.

When I try to connect to the KPI's from SSAS thru MOSS (as depicted in the blog you sent the link to), I'm asked for a ODC connection.

How do I create/determine what my ODC connection is to my SSAS Cube/KPIs? I've seen ways to create it via Excel, but once again, can I bypass Excel and just create the ODC myself?

Thanks.
|||The Office Data Connection Wizard (which I think is shared among Office apps like Excel and InfoPath) allows you to create ODC files - there may be tools to create ODC files outside of Office, but I'm not aware of them.

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
SarahIf the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2M
SFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set u
p
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQ
L
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is show
n
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah|||Thanks! Does this mean to re-run the OS installation? I'm assuming it does.
No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:%23O
5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSF
TNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mysql
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
SarahThis is a multi-part message in MIME format.
--=_NextPart_000_0014_01C4D0C3.B586D890
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks! Does this mean to re-run the OS installation? I'm assuming it =does. No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:%23O5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for =an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You =will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
-- "Sarah S" <psusarah@.ncx.com> wrote in message =news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't =know.
I can't find a way to graphically view the databases on my server. =It is running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't =set up the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience =with mySQL (which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing =is shown relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
--=_NextPart_000_0014_01C4D0C3.B586D890
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks! Does this mean to re-run the OS =installation? I'm assuming it does. No SQL tools were installed originally.
"AnthonyThomas" wrote in message news:%23O5w9q0zEHA.=3120@.TK2MSFTNGP12.phx.gbl...
If the =SQL Server administrative tools have been installed, look for an Enterprise =Manager icon in the Programs\Microsoft SQL Server folder.

If they =haven't, rerun the installation and add the admin tools. You will also =have to reapply any service packs and hotfixes.

Sincerely,

Anthony = Thomas

--
"Sarah S" wrote in =message news:%23bIsDGqzEHA.=2192@.TK2MSFTNGP14.phx.gbl...I am embarassed even to ask this question here, but I truly don't know.I can't find a way to graphically view the databases on =my server. It is running Back Office SBS 4.5, which includes SQL =Server 7.0. I didn't set up the server, someone else did, but I do have =the installation disks.I don't know much about SQL Server, but =I've had some experience with mySQL (which I've used phpMyAdmin to view/edit).I've looked around for applications in the start =menu, but nothing is shown relating to SQL Server. Can anyone please help?Thanks for the help.Sarah

--=_NextPart_000_0014_01C4D0C3.B586D890--

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
If the SQL Server administrative tools have been installed, look for an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas

"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
|||Thanks! Does this mean to re-run the OS installation? I'm assuming it does. No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:%23O5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas

"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah

newbie: value expression... uses an aggregate expression w/o a sco

simply added a report parameter for a working report (which went fine and w/o
issue..)
next thing, went to build the report in VS.NET and got 100 Build Errors
telling me that every TextBox on my summary page has the error:
'The value expression for the textbox '...' uses an aggregate expression
with a scope...'
Newbie (I know), but what on earth did I do that would have screwed up
everything like this?
( Scope for what? The report and parameters already have everything they
need as far as data goes.)
Rob(this is a frickin nightmare...)
So it looks like every time I try to add a new report parameter, after
deleting the original (simply the report parameter became a different report
item), about half of the textboxes which are sums and counts threw this error
(which is nutty as heck, since about 2/3, which have the same thing are OK).
Is this some kind of bug? (Deleted all Report Parameters and I can compile
w/o errors.)
What's going on with this and what does it take to 'set the scope'.
Rob
"RobKaratzas" wrote:
> simply added a report parameter for a working report (which went fine and w/o
> issue..)
> next thing, went to build the report in VS.NET and got 100 Build Errors
> telling me that every TextBox on my summary page has the error:
> 'The value expression for the textbox '...' uses an aggregate expression
> with a scope...'
> Newbie (I know), but what on earth did I do that would have screwed up
> everything like this?
> ( Scope for what? The report and parameters already have everything they
> need as far as data goes.)
> Rob
>

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 Tongue Tied 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:

For reference: My current XMLA query to discover the datasources is:

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):

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

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.

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)

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:

For reference: My current XMLA query to discover the datasources is:

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):

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

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.

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)

Anyway, thanks for your help!
Philipp

Newbie: Updating record with User information

Hello,

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd like to
also update this value when the record is changed. If I understand things
correctly, I'll need a trigger to do this, but I've never created a trigger
before. Is this an easy thing to accomplish?

Thanks!

RickRico (you@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol

SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)

The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.

A trigger fires once per statement, so there can be many rows in these
tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That's excellent! Thank you very much for your help Erland, that was
exactly what I needed! (that will make coding in VB much easier. ;)

Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98ADF09B38EBDYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (you@.me.com) writes:

Quote:

Originally Posted by

>I have a table that has a UserUpdated field which has a default value of
>SUSER_SNAME(). This works great when the record is created, but I'd
>like to also update this value when the record is changed. If I
>understand things correctly, I'll need a trigger to do this, but I've
>never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jan 3, 4:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Rico (y...@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdatedfieldwhich has adefaultvalueof
SUSER_SNAME(). This works great when the record is created, but I'd
like to alsoupdatethisvaluewhen the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATEASUPDATEtbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

Thanks.
Rayne|||Rayne (wifetalks@.gmail.com) writes:

Quote:

Originally Posted by

No, but you could write a program to generate them.

You could also consider third-party tools ApexSql (www.apexsql.com)
has SQL Audit, for instance. I have not tried it myself.

Newbie: UPDATE a Table

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:529989
Hi,
I want to insert some new data in a Table. There is a possibility that there
are duplicate rows .
An Insert is obviously not allowed due to PK violatons.
The new data reside on a temporary Table and there are NO duplicates!
Will an Update do?
I simply want to :
"If the row exists replace it, if not insert the new one"
TIA
-steveIf you know you are replacing the old rows when you have a match, then
why not just delete them and then insert this scrubbed data, like this?
BEGIN
DELETE FROM Foo
WHERE Foo.key_col
IN (SELECT key_col FROM Bar);
INSERT INTO Foo
SELECT * FROM Bar;
END;|||Ok, thanx!
But, for info, there is no command that will do this in one step ?
and in my case the PK consists of multiple columns. However i get an error:
DELETE FROM tblMeasQAHor
WHERE
(tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
tblMeasQAHor.Valide, tblMeasQAHor.val)
IN
(SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
why? i tried a few combinations and it doesn't work. If i only leave one
column then it's OK.
TIA
"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
> If you know you are replacing the old rows when you have a match, then
> why not just delete them and then insert this scrubbed data, like this?
>
> BEGIN
> DELETE FROM Foo
> WHERE Foo.key_col
> IN (SELECT key_col FROM Bar);
> INSERT INTO Foo
> SELECT * FROM Bar;
> END;
>|||You'll need to do this in 2 steps. Delete then Insert
For the delete, test this out before you execute it on production code.
Replace temp1
with whatever your temp table name is
DELETE FROM tblMeasQAHor
FROM tblMeasQAHor t1
JOIN temp1 t2 on t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val
"steve" <steve@.here.com> wrote in message
news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> Ok, thanx!
> But, for info, there is no command that will do this in one step ?
> and in my case the PK consists of multiple columns. However i get an
error:
> DELETE FROM tblMeasQAHor
> WHERE
> (tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
> tblMeasQAHor.Valide, tblMeasQAHor.val)
> IN
> (SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
>
> why? i tried a few combinations and it doesn't work. If i only leave one
> column then it's OK.
> TIA
> "--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
> 1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
>|||I just want to clarify. If you want to do it the way Joe recommends, you'll
need to do it in 2 steps. You could also do a correlated subquery
(untested)
INSERT INTO tblMeasQAHor
SELECT *
FROM temp1 t1
WHERE NOT EXISTS (
SELECT 'X' FROM tblMeasQAHor t2 WHERE t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val)
I'm not sure which you prefer. Also, I generally recommend using column
names in the insert and select. I don't know what the table looks like
structurally so I went with the select * approach.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:eY77X9vYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> You'll need to do this in 2 steps. Delete then Insert
> For the delete, test this out before you execute it on production code.
> Replace temp1
> with whatever your temp table name is
> DELETE FROM tblMeasQAHor
> FROM tblMeasQAHor t1
> JOIN temp1 t2 on t1.MENVid = t2.MENVid
> AND t1.date_time = t2.date_time
> AND t1.CodePol = t2.CodePol
> AND t1.Valide = t2.Valide
> AND t1.val = t2.val
>
> "steve" <steve@.here.com> wrote in message
> news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> error:
this?
>

newbie: TSQL command for number of rows ?

I couldnt find on google a command to get the number of rows in a table.
Any suggestions ?
TIA
-steveSELECT COUNT(*)
FROM YourTable
David Portas
SQL Server MVP
--|||Use the TOP statement
Example:
USE NORTHWIND
GO
Select TOP 5 From Orders
But remember that TOP is also recommended with the use of order because the
TOP clause will produce an unpredictable subset of n-Rows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"steve" <steve@.here.com> schrieb im Newsbeitrag
news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||SELECT COUNT(*) FROM tbl
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <steve@.here.com> wrote in message news:Ph4ie.56688$th3.616072@.wagner.videotron.net.
.
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||Ok, guess I didnt get your questions that right X-)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:u7Wq6ljWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Use the TOP statement
> Example:
> USE NORTHWIND
> GO
> Select TOP 5 From Orders
> But remember that TOP is also recommended with the use of order because
> the TOP clause will produce an unpredictable subset of n-Rows.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steve" <steve@.here.com> schrieb im Newsbeitrag
> news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>|||I am useless!
Thanx a lot!!
-steve
"steve" <steve@.here.com> a crit dans le message de news:
Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>

Saturday, February 25, 2012

Newbie:why osql doesn't let me logon ?

Newbie:why osql doesn't let me logon ?

Newbie:why osql doesn't let me logon ?

Newbie:UNION with SELECT TOP

Newbie:preparation to reindex SQL db

Newbie:preparation to reindex SQL db

Newbie:preparation to reindex SQL db

Newbie:if query is successful, how to response.write on screen ?

Newbie:help on maximum of a series of columns

Newbie: XML as datasource

Newbie: working with expressions on tables

Newbie: which replication strategy should I use?

Newbie: what kind of "cube" is this?

Newbie: what kind of "cube" is this?

Newbie: Viewing KPIs thru MOSS

newbie: viewing data in SQL Server 7.0

newbie: viewing data in SQL Server 7.0

newbie: viewing data in SQL Server 7.0

newbie: value expression... uses an aggregate expression w/o a sco

Newbie: Use XMLA to connect to SQL Server

Newbie: Use XMLA to connect to SQL Server

Newbie: Use XMLA to connect to SQL Server

Newbie: Updating record with User information

Newbie: UPDATE a Table

newbie: TSQL command for number of rows ?

Metadata Services

Blog Archive

About Me