March 6, 2008 at 3:08 pm
I need to create views on our production database for production support.
However, there is a table with a xml schema collection attached and I get this error when trying to create a view -
Views and inline functions cannot return xml columns that are typed with a schema collection registered in a database other than current. Column "LMPXML" is typed with the schema collection "LMPXML", which is registered in database "LMP".
Anyway around this?
March 7, 2008 at 2:10 pm
Views and inline functions cannot return xml columns that are typed with a schema collection registered in a database other than current. Column "LMPXML" is typed with the schema collection "LMPXML", which is registered in database "LMP".
Are you creating the views in a database that is not LMP?
March 7, 2008 at 3:13 pm
Yes, I was trying to create the views in a separate database.
We don't want them to have direct access to the production database and limit what fields they can see...
March 7, 2008 at 4:49 pm
You should be able to fix this with CAST() or CONVERT().
[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]
March 7, 2008 at 4:59 pm
I have never tried what you are attempting to do before. Based on the error message, I would say that you cannot accomplish your task on that particular column.
You could put the same query you were going to use for the view into a stored procedure. The user should be able to exec the stored procedure remotely, even if it contains the xml column.
March 9, 2008 at 6:15 pm
suggestion: create the view in the required database and create a synonym for that view in the desired database.
March 10, 2008 at 3:39 pm
Thanks...
The convert worked and I was able to create the view in a different database than the source table.
Thanks for your help
March 10, 2008 at 5:21 pm
Glad I could help.
[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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply