Is it Possible to query a Cube with Query Analyzer?

i would like to query either a cube or dimension direclty using tsql and query analyzer. i thought i would be able to use openquery but cant figure out the proper syntax. any help would be appreciated.

This is an example in a previous discussion here. I have not tried this example myself.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=658049&SiteID=1

HTH

Thomas Ivarsson

Is it possible to make a HTTP Post in TSQL? (SQL Server 2000)

I am new to TSQL and cannot find anything in my reference book or on the
internet about how this can be done.
If so, can you let me know how.
Many thanks
TomI am not sure if this is what you are talking about, but in 2k there is the
Web Assistant Wizard in Enterprise Manager. This will create an HTML file
based on your query. You can also use sp_makeweb in SQL 2k. If you are
using 2005 you must enable these as default is set to off. Use:
sp_configure 'Web Assistant Procedures', 1;
GO
Although this feature is still supported in 2005 it will be deprecated in
future releases as Microsoft wishes to focus more on Reporting Services for
this functionality.
BOL in both 2k and 2005 covers these features.
Hope this is helpful
"Tom" wrote:

> I am new to TSQL and cannot find anything in my reference book or on the
> internet about how this can be done.
> If so, can you let me know how.
> Many thanks
> Tom|||Thanks I'll look into this.
To be more explicit on what I am trying to do - I am converting a JAVA
procedure to T-SQL and need to post an XML document. (open a URL connection
and send an XML document to that URL)
In PL/SQL I know I would have to declare a PL/SQL function that refers to
(wraps a)a JAVA method that will do the actual posting. The PL/SQL function
can then be reffered to in other PL/SQL code as though it is a PL/SQL
function.
Can I use the wizard to do this? Are we talking at cross purposes...
"Derekman" wrote:
> I am not sure if this is what you are talking about, but in 2k there is th
e
> Web Assistant Wizard in Enterprise Manager. This will create an HTML file
> based on your query. You can also use sp_makeweb in SQL 2k. If you are
> using 2005 you must enable these as default is set to off. Use:
> sp_configure 'Web Assistant Procedures', 1;
> GO
> Although this feature is still supported in 2005 it will be deprecated in
> future releases as Microsoft wishes to focus more on Reporting Services fo
r
> this functionality.
> BOL in both 2k and 2005 covers these features.
> Hope this is helpful
> "Tom" wrote:
>

Is it ok for a SQL2K5 TSQL job to use a UNC pointing to a db on same

