sqlserver2000 date: sp2

Showing posts with label sp2. Show all posts

Friday, March 30, 2012

Increase in memory not working - Help Please

If you want to use 6GB set it to: 6144 MB.
I would also include the /3GB switch in the boot.ini with only 8GB
available. The general rule is: more than 4GB but less than 12GB
include /3G - over 12GB don't include /3GB because the OS needs more
than 1GB of memory available when you have more than 12GB of memory
available.
HTH,
Jeff
|||All of my SQL Servers show a max memory of 2147483647, though by default non
go beyond 2gb.
This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
Do I need Enterprise Edition?
"Jeffrey Williams" wrote:

> On Apr 3, 6:45 am, Ron <R...@.discussions.microsoft.com> wrote:
> What do you mean when you say it won't go above 2GB? If you are
> looking at the following number: 2147483647 in the option for Max
> Memory - remember, that number is in MB which is 2097151.9990234375
> GB, much larger than 2GB

> If you want to use 6GB set it to: 6144 MB.
> I would also include the /3GB switch in the boot.ini with only 8GB
> available. The general rule is: more than 4GB but less than 12GB
> include /3G - over 12GB don't include /3GB because the OS needs more
> than 1GB of memory available when you have more than 12GB of memory
> available.
> HTH,
> Jeff
>
|||If this is SQL server 2005 you do not need EE. If it is 2000 you do.
You can use TSQL instead of the GUI.
To check for AWE you would run
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'AWE enabled' ;
go
--if the above returns 1 then AWE is eanbled if 0 it is not
--to enable run
sp_configure 'AWE enabled', 1;
go
RECONFIGURE
GO
--now to set 6 gb ofr max ram
sp_configure 'max server memory', 6144 ;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
Need to ask some obvious questions you are in the system admin role on the
DB server ?
from this link:
http://msdn2.microsoft.com/en-us/library/ms190731.aspx
the following:
Windows Server 2003, Standard Edition supports physical memory up to 4
gigabytes (GB).
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
And finally
You must restart the instance of SQL Server for AWE to take effect.
"Ron" wrote:
[vbcol=seagreen]
> All of my SQL Servers show a max memory of 2147483647, though by default non
> go beyond 2gb.
> This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
> Do I need Enterprise Edition?
> "Jeffrey Williams" wrote:

Increase in memory not working - Help Please

