maximyshka
asked on
When using SQL Server 2012 as a warehouse, "unknown type" is displayed for columns
I was provided access to the datawarehouse in SQL Server 2012, when i connect I see that most of the data types for columns in the table shown as "unknown type" . Does any one know why?
Is there any difference between Datawarehouse and regular ms sql sdatabase?
Is there any difference between Datawarehouse and regular ms sql sdatabase?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I use ssms, however it shows "uknown type"
I need to connect to data from access, and i can't because data type for some fields shown as unknown.
I need to connect to data from access, and i can't because data type for some fields shown as unknown.
Please post a screen shot.
ASKER
below please find image , i put distortion on field names.
image wasn't attached...
but there may well be a big difference. It you are talking about a datawarehouse as being part of Analysis Services, then you will need SSDT Sql Server Data Tools. It essentially replaces BIDS in 12 and 14 and normally accessed via VS
Check out some tutorials on : http://msdn.microsoft.com/en-us/library/hh231701(v=sql.110).aspx
And the team blog on : http://msdn.microsoft.com/en-au/data/tools.aspx
If on the other hand it is a traditional datawarehouse - being essentially a database with tables, then the only reason that I can think of is it is a linked server and/or connected via an odbc style connection.
Would help to see that image, and also how you connect via SSMS.
but there may well be a big difference. It you are talking about a datawarehouse as being part of Analysis Services, then you will need SSDT Sql Server Data Tools. It essentially replaces BIDS in 12 and 14 and normally accessed via VS
Check out some tutorials on : http://msdn.microsoft.com/en-us/library/hh231701(v=sql.110).aspx
And the team blog on : http://msdn.microsoft.com/en-au/data/tools.aspx
If on the other hand it is a traditional datawarehouse - being essentially a database with tables, then the only reason that I can think of is it is a linked server and/or connected via an odbc style connection.
Would help to see that image, and also how you connect via SSMS.
make sure it is SSMS 2012
ASKER
We figutred out the reason why we had uknown data types. There are user defined dat types and account didn't have permissions to those.
Thanks for the help.
Thanks for the help.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for maximyshka's comment #a40392352
for the following reason:
Although there are some answers which may cause similar probllem, the cause for my problem was in permissioning
Accepted answer: 0 points for maximyshka's comment #a40392352
for the following reason:
Although there are some answers which may cause similar probllem, the cause for my problem was in permissioning
IF user defined data element, then could be an Alias, but, if user defined data types created by using "CREATE ASSEMBLY ..." and/or whenever assembly-name is speccified, then yes, needs REFERENCES permission.
Would have helped us if you had that image, and probably would have helped clarifying the meaning of "DATAWAREHOUSE" (ie by seeing your desktop / ssms connection, we could determine if it was a database).
Good to hear that you were able to solve the problem...
Would have helped us if you had that image, and probably would have helped clarifying the meaning of "DATAWAREHOUSE" (ie by seeing your desktop / ssms connection, we could determine if it was a database).
Good to hear that you were able to solve the problem...
So my guess was correct..
*laughing* nearly, but no cigar :)
Your inference / guess wasn't specific to (nor implied) user defined datatypes, but a more open and generic comment about maybe permissions to do with meta data views through the use of "some tool", indeed asking "what tool".
The obvious problem (with your guess) is we were told that SSMS was clearly being used as the (connection) tool to try to view the data types...
Had you expanded a little more on your "guess", had we seen the asked for image, then there might be more substance that you could have offered.
I did find it a little strange that a site dealing with user defined data types (using assembly-names) would not have automatically granted "REFERENCES" permission to everyone having access to that data...
Your inference / guess wasn't specific to (nor implied) user defined datatypes, but a more open and generic comment about maybe permissions to do with meta data views through the use of "some tool", indeed asking "what tool".
The obvious problem (with your guess) is we were told that SSMS was clearly being used as the (connection) tool to try to view the data types...
Had you expanded a little more on your "guess", had we seen the asked for image, then there might be more substance that you could have offered.
I did find it a little strange that a site dealing with user defined data types (using assembly-names) would not have automatically granted "REFERENCES" permission to everyone having access to that data...
ASKER
@ste5en yes i did not realized that you wrote it . Gave you the credit.
some reason i had difficulty uploading files .thanks every one for help.
some reason i had difficulty uploading files .thanks every one for help.
Actually, I slightly disagree that ste5en got the right answer...
Permissions is a fairly safe "guess" in sql server, but there was information provided (such as using SSMS) that was not part of the guess (citing "some tools" and "metadata views" asking "what tool").
And a guess does not make it a correct answer. See my previous comment.
But, it is your question and your prerogative... And no problems with that privilege.
And no problem with ste5en either, just more a recognition that he "snagged" an answer more so than making the right guess.
Permissions is a fairly safe "guess" in sql server, but there was information provided (such as using SSMS) that was not part of the guess (citing "some tools" and "metadata views" asking "what tool").
And a guess does not make it a correct answer. See my previous comment.
But, it is your question and your prerogative... And no problems with that privilege.
And no problem with ste5en either, just more a recognition that he "snagged" an answer more so than making the right guess.
Come on Mark ;) It's a pretty good guess. Just consider the absence of information in the original post..
btw, I like Carpenter's The Fog =:)
btw, I like Carpenter's The Fog =:)
ASKER
Mark, SSMS was actually suggested and i just said that i use it. I could not upload image some reason. It didn't work. We have difficult Admin, so the way we checked is some on logged in with admin credentials and it worked.
Alas... Admin types almost imply a right to be difficult :)
Yes, I did see that SSMS was specified in your post, and generally a great tool to use.
Becoming part of the sysadmin group will solve most SQL type permission issues, and always a good litmus test.
I am not wanting to point the finger at anyone... Indeed Stefan is a Microsoft MVP for SQL Server and as such earns a great deal of respect (especially from me).
The reason for my follow up was not as an objection, more so and mainly for any body else coming along with a similar problem so they can see a more comprehensive discussion.
And in being a MVP, Stefan is also aware of the precision associated with the "guess" especially when part of the thrust of his guess was asking "what tool". And, is probably the next question to be posed, being access from Access.
Speaking of which, when you access via Access, you might still have some issues and might need to manipulate : http://technet.microsoft.com/en-us/library/ms131694(v=sql.110).aspx
I happen to believe that your answer was the correct answer, and Stefan ably assisted by the mention of permissions.
But no harm, no foul and I am truly happy that you did get your answer.
Apologies for causing any undue concern.
Yes, I did see that SSMS was specified in your post, and generally a great tool to use.
Becoming part of the sysadmin group will solve most SQL type permission issues, and always a good litmus test.
I am not wanting to point the finger at anyone... Indeed Stefan is a Microsoft MVP for SQL Server and as such earns a great deal of respect (especially from me).
The reason for my follow up was not as an objection, more so and mainly for any body else coming along with a similar problem so they can see a more comprehensive discussion.
And in being a MVP, Stefan is also aware of the precision associated with the "guess" especially when part of the thrust of his guess was asking "what tool". And, is probably the next question to be posed, being access from Access.
Speaking of which, when you access via Access, you might still have some issues and might need to manipulate : http://technet.microsoft.com/en-us/library/ms131694(v=sql.110).aspx
I happen to believe that your answer was the correct answer, and Stefan ably assisted by the mention of permissions.
But no harm, no foul and I am truly happy that you did get your answer.
Apologies for causing any undue concern.
@Mark, you're welcome :)
And for Salomos sake: I wasn't after the points in this case. The community here would really benefit from an acurate description of the problem and the solution (attachements included ;).
btw, I still need to guess: It should be sufficient to grant VIEW ANY DEFINITION to that user. When it's about INFORMATION_SCHEMA then the user needs the appropriate access to the objects.
And for Salomos sake: I wasn't after the points in this case. The community here would really benefit from an acurate description of the problem and the solution (attachements included ;).
btw, I still need to guess: It should be sufficient to grant VIEW ANY DEFINITION to that user. When it's about INFORMATION_SCHEMA then the user needs the appropriate access to the objects.
Difference between Data Warehouse and Database
/ from http://www.differencebetween.info/difference-between-data-warehouse-and-database /
"Key difference: A data warehouse is a database used to store data. It is a central repository of data in which data from various sources is stored. The data warehouse is then used for reporting and data analysis. A database, on the other hand, is the basis or any data storage. It is an organized collection of data. A database is used to store data while a data warehouse is mostly used to facilitate reporting and analysis."