Microsoft generally recommends against networked db's per
http://support.microsoft.com/kb/304261. Unfortunately, there is a
dispute in our organization and advice from local Microsoft resources
against changing Trace flag 1807 to use UNCs even if the UNC points to
a local db (i.e., dbms, job and db are on the same server). A
Microsoft SQL SME even suggested the use of UNCs makes the local db i/
o go through the server's network stack even though everything is on
the same machine.
Currently, our SQL jobs use mapped drives. In order to for our SQL
jobs to be imported into our Tidal Enterprise Scheduler environment we
have to use UNCs for them to be parsed corrected by Tidal.
Any substantive clarity would be greatly appreciated.I fail to see what you would change the location of the database files (to UNC), where you really
seem to be talking about jobs. Two different things.
I too believe that UNC result in some pass through some network stack. For instance, try starting
SQL Server using LocalSystem and do some xp_cmdshell or backup to an UNC, even if local. ... from
memory, I should add.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<kkekoa@.gmail.com> wrote in message
news:ec667a6f-3cb1-4d7b-96f8-79472c2eac7b@.p69g2000hsa.googlegroups.com...
> Microsoft generally recommends against networked db's per
> http://support.microsoft.com/kb/304261. Unfortunately, there is a
> dispute in our organization and advice from local Microsoft resources
> against changing Trace flag 1807 to use UNCs even if the UNC points to
> a local db (i.e., dbms, job and db are on the same server). A
> Microsoft SQL SME even suggested the use of UNCs makes the local db i/
> o go through the server's network stack even though everything is on
> the same machine.
> Currently, our SQL jobs use mapped drives. In order to for our SQL
> jobs to be imported into our Tidal Enterprise Scheduler environment we
> have to use UNCs for them to be parsed corrected by Tidal.
> Any substantive clarity would be greatly appreciated.|||Jobs should have no direct contact with ANY of the SQL Server database
files, so the question should not come up. The only process that
touches those files is the SQL Server service. When jobs - or
anything else - needs to interact with a database it interacts with
the SQL Server service, and the service does all the work with the
files.
For whatever a job has to do that relates to files UNC paths are fine.
And I agree that there will be another layer of work using a UNC path
rather than the letter of a directly connected drive. I don't believe
there would be any difference between a mapped network drive and a UNC
reference to the same drive.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 00:00:45 -0800 (PST), kkekoa@.gmail.com wrote:
>Microsoft generally recommends against networked db's per
>http://support.microsoft.com/kb/304261. Unfortunately, there is a
>dispute in our organization and advice from local Microsoft resources
>against changing Trace flag 1807 to use UNCs even if the UNC points to
>a local db (i.e., dbms, job and db are on the same server). A
>Microsoft SQL SME even suggested the use of UNCs makes the local db i/
>o go through the server's network stack even though everything is on
>the same machine.
>Currently, our SQL jobs use mapped drives. In order to for our SQL
>jobs to be imported into our Tidal Enterprise Scheduler environment we
>have to use UNCs for them to be parsed corrected by Tidal.
>Any substantive clarity would be greatly appreciated.|||<kkekoa@.gmail.com> wrote in message
news:ec667a6f-3cb1-4d7b-96f8-79472c2eac7b@.p69g2000hsa.googlegroups.com...
> Microsoft generally recommends against networked db's per
> http://support.microsoft.com/kb/304261. Unfortunately, there is a
> dispute in our organization and advice from local Microsoft resources
> against changing Trace flag 1807 to use UNCs even if the UNC points to
> a local db (i.e., dbms, job and db are on the same server). A
> Microsoft SQL SME even suggested the use of UNCs makes the local db i/
> o go through the server's network stack even though everything is on
> the same machine.
I believe that's correct. It would be a very bad idea to use Trace flag 1807
and UNCs for local database files. Why would you want to do that?
> Currently, our SQL jobs use mapped drives. In order to for our SQL
> jobs to be imported into our Tidal Enterprise Scheduler environment we
> have to use UNCs for them to be parsed corrected by Tidal.
>
I don't understand what you mean. Jobs should have nothing to do with where
your databases are located. In fact jobs reside in the MSDB database. Jobs
are processed by the SQL Agent service so they will have access to whatever
drives the agent does - drive letters or UNC. Could you explain a bit more
about what the problem is.
--
David Portas

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.
INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>
|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any differenc
e.
I am modifying the script sp_comparedb originally written by Viktor Gorodni
chenko.
His script only compares the table schema. I am adding ability to compare in
dexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1
for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the d
atabase you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from dbo.sysindexke
ys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>>I am using SQL Server 2000 8.00.760 SP3
>> I am writing a TSQL Script to compare two databases and report any difference.
>> I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
>> His script only compares the table schema. I am adding ability to compare indexes,
>> Primary Keys and Foreign keys. All is working well except at one place.
>> DESC Key. If an index is declared with a DESC column, then the following two
>> cases behave differently.
>> use database_name
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from dbo.sysindexkeys
>> where id = object_id('any_table_name')
>> returns 1 for those columns of an index which has DESC clause.
>> Now the problem is that I don't use the database. Since the script
>> compares two different databases, it runs in the master databases
>> and loads all information from the databases_to_be_compared
>> into temp tables using EXEC call. So this is what I do
>> use master
>> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
>> 'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from '
>> +
>> @.dbname + '.dbo.sysindexkeys'
>> When this query runs, it returns null for all columns.
>> This can easily be tested in Query Analyser as follows
>> use master
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from different_database.dbo.sysindexkeys
>> where id = object_id('different_database.dbo.any_table_name')
>> Compare the results with the first query. While the first will return 0 or 1 for
>> descending,
>> the second will always return NULL.
>>
>>
>