SqlServer2005 SE SP2 - Windows2003 EE SP2
Here's the facts:
Server has 8gb.
/PAE is in the boot.ini
Lock pages in memory option has been set to on with permissions for the SQL
login
Server was re-booted
Use AWE to allocate memory was set via SSMS
I can't increase the Max Server Memory via SSMS
Any help would be appreciated.
Thanks
RonCan you RUN the following and post the output of the max server memory
statement?
Is your concern that you can use SSMS? Or can't set the max memory?Not
trying to be obtuse, just wondering where your concern is.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'max server memory' ;
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
"Ron" wrote:
> SqlServer2005 SE SP2 - Windows2003 EE SP2
> Here's the facts:
> Server has 8gb.
> /PAE is in the boot.ini
> Lock pages in memory option has been set to on with permissions for the SQL
> login
> Server was re-booted
> Use AWE to allocate memory was set via SSMS
> I can't increase the Max Server Memory via SSMS
> Any help would be appreciated.
> Thanks
> Ron
>|||Do you need Enterprise Edition or will this work in Standard Edition?
"Joe" wrote:
> Can you RUN the following and post the output of the max server memory
> statement?
> Is your concern that you can use SSMS? Or can't set the max memory?Not
> trying to be obtuse, just wondering where your concern is.
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory' ;
> GO
> sp_configure 'show advanced options', 0
> RECONFIGURE
> GO
> "Ron" wrote:
> > SqlServer2005 SE SP2 - Windows2003 EE SP2
> >
> > Here's the facts:
> >
> > Server has 8gb.
> > /PAE is in the boot.ini
> > Lock pages in memory option has been set to on with permissions for the SQL
> > login
> > Server was re-booted
> > Use AWE to allocate memory was set via SSMS
> >
> > I can't increase the Max Server Memory via SSMS
> >
> > Any help would be appreciated.
> >
> > Thanks
> >
> > Ron
> >|||The Sql should be version independant.
If you are referring to the amount of memory that is supported in SQL 2005
that is generally controlled by the version of the OS.
Let's see how much is being consumed:
select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
from master..sysperfinfo
where object_name = 'SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)', 'Total
Server Memory (KB)')
Then post the max server memory result
"Ron" wrote:
> Do you need Enterprise Edition or will this work in Standard Edition?
> "Joe" wrote:
> > Can you RUN the following and post the output of the max server memory
> > statement?
> > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > trying to be obtuse, just wondering where your concern is.
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory' ;
> > GO
> > sp_configure 'show advanced options', 0
> > RECONFIGURE
> > GO
> >
> > "Ron" wrote:
> >
> > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > >
> > > Here's the facts:
> > >
> > > Server has 8gb.
> > > /PAE is in the boot.ini
> > > Lock pages in memory option has been set to on with permissions for the SQL
> > > login
> > > Server was re-booted
> > > Use AWE to allocate memory was set via SSMS
> > >
> > > I can't increase the Max Server Memory via SSMS
> > >
> > > Any help would be appreciated.
> > >
> > > Thanks
> > >
> > > Ron
> > >|||Joe,
Here's the results:
Target Server Memory (KB)
1.505187988281 SQLServer:Memory Manager
Target
Server Memory (KB)
1578304 65792
SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
boot.ini and lock pages set.
I tried using SSMS to check on AWE and bump up Max memory, but Max memory
won't go past 2gb and SQL still only takes 1.5GB.
Do I need to run sp_config instead of using SSMS? Also the server is now in
production, so would a recycle be required?
Thanks
Ron
"Joe" wrote:
> The Sql should be version independant.
> If you are referring to the amount of memory that is supported in SQL 2005
> that is generally controlled by the version of the OS.
> Let's see how much is being consumed:
> select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> from master..sysperfinfo
> where object_name = 'SQLServer:Memory Manager'
> and counter_name in ('Target Server Memory (KB)', 'Total
> Server Memory (KB)')
> Then post the max server memory result
> "Ron" wrote:
> > Do you need Enterprise Edition or will this work in Standard Edition?
> >
> > "Joe" wrote:
> >
> > > Can you RUN the following and post the output of the max server memory
> > > statement?
> > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > trying to be obtuse, just wondering where your concern is.
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory' ;
> > > GO
> > > sp_configure 'show advanced options', 0
> > > RECONFIGURE
> > > GO
> > >
> > > "Ron" wrote:
> > >
> > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > > >
> > > > Here's the facts:
> > > >
> > > > Server has 8gb.
> > > > /PAE is in the boot.ini
> > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > login
> > > > Server was re-booted
> > > > Use AWE to allocate memory was set via SSMS
> > > >
> > > > I can't increase the Max Server Memory via SSMS
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Thanks
> > > >
> > > > Ron
> > > >|||On Apr 3, 6:45=A0am, Ron <R...@.discussions.microsoft.com> wrote:
> Joe,
> Here's the results:
> Target Server Memory (KB) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 1.505187988281 =A0SQLServer:Memory Manager =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Targ=et
> Server Memory (KB) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1578304 65792
> SQL can only access 1.5gb out of 8gb on the server. =A0The /PAE switch is =in
> boot.ini and lock pages set.
> I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> won't go past 2gb and SQL still only takes 1.5GB.
> Do I need to run sp_config instead of using SSMS? =A0Also the server is no=w in
> production, so would a recycle be required?
> Thanks
> Ron
>
> "Joe" wrote:
> > The Sql should be version independant. =A0
> > If you are referring to the amount of memory that is supported in SQL 20=05
> > that is generally controlled by the version of the OS.
> > Let's see how much is being consumed:
> > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > =A0 =A0 =A0 =A0 from master..sysperfinfo
> > =A0 =A0 =A0 =A0 where object_name =3D 'SQLServer:Memory Manager'
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 and counter_name in ('Target Server Memo=ry (KB)', 'Total
> > Server Memory (KB)')
> > Then post the max server memory result
> > "Ron" wrote:
> > > Do you need Enterprise Edition or will this work in Standard Edition?
> > > "Joe" wrote:
> > > > Can you RUN the following and post the output of the max server memo=ry
> > > > statement?
> > > > Is your concern that you can use SSMS? =A0Or can't set the max memor=y?Not
> > > > trying to be obtuse, just wondering where your concern is.
> > > > sp_configure 'show advanced options', 1
> > > > RECONFIGURE
> > > > GO
> > > > sp_configure 'max server memory' ;
> > > > GO
> > > > sp_configure 'show advanced options', 0
> > > > RECONFIGURE
> > > > GO
> > > > "Ron" wrote:
> > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > > > > Here's the facts:
> > > > > Server has 8gb. =A0
> > > > > /PAE is in the boot.ini
> > > > > Lock pages in memory option has been set to on with permissions fo=r the SQL
> > > > > login
> > > > > Server was re-booted
> > > > > Use AWE to allocate memory was set via SSMS
> > > > > I can't increase the Max Server Memory via SSMS
> > > > > Any help would be appreciated.
> > > > > Thanks
> > > > > Ron- Hide quoted text -
> - Show quoted text -
What do you mean when you say it won't go above 2GB? If you are
looking at the following number: 2147483647 in the option for Max
Memory - remember, that number is in MB which is 2097151.9990234375
GB, much larger than 2GB :)
If you want to use 6GB set it to: 6144 MB.
I would also include the /3GB switch in the boot.ini with only 8GB
available. The general rule is: more than 4GB but less than 12GB
include /3G - over 12GB don't include /3GB because the OS needs more
than 1GB of memory available when you have more than 12GB of memory
available.
HTH,
Jeff|||All of my SQL Servers show a max memory of 2147483647, though by default non
go beyond 2gb.
This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
Do I need Enterprise Edition?
"Jeffrey Williams" wrote:
> On Apr 3, 6:45 am, Ron <R...@.discussions.microsoft.com> wrote:
> > Joe,
> >
> > Here's the results:
> > Target Server Memory (KB)
> >
> > 1.505187988281 SQLServer:Memory Manager
> > Target
> > Server Memory (KB)
> >
> > 1578304 65792
> >
> > SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
> > boot.ini and lock pages set.
> >
> > I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> > won't go past 2gb and SQL still only takes 1.5GB.
> >
> > Do I need to run sp_config instead of using SSMS? Also the server is now in
> > production, so would a recycle be required?
> >
> > Thanks
> >
> > Ron
> >
> >
> >
> > "Joe" wrote:
> > > The Sql should be version independant.
> > > If you are referring to the amount of memory that is supported in SQL 2005
> > > that is generally controlled by the version of the OS.
> > > Let's see how much is being consumed:
> > > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > > from master..sysperfinfo
> > > where object_name = 'SQLServer:Memory Manager'
> > > and counter_name in ('Target Server Memory (KB)', 'Total
> > > Server Memory (KB)')
> >
> > > Then post the max server memory result
> >
> > > "Ron" wrote:
> >
> > > > Do you need Enterprise Edition or will this work in Standard Edition?
> >
> > > > "Joe" wrote:
> >
> > > > > Can you RUN the following and post the output of the max server memory
> > > > > statement?
> > > > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > > > trying to be obtuse, just wondering where your concern is.
> > > > > sp_configure 'show advanced options', 1
> > > > > RECONFIGURE
> > > > > GO
> > > > > sp_configure 'max server memory' ;
> > > > > GO
> > > > > sp_configure 'show advanced options', 0
> > > > > RECONFIGURE
> > > > > GO
> >
> > > > > "Ron" wrote:
> >
> > > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> >
> > > > > > Here's the facts:
> >
> > > > > > Server has 8gb.
> > > > > > /PAE is in the boot.ini
> > > > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > > > login
> > > > > > Server was re-booted
> > > > > > Use AWE to allocate memory was set via SSMS
> >
> > > > > > I can't increase the Max Server Memory via SSMS
> >
> > > > > > Any help would be appreciated.
> >
> > > > > > Thanks
> >
> > > > > > Ron- Hide quoted text -
> >
> > - Show quoted text -
> What do you mean when you say it won't go above 2GB? If you are
> looking at the following number: 2147483647 in the option for Max
> Memory - remember, that number is in MB which is 2097151.9990234375
> GB, much larger than 2GB :)
> If you want to use 6GB set it to: 6144 MB.
> I would also include the /3GB switch in the boot.ini with only 8GB
> available. The general rule is: more than 4GB but less than 12GB
> include /3G - over 12GB don't include /3GB because the OS needs more
> than 1GB of memory available when you have more than 12GB of memory
> available.
> HTH,
> Jeff
>|||If this is SQL server 2005 you do not need EE. If it is 2000 you do.
You can use TSQL instead of the GUI.
To check for AWE you would run
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'AWE enabled' ;
go
--if the above returns 1 then AWE is eanbled if 0 it is not
--to enable run
sp_configure 'AWE enabled', 1;
go
RECONFIGURE
GO
--now to set 6 gb ofr max ram
sp_configure 'max server memory', 6144 ;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
Need to ask some obvious questions you are in the system admin role on the
DB server ?
from this link:
http://msdn2.microsoft.com/en-us/library/ms190731.aspx
the following:
Windows Server 2003, Standard Edition supports physical memory up to 4
gigabytes (GB).
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
And finally
You must restart the instance of SQL Server for AWE to take effect.
"Ron" wrote:
> All of my SQL Servers show a max memory of 2147483647, though by default non
> go beyond 2gb.
> This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
> Do I need Enterprise Edition?
> "Jeffrey Williams" wrote:
> > On Apr 3, 6:45 am, Ron <R...@.discussions.microsoft.com> wrote:
> > > Joe,
> > >
> > > Here's the results:
> > > Target Server Memory (KB)
> > >
> > > 1.505187988281 SQLServer:Memory Manager
> > > Target
> > > Server Memory (KB)
> > >
> > > 1578304 65792
> > >
> > > SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
> > > boot.ini and lock pages set.
> > >
> > > I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> > > won't go past 2gb and SQL still only takes 1.5GB.
> > >
> > > Do I need to run sp_config instead of using SSMS? Also the server is now in
> > > production, so would a recycle be required?
> > >
> > > Thanks
> > >
> > > Ron
> > >
> > >
> > >
> > > "Joe" wrote:
> > > > The Sql should be version independant.
> > > > If you are referring to the amount of memory that is supported in SQL 2005
> > > > that is generally controlled by the version of the OS.
> > > > Let's see how much is being consumed:
> > > > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > > > from master..sysperfinfo
> > > > where object_name = 'SQLServer:Memory Manager'
> > > > and counter_name in ('Target Server Memory (KB)', 'Total
> > > > Server Memory (KB)')
> > >
> > > > Then post the max server memory result
> > >
> > > > "Ron" wrote:
> > >
> > > > > Do you need Enterprise Edition or will this work in Standard Edition?
> > >
> > > > > "Joe" wrote:
> > >
> > > > > > Can you RUN the following and post the output of the max server memory
> > > > > > statement?
> > > > > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > > > > trying to be obtuse, just wondering where your concern is.
> > > > > > sp_configure 'show advanced options', 1
> > > > > > RECONFIGURE
> > > > > > GO
> > > > > > sp_configure 'max server memory' ;
> > > > > > GO
> > > > > > sp_configure 'show advanced options', 0
> > > > > > RECONFIGURE
> > > > > > GO
> > >
> > > > > > "Ron" wrote:
> > >
> > > > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > >
> > > > > > > Here's the facts:
> > >
> > > > > > > Server has 8gb.
> > > > > > > /PAE is in the boot.ini
> > > > > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > > > > login
> > > > > > > Server was re-booted
> > > > > > > Use AWE to allocate memory was set via SSMS
> > >
> > > > > > > I can't increase the Max Server Memory via SSMS
> > >
> > > > > > > Any help would be appreciated.
> > >
> > > > > > > Thanks
> > >
> > > > > > > Ron- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > What do you mean when you say it won't go above 2GB? If you are
> > looking at the following number: 2147483647 in the option for Max
> > Memory - remember, that number is in MB which is 2097151.9990234375
> > GB, much larger than 2GB :)
> >
> > If you want to use 6GB set it to: 6144 MB.
> >
> > I would also include the /3GB switch in the boot.ini with only 8GB
> > available. The general rule is: more than 4GB but less than 12GB
> > include /3G - over 12GB don't include /3GB because the OS needs more
> > than 1GB of memory available when you have more than 12GB of memory
> > available.
> >
> > HTH,
> >
> > Jeff
> >sql

Wednesday, March 21, 2012

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

Martin

|||

Hi Martin

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

aero1

Hi Greg

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

Martin

|||

Hi Martin

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

aero1

Hi Greg

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Friday, March 9, 2012

Incompatible Frameworks between Sql Server June CTP and VS 2005

Hi,

I have a Sql Server 2005 June CTP which just arrived for me. I installed it on the my workstation which is XP Professional with SP2 successfully.
I also did attempted to install the latest (to my knowledge) of VS 2005 Beta which downloaded from MSDN from my work Version 8.0.50712.6 which has the framework version of 2.0.50712.
Obviously error is prompted regarding the framework conflict. So I uninstalled the new Sql Server June CTP in flavour of installing the VS2005.

At work, we have the Sql Server April CTP which has framework version 2.0.50215. I tested connection by using new VS 2005 I just installed to this server. Well, I can see it and able to goes amend the tables etc. Have not test the T-SQL or do any coding yet.

Can any one advice me that is there an version of VS 2005 which works happily with June CTP version, so I can use it together.

I still do have the April CTP for both apps at home which are installed on my home pc. I gather that this Sql Server June CTP would not work with April CTP Beta2 as well or is it?

Are there any package to downloads to upgrade this apps to get them to work together?

How you can help me, and thanks in advance

Punprom Kasemsant Tongue Tied

Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.

Paul

Incompatible Frameworks between Sql Server June CTP and VS 2005

Hi,

Can any one advice me that is there an version of VS 2005 which works happily with June CTP version, so I can use it together.

I still do have the April CTP for both apps at home which are installed on my home pc. I gather that this Sql Server June CTP would not work with April CTP Beta2 as well or is it?

Are there any package to downloads to upgrade this apps to get them to work together?

How you can help me, and thanks in advance

Punprom Kasemsant Tongue Tied

Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.

Paul

Friday, March 30, 2012

Increase in memory not working - Help Please

Increase in memory not working - Help Please

Wednesday, March 21, 2012

Incorrect processing order for views

Incorrect processing order for views

Incorrect processing order for views

Incorrect Order in rendering report

Friday, March 9, 2012

Incompatible Frameworks between Sql Server June CTP and VS 2005

Incompatible Frameworks between Sql Server June CTP and VS 2005

sqlserver2000 date

Blog Archive

About Me