Failed insert via Enterprise Manager

Hey guys..

Trying to insert a row via Enterprise Manager I get "object does not exist" error.. However it works on another server?!?

The table name is aa.aabc .. So i know what the problem is.. it thinks that aa is the owner.. I can insert using query analyzer by using the following syntax: insert into [aa.aabc] ... but it fails using: insert into aa.abc .. for obvious reasons..

I'm guessing it has something to do with the differences in the server config between the 2 servers as to why one server you can insert via enterprise manager and the other you cant..

The collation is different as the ANSI warning and padding in the connection info..

Can anyone else help me fix this problem, so that I can insert using enterprise manager?

CheersINSERT into EM Where?|||Databases --> Databasename --> Tables --> Tablename --> Return all rows.. Then you can scroll to the bottom row and insert in a similar way to access..

THanks

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnaceHi
"stephanie" wrote:

> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John|||Yes. But I changed the user with default schema to another schema, e.g. 'db
o'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>|||Hi
"stephanie" wrote:

> Yes. But I changed the user with default schema to another schema, e.g. '
dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John|||a user schema called : guest
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema a
re
> you dropping?
> John|||Hi
"stephanie" wrote:
[vbcol=seagreen]
> a user schema called : guest
> "John Bell" wrote:
>
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in advnace

Hi Stephanie,

Could you please post the exact message that you get?

What is the schema name that you are trying to delete?

Check to see if it owns any objects:

Code Snippet

select count(*) from sys.objects where [schema_id]=schema_id('<schema_name>')

Jag

|||

The following query should show what objects exist under the schema foo

select name from sys.objects where schema_id = schema_id('foo')

union

select name from sys.types where schema_id = schema_id('foo')

union

select name from sys.xml_schema_collections where schema_id = schema_id('foo')

Some schemas are undroppable: sys, dbo, information_schema

hth,

-Steven Gott

S/DET

SQL Server

|||

my schema is called guest and I amended the script

there is no row returned.

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnace
Hi
"stephanie" wrote:

> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John
|||Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:

> Hi
> "stephanie" wrote:
>
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>
|||Hi
"stephanie" wrote:

> Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John
|||a user schema called : guest
"John Bell" wrote:

> Hi
> "stephanie" wrote:
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> you dropping?
> John
|||Hi
"stephanie" wrote:
[vbcol=seagreen]
> a user schema called : guest
> "John Bell" wrote:
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

Fail to drop schema - Error 15150

I want to drop a schema but it shows Fail to drop schema - Error 15150.
I found that there is no object under the schema. Any idea? Thanks in
advnaceHi
"stephanie" wrote:
> I want to drop a schema but it shows Fail to drop schema - Error 15150.
> I found that there is no object under the schema. Any idea? Thanks in
> advnace
Is this the default schema for anyone?
SELECT * FROM sys.database_principals
WHERE default_schema_name = 'dbo'
John|||Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
I can't drop that schema with the same error 15150. Thanks
"John Bell" wrote:
> Hi
> "stephanie" wrote:
> > I want to drop a schema but it shows Fail to drop schema - Error 15150.
> > I found that there is no object under the schema. Any idea? Thanks in
> > advnace
> Is this the default schema for anyone?
> SELECT * FROM sys.database_principals
> WHERE default_schema_name = 'dbo'
> John
>|||Hi
"stephanie" wrote:
> Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> I can't drop that schema with the same error 15150. Thanks
>
Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
you dropping?
John|||a user schema called : guest
"John Bell" wrote:
> Hi
> "stephanie" wrote:
> > Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> > I can't drop that schema with the same error 15150. Thanks
> >
> Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> you dropping?
> John|||Hi
"stephanie" wrote:
> a user schema called : guest
> "John Bell" wrote:
> > Hi
> >
> > "stephanie" wrote:
> >
> > > Yes. But I changed the user with default schema to another schema, e.g. 'dbo'
> > > I can't drop that schema with the same error 15150. Thanks
> > >
> > Schemas such as dbo and INFORMATION_SCHEMA can't be dropped. What schema are
> > you dropping?
> >
> > John
Guest seems to be another schema that can't be dropped even if the schema
for the guest user is changed.
John

F8 to open object explorer. How to close?

You can use the F8 key to open object explorer. How do you close it with keyboard shortcut?

Unfortunately, it doesn't toggle closed (like in QA).

However, you can set the 'Auto-Hide' property to True and it will 'mostly' disappear when not in use.

|||

Arnie is correct. Please file this as an issue on Microsoft Connect. http://connect.microsoft.com/sqlserver/feedback/.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

F7 Key : Summary -> Object Explorer Details

Hi,

Where is the F7 key that in the past showed the Summary?

