Describe command.

Hi,

I have some (OLD) college notes that say if I type

Describe tablename;
Name NOT NULL? Type

in my SQL code window in access once run it will give me the structure of the table.

When I try to run I get a Invalid SQL statement error 'DELETE', 'INSERT'...

Where am I going wrong.

Thanksoh, it's simple, your old notes are wrong, access doesn't support that commandsql

descending dates in sql 2005 vs. sql 2000

I have been using Access 2003 as a front end to a SQL 2k database. Dates are sorted in a descending fashion. I recently upgraded the sql server database to 2005 and dates can no longer be sorted properly. I have set the database compatibility level to sql2000 within the sql2005 database engine but no luck. Any suggetions on how to fix this?

Thanks in advance.

Are you passing an explicit ORDER BY in the query?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
then transfer the data). You find this in Integration Services, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.
Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(
|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>
|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("sc
ript all objects and
then transfer the data). You find this in Integration Services, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("
script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wik...0Wizar
d

Deployment Question - problem with datasources...

Hi,
We have written a number of reports we would like to move to our Integration/Test Servers.
These reports, which access a Linked Informix database through a System ODBC DSN, work fine on
my development XP machine with a local MS SQL Server 2000.
Our Integration/Test system consists of:
- a MS SQL Server 2000 Server running on NT. On this box we created an ODBC DSN just like that on development.
- an IIS ASP.NET machine with .NET 1.1 Framework.
[Note: We did not install Report Manager and plan to access our Reports by URL.]
Problem: After modifying the ReportingServices Datasource to use our integration/test server, building,
then deploying to the target server we are getting the following error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Has anyone encountered this problem? And if so, what did you do to fix it? Many Thanks for any ideas!
So how did you install Reporting Services on your integration/testing server? This error happens when viewing the report using a URL? Can you access it through the RS web services?

Deployment and Configuration

I have several clients using several SQL server databases that have all been
setup on one machine which they access remotely using terminal services. Each
client has their own software installation with their own set of databases.
So, there would be many client installations on the same machine. How would i
setup reporting services so that each client has their own set of reports?
How would i go about deploying the reports for each client given that the
clients' databases would have different names and hence the connection
strings will be different? Please help!
regardsSet up a datasource for each client - unless you have hundreds, in which case
I am not sure.sql

Deploying reports and setting up reporting services on a clustered server

Hi

I have two questions:

1)

I need to deploy reports to a server in Spain, which I do not have direct access to. Is there any way that I can simply deploy the reports to a server here, that I have access to, and then copy the reports to the server in Spain? Or is there any other way? I cannot expect the customers in Spain to be able to use Microsoft Visual Studio 2005 themselves to deploy reports (they do not even have access to the tool).

2)

How do I set up reporting services on a clustered server? I have configured reporting services on both clusters, but somehow I cannot initialize both of them. Only on will be initialized at a time. Does anyone have experience with this?

Thanx :-)

Hello Heidi,

As for #1...

You can create the .RDL files from your machine, then place them in a location that the server in Spain has access to. Now, you can upload them into the reporting server via Report Manager. Select 'Upload File' from the menu bar in Report Manager, then navigate to the .RDL file and hit Ok.

For #2...

Are you trying to use an active/passive or active/active cluster? Here are a few links from Microsoft that might be able to help you.

http://msdn2.microsoft.com/en-us/library/ms157293.aspx
http://msdn2.microsoft.com/en-us/library/ms156453.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx

Hope this helps.

Jarret

Deploying Report Server behind web servers

