Hi- I have questions about mobile database limits. The application is entirely C# / Compact Framework 2.0, using VS2005. Devices we support are all running WM5. We're trying to incorporate what seems to be a large database that the application needs to talk to offline from any network, so it needs to be stored & hosted within the database and hence we started working with the various generations of what is now SQL Server Compact Edition.
In order to profile the responsiveness I worked up a test database with the general collection of fields we are interested in. The main table has 80,000 rows with one primary key and one of the numeric fields indexed. There is a 'nickname' table which contains about 150,000 items with just a string for a nickname and the key that nickname goes with. With all the rows and an alpha index for the nickname field the database is about 42 mb. Which is pushing the amount of storage on the handheld, but of our two models one is fine and one still has about 30 mb free with the DB on the handheld.
My problem seems to be that one kind of device can work with this database and one cannot. The device I was using the prototype the application is a HP iPAC hx2490b. On that device more than once I have corrupted the database, but not yet as a reproducible process.
My other device is a Dell Axim X51. On that device I seem to corrupt the database with very simple actions. Just now I tried a query like (From the Query Analyzer 3.0)
Code Snippet
Select * from ReagentNicknamesTable where alt_names like 'alpha%'
Which should hit 2099 of the 150,000 records. I got about 400 back and then the table was corrupted. On this device I cannot recover the database as there isn't enough disk space for two copies of the DB. So corruption in the field would be a showstopper. But getting corruption from a simple query like this means I really can't use SSCE.
This problem with the dell showed up when I was using the original device sql that came with VS2005. (SQL Server Mobile?) The cabs & dll's were dated from 2005 and had a build number of 3.0.5206.0. I have since updated to the current version of SQL Server Compact Edition and the dlls have build number 3.0.5300.0. The IDE is set to Compact Framwork 2.0, sp1: cgacutil reports CF 2.0.6129.0.
So help! Have I hit some listed or unlisted limits to SSCE on the Compact Framework? thanks.
I have a database was showing (Suspect). It looks not able to log into the SQL database. After I reboot the machine it turned to recovering for a while and then changed to suspect again. I did the following steps: 1. set database to emergency mode (Status = -32768) 2. after that I tried to do DBCC checkdb but I received “cannot open ‘database’…”
So I have to restore the database from latest backup but I lost one-hour data. My questions are: 1. how to recovery the corruption database instead of restoring from backup? 2. From following error log, does anyone know what is the reason causing database “suspect”?
The database is running at SQL7 and SP1. The database has 1g data (50% free space). The Log file has 600MB.
Thanks for any info/advice,
Stella Liu
Error Log: 2000-01-31 16:07:04.92 spid11 Using 'sqlimage.dll' version '4.0.5' Dump thread - spid = 11, PSS = 0x4935b0cc, EC = 0x4935b27c Stack Dump being sent to d:MSSQL7logSQL00007.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 01/31/00 16:07:59 spid 11 * * Input Buffer 210 bytes - * I N S E R T I N T O P P F R e p r e s e n t a t i v e s * V A L U E S ( 4 4 1 1 8 , 1 1 7 9 4 3 1 4 , { f n g e t i m a g e * ( 1 ) } ) e x e c s p _ d r o p o r p h a n s c o m m i t t r a * n * ************************************************** ***************************** ------------------------------------------------------------------------------- Short Stack Dump 0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b) 0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0) 0x005a6741 Module(sqlservr+1a6741) (Page::MakeInsertSpace+45) 0x0040df03 Module(sqlservr+df03) (PageRef::InsertRows+132) 0x005b0531 Module(sqlservr+1b0531) (XDES::UndoPageOperation+228) 0x00431ceb Module(sqlservr+31ceb) (XDES::RollbackToLsn+22a) 0x00430560 Module(sqlservr+30560) (XDES::Rollback+14e) 0x00431abf Module(sqlservr+31abf) (XCB::Rollback+2f9) 0x0051ac85 Module(sqlservr+11ac85) (clean_process+162) 0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9) 0x4106135c Module(opends60+135c) (execute_event+659) 0x4106164b Module(opends60+164b) (process_commands+f3) 0x4109285a Module(ums+285a) (ProcessWorkRequests+ed) 0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139) 0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce) 0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be) 2000-01-31 16:08:02.65 spid11 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000) 2000-01-31 16:08:03.34 kernel SQL Server Assertion: File: <page.cpp>, line=2809 Failed Assertion = 'sid >= m_slotCnt || m_slots[-sid].offset ==0'. 2000-01-31 16:08:03.51 spid11 Error: 3624, Severity: 20, State: 1. 2000-01-31 16:10:57.29 spid11 Error: 3314, Severity: 21, State: 4 2000-01-31 16:10:57.29 spid11 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (56:11888:81).. 2000-01-31 16:10:57.37 spid11 Error: 9001, Severity: 21, State: 1 2000-01-31 16:10:57.37 spid11 The log for database 'WebPDM' is not available.. 2000-01-31 16:12:39.73 spid11 Error: 3314, Severity: 21, State: 2 2000-01-31 16:12:39.73 spid11 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (55:12913:1).. 2000-01-31 16:12:44.28 spid6 Closing file d:mssql7datawebpdmdata.mdf. 2000-01-31 16:12:44.46 spid6 Closing file d:mssql7datawebpdmlog.ldf. 2000-01-31 16:12:44.60 spid6 Starting up database 'WebPDM'. 2000-01-31 16:12:44.60 spid6 Opening file d:mssql7datawebpdmdata.mdf. 2000-01-31 16:12:44.92 spid6 Opening file d:mssql7datawebpdmlog.ldf. 2000-01-31 16:12:51.65 spid6 Recovery progress on database 'WebPDM' (7): 14 percent. 2000-01-31 16:12:55.39 spid6 Recovery progress on database 'WebPDM' (7): 28 percent. 2000-01-31 16:12:58.14 spid6 Recovery progress on database 'WebPDM' (7): 42 percent. 2000-01-31 16:13:08.39 spid6 102 transactions rolled forward in database 'WebPDM' (7). 2000-01-31 16:13:08.39 spid6 Recovery progress on database 'WebPDM' (7): 57 percent. 2000-01-31 16:13:08.40 spid6 Recovery progress on database 'WebPDM' (7): 71 percent. 2000-01-31 16:13:08.59 spid6 Using 'sqlimage.dll' version '4.0.5' Dump thread - spid = 6, PSS = 0x41308084, EC = 0x41308234 Stack Dump being sent to d:MSSQL7logSQL00008.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 01/31/00 16:13:18 spid 6 * * ************************************************** ***************************** ------------------------------------------------------------------------------- Short Stack Dump 0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b) 0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0) 0x005a6741 Module(sqlservr+1a6741) (Page::MakeInsertSpace+45) 0x0040df03 Module(sqlservr+df03) (PageRef::InsertRows+132) 0x005b0531 Module(sqlservr+1b0531) (XDES::UndoPageOperation+228) 0x00431ceb Module(sqlservr+31ceb) (XDES::RollbackToLsn+22a) 0x005adfae Module(sqlservr+1adfae) (RecoveryMgr::UndoPass+1c8) 0x004d125a Module(sqlservr+d125a) (RecoveryMgr::RecoverDb+35c) 0x004ce9f7 Module(sqlservr+ce9f7) (DBTABLE::Startup+630) 0x004cdfd4 Module(sqlservr+cdfd4) (DBMgr::StartupDB+2c4) 0x006cc585 Module(sqlservr+2cc585) (TaskDbRestart::ProcessTskPkt+14f) 0x004d79e9 Module(sqlservr+d79e9) (GlobalTskMgrProc+117) 0x4109285a Module(ums+285a) (ProcessWorkRequests+ed) 0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139) 0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce) 0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be) 2000-01-31 16:13:18.95 spid6 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000) 2000-01-31 16:13:19.01 kernel SQL Server Assertion: File: <page.cpp>, line=2809 Failed Assertion = 'sid >= m_slotCnt || m_slots[-sid].offset ==0'. 2000-01-31 16:13:19.04 spid6 Location: page.cpp:2809 Expression: sid >= m_slotCnt || m_slots[-sid].offset ==0 SPID: 6 Process ID: 220 2000-01-31 16:13:19.07 spid6 Error: 3624, Severity: 20, State: 1. 2000-01-31 16:13:19.07 spid6 Error: 3314, Severity: 21, State: 3 2000-01-31 16:13:19.07 spid6 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (56:11888:81).. 2000-01-31 16:14:50.73 spid6 Error: 3414, Severity: 21, State: 1 2000-01-31 16:14:50.73 spid6 Database 'WebPDM' (database ID 7) could not recover. Contact Technical Support..
Hi I'm new to SQL, and have a big problems that I need help with. I just hired on to as the Network Admin for the company, the company has an NT4.0 box with SQL 6.5 running. The previous administrator has never done a SQL backup of the dbase, so no import or restore can be done. Now the SQL application was corrupted, it would not regconize itself or can be register. After the reinstallation of the SQL 6.5, there are still a folder of data under Mssql. I assumed that this is the dbase but have no idea what the the dbase actually call, if browse through using explore you will see the files name VMData.dat 550 mb in size and VMLog.dat 25 mb in size. If anyone know how I can retrieved this dbase it would be great help, or if you know of a firm that I can contact that would work too. Thank you for your help in advance.
Hi, I created a nonclustered index on sysobjects table. After that, when I create a new object and sysobjects is modified, the database does not work properly. I used DBCC CHECKDB to repair it. It works but as soon as a new object is created, again database get corrupted (CHECKDB reports error). I cannot drop this index with DROP INDEX command. I'm wondered if an index on system table cannot be dropped, why it can be created? If it is allowed to create index, why it corrupts the database? I found this link: http://support.microsoft.com/kb/106122 Although it mentions SP_FixIndex can drop such indexes but there are no description about it. Any help would be greatly appreciated. Leila
I know that power failure can corrupt a sdf file (http://support.microsoft.com/kb/296076/en-us)
Apart from that, is there any other reason can corrupt a SqlCe2.0 sdf database?
Another problem I found in my sdf database is corrupted record. There is a record in the database has all field with "null" value. When I tried to compact this database in "Query Analyzer", it has cursor there forever. I can still open the database and view those record. I tried to delete this record by saying "delete from job where ... is null", that database is fixed!!! I can compact that dataase again.
Note: I have transaction when writing data to database and my application has quite a lot database corruption issue which is nightware now.
Does anybody know what causes this and how to avoid this problem?
I am having an issue for transaction log database backup getting failed and throw me a following error. I never seen this corruption error before so Is there any solution for it?This error is from my Log file:-
Failed:(-1073548784) Executing the query "BACKUP LOG [Xe] TO Â DISK = N'D:XeXeXe_backup_201507230922.trn' WITH NOFORMAT, NOINIT, Â NAME = N'Xe_backup_20150723092224', SKIP, REWIND, NOUNLOAD, Â STATS = 10
" failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.This is from SQL JOB error"-Â
Executed as user: SqlAdmin. ...ion 9.00.5324.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   Started:  9:22:20 AM  Progress: 2015-07-23 09:22:24.08   Source: {297F9C99-05AE-47BD-AA70-3E25DDD78CAB}    Executing query "DECLARE @Guid UNIQUEIDENTIFIER    EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2015-07-23 09:22:24.91   Source: Back Up Database (Transaction Log)    [code]....
The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
I have a SQL Server 2005 Compact Edition database that synchronizes with a SQL Server 2005 Standard database via merge replication.
The problem I have is when I cancel a synchronization, the database is left in such a state that I cannot add any new records. I dug around a bit and have found that in the INFORMATION_SCHEMA.COLUMNS view, AUTOINC_MAX has been incorrectly set to the same value as AUTOINC_MIN for the primary keys, which explains the 'out of range' error I get in my program when running off such a database.
In contrast, a non-corrupted database has AUTOINC_MAX set to AUTOINC_MIN + 999, presumably because of my subscriber identity range of 1000.
If this is the only thing that has gone wrong, all I need to do is programmatically set the AUTOINC_MAX values to AUTOINC_MIN + 999, however I can't find out where to do this (and INFORMATION_SCHEMA.COLUMNS is a view so it can't be updated obviously.)
I have also heard that 'compacting' the database may help, but I don't know how to do this either!
I have SQL Server Enterprise set up with Merge replication, it is being used to Sync with Windows Mobile Devices as the subscribers.
All the Windows Mobile subscribers have been synchronising fine up until recently, when 1 of the Subscribing Windows Mobile devices received this error. The day after, another device Synchronised fine the first time (note that the Synchronisation was a long one and the SDF grew in size dramatically while the synchronisation occured, but the Sync worked fine and the SDF dropped back to its initial size) The 2nd sync 5 mins later failed however so this Mobile device has the same error also.
Any suggestions as to the below are much appreciated
a) Why this error occurs? b)How do you resolve this error? c)What is the Repair utility mentioned in the error, and is it of use to me?
Thanks in advance, I am very greatful for any advice.
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>" SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
Hi All Can anyone tell me what this simple SQL query would be: Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on: I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD. When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables: D: D1, D2, D3B: B1, B2, B3 Table BD:B1-D1B2-D1B2-D2 When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this? Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3) SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL) Any thoughts?
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name Gates || Bill Boyce || Mike Gates || Bill Gates || Phil
Results I want:
Last Name || First Name || Count Gates || Bill || 2 Boyce || Mike || 1 Gates || Phil || 1
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
from sales_contact sc , invoice i , invoice_line_item il , sales_region_special_section srss , sales_region sr , issue_date idd
where sc.sales_contact_id = i.sales_contact and i.invoice_id = il.invoice and srss.sales_region = sr.sales_region_id and il.issue_date = idd.issue_date_id and srss.invoice_line_item = il.invoice_line_item_id
order by sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0 if count(*) from question where serialno > $serial != 0 else select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like SELECT Count(*) AS Expr1 FROM Table1 WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate ,OutEndDate,OutStartTime,OutEndTime,OutID FROM vwOutOfOffice LEFT OUTER JOIN vwStaffList ON OutUser=StaffID WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example Current date=29/02/2008 Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear 27/02/2008--28/02/2008--09:00------17:00-----No (because of date) 28/02/2008--03/03/2008--08:00------17:00-----Yes 28/02/2008--29/02/2008--17:00------17:00-----Yes 29/02/2008--29/02/2008--12:00------14:00-----Yes 29/02/2008--29/02/2008--14:30------17:00-----No (because of time) 02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
ID Number NameJoin DateSex 101 Jason01/02/1996M 117 Robert06/25/1999M 113 Doug08/12/1999M 161 Marian07/30/1998F
Please let me know the SQL queries for the following questions for the above table:
1)Write a Select statement that selects the persons whose Type is "M" and who have joined in the last four years and place the result in an array. Today's date is 12/31/1999.
2) Write a Select statement that selects the persons who do not have unique ID Number and place the results in an array.
3) Write code to sort the array by Name + Join Date. Assume that the array has already been created and is sorted in the same manner as the table above. The Join Date is of type smalldatetime. The Name field should be sorted case insensitively.
How do I retrieve all rows from a middle_name column that have a character length of <= 2. This table is populated with middle names and middle initials. I want to isolate the middle initials only.
