DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

?

For applications that are put in a folder on the user's computer, the database folder uses the application folder.

?

For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

? For applications that are put in a folder on the user's computer, the database folder uses the application folder. ? For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

sql

DatabaseDefaults Policy (not working?)

I created the following policy. It's a basic policy for enforcing database settings. I then created a database in violation of the policy and didn't generate an error. I also altered the database to violate the policy and still didn't get any policy errors.

alter database test2

set auto_close on

<?xml version="1.0" encoding="utf-8" ?>

- <SfcSerializerFragment SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.PolicyStore">

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Policy">

<SfcProperty Name="Name">Enterprise_DatabaseCreate</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Condition">DatabaseDefaults</SfcProperty>

<SfcProperty Name="PolicyGroup">Enterprise</SfcProperty>

<SfcProperty Name="Enabled">True</SfcProperty>

<SfcProperty Name="AutomatedPolicyExecutionMode">CheckOnChanges</SfcProperty>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.TargetFilter">

<SfcProperty Name="TargetType">DATABASE</SfcProperty>

<SfcProperty Name="Filter">Server/Database</SfcProperty>

<SfcProperty Name="TargetTypeSkeleton">Server/Database</SfcProperty>

</SfcInstance>

</SfcInstance>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Condition">

<SfcProperty Name="Name">DatabaseDefaults</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Expression"><Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoClose</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatisticsEnabled</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoShrink</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoUpdateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator></SfcProperty>

<SfcProperty Name="Facet">IDatabaseFacet</SfcProperty>

</SfcInstance>

</SfcSerializerFragment>

I then created a subsequent policy (policy 2) with just the "AUTO CLOSE = FALSE" and this will ONLY throw an error when I "TEST" the policy. It doesn't throw the error when I run the ALTER DATABASE TSQL.

I created a 3rd database in violation of POLICY2 and was successfuly able to create the db without generating an error.

Is policy manager working properly? Shouldn't it work on CREATE DATABASE? Shouldn't it work without selecting "TEST" on policy?

Thanks

Steve

(SteveBouffard@.discussions.microsoft.com) writes: > I created the following policy. It's a basic policy for enforcing > database settings. I then created a database in violation of the policy > and didn't generate an error. I also altered the database to violate > the policy and still didn't get any policy errors. But if you look in Mgmt Studio, doesn't the database have some warning sign attached to it? I was not able to import your XML document, but you have CheckOnChanges. To get an error when you violated a policy you need Enforce. But when I tried to set up a policy to prohibit databases with auto-close, Enforce was greyed out. I guess this is because CREATE and ALTER DATABASES are commands that cannot be rolled back, so it is not possible to enforce them. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||

The execution modes for a policy are determined by the facet. The facet defines the events to listen to for Enforce and Check on Change (CoC). In order for the facet to define events for Enforce, the target type (e.g. database, table, view, etc) must be completely transactional. In the case of Database, create database is not completely transactional and therefore enforce is not support on conditions built-on database facets. Yes, the create database transaction can be canceled, but the system is not returned to the previous state. Therefore, DMF doesn't support enforce.

Cheers,

Dan

|||ok, thanks

DatabaseDefaults Policy (not working?)

I created the following policy. It's a basic policy for enforcing database settings. I then created a database in violation of the policy and didn't generate an error. I also altered the database to violate the policy and still didn't get any policy errors.

alter database test2

set auto_close on

<?xml version="1.0" encoding="utf-8" ?>

- <SfcSerializerFragment SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.PolicyStore">

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Policy">

<SfcProperty Name="Name">Enterprise_DatabaseCreate</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Condition">DatabaseDefaults</SfcProperty>

<SfcProperty Name="PolicyGroup">Enterprise</SfcProperty>

<SfcProperty Name="Enabled">True</SfcProperty>

<SfcProperty Name="AutomatedPolicyExecutionMode">CheckOnChanges</SfcProperty>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.TargetFilter">

<SfcProperty Name="TargetType">DATABASE</SfcProperty>

<SfcProperty Name="Filter">Server/Database</SfcProperty>

<SfcProperty Name="TargetTypeSkeleton">Server/Database</SfcProperty>

</SfcInstance>

</SfcInstance>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Condition">

<SfcProperty Name="Name">DatabaseDefaults</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Expression"><Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoClose</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatisticsEnabled</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoShrink</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoUpdateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator></SfcProperty>

<SfcProperty Name="Facet">IDatabaseFacet</SfcProperty>

</SfcInstance>

</SfcSerializerFragment>

I then created a subsequent policy (policy 2) with just the "AUTO CLOSE = FALSE" and this will ONLY throw an error when I "TEST" the policy. It doesn't throw the error when I run the ALTER DATABASE TSQL.

I created a 3rd database in violation of POLICY2 and was successfuly able to create the db without generating an error.

Is policy manager working properly? Shouldn't it work on CREATE DATABASE? Shouldn't it work without selecting "TEST" on policy?

Thanks

Steve

(SteveBouffard@.discussions.microsoft.com) writes: > I created the following policy. It's a basic policy for enforcing > database settings. I then created a database in violation of the policy > and didn't generate an error. I also altered the database to violate > the policy and still didn't get any policy errors. But if you look in Mgmt Studio, doesn't the database have some warning sign attached to it? I was not able to import your XML document, but you have CheckOnChanges. To get an error when you violated a policy you need Enforce. But when I tried to set up a policy to prohibit databases with auto-close, Enforce was greyed out. I guess this is because CREATE and ALTER DATABASES are commands that cannot be rolled back, so it is not possible to enforce them. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||

The execution modes for a policy are determined by the facet. The facet defines the events to listen to for Enforce and Check on Change (CoC). In order for the facet to define events for Enforce, the target type (e.g. database, table, view, etc) must be completely transactional. In the case of Database, create database is not completely transactional and therefore enforce is not support on conditions built-on database facets. Yes, the create database transaction can be canceled, but the system is not returned to the previous state. Therefore, DMF doesn't support enforce.

Cheers,

Dan

|||ok, thanks

DatabaseDefaults Policy (not working?)

I created the following policy. It's a basic policy for enforcing database settings. I then created a database in violation of the policy and didn't generate an error. I also altered the database to violate the policy and still didn't get any policy errors.

alter database test2

set auto_close on

<?xml version="1.0" encoding="utf-8" ?>

- <SfcSerializerFragment SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.PolicyStore">

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Policy">

<SfcProperty Name="Name">Enterprise_DatabaseCreate</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Condition">DatabaseDefaults</SfcProperty>

<SfcProperty Name="PolicyGroup">Enterprise</SfcProperty>

<SfcProperty Name="Enabled">True</SfcProperty>

<SfcProperty Name="AutomatedPolicyExecutionMode">CheckOnChanges</SfcProperty>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.TargetFilter">

<SfcProperty Name="TargetType">DATABASE</SfcProperty>

<SfcProperty Name="Filter">Server/Database</SfcProperty>

<SfcProperty Name="TargetTypeSkeleton">Server/Database</SfcProperty>

</SfcInstance>

</SfcInstance>

- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Condition">

<SfcProperty Name="Name">DatabaseDefaults</SfcProperty>

<SfcProperty Name="Description" />

<SfcProperty Name="Expression"><Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoClose</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatisticsEnabled</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoShrink</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoUpdateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator></SfcProperty>

<SfcProperty Name="Facet">IDatabaseFacet</SfcProperty>

</SfcInstance>

</SfcSerializerFragment>

I then created a subsequent policy (policy 2) with just the "AUTO CLOSE = FALSE" and this will ONLY throw an error when I "TEST" the policy. It doesn't throw the error when I run the ALTER DATABASE TSQL.

I created a 3rd database in violation of POLICY2 and was successfuly able to create the db without generating an error.

Is policy manager working properly? Shouldn't it work on CREATE DATABASE? Shouldn't it work without selecting "TEST" on policy?

Thanks

Steve

(SteveBouffard@.discussions.microsoft.com) writes: > I created the following policy. It's a basic policy for enforcing > database settings. I then created a database in violation of the policy > and didn't generate an error. I also altered the database to violate > the policy and still didn't get any policy errors. But if you look in Mgmt Studio, doesn't the database have some warning sign attached to it? I was not able to import your XML document, but you have CheckOnChanges. To get an error when you violated a policy you need Enforce. But when I tried to set up a policy to prohibit databases with auto-close, Enforce was greyed out. I guess this is because CREATE and ALTER DATABASES are commands that cannot be rolled back, so it is not possible to enforce them. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||

The execution modes for a policy are determined by the facet. The facet defines the events to listen to for Enforce and Check on Change (CoC). In order for the facet to define events for Enforce, the target type (e.g. database, table, view, etc) must be completely transactional. In the case of Database, create database is not completely transactional and therefore enforce is not support on conditions built-on database facets. Yes, the create database transaction can be canceled, but the system is not returned to the previous state. Therefore, DMF doesn't support enforce.

Cheers,

Dan

|||ok, thanks