We'd like to deploy our report servers behind our web tier in the app tier
in order to prevent our clients from being able to use url access directly.
My main concern has to do with a chance that a client may attempt to alter
the content of report parameters or otherwise probe around.
Our web code is able to forward requests to the report server and render
reports correctly, but toolbar functionality seems to be broken. The web
developer tells me that the toolbar functionality is too stateful, so
paging (as an example) won't work.
Is what we're trying to do (request forwarding) unsupported? Are my fears
regarding exposing the report server in our DMZ unwarranted? If so, I must
be missing something.What about building your own interfaace or getting the Report Stream from
Reporting Webservice to Qrite the Stream to the browser ? There you could
lock down your reporting server and connect to the RS with special lockedup
credentials.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JoeA" <joea@.nospam.net> schrieb im Newsbeitrag
news:Xns963D69BAFC14Emailmailcom@.207.46.248.16...
> We'd like to deploy our report servers behind our web tier in the app tier
> in order to prevent our clients from being able to use url access
> directly.
> My main concern has to do with a chance that a client may attempt to alter
> the content of report parameters or otherwise probe around.
> Our web code is able to forward requests to the report server and render
> reports correctly, but toolbar functionality seems to be broken. The web
> developer tells me that the toolbar functionality is too stateful, so
> paging (as an example) won't work.
> Is what we're trying to do (request forwarding) unsupported? Are my fears
> regarding exposing the report server in our DMZ unwarranted? If so, I
> must
> be missing something.|||Thanks for the reply. We have considered that - however we didn't want to
have to recreate all of the goodies in the toolbar.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in news:#1t7uEQRFHA.2136@.TK2MSFTNGP14.phx.gbl:
> What about building your own interfaace or getting the Report Stream
> from Reporting Webservice to Qrite the Stream to the browser ? There
> you could lock down your reporting server and connect to the RS with
> special lockedup credentials.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

deploying MSDE - what do I need to do

We're about to ship a product, and we've used access to develop it to make things easier, but we want to use MSDE.

I've never had any luck with MSDE -

1. I can import the database into a sql server database in enterprise manager, is there a way to import a database into a msde database in interprise manager?

2. what would I do in the installer? I need to install the msde engine right? and then run sql scripts on the engine?

Thanks in advance!Have you read this article...

http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp|||thanks! thats exactly what I needed :)

Deploying MSDE

I am writing an application in VB.Net that includes a database. I could use
either an Access database (MS Jet) or MSDE. I would rather use MSDE because
it has useful features like triggers and complex stored procedures, but I am
worried about deployment.
My users have all kinds of machines, running anything from Windows 98 to
Windows Server 2003. Some may already have MSDE or SQL Server 2000 installed,
most will not. I will use Installshield Express 4 to make the deployment
package. Some users will download the package from my web site, some will
receive CDs.
I am worried that the complexities of deploying to such a wide range of
target machines may be too great and that I should revert to using a Jet
database.
Does anybody have relevant experience? Are there any useful books or
articles that I could read?
Many thanks
Peter
I have been thru a similar situation where I had a VB.NET Apps with MSDE and
Installshield for deployment on all win OS. I dont have any books or articles
for the same but I could do that. Installing MSDE on a m/c where it already
has a MSDE or SQL svr is not an issue as u should install MSDE with a new
unique instancename always.
siaj
"PeterH" wrote:

> I am writing an application in VB.Net that includes a database. I could use
> either an Access database (MS Jet) or MSDE. I would rather use MSDE because
> it has useful features like triggers and complex stored procedures, but I am
> worried about deployment.
> My users have all kinds of machines, running anything from Windows 98 to
> Windows Server 2003. Some may already have MSDE or SQL Server 2000 installed,
> most will not. I will use Installshield Express 4 to make the deployment
> package. Some users will download the package from my web site, some will
> receive CDs.
> I am worried that the complexities of deploying to such a wide range of
> target machines may be too great and that I should revert to using a Jet
> database.
> Does anybody have relevant experience? Are there any useful books or
> articles that I could read?
> Many thanks
> --
> Peter

Deploying Jet OleDb Provider

If I'm posting to the wrong forum, please correct me.

I'd like to use an Access database for an application I'm developing for retail sale, and I need to do these things:

Determine during setup if the target machine has the Jet OleDb Provider installed. If so, determine whether the version is adequate. If necessary, install the correct provider.

I don't seem to be able to find any documentation or downloads covering this scenario, however. This seems odd, since I would have imagined the scenario to be rather common.

Has anyone had any successful experience with these issues?

Thanks

Hi,

if you are having a installer package, you can query the registry for the MDAC key which is described and worked for me in many cases here:

http://support.microsoft.com/?kbid=301202

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That looks good, thanks.

Would you happen to know where I can get a redistributable Jet provider? I'm looking here:

http://msdn.microsoft.com/data/mdac/downloads/

But all of those are user-initiated setups. You're correct—I'm using an installer.

|||Hi there,

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257604

HTH, Jens Suessmeyer.

PS: The one included in VS is only to check the existence of MDAC.

http://www.sqlserver2005.de
|||

Ah, you're the greatest!

|||:-D Nice to hear that. :-D

-Jens.|||

Say, tell me... did you look those articles up specifically for this thread, or did you already have them on hand.

