Describing a table structure

Hi,
I would like to know the syntax in MS SQL 2K for describing a table, without
having to expand everything in the object browser.
I know in Oracle it is: desc <table_name>;
Thanks,
Jig.
Do you mean
sp_help tblname
"Jig Bhakta" wrote:

> Hi,
> I would like to know the syntax in MS SQL 2K for describing a table, without
> having to expand everything in the object browser.
> I know in Oracle it is: desc <table_name>;
> Thanks,
> Jig.
|||is that the only way?
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Do you mean
> sp_help tblname
> "Jig Bhakta" wrote:
|||This will create a script for it
http://www.mindsdoor.net/DMO/DMOScripting.html
and this will do a database or all databases on a server
http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi,
there may be other ways, but this gives the info in more
or less the same format as you get in Oracle. Just make
sure that you select 'Results in grid' from the Query tab.
Jig,
that is the correct format
Mark
[vbcol=seagreen]
>--Original Message--
>is that the only way?
>"Nigel Rivett" wrote:
describing a table, without
>.
>

Deployment issue : COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not va

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server, I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

In my Bin folder, I do have the reference to the Interop.DTS.dll. The web server admin tells me that the dtsffile.dll, dtspkg.dll, dtspump.dll and axscphst.dll are registered onto the web server.

I am struggling to find the reasons for this. I have tried to replicate this error onto my machine to help me finding out the cause of this problem but in vain!

Can you help please?

Thank you.

Hello,

Just an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job.

Hope this helps.

Deployment issue : COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either no

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server, I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

In my Bin folder, I do have the reference to the Interop.DTS.dll. The web server admin tells me that the dtsffile.dll, dtspkg.dll, dtspump.dll and axscphst.dll are registered onto the web server.

I am struggling to find the reasons for this. I have tried to replicate this error onto my machine to help me finding out the cause of this problem but in vain!

Can you help please?

Thank you.

Hello,

Just an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job.

Hope this helps.

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Dependencies of a table

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)

Table: Region

Field: Region_ID

--

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.

There is no ddl trigger in sql2k. So, it would be hard to track down the point in time of the creation of the column (unless you have profiler turned on).

The best you can do is to check syscomments for the object/column name that might be used in other porcesses.

|||

it could be a trigger.

expand em to the table you need.

right click the table and then click "manage trigger"

If the dropdownlist contains something other than new

then thats the trigger thats affecting your logic

Dependencies of a table

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)

Table: Region

Field: Region_ID

--

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.

Hello all,

Never mind the question I asked. I found the answer. FYI, just in case you want to know: It's under "manage trigger" within the Enterprise Manager.

Peter.

Dependencies are lost when alter an object

Hello,
I have an old problem but now it became ugly...
I have an object (for example a function) that is used in some views. If I am looking on dependencies, it shows me the 8 views whcih depends on it. But if I edit the function in Query Analyser and run the script (no modification on the script), it deletes
all the dependencies for the 8 views on it... The problem is worst because when I generate the initial snapshot for a merge replication, it will affect the generation order of the scripts and I get error when trying to create the views (on subscriber), s
aying that the function doesn't exist in database...
Any ideea how can I preserve the dependencies?
Thanks in advance
Catalin,
I've also wondered what is the cause of this. There was mention a while back
that it is caused by using enterprise manager to do certain changes to
objects rather than using QA. Also, it has been mentioned that renaming
objects can corrupt dependencies.
I haven't found a definitive article explaining this, but as a workaround,
if you have this problem, you might want to run sp_refreshview on your views
prior to replication.
HTH,
Paul Ibison
|||I don't work with EM, only with QA.
For example: I run this script, to create 2 functions:
CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
GO
CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM dbo.Child ())
GO
As you can see _Parent depens on Child.
At this moment everything is fine on dependencies (each one shows the other)
But if I execute the next script:
ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
I loose all the referencies. And I need to generate the initial snapshot for a replication. And because no dependencies found, it will create the function in the order of names (and "_Parent" is before "Child") and when it tries to create the database (on
subscriber) I get the error because it tries to create _Parent before Child...
|||Hi Catalin,
If you don't have very many complex dependencies among functions, you may be
able to restore the dependency information by altering _Parent after you
alter Child.
HTH
-Raymond
"Catalin" <anonymous@.discussions.microsoft.com> wrote in message
news:0D8E0108-CACC-4491-A1B2-DFCD4FE641C6@.microsoft.com...
> I don't work with EM, only with QA.
> For example: I run this script, to create 2 functions:
> CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> GO
> CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM
dbo.Child ())
> GO
> As you can see _Parent depens on Child.
> At this moment everything is fine on dependencies (each one shows the
other)
> But if I execute the next script:
> ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> I loose all the referencies. And I need to generate the initial snapshot
for a replication. And because no dependencies found, it will create the
function in the order of names (and "_Parent" is before "Child") and when it
tries to create the database (on subscriber) I get the error because it
tries to create _Parent before Child...
|||Yes, but... unfortunately I have a database with more that 1000 objects and I need to replicate it... In this case I need to "reconstruct" the dependencies tree. The simplest way that I think is to develop a small VB application that will try to re-establ
ish the order (trying each script to execute in a blank database and to determine the missing object)...

Deny select to "everyone" except object owner

Hello,
I want to create a table that has denies all select, update, delete
access for all users except the owner of the table. This isn't really
hard to do, however what I'm trying to accomplish is that access is
also denied from the SQL Server Manager, meaning the dbo must also be
denied.
Is this possible? I've tried to deny select access for the object on
the dbo, however this doesn't do anything...
Thanks in advance for any answers.
Best regards,Not possible - you cannot deny any permissions to the owner of the
database (ie. the dbo user within the database). And, as an extension
to that, you cannot deny any permissions in any database to members of
the sysadmin server role as they're implicitly owners of every database.
You can, however, create a login on the server, make it the owner of the
database (ie. the login will map to the dbo user within the database)
and then disable that login so nobody can use it. That would
effectively deny access to anything within the database to the dbo user
since that user would not even be able to connect to the SQL instance.
*mike hodgson*
http://sqlnerd.blogspot.com
Peter wrote:

>Hello,
>I want to create a table that has denies all select, update, delete
>access for all users except the owner of the table. This isn't really
>hard to do, however what I'm trying to accomplish is that access is
>also denied from the SQL Server Manager, meaning the dbo must also be
>denied.
>Is this possible? I've tried to deny select access for the object on
>the dbo, however this doesn't do anything...
>Thanks in advance for any answers.
>
>Best regards,
>
>

Deny permission to the object browser in query analizer

I have a sql server 2000 and i want that a user have only acess to a
determined table in a database. I don't want the user can browse the other
objects thru the object browser in the query analizer. Is that possible?
--
JLoboNo they can see objects. They can only access the objects
you grant permissions to though.
-Sue
On Wed, 15 Mar 2006 08:20:11 -0800, JLobo
<JLobo@.discussions.microsoft.com> wrote:

>I have a sql server 2000 and i want that a user have only acess to a
>determined table in a database. I don't want the user can browse the other
>objects thru the object browser in the query analizer. Is that possible?