How to fix SQLExpress "attempt to attach an auto-named database" e

I'm authoring a .net webservice that connects to a SQLExpress database in the
same project. When the code calls the database the following exception is
thrown:
"An attempt to attach an auto-named database for file
C:\[...]\App_Data\App_Data\MyDatabase.mdf failed. A database with the same
name exists, or specified file cannot be opened, or it is located on UNC
share."
I've read all the online help I can find but none of the suggestions work.
* I've deleted the SQLExpress folder in my ApplicationData
* I've made sure to enable user instances in the server and RECONFIGURE
* I've restarted the service
* I've ensured that ASPNET and NETWORK SERVICE has rights to the APP_DATA
folder.
Nothing changes. What is the cause of this issue' How can I fix it'
Thanks,
JasonSolved.
It took finally writing this post to see that the rendered connection string
renders "APP_DATA" twice.
This is how the project defaults - I didn't do that. Screw them.
Jason
"Jason" wrote:
> I'm authoring a .net webservice that connects to a SQLExpress database in the
> same project. When the code calls the database the following exception is
> thrown:
> "An attempt to attach an auto-named database for file
> C:\[...]\App_Data\App_Data\MyDatabase.mdf failed. A database with the same
> name exists, or specified file cannot be opened, or it is located on UNC
> share."
> I've read all the online help I can find but none of the suggestions work.
> * I've deleted the SQLExpress folder in my ApplicationData
> * I've made sure to enable user instances in the server and RECONFIGURE
> * I've restarted the service
> * I've ensured that ASPNET and NETWORK SERVICE has rights to the APP_DATA
> folder.
> Nothing changes. What is the cause of this issue' How can I fix it'
> Thanks,
> Jason|||Congratulations!
Have a great day!
Charles Wang
Microsoft Online Community Support|||But it still doesn't work right. After the change, it will not have that
error in run-time, but now it won't work in design-time. The DataSet
visualizer can't find the datasource from the given connectionString.
This is using the Application project template for WebService and not the
Website template. That may be part of the problem.
Jason
"Charles Wang[MSFT]" wrote:
> Congratulations!
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Thanks for your response.
I recommend that you try to connect the SQL Server Express on other clients
via SQL Query Analyzer or other SQL client tools. If you manage to access
the server, this issue is most likely caused by some settings in your
Webservice IDE (vs.net 2005?). You can post this issue to .net public
community for best support.
Also, for now, I recommend that you check your connection string, by
default the SQLExpress instance name should be set to "<server
name>\SQLEXPRESS".
You may want these references:
http://blog.davebouwman.net/2006/03/27/SQLExpressConnectionProblems.aspx
http://msdn.microsoft.com/vbrun/vbfusion/usingsqlexpress/
http://www.connectionstrings.com/
Charles Wang
Microsoft Online Community Support|||Hi ,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support|||Charles,
Thanks, but no. Those suggestions seem to address a different issue. I
think what's happening here is that when using the non-website project
template for ASP.Net Webservice (downloaded from Microsoft's site) the
connection string contains an extra "APP_DATA" directory literal.
At design time, this is the correct setting and the dataset designers can
find and connect to the database correctly, but at runtime the "APP_DATA"
literal is /extra/ because the "|DataDirectory|" placeholder is resolved
differently to already include the APP_DATA folder.
I'm posting these findings to save others from frustration, but I've in fact
changed direction.
Jason
"Charles Wang[MSFT]" wrote:
> Hi ,
> Just checking in to see if the suggestions were helpful. Please let us know
> if you would like further assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Appreciate your posting here for having others benefit from this issue.
My former reply may not understand your issue clearly. I thought it was
just a normal SQL Server break/fix issue on connection.
This issue is most likely a .net development issue.I recommend that you
post this issue to public .net newsgroups for best professional support.
If you also want to get support on this newsgroup, I would like to get a
simple project example from you so that I can try to reproduce this issue
for further research.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||I just had the same problem. I noticed that Permissions on APP_DATA
were not being inherited by the .mdf and .ldf files. Made sure ASPNET
had permission to folder then changed to inherit permissions and all
works OK. Don't know security issues but this is a development site so
don't care.

How to fix SQLExpress "attempt to attach an auto-named database" e

I'm authoring a .net webservice that connects to a SQLExpress database in th
e
same project. When the code calls the database the following exception is
thrown:
"An attempt to attach an auto-named database for file
C:\[...]\App_Data\App_Data\MyDatabase.mdf failed. A database with the sa
me
name exists, or specified file cannot be opened, or it is located on UNC
share."
I've read all the online help I can find but none of the suggestions work.
* I've deleted the SQLExpress folder in my ApplicationData
* I've made sure to enable user instances in the server and RECONFIGURE
* I've restarted the service
* I've ensured that ASPNET and NETWORK SERVICE has rights to the APP_DATA
folder.
Nothing changes. What is the cause of this issue' How can I fix it'
Thanks,
JasonSolved.
It took finally writing this post to see that the rendered connection string
renders "APP_DATA" twice.
This is how the project defaults - I didn't do that. Screw them.
Jason
"Jason" wrote:

> I'm authoring a .net webservice that connects to a SQLExpress database in
the
> same project. When the code calls the database the following exception is
> thrown:
> "An attempt to attach an auto-named database for file
> C:\[...]\App_Data\App_Data\MyDatabase.mdf failed. A database with the
same
> name exists, or specified file cannot be opened, or it is located on UNC
> share."
> I've read all the online help I can find but none of the suggestions work.
> * I've deleted the SQLExpress folder in my ApplicationData
> * I've made sure to enable user instances in the server and RECONFIGURE
> * I've restarted the service
> * I've ensured that ASPNET and NETWORK SERVICE has rights to the APP_DATA
> folder.
> Nothing changes. What is the cause of this issue' How can I fix it'
> Thanks,
> Jason|||Congratulations!
Have a great day!
Charles Wang
Microsoft Online Community Support

how to find the windows path of custom assembly using c# code?

I am using a custom assembly. My assembly has to read a particular file and
take some values from it. I set the required permissions and everything works
good. However I dont want to hardcode the location of my text file. I decided
to put the text file in the same location as dll file i.e in ..\Program
Files\Reporting Services\ReportServer\bin folder and thought of finding the
path in my class library using something like
String pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
But this method always gives me #Error in my report expression. I also tried
by removing all other code and just returning the path. Also if I try to
return System.Reflection.Assembly.GetExecutingAssembly().FullName; it works
good. I am totally confused as how one property will work and another one
does not. Is there anything I am missing like setting some reference or
changing some permission etc.
I want to know the location of the dll i.e windows path(C:\Program
Files\Reporting Services\ReportServer\bin) using c# code which will work with
reporting services.
Thanks in advance.Probably the exception you are getting is FileIOPermission. If you look at
the Assembly.CodeBase Property topic in BOL, you will see that it requires
FileIOPermission. To confirm this, disable CAS in RS (comment the
<securityPolicy> element in Report Server web.config) and see if it will
execute fine.
See
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/2bd7a109fd6dbe4b/df80503c4773a1b0?lnk=st&q=fileiopermission+assert+group%3A*.reportingsvcs&rnum=1&hl=en#df80503c4773a1b0
or increase the expression CAS policy to FullTrust by changing the
MyComputer GodeGroup in rssrvpolicy.config as follows
<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Description="This code group grants
MyComputer code Execution permission. ">
<IMembershipCondition
class="ZoneMembershipCondition"
version="1"
Zone="MyComputer" />
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"msdnuser" <ringt@.nospam.nospam> wrote in message
news:3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com...
> I am using a custom assembly. My assembly has to read a particular file
> and
> take some values from it. I set the required permissions and everything
> works
> good. However I dont want to hardcode the location of my text file. I
> decided
> to put the text file in the same location as dll file i.e in ..\Program
> Files\Reporting Services\ReportServer\bin folder and thought of finding
> the
> path in my class library using something like
> String pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
> But this method always gives me #Error in my report expression. I also
> tried
> by removing all other code and just returning the path. Also if I try to
> return System.Reflection.Assembly.GetExecutingAssembly().FullName; it
> works
> good. I am totally confused as how one property will work and another one
> does not. Is there anything I am missing like setting some reference or
> changing some permission etc.
> I want to know the location of the dll i.e windows path(C:\Program
> Files\Reporting Services\ReportServer\bin) using c# code which will work
> with
> reporting services.
> Thanks in advance.
>
>|||Thanks for Teo's inputs,
Hi Ringt,
I think you can try checking the CAS permission as Teo suggested first. To
temporarly turn off .NET code access security, we can also execute the:
caspol -s off under the VS.NET command console.....
In addition, as for getting assembly path, we don't quite suggest use
codebase since this is not always correctly in some certain scenario (when
assemblies are added into GAC and the original path not in application's
directory...). I think you can also try
AppDomain.BaseDirectory , this will return the base physical location of
our application(if we execute it on the main appdomain......)..
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Tue, 13 Dec 2005 16:27:58 -0500
| Lines: 67
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: mail01-ips.ips-sendero.com 12.163.123.194
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:64985
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Probably the exception you are getting is FileIOPermission. If you look
at
| the Assembly.CodeBase Property topic in BOL, you will see that it
requires
| FileIOPermission. To confirm this, disable CAS in RS (comment the
| <securityPolicy> element in Report Server web.config) and see if it will
| execute fine.
|
| See
|
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_frm/thread/2bd7a109fd6dbe4b/df80503c4773a1b0?lnk=st&q=fileiopermission+as
sert+group%3A*.reportingsvcs&rnum=1&hl=en#df80503c4773a1b0
|
| or increase the expression CAS policy to FullTrust by changing the
| MyComputer GodeGroup in rssrvpolicy.config as follows
| <CodeGroup
| class="FirstMatchCodeGroup"
| version="1"
| PermissionSetName="FullTrust"
| Description="This code group grants
| MyComputer code Execution permission. ">
| <IMembershipCondition
| class="ZoneMembershipCondition"
| version="1"
| Zone="MyComputer" />
|
| --
| HTH,
| ---
| Teo Lachev, MVP, MCSD, MCT
| "Microsoft Reporting Services in Action"
| "Applied Microsoft Analysis Services 2005"
| Home page and blog: http://www.prologika.com/
|
| ---
| "msdnuser" <ringt@.nospam.nospam> wrote in message
| news:3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com...
| >
| > I am using a custom assembly. My assembly has to read a particular file
| > and
| > take some values from it. I set the required permissions and everything
| > works
| > good. However I dont want to hardcode the location of my text file. I
| > decided
| > to put the text file in the same location as dll file i.e in ..\Program
| > Files\Reporting Services\ReportServer\bin folder and thought of finding
| > the
| > path in my class library using something like
| >
| > String
pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
| >
| > But this method always gives me #Error in my report expression. I also
| > tried
| > by removing all other code and just returning the path. Also if I try to
| > return System.Reflection.Assembly.GetExecutingAssembly().FullName; it
| > works
| > good. I am totally confused as how one property will work and another
one
| > does not. Is there anything I am missing like setting some reference or
| > changing some permission etc.
| >
| > I want to know the location of the dll i.e windows path(C:\Program
| > Files\Reporting Services\ReportServer\bin) using c# code which will
work
| > with
| > reporting services.
| >
| > Thanks in advance.
| >
| >
| >
|
|
||||Hi Teo and Steven,
Thank you both for the replies. I am able to get the executing path of
assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I give
FullTrust permissionset to "MyComputer" and
"Report_Expressions_Default_Permissions" Code Groups. Is there a way I can
reduce the permission and make them work?
Note: I also have my own permission set and code group to give my dll, read
permission to my text file which is not a config file. I am using Reporting
Services 2005.
Thanks,
ringt|||The links I included in my previous post should get you started although in
your case it is like catch-22. You need to grant access to a path which is
known only at runtime. You have to do some reasearch to find if this is
possible at all.
Good luck!
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"msdnuser" <ringt@.nospam.nospam> wrote in message
news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> Hi Teo and Steven,
> Thank you both for the replies. I am able to get the executing path of
> assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I give
> FullTrust permissionset to "MyComputer" and
> "Report_Expressions_Default_Permissions" Code Groups. Is there a way I can
> reduce the permission and make them work?
> Note: I also have my own permission set and code group to give my dll,
> read
> permission to my text file which is not a config file. I am using
> Reporting
> Services 2005.
> Thanks,
> ringt
>|||Hi ringt,
I'm afraid grant the sufficient CAS permission is necessary. However, I
agree with you that it's not necessary to grant the FullTrust to
LocalComputer zone... You can choose to adjust the existing PermissionSet
, e.g the "Execution" or add a new named PermissionSet and add those
FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
to your own assembly, you can consider strong-named your assemtly so that
you can grant permissionSet through Strong-named condition.....
So far I haven't found any resource directly describing modifying the
reporting service's security policy file, but there is on good article
discussing customize ASP.NET 2.0 security policys:
#How To: Use Code Access Security in ASP.NET 2.0
http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
ue
this article mentioned the similiar ideas on customizing existing security
policy file and add FileIOPermission for certain CodeGroup....
Hope helps. Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
<e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
<P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
<8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Wed, 14 Dec 2005 21:27:19 -0500
| Lines: 38
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| The links I included in my previous post should get you started although
in
| your case it is like catch-22. You need to grant access to a path which
is
| known only at runtime. You have to do some reasearch to find if this is
| possible at all.
|
| Good luck!
|
| --
| HTH,
| ---
| Teo Lachev, MVP, MCSD, MCT
| "Microsoft Reporting Services in Action"
| "Applied Microsoft Analysis Services 2005"
| Home page and blog: http://www.prologika.com/
|
| ---
| "msdnuser" <ringt@.nospam.nospam> wrote in message
| news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
| > Hi Teo and Steven,
| >
| > Thank you both for the replies. I am able to get the executing path of
| > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
give
| > FullTrust permissionset to "MyComputer" and
| > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
can
| > reduce the permission and make them work?
| >
| > Note: I also have my own permission set and code group to give my dll,
| > read
| > permission to my text file which is not a config file. I am using
| > Reporting
| > Services 2005.
| >
| > Thanks,
| > ringt
| >
| >
|
|
||||Hi there,
As Steven mentioned, I have to give fullTrust permission to get the path
during runtime. Even then as Toe have mentioned asserting permissions to a
path known at runtime is not successful to me. It throws me #Error again. Is
there any other way (even by reducing or modifying some permissions) to
implement this?
Expecting your help.
Thanks.
"Steven Cheng[MSFT]" wrote:
> Hi ringt,
> I'm afraid grant the sufficient CAS permission is necessary. However, I
> agree with you that it's not necessary to grant the FullTrust to
> LocalComputer zone... You can choose to adjust the existing PermissionSet
> , e.g the "Execution" or add a new named PermissionSet and add those
> FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
> to your own assembly, you can consider strong-named your assemtly so that
> you can grant permissionSet through Strong-named condition.....
> So far I haven't found any resource directly describing modifying the
> reporting service's security policy file, but there is on good article
> discussing customize ASP.NET 2.0 security policys:
>
> #How To: Use Code Access Security in ASP.NET 2.0
> http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
> ue
> this article mentioned the similiar ideas on customizing existing security
> policy file and add FileIOPermission for certain CodeGroup....
>
> Hope helps. Thanks,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
> --
> | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
> | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
> <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
> <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
> <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
> | Subject: Re: how to find the windows path of custom assembly using c#
> code?
> | Date: Wed, 14 Dec 2005 21:27:19 -0500
> | Lines: 38
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The links I included in my previous post should get you started although
> in
> | your case it is like catch-22. You need to grant access to a path which
> is
> | known only at runtime. You have to do some reasearch to find if this is
> | possible at all.
> |
> | Good luck!
> |
> | --
> | HTH,
> | ---
> | Teo Lachev, MVP, MCSD, MCT
> | "Microsoft Reporting Services in Action"
> | "Applied Microsoft Analysis Services 2005"
> | Home page and blog: http://www.prologika.com/
> |
> | ---
> | "msdnuser" <ringt@.nospam.nospam> wrote in message
> | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> | > Hi Teo and Steven,
> | >
> | > Thank you both for the replies. I am able to get the executing path of
> | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
> give
> | > FullTrust permissionset to "MyComputer" and
> | > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
> can
> | > reduce the permission and make them work?
> | >
> | > Note: I also have my own permission set and code group to give my dll,
> | > read
> | > permission to my text file which is not a config file. I am using
> | > Reporting
> | > Services 2005.
> | >
> | > Thanks,
> | > ringt
> | >
> | >
> |
> |
> |
>|||Hi Teo and Steven,
Please ignore my last reply in this thread. I made a mistake of not
appending my file name after I find the path using
System.AppDomain.CurrentDomain.BaseDirectory. Thus my problem is solved now.
Thank you very much.
"msdnuser" wrote:
> Hi there,
> As Steven mentioned, I have to give fullTrust permission to get the path
> during runtime. Even then as Toe have mentioned asserting permissions to a
> path known at runtime is not successful to me. It throws me #Error again. Is
> there any other way (even by reducing or modifying some permissions) to
> implement this?
> Expecting your help.
> Thanks.
>
> "Steven Cheng[MSFT]" wrote:
> > Hi ringt,
> >
> > I'm afraid grant the sufficient CAS permission is necessary. However, I
> > agree with you that it's not necessary to grant the FullTrust to
> > LocalComputer zone... You can choose to adjust the existing PermissionSet
> > , e.g the "Execution" or add a new named PermissionSet and add those
> > FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
> > to your own assembly, you can consider strong-named your assemtly so that
> > you can grant permissionSet through Strong-named condition.....
> >
> > So far I haven't found any resource directly describing modifying the
> > reporting service's security policy file, but there is on good article
> > discussing customize ASP.NET 2.0 security policys:
> >
> >
> > #How To: Use Code Access Security in ASP.NET 2.0
> > http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
> > ue
> >
> > this article mentioned the similiar ideas on customizing existing security
> > policy file and add FileIOPermission for certain CodeGroup....
> >
> >
> > Hope helps. Thanks,
> >
> > Steven Cheng
> > Microsoft Online Support
> >
> > Get Secure! www.microsoft.com/security
> > (This posting is provided "AS IS", with no warranties, and confers no
> > rights.)
> >
> >
> >
> >
> >
> >
> > --
> > | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
> > | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
> > <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
> > <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
> > <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
> > | Subject: Re: how to find the windows path of custom assembly using c#
> > code?
> > | Date: Wed, 14 Dec 2005 21:27:19 -0500
> > | Lines: 38
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> > | X-RFC2646: Format=Flowed; Original
> > | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
> > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | The links I included in my previous post should get you started although
> > in
> > | your case it is like catch-22. You need to grant access to a path which
> > is
> > | known only at runtime. You have to do some reasearch to find if this is
> > | possible at all.
> > |
> > | Good luck!
> > |
> > | --
> > | HTH,
> > | ---
> > | Teo Lachev, MVP, MCSD, MCT
> > | "Microsoft Reporting Services in Action"
> > | "Applied Microsoft Analysis Services 2005"
> > | Home page and blog: http://www.prologika.com/
> > |
> > | ---
> > | "msdnuser" <ringt@.nospam.nospam> wrote in message
> > | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> > | > Hi Teo and Steven,
> > | >
> > | > Thank you both for the replies. I am able to get the executing path of
> > | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
> > give
> > | > FullTrust permissionset to "MyComputer" and
> > | > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
> > can
> > | > reduce the permission and make them work?
> > | >
> > | > Note: I also have my own permission set and code group to give my dll,
> > | > read
> > | > permission to my text file which is not a config file. I am using
> > | > Reporting
> > | > Services 2005.
> > | >
> > | > Thanks,
> > | > ringt
> > | >
> > | >
> > |
> > |
> > |
> >
> >|||You're welcome Ringt,
Also, I didn't mean that we have to grant FullTrust permission , but should
grant whatever permission that is necessary for our custom operations. and
for your scenario, this is the FileIOPermission... :-)
Anyway, glad that you've got it working....
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--
| Thread-Topic: how to find the windows path of custom assembly using c#
code?
| thread-index: AcYCXj/lieGZBwSnQZaVOJeA9HQJ8Q==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
<e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
<P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
<8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
<uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
<5s9mSKWAGHA.1504@.TK2MSFTNGXA02.phx.gbl>
<92BCD84D-F5A4-4E8E-AF01-463F71D0FB24@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Fri, 16 Dec 2005 08:32:04 -0800
| Lines: 131
| Message-ID: <A04C0A67-0357-46A7-B39B-793FBC570C33@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA
03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65287
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Teo and Steven,
|
| Please ignore my last reply in this thread. I made a mistake of not
| appending my file name after I find the path using
| System.AppDomain.CurrentDomain.BaseDirectory. Thus my problem is solved
now.
| Thank you very much.
|
|
|
| "msdnuser" wrote:
|
| > Hi there,
| >
| > As Steven mentioned, I have to give fullTrust permission to get the
path
| > during runtime. Even then as Toe have mentioned asserting permissions
to a
| > path known at runtime is not successful to me. It throws me #Error
again. Is
| > there any other way (even by reducing or modifying some permissions) to
| > implement this?
| >
| > Expecting your help.
| >
| > Thanks.
| >
| >
| >
| > "Steven Cheng[MSFT]" wrote:
| >
| > > Hi ringt,
| > >
| > > I'm afraid grant the sufficient CAS permission is necessary. However,
I
| > > agree with you that it's not necessary to grant the FullTrust to
| > > LocalComputer zone... You can choose to adjust the existing
PermissionSet
| > > , e.g the "Execution" or add a new named PermissionSet and add
those
| > > FileIOPermissions in that PermissionSet. To grant the custom
PermssionSet
| > > to your own assembly, you can consider strong-named your assemtly so
that
| > > you can grant permissionSet through Strong-named condition.....
| > >
| > > So far I haven't found any resource directly describing modifying
the
| > > reporting service's security policy file, but there is on good
article
| > > discussing customize ASP.NET 2.0 security policys:
| > >
| > >
| > > #How To: Use Code Access Security in ASP.NET 2.0
| > >
http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
| > > ue
| > >
| > > this article mentioned the similiar ideas on customizing existing
security
| > > policy file and add FileIOPermission for certain CodeGroup....
| > >
| > >
| > > Hope helps. Thanks,
| > >
| > > Steven Cheng
| > > Microsoft Online Support
| > >
| > > Get Secure! www.microsoft.com/security
| > > (This posting is provided "AS IS", with no warranties, and confers no
| > > rights.)
| > >
| > >
| > >
| > >
| > >
| > >
| > > --
| > > | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| > > | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
| > > <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
| > > <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
| > > <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
| > > | Subject: Re: how to find the windows path of custom assembly using
c#
| > > code?
| > > | Date: Wed, 14 Dec 2005 21:27:19 -0500
| > > | Lines: 38
| > > | X-Priority: 3
| > > | X-MSMail-Priority: Normal
| > > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > > | X-RFC2646: Format=Flowed; Original
| > > | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
| > > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > > | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
| > > | Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| > > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:65108
| > > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > > |
| > > | The links I included in my previous post should get you started
although
| > > in
| > > | your case it is like catch-22. You need to grant access to a path
which
| > > is
| > > | known only at runtime. You have to do some reasearch to find if
this is
| > > | possible at all.
| > > |
| > > | Good luck!
| > > |
| > > | --
| > > | HTH,
| > > | ---
| > > | Teo Lachev, MVP, MCSD, MCT
| > > | "Microsoft Reporting Services in Action"
| > > | "Applied Microsoft Analysis Services 2005"
| > > | Home page and blog: http://www.prologika.com/
| > > |
| > > | ---
| > > | "msdnuser" <ringt@.nospam.nospam> wrote in message
| > > | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
| > > | > Hi Teo and Steven,
| > > | >
| > > | > Thank you both for the replies. I am able to get the executing
path of
| > > | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only
if I
| > > give
| > > | > FullTrust permissionset to "MyComputer" and
| > > | > "Report_Expressions_Default_Permissions" Code Groups. Is there a
way I
| > > can
| > > | > reduce the permission and make them work?
| > > | >
| > > | > Note: I also have my own permission set and code group to give my
dll,
| > > | > read
| > > | > permission to my text file which is not a config file. I am using
| > > | > Reporting
| > > | > Services 2005.
| > > | >
| > > | > Thanks,
| > > | > ringt
| > > | >
| > > | >
| > > |
| > > |
| > > |
| > >
| > >
|

