question

Katie 1 avatar image
Katie 1 asked

Running scripts in ssms

Hello everyone,

I have a bunch of DDL's that are to be run on a db.All the script is doing is dropping a bunch of tables, constraints and then creation of some tables. The problem is when i run the script all together as a single script, i get the error

"Cannot find the object " dbo.example" because it does not exist or you do not have permissions"

I see a bunch of these errors when i run it altogether.. to trouble shoot.. ran each single script to drop or create the constraint, table separately, it works fine. Even the execution order also does not change.. but still.. I dont understand .. why does this happen?

Thanks!

sql-server-2005t-sqladministrationddl
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
can you post the script?
3 Likes 3 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - we'll need an example of the script really...
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
has the script been run more than once and then failed midway? Assuming that the script does not have a transaction block associated with it.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This ought to work, so there's either something up with the script or it's in the wrong database... schema's... Not sure without seeing what's going on.
0 Likes 0 ·

2 Answers

·
Slaven avatar image
Slaven answered

Are you use GO between statments?

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Katie 1 avatar image Katie 1 commented ·
Yes I am using it.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

Clearly, for some reason, either you haven't created example at all or it isn't in the schema dbo. Either you gave it a different schema or you didn't specify a schema and it defaulted to something other than dbo. If you create an object without specifying the schema it gets added to the schema associated with the current user.

Run the create part and check what schema the object is in.

Maybe you used credentials that have DDL permissions but isn't a member of the server role sysadmin or of the database role db_owner. Try executing select current_user. If it doesn't return dbo then this could be the problem.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Katie 1 avatar image Katie 1 commented ·
David it is a dbo schema.
0 Likes 0 ·
David Wimbush avatar image David Wimbush commented ·
It's a tough one. Can you post the script? Otherwise we're all just guessing really.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.