Memory Usage Of SQL Server 2005 Mobile Edition
Hi forum readers,
we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.
We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?
Does anyone have a deeper insight into that server an can answer my questions.
Best regards,
Tobias
View Complete Forum Thread with Replies
Related Forum Messages:
Memory Usage By SQL 2005 Enterprise Edition
Hi Guys, I have a question on my SQL server 2005 EE memory usage. It got 24 GB and when I see on Task manager it always shows less than 3gb usage. AWE enabled on this machine and here are the DBCC MEMORYSTATUS output. Pls let me know or educate me how to understand these stats. Thanks in advance. Memory Manager KB ------------------------------ -------------------- VM Reserved 1679452 VM Committed 254756 AWE Allocated 20840448 Reserved Memory 1024 Reserved Memory In Use 0 (5 row(s) affected) Memory node Id = 0 KB ------------------------------ -------------------- VM Reserved 1675292 VM Committed 250748 AWE Allocated 20840448 MultiPage Allocator 27232 SinglePage Allocator 761896 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 20024 MultiPage Allocator 1544 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 1632404 VM Committed 211092 AWE Allocated 20840448 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 5352 (7 row(s) affected) MEMORYCLERK_SQLQUERYEXEC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9440 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1096 MultiPage Allocator 88 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 1480 VM Committed 1480 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 264 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 6528 VM Committed 6528 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 4544 MultiPage Allocator 12288 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2816 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCLR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 192 (7 row(s) affected) MEMORYCLERK_SQLHTTP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SNI (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 360 MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_FULLTEXT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLXP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1920 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLQERESERVATIONS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 157400 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_HOST (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 80 (7 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 10592 MultiPage Allocator 6672 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 24 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_OBJCP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 85480 MultiPage Allocator 192 (7 row(s) affected) CACHESTORE_SQLCP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 601672 MultiPage Allocator 616 (7 row(s) affected) CACHESTORE_PHDR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1568 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XPROC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_TEMPTABLES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 128 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_NOTIF (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_VIEWDEFINITIONS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBTYPE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBELEMENT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_STACKFRAMES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_BROKERTBLACS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 136 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERKEK (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERDSH (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERRSB (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERREADONLY (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERTO (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_EVENTS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 952 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SCHEMAMGR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 6064 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2352 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3616 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 368 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LBSS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 5744 MultiPage Allocator 48 (7 row(s) affected) OBJECTSTORE_SERVICE_BROKER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 4096 VM Committed 4096 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1640 MultiPage Allocator 0 (7 row(s) affected) Buffer Distribution Buffers ------------------------------ ----------- Stolen 8240 Free 16100 Cached 88818 Database (clean) 2119366 Database (dirty) 371998 I/O 0 Latched 9 (7 row(s) affected) Buffer Counts Buffers ------------------------------ -------------------- Committed 2604531 Target 2604531 Hashed 2491373 Stolen Potential 53825 External Reservation 17724 Min Free 304 Visible 177664 Available Paging File 1380845 (8 row(s) affected) Procedure Cache Value ------------------------------ ----------- TotalProcs 11877 TotalPages 86207 InUsePages 863 (3 row(s) affected) Global Memory Objects Buffers ------------------------------ -------------------- Resource 300 Locks 208 XDES 162 SETLS 8 SE Dataset Allocators 16 SubpDesc Allocators 8 SE SchemaManager 757 SQLCache 2069 Replication 143 ServerGlobal 27 XP Global 2 SortTables 1523 (12 row(s) affected) Query Memory Objects Value ------------------------------ ----------- Grants 1 Waiting 0 Available (Buffers) 45776 Maximum (Buffers) 65451 Limit 65451 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Last Target 68895 (11 row(s) affected) Small Query Memory Objects Value ------------------------------ ----------- Grants 0 Waiting 0 Available (Buffers) 3506 Maximum (Buffers) 3506 Limit 3506 (5 row(s) affected) Optimization Queue Value ------------------------------ -------------------- Overall Memory 1166524416 Target Memory 398794752 Last Notification 1 Timeout 6 Early Termination Factor 5 (5 row(s) affected) Small Gateway Value ------------------------------ -------------------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 250000 Threshold 250000 (6 row(s) affected) Medium Gateway Value ------------------------------ -------------------- Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor 12 (5 row(s) affected) Big Gateway Value ------------------------------ -------------------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 (5 row(s) affected) MEMORYBROKER_FOR_CACHE Value -------------------------------- -------------------- Allocations 88647 Rate -250 Target Allocations 130221 Future Allocations 0 Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_STEAL Value -------------------------------- -------------------- Allocations 6637 Rate 220 Target Allocations 48681 Future Allocations 0 Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_RESERVE Value -------------------------------- -------------------- Allocations 19803 Rate 2302 Target Allocations 73496 Future Allocations 31702 Last Notification 1
View Replies !
How Do I Measure Bandwith Usage For Merge Replication SQL 2005 To Sql Server Mobile 2005?
Hi, Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device. Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ? thnx, pdns.
View Replies !
SQL Server 2005 DTS/Memory Usage
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes. Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following? 1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this? 2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB? Thanks for any help.
View Replies !
SQL Server 2005 Mobile Edition Vs. RMS
Hello, I am developing a mobile application in which i have to create a database.. My database contains relationships between its tables.. I would like to know what is the best solution for developing the database?? SQL mobile edition or RMS!!! If you say RMS, how should i create the relationships between my tables?? thnx
View Replies !
Help Needed In Merge Replication, SQL Server 2005 Mobile Edition And VC# 2005
I have written following code in my application I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ]. Can anybody please tell me, where I went wrong ??? In this code anywhere else???? Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf @"Data Source = .DbDotNetCF.sdf"; The code is as follows: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe; namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf"; private DataSet dsJobLists; public Form1() { InitializeComponent(); } private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } } private void Sync() { SqlCeReplication repl = new SqlCeReplication(); repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll"; repl.Publisher = @"RAHU"; repl.PublisherDatabase = @"DotNetCF"; repl.PublisherSecurityMode = SecurityType.NTAuthentication; repl.Publication = @"PubDotNetCF"; repl.Subscriber = @"SubDotNetCF"; repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;"; try { if (!System.IO.File.Exists(filename)) { repl.AddSubscription(AddOption.CreateDatabase); } repl.Synchronize(); } catch (SqlCeException ex) { DisplaySQLCEErrors(ex); } finally { repl.Dispose(); } // Display Same Data In Another DataGrid : dataGrid1 SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'"); SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn); if (dsJobLists == null) { dsJobLists = new DataSet(); } try { dsJobLists.Clear(); daJobLists.Fill(dsJobLists, "JobLists"); dataGrid1.DataSource = dsJobLists.Tables["JobLists"]; } catch (SqlCeException ex) { DisplaySQLCEErrors(ex); } } private void DisplaySQLCEErrors(SqlCeException ex) { for (int i = 0; i < ex.Errors.Count; i++) { MessageBox.Show("Index #" + i.ToString() + "" + ex.Errors.Source + "" + "Error: " + ex.Errors.Message, "Error No. " + ex.Errors.NativeError.ToString()); } } private void Form1_Load(object sender, EventArgs e) { Sync(); DeleteDB(); if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } } I have created a merge replication correctlly( I suppose, there were no errros) Please help Your help will be appriciated
View Replies !
Update Sql Server 2005 Mobile Edition
I have use this guide to make a application for windows mobile 5.0. http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/DataBinder.htm Everything is work. I can add, remove data in the dataset. But i can't update the sql server database. can somebody tell me whats wrong? Nitro.
View Replies !
SQL Server 2005 Mobile Edition Free?
Hello I have a question: is the SQL Server 2005 mobile edition free? or is it necessary to buy a licence when i use it combinated with a comercial program which i have delevoped by myself and i sell it? greetings stefan lederer
View Replies !
Creating SQL Server 2005 Mobile Edition On Desktop
Hello Everybody I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application. Anybody having this solution for this prob. Regards Chikuu
View Replies !
Mobile Solution With SQL Server 2005 Express Edition And Smartphone
Hi,i want to create a solution which shall consist of a sql server 2005 -data base (express edition if the following features are available byexpress edition) and a mobile windows application (running on asmartphone under windows mobile 5.0).The sql server shall be installed on a ordinary laptop using windowsxp (not professionall edition).Which would be the best way to exchange the data between mobile deviceand pc? I learned that there is a SQL Server Compact Edition for themobile device and that rda would be a way to exchange data - but theni would need internet information system and this does not run on xphome edition.Which alternatives would you suggest me?Thanx in advanceWolfgang
View Replies !
SQL CE 3.0 SQL Server 2005 Mobile Edition Database Password Problem
Hi, I was wondering if someone could help me as this is a bit of a puzzle. I have created a database using a password, the create table scripts are executed successfully using the connection string however when I try and use the same connection string to insert some data into the database an error is produced, with the following message 'The specified password does not match the database password'. I have opened the database using the SQL Server 2005 Management Studio and provide the password and I can access the database without a problem. When the connection object is first instantiated all the Connection String property, the connStr variable and the modifiedConnStr are all correct (by this I mean that the data source and the password are present). However when the connection object is accessed again the Connection String property and the connStr variable are both set incorrectly (by this I mean that the password= section of the string is missing) however the modifiedConnStr is set correctly (by this I mean that the password= section is present in the string). The connection object does not get set in between creating and accessing the connection object. I have resorted to using no password, however I really need to be able to use a password. Here is a copy of the connection string I am using (Copied directly from the quick watch window): "Data Source = '\Program Files\Application\DataBase\MyDatabase.sdf'; password="Pa55word";" I am sorry this a little long winded however I would like to provide as much information as possible. I hope someone can help me with this problem. Thank you for reading. Paul Diston
View Replies !
SqlCeConnection Failing To Open A Connection To The SQL Server 2005 Mobile Edition
Good day, I am writing an application using VB.Net 2005 for the Windows CE 5.0 device and it is connecting to a SQL Server 2005 Mobile Edition Database. The trouble I'm having is establishing a connection to a SQL Server Mobile database on the device. Here is the code I am using: Code Snippet Public gConnectionString As String = "Data Source=Program FilesMobAppMobDB.sdf;Persist Security Info=False;" Code Snippet Imports System.Data.SqlServerCe Public Class DBManager Public Shared gSQLCEConnection As SqlCeConnection Public Shared Function OpenDB() As Boolean If gSQLCEConnection IsNot Nothing Then Throw New InvalidOperationException("Connection already open.") End If gSQLCEConnection = New SqlCeConnection(gConnectionString) Try gSQLCEConnection.Open() 'Error occurs here Return True Catch ex As SqlCeException MsgBox(ex.Message & vbCrLf & ex.HResult & vbCrLf & ex.NativeError & vbCrLf & ex.Source) Return False End Try End Function In the immediate window I recieve the following messages: A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll I believe that the first few lines are caused by the icons/pictures that I have included with my app, but the SqlCeException line occurs when the connection to the .SDF file is trying to be established. Here is the exception output: Message: "" (Blank) HResult: -2147024882 NativeError: 0 Source: SQL Server 2005 Mobile Edition ADO.NET Data Provider Programs under Remove Programs on the PDA are: Microsoft .NET CF 2.0 ENU-String R... Microsoft .NET Compact Framework... Microsoft SQL Client Microsoft SQL Mobile 2005 Microsoft SQL Mobile 2005 [EN] Microsoft SQL Server 2005 Compact... Microsoft SQL Server 2005 Compact... Microsoft SQL Server 2005 Compact... Microsoft SQLCE 2.0 Microsoft SQLCE 2.0 Dev Any ideas on what could be stopping this connection from being established? It was working before but all of a sudden just stopped. I have warm booted, cold booted, and rebuilt with no luck and also checked that SQL Query Analyzer is not running on the PDA and that the connection string is valid. The call to OpenDB occurs in the form_load of the startup object. Thanks in advance, Leon
View Replies !
Query Performance Issue Of Sql Server 2005 Mobile Edition On Device
Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0. Same query on sql 2005 mobile ed. takes 11 minutes . on database having same data. Sample query SELECT routes.location,routes.equipment_type, routes.contract_type, routes.maintenance_interval,routes.bank_description,routes.Unit_Des, routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when task_last_completed.due_date is NULL then getdate()-1 else due_date end) as due_date FROM routes left outer join tasks on tasks.model = routes.model and tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on tasks.task_id = task_by_contract_type.task_id and task_by_contract_type.contract_type = routes.contract_type and task_by_contract_type.model = routes.model left outer join task_last_completed on routes.unit_no = task_last_completed.equipment_Id and tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE 'S153825-01%' group by routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY routes.location, routes.bank_description, routes.Unit_Des WHY????????????????
View Replies !
SQL Mobile Server 2005 To SQL Server Developer Edition 2000
I am looking for a detailed tutorial that explains how to set-up merge replication services for a major project. I have seen all of the claims that SQL Mobile Server 2005 is able to connect to a merge publication with SQL Server 2000. However, where are the tutorials? I must have a proof of concept in a few days to quote this project. Can anybody help with this issue? Thanks!
View Replies !
SQL Server 2005 Mobile Edition Using Server Tools
I have installed SQL Server 2005 Mobile edition and also the server tools. I configured a website to sync with my mobile application. When I go t check the SQL server Mobile agent with this url: http://localhost/MobileTest/sqlcesa30.dll i get a http 500 internal error I am using remote desktop to access the machine, when I am directly on the machine there is no error I get the correct message in the browser "SQL Server Mobile Server Agent 3.0" Is access to the web site via remote desktop a problem? jawahar
View Replies !
Memory Settings In SQL Server 2005 Standard Edition
Hi, I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far: No maximum - one instance used about 12GB of RAM so then the others struggled Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused. Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances. I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB. How do I go about configuring SQL Server to behave like this? Is it possible? Thanks.
View Replies !
SQL Server 2005 Workgroup Edition In SBS 2003 R2 Max Memory Question
We have only a small busines and haven't got too much experience with servers and now have a proliant server coming in with SBS 2003 R2 Premium with the 4 GB max memory that SBS 2003 can handle according to the specs. We weren't planning on using the SQL 2005 worlgroup edition up till now, but now we might. According to the specs of SQL server 2005 workgroup edition however, it has a max RAM of 3GB! Is this going to be a problem and should we keep using our previous DB, or can we migrate toward SQL server with the 4 GB of RAM?
View Replies !
MS SQL 2005 && Memory Usage Question
OVERVIEW I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage. The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down. QUESTION Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds. I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true? What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this? Thanks for your help.
View Replies !
SQL 2005 (32-bit) Cluster Memory Usage
We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup: 32-bit Windows Server 2003 SP1 SQL 2005 SP2, 2-node cluster 16 GB memory on each node /3GB /PAE switches in the boot.ini file AWE Enabled, min memory=0, max memory=14000 The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage? I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000? Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know. Chris
View Replies !
SQL 2005 Memory Usage Just Keeps Rising
Hi, I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db. The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak. The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException. I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be? Thanks, Paul G
View Replies !
SQL 2005 Express - Sqlserv.exe Memory Usage Always Increasing.
Hi, I'm hoping that some might have an answer for me after much net searching. I have a server (200GB disk space, Dual 3.8 GHZ processors, 4GB memory) that hosts 6, very small, SharePoint sites (WSS 2.0) and SQL 2005 express handling 1 config DB and 7 content DB's. The biggest DB at the moment is only 300 MB and the sites are not actively being used yet, they are only open to a select number of users (+ - 25 in total) who are using them as reference "areas" at the moment. Each site is running in it's own application pool as well. I find that the sqlserv.exe process increases in memory usage and does not seem to decrease. It gets to the point of 960 MB usage and then databases cannot be used (SQL 2005 Express max memory is 1GB). SQL seems to "close" them down and site errors being received are "site is not in configuration database". When the SQL service is restarted the memory usage idles around 70 - 80 MB, the sites are 100% again but after an hour or 2 the memory usage is sitting at 560 MB again and doesn't seem to decrease. Is there a way I can bring this memory usage down? Any feedback would be greatly appreciated.
View Replies !
Memory Usage In SQL Server
Hello all,How can I tell how much memory SQL Server is using on a server. OnWindows 2000, whenever I go to Task manager/processes/memory usage SQLServer seems to be showing 1,744,124K. On all of my servers withvarious size, usage of databases, all of them show SQL Server to beusing about the same amount of memory. Can someone explain this to me?Shouldn't it use more for larger databases, heavy hitting databases?Also, I normally check Dynamically configure SQL Server memory and putthe maximum threshold to a little bit less than the max on the server.The minimum query memory is set to 1024. Is that 1024 a subset of thememory used by SQL Server, or is this additional that can be used?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
SQL Server Memory Usage
Hi All, Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important. What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file. Upgrading the memory is currently not an option :( I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it? Can SQL release the memory and still act as normal? Any input would be generously appreciated Cheers!
View Replies !
SQL Server - Memory Usage
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it. Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem. Is this not counter-intuitive? Or is this the correct solution? Thanks.
View Replies !
Memory Usage For Sql Server
Hi, One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now. We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point. We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%. Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage. There is no noticable difference performancewise yet. My questions are: Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it? Thanks in advance for your opinions, Shaili
View Replies !
Memory Usage Of SQL Server Ce
Hi, I'm wondering about the memory usage of SQL Server Ce and the Compact Framework. I've heard I can use only 32 MB per process. SQL Server Ce runs in process and my database file is about 60 MB big. 1. Does that means, I can only use for my application: 32 MB - CLR - SQL-Server Runtime - parts of my database file hold in memory 2. Is SQL Server Ce swapping parts of my database file into my process memory? If so, is there a chance to group some tables, that they are swapped together to increase performance? 3. Means big databases big memory usage in my process memory? Thanks, Stefan
View Replies !
SQL Server Process And Memory Usage
Hi AllSome my SQL Server are experience high memory usage.1. How can I detect which process which process cause the big memoryusage and not released?2. Which sql server components in this memory, and what are their usagedistribution?Any help will be appreciated.ThanksWillie
View Replies !
Increasing Memory Usage On SQL Server
Hi all, Our Production server has 4GB RAM and is running SQL Server 7.0. By default since SQL Server 7.0 Standard Edition can take up only less than 2GB, our SQL Server is now using only 1.8GB (leaving the rest for the OS, Windows 2000 Server). Inorder for SQL Server to take advantage of more than 2GB of RAM it is suggested that boot.ini be modified to include the switch, /3GB Has anyone seen any issues with doing this? Is it safe to do so on the Standard Edition of SQL Server 7.0? Thanks in advance, Praveena
View Replies !
Sql Server Performance And Memory Usage
Hi All, I'm new at SQL Server, I decided to use it in order to get all the advantages of using Vb.net and Sql. My server is a SQL standard version. I'm using a relational DB most of the time for complex select queries, everytime the server is used it performs 30 or 40 queries at the time, and I have recently realized that server consumes a lot of memory after one or two days of beign up. Let's say that if I restart the SQL Server memory usage is about 650 Mb, but after two days memory is 1.4Mb, I have used Sql Profiler and Tunning Assistant where it recommended me to create some indexes, which indeed I created them, but that did not solved the memory problem, although some queries run faster. My questions are: is this memory usage is normal ? if not, what should I check out to reduce memory usage? Thanks in advance George
View Replies !
Memory Usage For SQL SERVER 2000
Good day to all of you I am faced SQL SERVER 2000 Memory usage problem. I am using Windows 2000 Server SP4 and SQL SERVER 2000 SP4. When User running some in-house application software, the memory for sqlservr.exe was increased . But, when user logout from the software. The sqlservr.exe did not decrease the memory. I have around 100 Users in my company. SQL Memory will countinues increase till max memory usage in CPU. May i know how to order SQL SERVER need to purge memory when USER was log off from the Program? Or my SQL SERVER was corrupted /missing file? Please advice Many thanks! Melvin
View Replies !
Replication Between 2000, 2005, && Mobile Edition (with Identities)
Hi, I have a server "A" (which is SQL 2000), a server "B" (SQL 2005), and mobile devices running SQL mobile edition. I need to set up transactional replication between server A & B (with A as the publisher/distributor), and then B needs to be setup with merge replication between it and the mobile devices. Basically I have mostly reference data being pushed out from server A (with only one or two updateable tables) to server B. The mobile devices then connect to server B via a web service, to receive the latest data. The mobile devices can update data, but send the updated data back to server B via another web service (not via merge rep). Server B then sends this updated data back to server A, also using a service (as apposed to native replication). The problem I have is that most tables have identity columns set as their primary keys. I've read about identity management, and would like to specify identity ranges for servers A & B, but this only seems to be possible when using merge rep (or tran. with updating subscribers), not normal (i.e. one-way) transactional rep. Is there a way to get around this? I was going to just replicate without worrying about having identities on the tables on server B, but of course it needs the primary keys (and therefore the identities) for its merge replication to the mobile devices. Any assistance would be hugely appreciated. Thanks, David
View Replies !
SQL Server Memory Usage Continue To Increase
Hi, On the last Sunday 26/12/04 on 20:00 I got on one of our applications in the customer site this error: "There is insufficient system memory to run this query." Details: Computer: IBM XSERIES_345, dual CPU 2.8 Intel xeo, 1,047,952 KB ram, windows 2000 server sp4. Disk space: 4395 MB, Virtual memory: initial size 1536 MB, Max size 3072 MB. Registry size: current 14 MB, Max 90 MB. SQL Server 2000 SP 3, two production databases (1) Data size 100 MB, log size 15 MB (2) Data size 500 MB, log size 125 MB All the memory options are configured as default i.e. dynamically configure for memory and not changes in any sp_configure options. SQL server is the only major application running on the machine. Facts: On that evening the I've checked the Task Manager and found that SQL Server use 500 MB in Mem Usage and 1200 MB in VM size. In the query analyzer I execute the select query which results the "…insufficient system memory…" error on both databases and on the one with more data it failed with this error, but on the other database it completed successfully. I restart SQL Server service to solve it, and as for today 28/12/04 at 08:00 it use 590 MB in Mem Usage and 590 MB in VM size. I have checked the system with Performance monitor and didn't find any problems I use the Memory: Pages/sec, Memory: Available Bytes, Physical Disk: % Disk Time, Processor: % Processor Time, SQL Server Buffer: Buffer Cache Hit Ratio. We have 8 user connections for one of the database and 16 for the other one. Our system runs with this configuration for 2 months, I backup the databases FULL, DIFF and LOG backup. It's not real time system, and the load on the SQL is medium, but we use bad queries as update for all fields in one query (please don't ask…) I know that SQL server is greedy with the memory and released memory only if other process needs it. Any ideas why it happens? Thanks, Yaniv
View Replies !
SQL Server Mobile 2005 Merge Replication Problem On Windows Mobile 5.0
Dear ppl, I am writing an application for a device (MDA Pro T-Mobile) having Windows Mobile 5.0 using -MS .NET Compact Framework 2.0 SP-1 -SQL Mobile 2005. -VS 2005 .NET The application uses Merge Replication. The error occurs in the Synchronise() Method of the SqlCeReplication object. "SQL Server Mobile encountered problems when opening the database." repl.AddSubscription(AddOption.CreateDatabase); repl.Synchronize(); I don't understand why I am having this error. It does create the database on AddSubscription() method but it is failing opening the database on Synchronise(). I have also tried uninstalling and then reinstalling all the SQL Mobile components in the following order. -sqlce30.wce5.armv4i.cab -sqlce30.repl.wce5.armv4i.cab -sqlce30.dev.ENU.wce5.armv4i.cab However, when i run this application on a device(Dell X50 AXIM) with Pocket PC 2003, it runs fine creating the database and sysnchronising it. The target platform for the project is Windows Mobile 5.0 Pocket PC SDK. Does any one have any clue what could be the problem ? Regards Nabeel
View Replies !
Memory Leak...How To Get Memory Usage For Each Connections?
Hi Guys,I find there is memory leak in our SQL 2000+SP3 databass. We have aclient-server system, and the there are Jrun4.0 running JSP and someotherJava application talks to the database.However, for only less than 40 users, the SQL 2000 memory usage willincrease consistantly until it use all the memory it can get ,about 1.7Gmemory. Then eventually the SQL server process will hang in couple days.I don't know whether JRun JSP stuff or Java application is the culprit, andI am wondering if there is a way to monitor the memory usage of everydatabase connection.I would love to know what you guys think!Thanks a lot!
View Replies !
Problem Using SQL Server Mobile 2005 With Windows Mobile 5 Application
Hello, I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database. The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration. The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK. So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h". Did I miss something to install ? Do you know how I can resolve the problem ? By advance, thank you for any answer. -- Gregoire
View Replies !
SQL 2005 Mobile Edition Error: The Buffer Pool Is Too Small Or There Are Too Many Open Cursors.
I am working with merge replication. The db server is running Windows 2003 Server Std x64 with SQL 2005 Std x64. The IIS server is running Windows 2003 Server Stx x86. The clients are Windows Mobile 5.0 running SQL 2005 Mobile. I have previously been able to get the test client to initialize a subscription to the publication. I made some changes recently and added about 7 tables, bringing the total articles to 104. Some of the articles have unpublished columns, which means they are using vertical filtering as well. This publication is also using parameterized filters. During publication creation, there are several warnings that "Warning: column 'rowguid' already exists in the vertical partition". The snapshot agent runs without any errors. When the client attempts to initialize, I get the error "The buffer pool is too small or there are too many open cursors." My question is 2 part. First, what is causing this error, and second, how can it be resolved?
View Replies !
Isqlw For Sql Server Mobile Edition (sql Ce 3)
In sql ce 2.0 in window ce 2003, I use isqlw20.exe to create database and table. Developed application with eVC++ 4.0 Now I have to use window mobile 5, VS 2005 for native C++ development, sql server mobile edition, but I can't found isqlw for this version of sql. I do not have sql server install in my desktop PC. So how could I create database and table with the pocket pc's database ? Thanks.
View Replies !
SQL Server Compact/Mobile Edition Versions
I am confused about the version numbers for SQL Server Compact/Mobile. It _looks_ like the versions went from CE 2.0 to Mobile 2005 to CE 3.0 (or 3.1). Is this correct? In fact, I found one location the specifically said Compact Edition is the next version of Mobile. In another location I saw Compact Edition referred to as 2005 CE. This has got me going because I am creating a Mobile/CE app and I keep getting a "Initialize SQL Server Reconciler" and the best information I can find indicates a mismatch between the server component and the client component. So, what are the current versions of Mobile/CE? Is there a location that lists all of the components and what version they should be? Thanks. Chris Mecum
View Replies !
How Do I Use The Crosstab Query In Sql Server Mobile Edition
I can use crosstab query(intersection table) in sql server. for example : CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY] GO INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60) INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70) INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75) INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57) INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100) Go -------- declare @sql varchar(8000) set @sql = 'select name,' select @sql = @sql + 'sum(case subject when ''+subject+'' then source else 0 end) as ''+subject+'',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name' exec(@sql) But I cannot use it in sql server mobile editon. HOw do I? thank you very much!!!
View Replies !
|
TRACKER
