April 18, 2011 at 11:58 pm
Hello,
I'm trying to use 'openrowset' to query and/or update a table in a remote database. Usually, it works fine, but it exhibits strange behavior for a particular table which has a few columns whose names contain a dot (.).
When I try an 'openrowset' select on that table, all the columns are returned except for those columns with dots in their names.
And if I try an update or insert into those columns, I get the error "'column_name' is an invalid column name".
Note that, querying or updating the table directly on the database itself, without openrowset, works fine, and the 'dot'-columns are returned by the select statement.
Is this to be expected? Can it be circumvented in any way or do I have to change the column names to remove the dots?
Any help is appreciated.
April 19, 2011 at 3:11 am
Check out the rules for identifiers in Books Online. Embedding a "dot" in the column name breaks the rules for regular identifiers and forces the use of delimiters. Why not change the dots to underscores?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 3:36 am
So, if I understand correctly, even with the use of square brackets around the column names, the dot is still not entirely legal within identifiers and will still pose a problem?
Your suggestion of replacing the dots with underscores is something I already had in mind, but I was just wondering if there was another issue involved.
Thank you very much.
April 19, 2011 at 3:47 am
put the column name with in squre brackets []
April 19, 2011 at 3:58 am
That's the issue, I've already placed the column names within square brackets, and the problem persists.
April 19, 2011 at 4:00 am
marwa.elghali (4/19/2011)
So, if I understand correctly, even with the use of square brackets around the column names, the dot is still not entirely legal within identifiers and will still pose a problem?Your suggestion of replacing the dots with underscores is something I already had in mind, but I was just wondering if there was another issue involved.
Thank you very much.
You can use almost any character you like for delimited identifiers - but in forcing the use of delimiters, you are placing a restriction on yourself, and more so by using dots which have a special meaning - not just to SQL Server but to client apps too. I think the problems you experienced which prompted your post are really only dipping your toes in the water.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy