March 29, 2011 at 10:19 am
Morning all,
We have a third party app that was installed and uses some really generic names. We have been attempting to setup synonyms so that the marketing department can do some excel queries directly against the data and allow them to better understand the table structure. The problem we are having is that no matter what we do the synonyms do not show up in Excel. We are using SQL 2005 SP2 with Office 2007. We can see the synonyms everwhere except in excel even after selecting show synonyms.
We have done weeks worth of interent searches and trouble shooting but I have not seen any posts or articles were others have had this problem.
Has anyone run across this before and if so what have you done to overcome this problem?
Thanks in advance for any input you might have to share.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 29, 2011 at 3:12 pm
Side comment ... Why aren't you on SP3 ?
Any time someone is on an old version, it raises the question of if their problem would be fixed by being on the current release.
March 29, 2011 at 3:16 pm
No good reason other than just have not got around to testing it with the product on the server. This is a mission critical server so it takes alot longer to get any changed approved by all the appropriate parties.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 29, 2011 at 4:47 pm
Is there a specific motivation in your organization towards using synonyms over views containing SELECT * FROM SCHEMA_NAME.TABLE_NAME in this scenario?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2011 at 7:07 am
The motivation comes from the overly generic names used by the thir party software. For example the table structure looks something like this.
Bi001
Bi002
Bi003
Now thoise tables are actually the account data customer data and address tables but you would never get that from the names they were given. The synonyms would allow the marketing people to use this data a little more easily by identifying the table in something more user freindly.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2011 at 7:24 am
I meant a motivation to use synonyms, specifically. Have you considered using views to accomplish the same end result?
Something like this:
CREATE VIEW dbo.account_data
AS
SELECT *
FROM dbo.Bi001 ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2011 at 7:31 am
Ah well I suppose we could use a veiw but this seemed like a proper use for a synonym. Excel has a check box to show synonyms so it seems like it should actually show them. We've already created the synonyms for the 400+ tables and while I would go the route of creating all the veiws required if necessary I would like to exaust this avenue first.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2011 at 1:01 pm
Not sure I can add much however I've just tried this on my system which is running Microsoft SQL Server 2005 - 9.00.3077.00 (X64) SP2 and Excel 2007 and I could see the synonyms in Excel without any issues.
Could it be a permission issue on the synonyms where the excel user connection cannot access them? Just thinking out loud here.
March 30, 2011 at 1:31 pm
I thought it might be a permission issue at first but we are using windows autenticated and I am and admin so that should not really be an issue.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 12, 2014 at 1:12 pm
Hi
I have sql 2008 r2 sp2 installed.I have created a synonym that pulls the data from the remote table.
I want to pull the data from Excel 2007 using the synonym. for some reason this is not working.
Has anybody have the same issue? Appreciate your help on this, thank in advance.
September 12, 2014 at 2:46 pm
We can see the synonyms everwhere except in excel even after selecting show synonyms.
Where did you select this option to show synonyms?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply