|
I have this code, how i can place the result to the MSword ? 
SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM
sys.columns c
LEFT OUTER JOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = ''MS_Description''
WHERE
OBJECTPROPERTY(c.object_id, ''IsMsShipped'')=0
ORDER
BY OBJECT_NAME(c.object_id), c.column_id '
Thank u before... 
Irvinia
|
|
|
|
|
You may want to read up on Office Interop or PIA.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Thank u Paul, i'll try.
Irvin
|
|
|
|
|
Not a problem 
Do a search on this site and there are some articles floating around here in regards to Office Interop.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
I'm not sure exactly how to do this which is why I don't know exactly what to put in the post Subject. Pretty new to SQL. Help is appreciated.
I want to use the information from a system view to get information about table permissions in our database.
Right now I use:
select Grantee, Table_Name, Privilege_Type from information_schema.table_privileges
which gives me something like this:
GRANTEE---TABLE_NAME----PRIVILEGE_TYPE
User1------Table1----------Update
User1------Table1----------Insert
User1------Table1----------Select
User2------Table1----------Select
User2------Table1----------Update
But it's over 1500 records long. I'd prefer something like this:
TABLE-----USER1-----------------------USER2
Table1-----Insert/Select/Update--------Select/Update
Can anyone tell me how I could do that? Or have any better ideas of how to view the information_schema.table_privileges data in a more readable format?
Thank you!
|
|
|
|
|
The readability would be a matter of an opinion
If you set up your results as in your post, you could have one very long row (depending on how many users you have).
Perhaps, you could get a starting point if you take tables as rows and then users for each table with fixed columns as privileges. Something like the following. Then if you like you can start pivoting on tables or users using sub selects in FROM section and modify the structure as you like.
Also notice that you can place FOR XML AUTO at the end of the query, if it makes the results more readable.
select userinfo.Table_Name,
userinfo.Grantee ,
case when selectinfo.GRANTOR is not null then 'Yes' else '' end SelectPermission,
case when insertinfo.GRANTOR is not null then 'Yes' else '' end InsertPermission,
case when updateinfo.GRANTOR is not null then 'Yes' else '' end UpdatePermission,
case when updateinfo.GRANTOR is not null then 'Yes' else '' end DeletePermission
from information_schema.table_privileges as userinfo
left outer join
information_schema.table_privileges as selectinfo
on userinfo.grantee = selectinfo.grantee
and userinfo.table_catalog = selectinfo.table_catalog
and userinfo.table_schema = selectinfo.table_schema
and userinfo.table_name = selectinfo.table_name
and selectinfo.PRIVILEGE_TYPE = 'SELECT'
left outer join
information_schema.table_privileges as insertinfo
on userinfo.grantee = insertinfo.grantee
and userinfo.table_catalog = insertinfo.table_catalog
and userinfo.table_schema = insertinfo.table_schema
and userinfo.table_name = insertinfo.table_name
and insertinfo.PRIVILEGE_TYPE = 'INSERT'
left outer join
information_schema.table_privileges as updateinfo
on userinfo.grantee = updateinfo.grantee
and userinfo.table_catalog = updateinfo.table_catalog
and userinfo.table_schema = updateinfo.table_schema
and userinfo.table_name = updateinfo.table_name
and updateinfo.PRIVILEGE_TYPE = 'UPDATE'
left outer join
information_schema.table_privileges as deleteinfo
on userinfo.grantee = deleteinfo.grantee
and userinfo.table_catalog = deleteinfo.table_catalog
and userinfo.table_schema = deleteinfo.table_schema
and userinfo.table_name = deleteinfo.table_name
and deleteinfo.PRIVILEGE_TYPE = 'DELETE'
order by Table_Name, Grantee
The need to optimize rises from a bad design
|
|
|
|
|
I believe the answer to your problem is to use a Pivot query in SQL 2005. I'm not sure how to due this, but it's worth investigating.
|
|
|
|
|
Hi
How to remove Identity(1,1) property only from column in table in sql server 2000.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
You must recreate the table so:
- get the data into another table
- drop the table
- create the table without identity specification
- place the data back to the new table
The need to optimize rises from a bad design
|
|
|
|
|
try a google search for "drop identity constraint tsql"
|
|
|
|
|
Do you have any specific article in mind? Note that this was for version 2000.
The need to optimize rises from a bad design
|
|
|
|
|
I generally find SQL Server Books OnLine is an ideal place to start with help with SQL Server but that is just me.
|
|
|
|
|
Yeah, but I'm under the impression that in version 2000 the identity can only be disabled. Constraints can be dropped, but is identity modelled as constraint in 2000? Didn't find any clear answer for that.
The need to optimize rises from a bad design
|
|
|
|
|
I have a 'product' table, with an integer foreign key into a 'productLine' table. I populate a Products menu with one child item for each product line, and when that menu item is clicked, an image gallery for that product line opens. My issue is that I need the product line name on the gallery page, so I must either pass the productLineId in the url, and use it as a filter parameter in my product image query, and perform a separate lookup to get the product line name, or join the two tables, and pass the product line name as part of the url. I don't feel comfortable with using the join only for this reason. What are the general opinions on a matter like this?
|
|
|
|
|
Personally, unless there is a very good reason not to, I would pass the name and id. You need the id to look up the images, but database access is relatively expensive so I would avoid the round trip just to get the name.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: I would avoid the round trip just to get the name.
I'm already avoiding the round trip by using a denormalised view, where each product record includes the product name view.
|
|
|
|
|
Well there you go, you are doing it the way I would. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How many of these lookups do you have? It seems likely your data layer would be caching them anyway...
|
|
|
|
|
I have file in macro word name : Dataku.
is anybody could tell me, how to execute that file from sql 2005.
Thanks before. 
irvinia
|
|
|
|
|
irvinia wrote: I have file in macro word name : Dataku.
is anybody could tell me, how to execute that file from sql 2005.
Are you saying you want to run a macro in MS Word from within your database? I don't think that is possible...
|
|
|
|
|
Yes, i want to run macro in MS Word from database, and maybe that's not possible. ok, i will try looking for other way.
thank u for your replay, ChandraRam
|
|
|
|
|
If you're trying to use the article you mentioned before(www.codeproject.com/KB/miscctrl/data_dictionary.aspx[^]), the idea is that you place the macro in a new Word document (using macro editor), modify it and run it.
If it is something else you're after, please describe with more details.
The need to optimize rises from a bad design
|
|
|
|
|
Thank you for your replay.
i just try to change that code from that site to sql statement (maybe it's not simple), because i'm looking for the same method but with different language.
anyway, thanks again, Mike. i will looking for other way... 
|
|
|
|
|
hi all,
i simply write a small code of piece which create backup of Sql server database. It works well but whenever i set the target path of back file to Desktop( or program files folder) then it gives following error
"Can not open backup device" & Target Path & ". Operating system error 5(Access is denied) /or Operating system error 3(Error not found).
Any body can help me to resolve this error ?
|
|
|
|
|
Hi Rupesh,
The backup crated by the sql server can be stored only on the machine where the SQL server is installed. Just create a folder in SQLServer and give full permission to this folder and then try to create the backup. I hope it will work.
Thx
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
