May 12, 2009 at 9:38 am
RBarryYoung (5/12/2009)
Actually, looking at the error again, it looks more like a schema & schema permission problem than a DB one. It is apparently trying to create the table [JaneDoe].[whatever]. The first thing to ask is should it be doing that?If the answer is yes, then check to make sure that the JaneDoe schema exists, and that she is the owner of it.
If the answer is no, then her default schema is wrong and probably should be set to dbo.
I don't think it's trying to create a table named JaneDoe. The error message says "Creating DeleteOptions table", so I think it's trying to create a table named "DeleteOptions". But, I think it's acting like it wants to use the JaneDoe schema. Which is absurd, because there's no such schema. I can run it as myself, but there's no schema with the same name as my user name.
I have noticed that if I change her user name for this database, it changes the error message accordingly (which seems kind of bizarre). And her default schema is dbo. I don't get it. I am probably missing something very obvious and will kick myself for it later.
May 12, 2009 at 9:44 am
Roy Ernest (5/12/2009)
Glad to be of help. Now you can debug the issue much more efficiently. There are couple of articles written here in SqlServercentral.com itself that will help you with working with Profiler. One of them is written by Brad Mcgehee. It is written in 3 parts. That should help you.
Sounds like a must-read. I will go check it out. Thank you!
May 12, 2009 at 9:56 am
jblevins (5/12/2009)
RBarryYoung (5/12/2009)
Actually, looking at the error again, it looks more like a schema & schema permission problem than a DB one. It is apparently trying to create the table [JaneDoe].[whatever]. The first thing to ask is should it be doing that?If the answer is yes, then check to make sure that the JaneDoe schema exists, and that she is the owner of it.
If the answer is no, then her default schema is wrong and probably should be set to dbo.
I don't think it's trying to create a table named JaneDoe. The error message says "Creating DeleteOptions table", so I think it's trying to create a table named "DeleteOptions". But, I think it's acting like it wants to use the JaneDoe schema.
Yes, that is what I was saying, [JaneDoe].[whatever] is a tbale named "whatever" in the "JaneDoe" schema. That appears to be what it is trying to do.
Which is absurd, because there's no such schema. I can run it as myself, but there's no schema with the same name as my user name.
Nonetheless, that's what the error message seems to indicate:
The specified schema name "JaneDoe" either does not exist or you do not have permission to use it.Creating DeleteOptions table - Filter Server Main
The "schema name "JaneDoe" either does not exist or" indicates that it is trying to use that schema. Unless it is explicitly naming the schema, as in "[JaneDoe].[DeleteOptions]", the logical implication is that her schema is defaulting to that. Or it is possible that the app really is trying to create personal tables in the user's personal schema.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 10:23 am
Yes, that is what I was saying, [JaneDoe].[whatever] is a tbale named "whatever" in the "JaneDoe" schema. That appears to be what it is trying to do.
Ok. Now I get what you are saying. I can't imagine why this stupid app would be trying to do that. Do you have any ideas as to why it would be attempting this?
Nonetheless, that's what the error message seems to indicate:
The specified schema name "JaneDoe" either does not exist or you do not have permission to use it.Creating DeleteOptions table - Filter Server Main
The "schema name "JaneDoe" either does not exist or" indicates that it is trying to use that schema. Unless it is explicitly naming the schema, as in "[JaneDoe].[DeleteOptions]", the logical implication is that her schema is defaulting to that. Or it is possible that the app really is trying to create personal tables in the user's personal schema.
The app does not explicitly name the schema. I had her user name as "Jane Doe" and it complained about a syntax error near "Doe". So I made it "JaneDoe", and now I get this new error. Let me clarify that.
Her user name in Databases -> Security -> Logins is "DOMAIN\jdoe". The live ERP database is named "LIVE". So in Databases -> LIVE -> Security -> Users she is "JaneDoe". It is by changing this name that it changes the error message. I had her as "DOMAIN\jdoe" to match the login, but it barked at me about the "\" being an invalid character.
Should I remove all references to her and try again from scratch?
May 12, 2009 at 12:05 pm
Maybe, but I don't think it's a database problem, per se, but rather some disconnect between how the database is setup vs., what the app thinks that it should be. You might want to try using Profiler to see exactly what they app is trying to do in SQL when this error occurs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2009 at 8:53 am
Many thanks to all for your helpful suggestions. The user is still unable to run the application. Perhaps it is better this way. The app uses Excel to manipulate 2.5 million rows of data (increasing by 60,000 rows or so every month) using cursors (yeah, it's a speed demon alright). Her computer is probably too slow to run it anyway. On a box with a quad core Q6600 and 4GB RAM it takes over an hour to run the process. The user has an older, single core laptop with 1GB RAM. I don't think it's worth messing with any more.
Thanks again to all for your help.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply