Easy questions about replication & Publication

Hi...
We have a SQL Server Std Edition that make syncronisation(merge
publication) with 100 mobile device.We create100 publications according to
our employees user id.We use articles with joins and user id for all
publications.The mobile devices make syncronisation over this publications.
Problem1:When I execute an insert or update statement in database , it cost
min 10 minutes for response.Last night I execute an update sql(for 10 rows)
at night.When i wake up morning,the transaction log is get full, and only 2
of the updates make succesfully.
Why database ddl operations get a long time to execute?
Problem2:How can I control transaction log automatically?
I'll wait for your answers..
Thanks...
answered above.
http://www.zetainteractive.com - Shift Happens!
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
"The_TOZ" <tunc@.te-mob.com> wrote in message
news:65A79B21-C9D6-498A-BC7F-3E2053C53EF3@.microsoft.com...
> Hi...
> We have a SQL Server Std Edition that make syncronisation(merge
> publication) with 100 mobile device.We create100 publications according to
> our employees user id.We use articles with joins and user id for all
> publications.The mobile devices make syncronisation over this
> publications.
>
> Problem1:When I execute an insert or update statement in database , it
> cost min 10 minutes for response.Last night I execute an update sql(for
> 10 rows) at night.When i wake up morning,the transaction log is get full,
> and only 2 of the updates make succesfully.
> Why database ddl operations get a long time to execute?
> Problem2:How can I control transaction log automatically?
> I'll wait for your answers..
> Thanks...

Easy question for experienced developers

How do I add a previously-made SQL Compact Edition database to my Visual Basic.Net 2005 project inside Visual Studio, and ensure that it gets built into the ClickOnce-deployed solution? Our dba has completed the db design for our project, and I need to add the file to the project I am writing business logic for.

I've been writing software for about 15 years but just now using SQL CE for the first time. I hope my questions are not too simplistic for this forum, but I ran into a problem with something so simple that I'm at a loss, and I thought I'd throw it in this newbie thread.

To wit, what is wrong with this syntax:

INSERT INTO refState(StateName)
VALUES('Alberta')
INSERT INTO refState(StateName)
VALUES('Alaska')

This is a snippet from a T-SQL script I've been using for years that populates reference tables for things like states, countries, etc.

When I try to run the query in Visual Studio 2005 Pro while I have my refState table open in the IDE, I receive the following error:

'Unable to parse query text'.

Then I say to myself, What? Why? So I try to verify the SQL syntax, and then I receive this new error:

'This command is not supported by this provider.'

On the surface it looks like ANSI SQL is out the window. So, I'm guessing I'm missing some fundamental understanding.

Little help? thanx

|||

Well, consider that the Compact Edition is not SQL Server and does not understand TSQL. The problem with your SQL is that the SQLCe engine can process one (and only one) SQL statement at a time. It does not understand the concept of multiple operations or resultsets as supported in TSQL.

In my EBook, I show an example (and a class) used to import schema files that can be used to create a SQLCe database and populate the schema. Gettting data into the database is also a challenge as SSIS does not work well with SQLCe (yet). One approach is to setup Merge Replication with a SQL Server Workgroup (or better) engine, use RDL or the new (unreleased) ADO Synchronization Services. Sure, you can write your own INSERT loop to import data and this would not be that hard to do... code-intensive but doable.

hth

See www.hitchhikerguides.net

|||

Thx for the feedback Bill.

Also, I found what we needed as far as setting up private ClickOnce deployments (which is what we're using) at the following URL:

http://msdn2.microsoft.com/en-us/library/bb219482.aspx

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:
> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
Risun
Risun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun
sql

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Early issues with Compact Edition

I've just downloaded and installed sql server 2005 compact edition, and I'm having some issues that I'd like to get clarification on. Minor stuff, but irritating.

1. Although you can drop an sql file onto the management studio for a normal mssql connection, you can't do this with a compact edition connection. Instead you have to paste the contents. True? Or am I goofing up somehow?

2. Compact edition doesn't support varchar, but does support nvarchar. True? Or [etc]

3. I can't seem to get compact edition to run a sql script. It seems to only take one command at a time. I'm pretty sure this is a screwup on my part since the tutorials show scripts being run. Are there limitations that I don't know about?

TIA for any help.

Thanks for using SQL Server Compact.

1) We integrate with tools for big products like SQL Server Management Studio, Visual Studio. We have just strated this. Sure, we will take this feedback and improve upon like Drag-N-Drop you are expecting.

2) Yes, we dont support varchar. The world started with ANSI and is now moving to UNICODE. We being new product started now, dont want to reinvent the wheel and started with UNICODE. After all UNICODE is super set of ANSI. And, we have internal techniques to store it in a compact manner. So, no plans to support varchar/char/text in future too.

3) SQL Compact does not support batch queries. However, in the tools you should not experience it at tools level like SSMS/VS. If you select multiple SQL queries, there is a batch processor in these tools which automatically splits the batch into individual queries and runs them. However, all of them are exected in a single transaction.