The reason I ask is because of the cumbersome task of sifting through the KB. I know I probably shouldn't, but lately I've pretty much given up on trying to find anything useful there among the myriad of articles that don't apply to the issue I'm looking for.

You must be pretty good at it.

|||Thats something in the middle, for the most cases I ran into the same problems / issues like the posters. Knowing the *right* keywords from my experience its easy to find the right articles on the internet.

-jens.|||

Ah, I see. Makes sense.

Tell me—since clearly you've already fought this battle—do you have your programs install MDAC in order to support Access, or do you instead opt for the OleDb provider?

The reason I ask is, according to my research on the subject, MDAC stopped including it after version 2.5. In fact, the second KB article you referred me to (thanks again) discusses MDAC only, and not OleDb.

That said, have you even been able to find a silent install for the standalone OleDb provider?

Thanks.

Deploying a report to a remote server

How do I deploy are report that is not on my network?
I need to deploy my report to the production server. I have FTP access, and
I usually Remote Desktop to the machine, but its not on my network.
When I try to deploy to the remote server, I get an error saying that that
server can't be accessed.
What is the solution? How can I deploy my reports?
Thank you!!I believe all you need to do is copy the RDL to a folder accessible from the
production server, then Add the report on the production server. There's a
button to Browse that lets you pick the RDL file from a folder on the
production server.
At my company (www.icco.com) we have built a tool to deploy batches of
reports at a time. If you are interested contact me by email or phone (954)
5471293.
"Julie" wrote:
> How do I deploy are report that is not on my network?
> I need to deploy my report to the production server. I have FTP access, and
> I usually Remote Desktop to the machine, but its not on my network.
> When I try to deploy to the remote server, I get an error saying that that
> server can't be accessed.
> What is the solution? How can I deploy my reports?
> Thank you!!

deploy to another server

I do not have access to VS, but I hear it can do this... how can I do it without VS?

I am using CREATE ASSEMBLY to load dll form my server to SQLServer on my server (Same machine). How can I load the dll to another server? Do I have to write it on a directory on that server first? I tried to run the command from my pc connected to the other server, and I get a "cannot find file" error.

The FROM clause in CREATE ASSEMBLY can either take a path to the dll (which is what you are doing), but it can also instead of the path take the binary representation of the dll.

So in your case, you can either use a path which the remote server accesses and has permissions to - or you can take the binary representation of the assembly instead.

Niels

|||Thanks. How do I do that binary representation? All the documentation I see is about the path to the dll. I'm new to .net development so I don't know where to go for help.|||

The syntax is

CREATE ASSEMBLY <asm_name> FROM 0x...

where 0x... is the hex binary representation.

The simplest way to get this is the use Management Studio. Choose the assembly in the object explorer, and click Script Assembly As -> Create To > New Query Editor Window. This will output the right syntax for you.

An alternative is code like below to convert the assembly to binary.

using System.IO;

using System;

using System.Text;

public class FileToHexBinary

{

public static void Main(String[] args)

{

FileStream fs = new FileStream(args[0], FileMode.Open);

int size = (int)fs.Length;

byte[] bytes = new byte [size];

fs.Read (bytes, 0, size);

fs.Close ();

StringBuilder builder = new StringBuilder(size * 2);

foreach( byte b in bytes)

{

builder.Append(b.ToString("x2"));

}

Console.WriteLine("0x" + builder.ToString());

}

}

|||Why b.ToString("x2")? What does the x2 mean? Whats the difference between "x" and "x2"?

deploy SQL2000 database to other server

hi,
I am sorting data from all different sources, xml, access, text..etc
to my own sql2000 server. (computer name is "testingmachine")
atfter I clean up the database, I will give the final database to the
client and install in their server (computer name is "SQLS")
1) what's the best way to move database structure and data over to
their server? there are few different methods, which one is the best
solution in my situation. thank you!
PS. I use *.adp ("access running time" as frontend client interface)
thank you!
This KB article should give you a few methods. You can decide which way is
best for your situation:
http://support.microsoft.com/default...46&Product=sql
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Bob Yang" <bobyang3@.hotmail.com> wrote in message
news:35582154.0406301458.2553431b@.posting.google.c om...
> hi,
> I am sorting data from all different sources, xml, access, text..etc
> to my own sql2000 server. (computer name is "testingmachine")
> atfter I clean up the database, I will give the final database to the
> client and install in their server (computer name is "SQLS")
> 1) what's the best way to move database structure and data over to
> their server? there are few different methods, which one is the best
> solution in my situation. thank you!
>
> PS. I use *.adp ("access running time" as frontend client interface)
> thank you!