The Summary has been changed to 'Object Explorer Details' but is no more accessible by pressing the F7 key :-(

Why did you do that MS ?

You can set your keyboard scheme as that of Sql 2000. Go to Tools -> Options. Select Environment -> Keyboard and then choose the 'SQL Server 2000' Keyboard scheme.

Hope that helps.

Thanks,
Kuntal

|||

Sure it helps !

Thanks a lot.

|||With keyboard sheme SQL Server 2000. F2 for renaming doesn't work. I am not all happy with that sheme..., looking forward for a real fix or better walk-around.

F7 Key : Summary -> Object Explorer Details

Hi,

Where is the F7 key that in the past showed the Summary?

The Summary has been changed to 'Object Explorer Details' but is no more accessible by pressing the F7 key :-(

Why did you do that MS ?

You can set your keyboard scheme as that of Sql 2000. Go to Tools -> Options. Select Environment -> Keyboard and then choose the 'SQL Server 2000' Keyboard scheme.

Hope that helps.

Thanks,
Kuntal

|||

Sure it helps !

Thanks a lot.

|||With keyboard sheme SQL Server 2000. F2 for renaming doesn't work. I am not all happy with that sheme..., looking forward for a real fix or better walk-around.

extracting DDLs in SQL Server

I really apprciate any idea's

On extracting ddl's for each object on a separate file in SQL Server?

Something like ddl's for:
procedure1.sql, procedure2.sql....so on procedure100.sql
onto a file server.

Does SQL Server has any API's like Oracle's dbms_metadata?

Or can we use SQL enterprise manager to do this job?

Thanks, Madhavi.In Query analyser, you can right click on most objects in the object browser
and select "Script object to" and choose your reference....

In enterprise manager you can right click on a DB and select "Generate SQL Script"...from there a window appears where you can choose what object types to script (Tables/UDF/Stored procs/User defined types...)|||Thanks

How can I automate this process? As, I will have to schedule this process to run daily.

Madhavi.|||I'd use SQL-DMO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_85f7.asp).

-PatP

Extracting Contents of Object Variable in script Task

Hey Guys,

Im pretty new to Scripting in SSIS, but i have worked a fair bit out already, although i am stuck with the following problem.. If anyone can help out that would be great!!

Ok, i have a package that loops through records in a table, and extracts the detailed lines in a For Each loop. and stores them in a variable called DetailRecordSet with an data type of object.

I have a built a script task so that i can send out an email confirmation for each order, and i want to list the details in a HTML Table in the body of the message ( I have read that the standard send mail task wont support this, but found an example of using .NET code to generate the email message which will support it) .

What i want to know is how do i reference the columns in the Object variable so i can extract each line and add it into my string that i am creating with HTML codes.. I

e:( str = "<TABLE> <TR>" + Cstr(Variable for 1st column in recordset) + " </TR>"

if any can help my, that would be most appriciated.. Ive tried all different things that ive found on the net, but nothing is working.

Thanks in Advance

Scotty

To know how to interact with SSIS object variable's underlying type, you need to know the type itself. That is, the type of the "thing" stored by reference in the SSIS object variable.

What the actual underlying type of the variable stored in the SSIS variable of type object?

System.Windows.Forms.MessageBox.Show(Dts.Variables("DetailRecordSet").Value.GetType().FullName)

The above MessageBox() will display the underlying type in your script task, excepting Com callable wrappers (of type System.__Object).
|||

Sorry for the late reply Jaegd. but have been away for a few days,,

This object type is System.Object. from the variable section i select the data type to be Object.

I appricate your help still, cause i am still stuck

Thanks

Scotty

|||

As per the previous reply, knowing what is in the object is crucial. From your reply it is a COM object, so I'll guess that it is probably an ADO recordset. IN which case take a look here for some links and pointers - http://blogs.conchango.com/jamiethomson/archive/2005/02/08/SSIS_3A00_-Accessing-a-recordset-in-a-script-task.aspx

If that does not help, then help me, as I don't understand how you got there. So what has populated the variable exactly, and what method was used? If it is the For Each loop, what enumerator have you used?

Extracting Attribute Values from XML Using XQuery

I am serializing a .NET object as XML and passing it as a parameter to a stored procedure. I am new to XQuery and have tried other places to find the answer to this question but have so far been unsuccessful.

How can I get the value of an attribute out of an XML document as a standard SQL Server data type? For instance, if my XML document were:

<RootNode>
<Object ID = "1234">
<AnElement>Some Other Value</AnElement>
</Object>
</RootNode>

What would be the correct XQuery to get the value of the ID attribute in the Object element?

Thanks in advance.

Kevin J Lambert

create table #xml_temp (

xml_col xml

)

insert into #xml_temp values('<RootNode>

<Object ID = "1234">

<AnElement>Some Other Value</AnElement>

</Object>

</RootNode>')

--With query() function

select xml_col.query('data(/RootNode/Object/@.ID)') from #xml_temp

--With value() function

select xml_col.value('data(/RootNode/Object/@.ID)[1]','int') from #xml_temp