Thanks,

Laxmi

each time I build program, data is lost

I am using visual basic 2008

I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,

when I close the program and build again, the data previosly updated are deleted, and I get empty database? why is that. do i need to set some copy to.........properties. i have used copy if new.

I want to add something to it, that when I manually entered data, they are not gone. But when I entered from form, next time the data lost.|||

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

Dynamic SQL Cache

I am running SQL Server 2005 SP2 Standard Edition on Windows 2003 Server
Standard Edition (4 gb RAM, No AWE Enabled, default Max/Min Memory
settings). From PerfMon traces the overall server memory appears to be just
fine all the time. The SQL Server appears to run out of available SQL
memory resources during certain ETL jobs and log the following errors:
--Error: 701, Severity: 17, State: 123
--There is insufficient system memory to run this query.
I see significant spikes in the SQLServer:Memory Manager:SQL Cache Memory
counter during times when I experience "Out Of Memory" errors on my SQL
Server. The value is normally 0 until milliseconds before I get the 701
Errors. According to BOL this Counter represents the "Total amount of
dynamic memory the server is using for the dynamic SQL cache."
What exactly is the dynamic SQL cache? What should I look for in tracking
down this memory resource hog?Hi Ulysses,
I understand that you encountered the insufficient system memory error
during your ETL jobs execution.
If I have misunderstood, please let me know.
Dynamic SQL cache means that the dynamic memory that your SQL Server
instance caches. By design SQL Server will try to occupy memory as much as
possible to ensure its performance. SQL Server frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory. However in memory intensive usage situations,
this happens uncertainly.
You may refer to:
Dynamic Memory Management
http://msdn2.microsoft.com/en-us/library/ms178145.aspx
ETL jobs for extracting large volume of data may consume large memory for
SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
memory at most. I recommend that you use /3GB switch or enable AWE option
in your SQL Server 2005.
You may refer to the following articles:
Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/en-us
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi
>SQL Server frees memory when it has
> more than the min server memory setting,
BOL says
The database engine will not free any of the acquired memory until it
reaches the amount specified in min server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:azN3LJrpIHA.1788@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ulysses,
> I understand that you encountered the insufficient system memory error
> during your ETL jobs execution.
> If I have misunderstood, please let me know.
> Dynamic SQL cache means that the dynamic memory that your SQL Server
> instance caches. By design SQL Server will try to occupy memory as much as
> possible to ensure its performance. SQL Server frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory. However in memory intensive usage
> situations,
> this happens uncertainly.
> You may refer to:
> Dynamic Memory Management
> http://msdn2.microsoft.com/en-us/library/ms178145.aspx
> ETL jobs for extracting large volume of data may consume large memory for
> SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
> memory at most. I recommend that you use /3GB switch or enable AWE option
> in your SQL Server 2005.
> You may refer to the following articles:
> Enabling AWE Memory for SQL Server
> http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750/en-us
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ===========================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ============================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>
>|||Sorry for delaying this response since I took a sick leave these two days.
Hi Uri,
Could you please point out which paragraph that BOL describes this
information?
From the article "Dynamic Memory Management" in BOL, you can find the
following description:
" A SQL Server instance continues to acquire physical memory until it
either reaches its max server memory allocation target or Windows indicates
there is no longer an excess of free memory; it frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory."
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks. I did enable the /3GB switch and enabled AWE in SQL Server 2005 and
then set the min / max memory using sp_configure and this allowed for the
ETL process to run without interupting the other processes (i.e., no more
Out-of-Memory errors). I do not know if it was simply enabling the "AWE
/3GB" or manually setting the "min/max" memory that fixed the problem.
Perhaps it was the combination of both changes. I have moved on to more
pressing issues now (i.e., Linked Server Connection Error: 18456)|||Hi Ulysses,
Thanks for your response for letting me know this issue has been fixed.
Just clarify one thing that actually you can just enable one of the two
options:/3GB and AWE. In other words, if you specify /3GB switch, you do
not need to enable AWE; otherwise, you can enable AWE together with /PAE
switch. You may refer to:
Large memory support is available in Windows Server 2003 and in Windows 2000
http://support.microsoft.com/kb/283037/en-us
Regarding the linked server connection error, I will continue working with
you for further research under your other post "Linked Server Connection
Error: 18459".
Please feel free to let me know if you have any other questions or concerns.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi Charles
I hope now you are doing well.
I was mention BOL (SS2000) Min server memory option then Effects of min and
max server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:3FUxTgpqIHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Sorry for delaying this response since I took a sick leave these two days.
> Hi Uri,
> Could you please point out which paragraph that BOL describes this
> information?
> From the article "Dynamic Memory Management" in BOL, you can find the
> following description:
> " A SQL Server instance continues to acquire physical memory until it
> either reaches its max server memory allocation target or Windows
> indicates
> there is no longer an excess of free memory; it frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory."
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>