deploy SQL2000 database to other server

hi,
I am sorting data from all different sources, xml, access, text..etc
to my own sql2000 server. (computer name is "testingmachine")
atfter I clean up the database, I will give the final database to the
client and install in their server (computer name is "SQLS")
1) what's the best way to move database structure and data over to
their server? there are few different methods, which one is the best
solution in my situation. thank you!
PS. I use *.adp ("access running time" as frontend client interface)
thank you!This KB article should give you a few methods. You can decide which way is
best for your situation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546&Product=sql
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Bob Yang" <bobyang3@.hotmail.com> wrote in message
news:35582154.0406301458.2553431b@.posting.google.com...
> hi,
> I am sorting data from all different sources, xml, access, text..etc
> to my own sql2000 server. (computer name is "testingmachine")
> atfter I clean up the database, I will give the final database to the
> client and install in their server (computer name is "SQLS")
> 1) what's the best way to move database structure and data over to
> their server? there are few different methods, which one is the best
> solution in my situation. thank you!
>
> PS. I use *.adp ("access running time" as frontend client interface)
> thank you!

deploy SQL2000 database to other server

hi,
I am sorting data from all different sources, xml, access, text..etc
to my own sql2000 server. (computer name is "testingmachine")
atfter I clean up the database, I will give the final database to the
client and install in their server (computer name is "SQLS")
1) what's the best way to move database structure and data over to
their server? there are few different methods, which one is the best
solution in my situation. thank you!
PS. I use *.adp ("access running time" as frontend client interface)
thank you!This KB article should give you a few methods. You can decide which way is
best for your situation:
http://support.microsoft.com/defaul...546&Product=sql
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Bob Yang" <bobyang3@.hotmail.com> wrote in message
news:35582154.0406301458.2553431b@.posting.google.com...
> hi,
> I am sorting data from all different sources, xml, access, text..etc
> to my own sql2000 server. (computer name is "testingmachine")
> atfter I clean up the database, I will give the final database to the
> client and install in their server (computer name is "SQLS")
> 1) what's the best way to move database structure and data over to
> their server? there are few different methods, which one is the best
> solution in my situation. thank you!
>
> PS. I use *.adp ("access running time" as frontend client interface)
> thank you!

deploy SQL Server 2000

I have read a very good article, "Creating and Deploying Access
Solutions with the Microsoft Data Engine" by Scott Smith, published in
January 1999. Now i wish to do the same thing, I mean install sql
server 2000, at the end of an installation.
I think I have to change the line:
start /wait msdex86.exe -s -a -f1 "sql70ins.iss"
, which "extract and install MSDE on the user's machine".
But I still coudn't find what to use instead of it.
Thanks !
RaduHave a look here :- http://www.microsoft.com/sql/msde/
--
HTH
Ryan Waight, MCDBA, MCSE
"Radu Cernea" <r_cernea@.yahoo.com> wrote in message
news:6ff3b57a.0311100635.c0d963@.posting.google.com...
> I have read a very good article, "Creating and Deploying Access
> Solutions with the Microsoft Data Engine" by Scott Smith, published in
> January 1999. Now i wish to do the same thing, I mean install sql
> server 2000, at the end of an installation.
> I think I have to change the line:
> start /wait msdex86.exe -s -a -f1 "sql70ins.iss"
> , which "extract and install MSDE on the user's machine".
> But I still coudn't find what to use instead of it.
> Thanks !
> Radu

Deploy database develop from MSDE

Hi,
I have a small access database develop using msde as back end. I want to put
this backend database on shared drive so that other user can access to it.
My question is
1 - Does I need to setup MSDE on every workstation?
2 - Can other user connect to this database using ODBC?
SF
Thank you very much for this useful tip.
SF
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:4s0fftFsu16iU1@.mid.individual.net...
> hi,
> SF wrote:
> SQL Server/MSDE is not a file based database like Access is... so you only
> have to put the database on the machine hosting the MSDE instance (and not
> on a shared folder).. obviously every machine in the lan can then access
> the MSDE service...
> ODBC api? yes, if you like the hard way, but you probably shoud consider
> other data acces methods, like ADO, Ado.Net and the like...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim

|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm