Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

'Default' NON_EMPTY_BEHAVIOR behavior...

Hi,

The below query returns 20 rows when run against our production DW. When the NON_EMPTY_BEHAVIOR is removed, it returns 10 rows. Note the lack of a member list on the N_E_B. Anyone else seen this, or have any ideas as to why it's causing me a 'problem'?

WITH MEMBER

[Container].[Container Type].[AllCalc] AS [Container].[Container Type].[All],

NON_EMPTY_BEHAVIOR={}

SELECT

([Measures].[Container Load Count]) ON COLUMNS,

NON EMPTY

[Trade].[Trade].&[EAST_AFRICA] * [Container].[Container Number].[All].Children ON ROWS

FROM [DW_SM]

WHERE (

<various slicers>,

[Container].[Container Type].[AllCalc]

)

Thanks,

Will.

You should remove NON_EMPTY_BEHAVIOR from your calculated member, because it is set incorrectly. Specifying an empty set for NEB is FAAP always wrong. And really to get any benefit out of it in the form you are trying to use it, it should've only been used on calculated measure, not on calculated member.

Default name for domain

Hello, I'm creating a script that will create logins for groups of a
windows machine, and I want to know if there's a way for me not to have to
specify the machine domain name.
Something like the '.\sqlexpress' name to identify the sqlexpress instance
in running in the local machine:
CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
Does something like this exist?
Regards,
Pablo MontillaOne option is to use variables and dynamically build the
SQL statements to create the logins. You can get the server
name using
select @.@.servername
You can use that to build the login names specific for a
machine.
-Sue
On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
<melkor@.odyssey.com.uy> wrote:

>Hello, I'm creating a script that will create logins for groups of a
>windows machine, and I want to know if there's a way for me not to have to
>specify the machine domain name.
>Something like the '.\sqlexpress' name to identify the sqlexpress instance
>in running in the local machine:
>CREATE LOGIN [.\O2 - Viewer] FROM WINDOWS
>Does something like this exist?
>Regards,
>Pablo Montilla
>|||Many thanks, I'll try that.
Pablo
On Wed, 21 Mar 2007 22:00:19 -0300, Sue Hoegemeier <Sue_H@.nomail.please>
wrote:

> One option is to use variables and dynamically build the
> SQL statements to create the logins. You can get the server
> name using
> select @.@.servername
> You can use that to build the login names specific for a
> machine.
> -Sue
> On Wed, 21 Mar 2007 18:31:41 -0300, "Pablo Montilla"
> <melkor@.odyssey.com.uy> wrote:
>
>

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.
sql

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.

|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)
|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.

Default Mobile Database Application

Dear All,

I am trying to build a simple mobile application. I am very new to this. Thus I am trying to access the build in database in the folder C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Device\Mobile\v3.0\Northwind.sdf. I am following the steps in the wizard to help me. When I press test connection is say Test Connection Succeeded. But when I press the ok button I get this error as "Unable to find the requested .Net Framework Data Provider. It may not be installed". I am running VS 2005. Can anyone pls help ?

This is a problem with the installation of VS2005 on your development PC. I've seen this same problem a few times. It is usually caused by a remnant of one of the beta or release candidate versions of VS2005 not being completely cleaned up on the development machine. I would follow the instructions in the VS2005 release notes to completely clean your machine and then do a fresh install of VS2005.

-Darren

|||

If you have uninstalled and reinstalled .NET Fx after VS Installation, this problem would arise. Can you please search for Data Providers in .NET Framework config file.

%SystemDrive%\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config

Open this file in NOTEPAD, and search for "SQL Server CE Data Provider". If it is not there then, you may try adding the following line AS IT IS in Data Providers section.

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

You may need to restart the VS or even the PC to make VS detect this new setting.

Let us know if this has worked for you!

Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Dear Laxmi,

I am very thankful for your answer but I dont get what do you meant by .NET FX? This is because I get very confuse when I go to the microsoft webpage for download. I am confuse with .NET Framework SDK and .Net Framework Version 2.0 Resistributable what is the different between both of them ? What is also the different between the x86 and x64 ?. Another thing is about the .NET Compact ? About this .NET Compact must I install in my pda or smartphone in order for my smart applications to work is it ? Sorry I am very new to this technology and also very much confused and mixed up. Thanks very much for your kind reply.

|||

I am really sorry about that in using the jargons. Ok. .NET FX means .NET Framework v2.0 Redistributable. The .NET FX SDK is nothing but extra development tools over .NET FX for users. x86 is Intel processor architecture - it represents 8086 series of processors such as i386, i486 .... Where as x64 means 64-bit processors. And the answer to your last question is, YES you have to install .NET Compact Framework 2.0 on the device (smartphone or pda or pocket pc) in order for you to make use of SQL Mobile 3.0 product with ADO.NET provider.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Excellent information. I had this exact problem and after a few days of searching finally found your answer. It has fixed my problem. Are there any more tweaks I might have to make due to having had previous versions of tools installed ?
I have already discovered the fix to problems with connecting to device emulators where registry keys have to be removed.|||

I have opened the file as you have recommended, but I don't see a "Data Providers" section except for the following..

<configProtectedData defaultProvider="RsaProtectedConfigurationProvider">
<providers>

...

Another section:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</DbProviderFactories>
</system.data>

So I am thinking hte system.data...

Is that the rigth location?

|||That worked for me - wondefull|||Hey, can you please, please help me?
I have exactly the same problem as described above;
unfortunately adding the line to the machine.config file doesn't work for me...
I restarted the VS and the whole PC, but it still doesn't work;
I don't have Data Providers section so I added that line to providers section, maybe it's the wrong place?
I will appreciate your help very much!
|||Ok, never mind.. I found out what was the problem - I not only had to add the given entry, but also I had to comment out some already existing entries :-)
Now everything works fine! (so far anyway :-P)|||I have a similar problem. I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.

I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.

Any help would be greatly appreciated.sql

Default merge agent profile settings

I'd like to be able to either change the default profile settings or change
the default profile to a user defined profile.
So far I've only been able to change the profile after the merge agent is
created. I'd like to change the defaults for
any new merge agents that are created.
TIA
here is an example of updating the merge default profile's LoginTimeOut from
15s to 120s.
update msdb.dbo.MSagent_parameters set value=120 where
parameter_name='-LoginTimeout' and profile_id=6
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darren" <deo.is@.unknown.com> wrote in message
news:%23$AjOiSGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> I'd like to be able to either change the default profile settings or
change
> the default profile to a user defined profile.
> So far I've only been able to change the profile after the merge agent is
> created. I'd like to change the defaults for
> any new merge agents that are created.
> TIA
>
>
|||Or like this?
sp_change_agent_parameter @.profile_id = 6, @.parameter_name =
N'-UploadReadChangesPerBatch', @.parameter_value = N'2000'
Are the default profile IDs documented anywhere?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIGGPvVGFHA.1068@.TK2MSFTNGP14.phx.gbl...
> here is an example of updating the merge default profile's LoginTimeOut
from[vbcol=seagreen]
> 15s to 120s.
>
> update msdb.dbo.MSagent_parameters set value=120 where
> parameter_name='-LoginTimeout' and profile_id=6
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Darren" <deo.is@.unknown.com> wrote in message
> news:%23$AjOiSGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> change
is
>
|||Found it in BOL under sp_help_agent_profile
"Darren" <deo.is@.unknown.com> wrote in message
news:OkkNEVcGFHA.400@.TK2MSFTNGP14.phx.gbl...
> Or like this?
> sp_change_agent_parameter @.profile_id = 6, @.parameter_name =
> N'-UploadReadChangesPerBatch', @.parameter_value = N'2000'
> Are the default profile IDs documented anywhere?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIGGPvVGFHA.1068@.TK2MSFTNGP14.phx.gbl...
> from
> is
>
|||This also assumes that you haven't changed the default. The update below
would update the default merge agent.
update msdb.dbo.MSagent_parameters set value=120 where
parameter_name='-LoginTimeout' and
profile_id = ( select profile_id from msdb.dbo.MSagent_profiles where
agent_type=4 and def_profile = 1 )
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIGGPvVGFHA.1068@.TK2MSFTNGP14.phx.gbl...
> here is an example of updating the merge default profile's LoginTimeOut
from[vbcol=seagreen]
> 15s to 120s.
>
> update msdb.dbo.MSagent_parameters set value=120 where
> parameter_name='-LoginTimeout' and profile_id=6
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Darren" <deo.is@.unknown.com> wrote in message
> news:%23$AjOiSGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> change
is
>
|||That's probably a better way to do it
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darren" <deo.is@.unknown.com> wrote in message
news:OkkNEVcGFHA.400@.TK2MSFTNGP14.phx.gbl...
> Or like this?
> sp_change_agent_parameter @.profile_id = 6, @.parameter_name =
> N'-UploadReadChangesPerBatch', @.parameter_value = N'2000'
> Are the default profile IDs documented anywhere?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIGGPvVGFHA.1068@.TK2MSFTNGP14.phx.gbl...
> from
> is
>

Default member problem

Hi,

I have Employee dimension with DomainName attribute which is used for role - based access to cube data.

I've tried to set DefaultMember using MDX expression and username function, like this:

IIF(StrToMember('[EmployeeDim].[DomainName].&[' + username + ']') IS NULL,[EmployeeDim].[DomainName][All], [EmployeeDim].[DomainName].&[' + username + ']')

but when I try to deploy this dimension I've got error message "The level '&[PEXIMBG\borko.novakovic]' object was not found in the cube when the string, [EmployeeDim].[DomainName].&[PEXIMBG\borko.novakovic], was parsed" when there is no any EmployeeDim member which have my username defined!

I thought that usage of IIF will resolve this error, but did not.

Is there any advice or hint how to avoid this behavior, because it is not common case that cube developer is member of EmployeeDim dimension which is populated from customer table.

Thanks in advance

Borko

How about if you test StrToMember() for an error in IIF(), rather than for Null, like:

IIF(IsError(StrToMember("[EmployeeDim].[DomainName].&[" + username + "]")),

[EmployeeDim].[DomainName][All],

StrToMember("[EmployeeDim].[DomainName].&[" + username + "]"))

|||

It works great!

Why IsError is not described in SQL Server Books Online?

I'm new in MDX and I did not know how to prevent engine to raise an error.

Anyway, Deepak, thank you very, very much!

Default member behaviour

I was under the impression that the behaviour of an MDX query would be the same if I didn't specify anything in the where clause or if I specified the hierarchies default members explicitly. But I've found an example on the Adventure Works cube where this is not the case. Is this expected?

For example:

If I execute the following:

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works]

The first few members I get back are

All Accounts, Balance Sheet, Net Income, Assets, etc. etc.

The [Account].[Accounts] hierarchy has the default member [Account].[Accounts].&[47] so I would expect the following MDX to return me exactly the same members, but it doesn't.

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works] WHERE ( [Account].[Accounts].&[47] )

It misses out Balance Sheet, Assets and other members.

Your assertion is right about omitting default members as long as we are talking about hierarchies belonging to *other dimensions*. Meaning dimensions you do not have on columns or rows.

For hierarchies belonging to the same dimension your assertion does not apply. Other forum participants may be able to describe this in more detail.

Default Logging (UseParentSetting) function works for the immediate child packge ONLY

I am not sure if this is a bug or not, but we have found that only the immediatelly called child package inherits the parent logging option, and others called byond the immediate child will loose the settings. For instance, say you have three packages, pkg1 will specify logging (checked events that will be logged), pkg2(which is called from pkg1) will see (by default) the checked events from pkg1, but pkg 3(which is called from pkg2) won't. You will have to manually check the logged events or load an existing settings file. This would be fine if you were dealing with just a few called packages, but for some cases (ours included) this would prove to be quite a pain having to manually check all the sub pkgs that didn't inherit the parent.

Is this a bug? Any easier ways of incorporating logged events in all of the sub-sub pkgs without manually checking the events?

Thanks in advance...

I think my problem is more in depth then I originally illustrated. When you define logging, (the provider and log connection) does this get inherited by other child packages, or must you define a provider/connection for every package?

|||I posted a similar question about logging, but I think I didn't ask it properly.

When you set LoggingMode=enabled and define the provider and checked events in the pkg(parent), don't any and all child packages inherit these properties as long as LoggingMode=UseParentSetting?

We setup and configured one pkg for logging, then set all sub pkgs to UseParentSetting, but when we would run the pkgs, the other pkgs below the immediatelly called child wouldn't get logged. So, pkg1(enabled logging) would call pkg2(useparent), then pkg2 called pkg3(useparent), and so on -- pkg1 and pkg2 would log, but 3,4,5... wouldn't.

When we check logging settings under pkg3(so on...) the events were not checked, we manually tried to check them but it created a unique logging pkg (LoggingMode=enabled).

Are we missing something here?|||

[Threads merged]

The LoggingMode setting is only for enabling and disabling the logging. It's only that distinction which can be inheritted from the parent. I.e., your choices are:

Enable logging on this package|||That's what I thought, unfortunatelly though, it doesn't log the other child packages as illustrated in my earlier post. Maybe, SeptCTP has some bugs in logging? We will try this scenerio on the RTM and see...

Thanks,

Default location of Data bases

Hi All,
I moved my main database from the C drive to the D drive
of my server. I left the rest of them on the C drive.
(master, msdb...) When I try to create a new DB it
defaults to the C drive since that is the way I installed
it. Is there a way to change that to the D drive with out
uninstalling and reinstalling?
Thanks,
JoeIn EM, right click on Server, then Properties. On Database Settings tab
choose Default data directory and Default log directory.
Ana
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:25c801c3fc6f$3a86d470$a101280a@.phx.gbl...
> Hi All,
> I moved my main database from the C drive to the D drive
> of my server. I left the rest of them on the C drive.
> (master, msdb...) When I try to create a new DB it
> defaults to the C drive since that is the way I installed
> it. Is there a way to change that to the D drive with out
> uninstalling and reinstalling?
> Thanks,
> Joe|||Thankssql

Default location of Data bases

Hi All,
I moved my main database from the C drive to the D drive
of my server. I left the rest of them on the C drive.
(master, msdb...) When I try to create a new DB it
defaults to the C drive since that is the way I installed
it. Is there a way to change that to the D drive with out
uninstalling and reinstalling?
Thanks,
JoeIn EM, right click on Server, then Properties. On Database Settings tab
choose Default data directory and Default log directory.
Ana
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:25c801c3fc6f$3a86d470$a101280a@.phx.gbl...
> Hi All,
> I moved my main database from the C drive to the D drive
> of my server. I left the rest of them on the C drive.
> (master, msdb...) When I try to create a new DB it
> defaults to the C drive since that is the way I installed
> it. Is there a way to change that to the D drive with out
> uninstalling and reinstalling?
> Thanks,
> Joe|||Thanks

Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
VivekHopefully following posting can help you:
1.
http://www.microsoft.com/technet/co...r />
96A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/co...r />
96A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:

> Hi,
> I have set British English as my default English. The DateTime field stil
l
> accepts the date as mdy. Why?
> Thanks
> Vivek

Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
Vivek
Hopefully following posting can help you:
1.
http://www.microsoft.com/technet/com...3004596A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/com...3004596A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:

> Hi,
> I have set British English as my default English. The DateTime field still
> accepts the date as mdy. Why?
> Thanks
> Vivek

Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
VivekHopefully following posting can help you:
1.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=stored+procedure+to+accept+dates+in+the+format&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=this+query+regarding+Date+format+in+SQL+Server&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:
> Hi,
> I have set British English as my default English. The DateTime field still
> accepts the date as mdy. Why?
> Thanks
> Vivek

Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

Default Language

Hi,
When running reports on a SQL 2000 Server database they are printing with $
signs instead of signs. printing from other apps like notepad on the
server or client ptints fine (i.e. with signs).
If I run SELECT @.@.LANGUAGE on the sql server it shows as us_english. The
same if I look at the general tab of the server in enterprise manager.
Could someone tell me how to change the default language to uk?
SQL Server 2000
on Windows 2000 Server
Thanks,
SteveSteve,
you can use :
sp_configure 'default language', 23
reconfigure with override
This resets the default language. The default only seems to apply to new
logins however and will affect the way strings are interpreted as dates but
it doesn't reset the value returned by @.@.language. I have heard that to
change the @.@.language value you have to reinstall, as it is picked up from
the locale at the time of installation, but have never confirmed this.
Anyway, I suspect that the $ sign is being added due to the settings of your
reporting application rather than anything to do with SQL Server. As far as
I recall, if you're using reporting services, the page setting overrides the
system locale settings, which would explain it.
HTH,
Paul Ibison|||Thanks Paul,
Thats most helpful. We were suspecting the application too but wanted to
cover all our bases before going to the third party.
Thanks,
Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLattPIKEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Steve,
> you can use :
> sp_configure 'default language', 23
> reconfigure with override
> This resets the default language. The default only seems to apply to new
> logins however and will affect the way strings are interpreted as dates
but
> it doesn't reset the value returned by @.@.language. I have heard that to
> change the @.@.language value you have to reinstall, as it is picked up from
> the locale at the time of installation, but have never confirmed this.
> Anyway, I suspect that the $ sign is being added due to the settings of
your
> reporting application rather than anything to do with SQL Server. As far
as
> I recall, if you're using reporting services, the page setting overrides
the
> system locale settings, which would explain it.
> HTH,
> Paul Ibison
>

Default Language

Hi,
When running reports on a SQL 2000 Server database they are printing with $
signs instead of signs. printing from other apps like notepad on the
server or client ptints fine (i.e. with signs).
If I run SELECT @.@.LANGUAGE on the sql server it shows as us_english. The
same if I look at the general tab of the server in enterprise manager.
Could someone tell me how to change the default language to uk?
SQL Server 2000
on Windows 2000 Server
Thanks,
Steve
Steve,
you can use :
sp_configure 'default language', 23
reconfigure with override
This resets the default language. The default only seems to apply to new
logins however and will affect the way strings are interpreted as dates but
it doesn't reset the value returned by @.@.language. I have heard that to
change the @.@.language value you have to reinstall, as it is picked up from
the locale at the time of installation, but have never confirmed this.
Anyway, I suspect that the $ sign is being added due to the settings of your
reporting application rather than anything to do with SQL Server. As far as
I recall, if you're using reporting services, the page setting overrides the
system locale settings, which would explain it.
HTH,
Paul Ibison
|||Thanks Paul,
Thats most helpful. We were suspecting the application too but wanted to
cover all our bases before going to the third party.
Thanks,
Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLattPIKEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Steve,
> you can use :
> sp_configure 'default language', 23
> reconfigure with override
> This resets the default language. The default only seems to apply to new
> logins however and will affect the way strings are interpreted as dates
but
> it doesn't reset the value returned by @.@.language. I have heard that to
> change the @.@.language value you have to reinstall, as it is picked up from
> the locale at the time of installation, but have never confirmed this.
> Anyway, I suspect that the $ sign is being added due to the settings of
your
> reporting application rather than anything to do with SQL Server. As far
as
> I recall, if you're using reporting services, the page setting overrides
the
> system locale settings, which would explain it.
> HTH,
> Paul Ibison
>

Default Language

Hi,
When running reports on a SQL 2000 Server database they are printing with $
signs instead of £ signs. printing from other apps like notepad on the
server or client ptints fine (i.e. with £ signs).
If I run SELECT @.@.LANGUAGE on the sql server it shows as us_english. The
same if I look at the general tab of the server in enterprise manager.
Could someone tell me how to change the default language to uk?
SQL Server 2000
on Windows 2000 Server
Thanks,
SteveSteve,
you can use :
sp_configure 'default language', 23
reconfigure with override
This resets the default language. The default only seems to apply to new
logins however and will affect the way strings are interpreted as dates but
it doesn't reset the value returned by @.@.language. I have heard that to
change the @.@.language value you have to reinstall, as it is picked up from
the locale at the time of installation, but have never confirmed this.
Anyway, I suspect that the $ sign is being added due to the settings of your
reporting application rather than anything to do with SQL Server. As far as
I recall, if you're using reporting services, the page setting overrides the
system locale settings, which would explain it.
HTH,
Paul Ibison|||Thanks Paul,
Thats most helpful. We were suspecting the application too but wanted to
cover all our bases before going to the third party.
Thanks,
Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLattPIKEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Steve,
> you can use :
> sp_configure 'default language', 23
> reconfigure with override
> This resets the default language. The default only seems to apply to new
> logins however and will affect the way strings are interpreted as dates
but
> it doesn't reset the value returned by @.@.language. I have heard that to
> change the @.@.language value you have to reinstall, as it is picked up from
> the locale at the time of installation, but have never confirmed this.
> Anyway, I suspect that the $ sign is being added due to the settings of
your
> reporting application rather than anything to do with SQL Server. As far
as
> I recall, if you're using reporting services, the page setting overrides
the
> system locale settings, which would explain it.
> HTH,
> Paul Ibison
>

DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

Default Isolation Level in Oracle

Does anyone know what the default transaction isolation level is in Oracle
and how does it defer from SQL Server 2005 ? I have heard that Snapshot
Isolation is Oracles default but has some differences. What are they ? If
Snapshot Isolation is Oracles default, why could it not be considered to be
default for SQL Server ?
ThanksHello,
Snapshot Isolation is equalent to Rollback segment in Oracle. By default in
SQL Server the isolation level is Read committed. Incase if you are planning
to use
Snapshot isolation you should really plan to size the TempDB database, this
is because the entire row versioning is handled in Tempdb.
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
> Does anyone know what the default transaction isolation level is in Oracle
> and how does it defer from SQL Server 2005 ? I have heard that Snapshot
> Isolation is Oracles default but has some differences. What are they ? If
> Snapshot Isolation is Oracles default, why could it not be considered to
> be default for SQL Server ?
> Thanks
>|||Also (I was waiting for someone who know Oracle before jumping in :-) ):

> If
Snapshot was introduced in SQL Server 2005. If this would be the new default
, it would break tens of
thousands of existing applications. Backwards compatibility is a big thing.
Also, snapshot generates
potentially a lot of I/O in tempdb, so it is not a panacea.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Hello,
> Snapshot Isolation is equalent to Rollback segment in Oracle. By default i
n SQL Server the
> isolation level is Read committed. Incase if you are planning to use
> Snapshot isolation you should really plan to size the TempDB database, thi
s is because the entire
> row versioning is handled in Tempdb.
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message news:OE3wrSadHHA.3976@.TK2MS
FTNGP06.phx.gbl...
>|||Tibor,
What do you think the standard isolation level should be assuming all the
kinks are worked out with regards to backwards compatibility and tempdb
issues.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>
> Snapshot was introduced in SQL Server 2005. If this would be the new
> default, it would break tens of thousands of existing applications.
> Backwards compatibility is a big thing. Also, snapshot generates
> potentially a lot of I/O in tempdb, so it is not a panacea.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>|||Hassan,
I don't see the usage of tempdb as an "issue". Using snapshot, you do a cons
cious trade. You buy
concurrency and pay with resource usage. In order to use either of the snaps
hot types, SQL Server
need to keep track of old values for all modifications in the database. I wo
uldn't want this on by
default.
So, it is not as much a matter of the default for the client app (ADO etc),
it is more whether we
want this defaulted on for a database.
Also, I don't find it realistic to change such an integral part of the produ
ct as the isolation
level. The change would have to be between two releases, and that would imme
diately affect most of
the apps out there. Unless you refer to RCSI (and not pyre snapshot), but I
haven't investigated
RCSI closely enough to say whether you truly get the same behavior across th
e board for traditional
RC compared to RCSI.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...

> Tibor,
> What do you think the standard isolation level should be assuming all the
kinks are worked out
> with regards to backwards compatibility and tempdb issues.
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
>|||The reason I bring this up with the default isolation level is time and
again, we get hit hard on our SQL Servers because of blocking ( change in
query plan,etc.) that immediately results in an unavailable SQL Server as
all the worker threads are consumed. I was hoping that if we get to the
default level similar to Oracle with RCSI, then half of our major issues on
SQL would have been resolved automatically ;)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
> Hassan,
> I don't see the usage of tempdb as an "issue". Using snapshot, you do a
> conscious trade. You buy concurrency and pay with resource usage. In order
> to use either of the snapshot types, SQL Server need to keep track of old
> values for all modifications in the database. I wouldn't want this on by
> default.
> So, it is not as much a matter of the default for the client app (ADO
> etc), it is more whether we want this defaulted on for a database.
> Also, I don't find it realistic to change such an integral part of the
> product as the isolation level. The change would have to be between two
> releases, and that would immediately affect most of the apps out there.
> Unless you refer to RCSI (and not pyre snapshot), but I haven't
> investigated RCSI closely enough to say whether you truly get the same
> behavior across the board for traditional RC compared to RCSI.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
>|||I see that you are saying. But changing to RCSI is only a matter of flipping
a database option, no
code changes are necessary.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:%238tyY4mdHHA.4688@.TK2MSFTNGP04.phx.gbl.
.
> The reason I bring this up with the default isolation level is time and ag
ain, we get hit hard on
> our SQL Servers because of blocking ( change in query plan,etc.) that imme
diately results in an
> unavailable SQL Server as all the worker threads are consumed. I was hopin
g that if we get to the
> default level similar to Oracle with RCSI, then half of our major issues o
n SQL would have been
> resolved automatically ;)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
>sql

Default Isolation Level in Oracle

Does anyone know what the default transaction isolation level is in Oracle
and how does it defer from SQL Server 2005 ? I have heard that Snapshot
Isolation is Oracles default but has some differences. What are they ? If
Snapshot Isolation is Oracles default, why could it not be considered to be
default for SQL Server ?
Thanks
Hello,
Snapshot Isolation is equalent to Rollback segment in Oracle. By default in
SQL Server the isolation level is Read committed. Incase if you are planning
to use
Snapshot isolation you should really plan to size the TempDB database, this
is because the entire row versioning is handled in Tempdb.
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
> Does anyone know what the default transaction isolation level is in Oracle
> and how does it defer from SQL Server 2005 ? I have heard that Snapshot
> Isolation is Oracles default but has some differences. What are they ? If
> Snapshot Isolation is Oracles default, why could it not be considered to
> be default for SQL Server ?
> Thanks
>
|||Also (I was waiting for someone who know Oracle before jumping in :-) ):
[vbcol=seagreen]
> If
Snapshot was introduced in SQL Server 2005. If this would be the new default, it would break tens of
thousands of existing applications. Backwards compatibility is a big thing. Also, snapshot generates
potentially a lot of I/O in tempdb, so it is not a panacea.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Snapshot Isolation is equalent to Rollback segment in Oracle. By default in SQL Server the
> isolation level is Read committed. Incase if you are planning to use
> Snapshot isolation you should really plan to size the TempDB database, this is because the entire
> row versioning is handled in Tempdb.
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>
|||Tibor,
What do you think the standard isolation level should be assuming all the
kinks are worked out with regards to backwards compatibility and tempdb
issues.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>
> Snapshot was introduced in SQL Server 2005. If this would be the new
> default, it would break tens of thousands of existing applications.
> Backwards compatibility is a big thing. Also, snapshot generates
> potentially a lot of I/O in tempdb, so it is not a panacea.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>
|||Hassan,
I don't see the usage of tempdb as an "issue". Using snapshot, you do a conscious trade. You buy
concurrency and pay with resource usage. In order to use either of the snapshot types, SQL Server
need to keep track of old values for all modifications in the database. I wouldn't want this on by
default.
So, it is not as much a matter of the default for the client app (ADO etc), it is more whether we
want this defaulted on for a database.
Also, I don't find it realistic to change such an integral part of the product as the isolation
level. The change would have to be between two releases, and that would immediately affect most of
the apps out there. Unless you refer to RCSI (and not pyre snapshot), but I haven't investigated
RCSI closely enough to say whether you truly get the same behavior across the board for traditional
RC compared to RCSI.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Tibor,
> What do you think the standard isolation level should be assuming all the kinks are worked out
> with regards to backwards compatibility and tempdb issues.
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
|||The reason I bring this up with the default isolation level is time and
again, we get hit hard on our SQL Servers because of blocking ( change in
query plan,etc.) that immediately results in an unavailable SQL Server as
all the worker threads are consumed. I was hoping that if we get to the
default level similar to Oracle with RCSI, then half of our major issues on
SQL would have been resolved automatically ;)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
> Hassan,
> I don't see the usage of tempdb as an "issue". Using snapshot, you do a
> conscious trade. You buy concurrency and pay with resource usage. In order
> to use either of the snapshot types, SQL Server need to keep track of old
> values for all modifications in the database. I wouldn't want this on by
> default.
> So, it is not as much a matter of the default for the client app (ADO
> etc), it is more whether we want this defaulted on for a database.
> Also, I don't find it realistic to change such an integral part of the
> product as the isolation level. The change would have to be between two
> releases, and that would immediately affect most of the apps out there.
> Unless you refer to RCSI (and not pyre snapshot), but I haven't
> investigated RCSI closely enough to say whether you truly get the same
> behavior across the board for traditional RC compared to RCSI.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||I see that you are saying. But changing to RCSI is only a matter of flipping a database option, no
code changes are necessary.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:%238tyY4mdHHA.4688@.TK2MSFTNGP04.phx.gbl...
> The reason I bring this up with the default isolation level is time and again, we get hit hard on
> our SQL Servers because of blocking ( change in query plan,etc.) that immediately results in an
> unavailable SQL Server as all the worker threads are consumed. I was hoping that if we get to the
> default level similar to Oracle with RCSI, then half of our major issues on SQL would have been
> resolved automatically ;)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
>