how to find statistics for a column

I am trying to code a tsql routine to check for the existence of statistics before a column is dropped. If the stats exist I want to drop the stats first and then drop the column. I Can do this manually but need it in a script.
MikeUm...why?

blindman|||First reason is that the column cannont be dropped while the statistic is pressent.
Second, there are 15 db's that need to have the obsolete column(s) dropped.
Third, all steps are documented.
Fourth, gui's take 2 long. Just run a command.
Fifth, There are dozens of obsoleted columns that are being dropped in the next release.

mike

eg.sample of the errors generated.
Server: Msg 5074, Level 16, State 8, Line 9

The statistics 'HHAltCouponName' is dependent on column 'HHAltCouponName'.

Server: Msg 4922, Level 16, State 1, Line 9

ALTER TABLE DROP COLUMN HHAltCouponName failed because one or more objects access this column.

How to find query plan for a stored procedure using temporary tables

This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro

--***********************************
use pubs
go
CREATE PROCEDURE Test @.percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor

--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)

--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1

go

set showplan_Text ON
go
EXEC Test @.percentage = 100
GO
set showplan_Text OFF
go
**************************************

I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.

I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.

RegardsOn 25 Oct 2006 15:41:52 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>This post is related to SQL server 2000 and SQL Server 2005 all
>editions.
>Many of my stored procedures create temporary tables in the code. I
>want to find a way to find the query plan for these procs

(snip)

Quote:

Originally Posted by

>I get the following error
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp2'.
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp1'.

Hi comp_databases_ms-sqlserver,

You get these errors because SET SHOWPLAN_TEXT ON tells SQL Server to
generate a plan INSTEAD OF executing the SQL. As a result, your temp
tables are not generated.

The only way to get execution plans is to allow SQL Server to execute
the statements as well as outputting the plan. You do this by issuing
the command
SET STATISTICS PROFILE ON;
Note that this includes other (run-time) info as well as the plan.

Of course, you can also decide to use CREATE TABLE for all temp tables
at the start of your procs instead of using INSERT INTO.

--
Hugo Kornelis, SQL Server MVP|||SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.|||On 26 Oct 2006 11:29:57 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
>troubleshoot performance problems of a proc with 2000+ lines of code
>that is using 10+ temp tables?
>I am not able to configure SQLDebugger from a client. I have to be on
>the server to use it. This applies to SQL2000.

Hi comp_databases_ms-sqlserver,

That's a pretty broad question!

Some of the things I'd look into if I was assigned this task would be
(in random order):

* Try to combine some or even all steps of the procedure into one single
query. Procs like this are often the result of procedural thinking. New
SQL coders with a background in procedural languages often tend to think
in the steps required to get somewhere. They will then code a sequence
of steps, with temp tables to hold intermediate results. A truly
set-based and declarative solution gives the optimzer more freedom to
rearrange steps and reduces the amount of moving data around. This can
yield huge benefits in performance.

* Create temp tables at the start of the stored proc. This reduces the
number of recompiles (and at 2000+ lines, recompiling the proc will
probably take a noteable amount of time). It also gives you the
opportunity to declare indexes on the temp tables BEFORE data is put
into them - this will reduce the number of recompiles even further and
it may speed up execution. However, it can also sometimes be better to
postpone index creation until after the temp table is populated, even
though this means accepting a recompilation. Test various strategies to
find out.

* Copy the code from the stored procedure to Query Analyzer and run it
one step at a time, using BEGIN TRAN, ROLLBACK and COMMIT as needed to
be able to repeat each step multiple times. This gives you the option to
get an execution plan for each step, and also to try different versions
of the query and/or different indexes to see how they change the
execution speed of that particular part of the proc.

* Setup a profiler trace, run the stored proc, then use the output from
the profiler trace to identify which part(s) of the stored proc are
responsible for the largest portion of the execution time.

* Check if your procedure might be subject to parameter sniffing (google
for it if you've never heard of the term).

* If you really can't combine the steps, consider breaking the procedure
in several smaller parts. This reduces compilation time when
recompilations are needed and can be leveraged to solve parameter
sniffing problems.

There are probably more things you can do, but these are the ones I can
think of at the top of my head.

Good luck!

--
Hugo Kornelis, SQL Server MVP

How to find missing records from tables involving composite primary keys

Table 1

Code	Quarter
500002	26
500002	27
500002	28
500002	28.5
500002	29

Table 2

Code	Qtr
500002	26
500002	27

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result

Code	Quarter
500002	28
500002	28.5
500002	29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

You can use the following query too...

Select
A.Code,
A.Quarter
From
[Table 1] A
Left Outer Join [Table 2] B on A.Code = B.Code And A.Quarter = B.Quarter
Where
B.Code is NULL

|||

Using NOT EXISTS is the fastest way to solve your problem. Use query below:

select t1.scrip_cd, t1.Qtr

from Table1 as t1

where not exists(

select * from Table2 as t2

where t2.scrip_cd = t1.scrip_cd and t2.Qtr = t1.Qtr

)

How to find Dependencies ?

I am looking for code or a solution to find all
dependencies in a .db fast and simple (or code). Any
suggestions would be great!
Example:
Here is the dependencies among these 4 tables:
1) Publishers (base table): does not depend on other
tables.
2) Authors (base table): does not depend on other tables.
3) Titles: has foreign key which depend on publishers
table.
4) Titleauthor: has 2 foreign keys which depend on
authors and publishers titleswhy?
is it because you want to make sure your new code doesn't
break any existing code?
check out DB Ghost @. www.dbghost.com - it can guarentee
your code doesn't break anything and make deploying your
changes a breeze.
>--Original Message--
>I am looking for code or a solution to find all
>dependencies in a .db fast and simple (or code). Any
>suggestions would be great!
>Example:
>Here is the dependencies among these 4 tables:
>1) Publishers (base table): does not depend on other
>tables.
>2) Authors (base table): does not depend on other tables.
>3) Titles: has foreign key which depend on publishers
>table.
>4) Titleauthor: has 2 foreign keys which depend on
>authors and publishers titles
>.
>|||Have you looked into the stored procedure sp_depends?
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all > dependencies in a .db fast and simple (or code). Any > suggestions would be great!
> > Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other > tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers > table.
> 4) Titleauthor: has 2 foreign keys which depend on > authors and publishers titles
>|||Yes, sp_depends does not solve the issue. It shows
dependencies on sp.
>--Original Message--
>Have you looked into the stored procedure sp_depends?
>--
>Keith, SQL Server MVP
>"Mike A" <mike@.countrymedic.com> wrote in message
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
>> I am looking for code or a solution to find all
>> dependencies in a .db fast and simple (or code). Any
>> suggestions would be great!
>> Example:
>> Here is the dependencies among these 4 tables:
>> 1) Publishers (base table): does not depend on other
>> tables.
>> 2) Authors (base table): does not depend on other
tables.
>> 3) Titles: has foreign key which depend on publishers
>> table.
>> 4) Titleauthor: has 2 foreign keys which depend on
>> authors and publishers titles
>.
>|||No trying to determine population order of the .db built
in another state.
>--Original Message--
>why?
>is it because you want to make sure your new code
doesn't
>break any existing code?
>check out DB Ghost @. www.dbghost.com - it can guarentee
>your code doesn't break anything and make deploying your
>changes a breeze.
>>--Original Message--
>>I am looking for code or a solution to find all
>>dependencies in a .db fast and simple (or code). Any
>>suggestions would be great!
>>Example:
>>Here is the dependencies among these 4 tables:
>>1) Publishers (base table): does not depend on other
>>tables.
>>2) Authors (base table): does not depend on other
tables.
>>3) Titles: has foreign key which depend on publishers
>>table.
>>4) Titleauthor: has 2 foreign keys which depend on
>>authors and publishers titles
>>.
>.
>|||hi Mike,
"Mike A" <mike@.countrymedic.com> ha scritto nel messaggio
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all
> dependencies in a .db fast and simple (or code). Any
> suggestions would be great!
> Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other
> tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers
> table.
> 4) Titleauthor: has 2 foreign keys which depend on
> authors and publishers titles
You can have a look at the UNDOCUMENTED (unsupported) stored procedure
sp_MSforeachtable..
sp_MSforeachtable can execute the command you pass as a parameter for each
table present in your database and you can try using sp_depends, that
returns all dependencies for the specified ('?') table object
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
you'll get an output similar to
ObjName
--
dbo.tableXXXXX
no object refers to this object....
or
ObjName
--
dbo.tableXXXXX
In the current database, the specified object is referenced by the
following:
name type
xxxxx xxxxx
to list FK constraint you can use a select which actually includes some
joins....
mixing the 2 command you can give this a try...
----
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
DECLARE @.MasterCMD VARCHAR (1000)
SET @.MasterCMD ='SELECT ''?'' AS ObjName
PRINT ''sp_depends''
PRINT ''--''
PRINT ''fk_scan''
PRINT ''--''
SELECT SO3.NAME FK_NAME, ''['' + SU.NAME + ''].['' + SO.NAME + '']''
FROM SYSFOREIGNKEYS SYSFK
--Foreign Key Constraint - Table info
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO
ON SYSFK.FKEYID = SO.ID
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO2
ON SYSFK.RKEYID = SO2.ID
--Name and ID of the FOREIGN KEY constraint
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS) SO3 ON SYSFK.CONSTID = SO3.ID
--Foreign Key Constraint - Owner info
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID
--Referenced in the FOREIGN KEY constraint - Owner info
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID
WHERE SU.NAME + ''.'' + SO.NAME = ''?'''
EXEC sp_MSforeachtable @.MasterCMD
----
you must be warned that Microsot does not recommend the use of UNDOCUMENTED
stored procedures that are unsupported and system objects direct
manipulation
hth
Andrea Montanari
montanari_andrea@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||It also shows dependencies on a table...
USE pubs
GO
exec sp_depends 'authors'
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:0a3001c34a22$5c817f80$a501280a@.phx.gbl...
> Yes, sp_depends does not solve the issue. It shows > dependencies on sp.
> > > >--Original Message--
> >Have you looked into the stored procedure sp_depends?
> >
> >-- > >Keith, SQL Server MVP
> > > >"Mike A" <mike@.countrymedic.com> wrote in message > news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> >> I am looking for code or a solution to find all > >> dependencies in a .db fast and simple (or code). Any > >> suggestions would be great!
> >> > >> Example:
> >> Here is the dependencies among these 4 tables:
> >> 1) Publishers (base table): does not depend on other > >> tables.
> >> 2) Authors (base table): does not depend on other > tables.
> >> 3) Titles: has foreign key which depend on publishers > >> table.
> >> 4) Titleauthor: has 2 foreign keys which depend on > >> authors and publishers titles
> >> > >.
> >

How to filter our system objects from the list

I'm using following statement to get the list of objects in given SQL
Server database (this is taken from VB6 code):
lstrSQL = "select name, refdate from dbo.sysobjects where " &
lstrFilter & " order by name"
I apply filter to narrow the results to particular type, for example
for procedures:
lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
For functions:
lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
N'IsTableFunction') = 1"
etc.
My question is: how can I filter out the objects created by the system
(I want only the objects created by the users to be left)?
Ideally, I'd like the solution to work in SQL Server 2005 and in the
older versions.
TIA
Dariusz Dziewialtowski.Use the xtype column to exclude the objects you don't want to show:
xtype: Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||select *
from sysobjects
where xtype <> 'S'
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Use the xtype column to exclude the objects you don't want to show:
> xtype: Object type. Can be one of these object types:
> C = CHECK constraint
> D = Default or DEFAULT constraint
> F = FOREIGN KEY constraint
> L = Log
> FN = Scalar function
> IF = Inlined table-function
> P = Stored procedure
> PK = PRIMARY KEY constraint (type is K)
> RF = Replication filter stored procedure
> S = System table
> TF = Table function
> TR = Trigger
> U = User table
> UQ = UNIQUE constraint (type is K)
> V = View
> X = Extended stored procedure
>
> "dariusz.dziewialtowski@.gmail.com" wrote:
>|||Try,
...
and objectproperty([id], 'IsMSShipped') = 0
AMB
"dariusz.dziewialtowski@.gmail.com" wrote:

> I'm using following statement to get the list of objects in given SQL
> Server database (this is taken from VB6 code):
> lstrSQL = "select name, refdate from dbo.sysobjects where " &
> lstrFilter & " order by name"
> I apply filter to narrow the results to particular type, for example
> for procedures:
> lstrFilter = "OBJECTPROPERTY(id, N'IsProcedure') = 1"
> For functions:
> lstrFilter = "OBJECTPROPERTY(id, N'IsInlineFunction') = 1
> or OBJECTPROPERTY(id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(id,
> N'IsTableFunction') = 1"
> etc.
> My question is: how can I filter out the objects created by the system
> (I want only the objects created by the users to be left)?
> Ideally, I'd like the solution to work in SQL Server 2005 and in the
> older versions.
> TIA
> Dariusz Dziewialtowski.
>|||Use the IsMSShipped object property|||Edgardo, Alejandro, Scott
Thank you for your so quick responses!
I was thinking about using IsMSShipped in the past but I was afraid
that it wouldn't work right - for example: the whole Northwind database
is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
all objects in that database in that case?
Hmm, I have to test it actually...
Again - thanks a lot for your help!
Dariusz Dziewialtowski.|||That's a good point.
Actually, the IsMSShipped property is only set for those objects that
someone set it on for. Anyone can mark an object so that the IsMSShipped bit
is true by running sp_MS_marksystemobject.
So there is really no way to know for sure what is shipped by MS and what
isn't. You'll have to come up with another way of determining which objects
you want to see and which you don't.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144681248.829348.140770@.i40g2000cwc.googlegroups.com...
> Edgardo, Alejandro, Scott
> Thank you for your so quick responses!
> I was thinking about using IsMSShipped in the past but I was afraid
> that it wouldn't work right - for example: the whole Northwind database
> is shipped by Microsoft - would the flag IsMSShipped be set to TRUE for
> all objects in that database in that case?
> Hmm, I have to test it actually...
> Again - thanks a lot for your help!
>
> Dariusz Dziewialtowski.
>|||Kalen,
Thanks a lot for your explanation!
Dariusz Dziewialtowski