I have a question:
On my WM5 devide I run 2 query's right after eachother:
DELETE FROM test,
ALTER TABLE test ADD COLUMN abc bit NOT NULL",
I get an error on the second statement. When I change the statement in "NULL" instead of "NOT NULL" it works.
test is a very large table. It seems as if query 2 is executed while the engine is still processing query 1. And then you propably get the NOT NULL statement violation. Is this possible???
I know that writing the registry has some latency because WM5 uses flash instead of ram. Does SQL Compact have the same latency?
Or is this another problem?
I've got a slow query that when run in management studio again the publisher database it completes in 2 seconds but when run on a compact 3.5 sdf it takes 5-10 minutes. After adding some indexes it gets down to 5 minutes at best. The complex explicit joins requiring both null or equal are required for the propper results.
Any suggestions would be appreciated.
Thanks,
Patrick
SELECT MasterCategories.description AS category, Masters.model, Masters.itemType, Items.barCode, Items.itemNumber, Items.itemId, Inventories.itemStatusId, Inventories.lastXferId, Inventories.returnDate, assignment.description AS assignedTo, assignment.twEntityId AS assignedToId, owners.twEntityId AS ownerId, owners.description AS owner, Inventories.kitId, Masters.masterId, MasterCategories.masterCategoryId, Descriptions.description, NULL AS kitDescription, Manufacturers.name AS manufacturer, Masters.manufacturerId, Items.organizationId, Masters.descriptionId, Masters.masterNumber, assignment.stockpointId, Items.meterLevel, Items.meterUomId, Inventories.personalIssue, Masters.upc, Items.enclosure, StatusCodes.description AS itemStatus, StatusCodes.statusCodeNumber, Inventories.recordId, TWEntities.twEntityId AS managerId, TWEntities.description AS manager, Items.deleted, assignment.stockpoint AS assignmentIsStockpoint, COALESCE (SUM(InventoryTransaction.quantity), 0) AS quantity FROM Masters INNER JOIN Descriptions ON Descriptions.descriptionId = Masters.descriptionId INNER JOIN Items ON Masters.masterId = Items.productId INNER JOIN Inventories ON Items.itemId = Inventories.itemId INNER JOIN TWEntities AS assignment ON assignment.twEntityId = Inventories.entityId INNER JOIN MasterCategories ON Masters.categoryId = MasterCategories.masterCategoryId LEFT OUTER JOIN InventoryKeys ON (Inventories.entityId = InventoryKeys.entityId OR Inventories.entityId IS NULL AND InventoryKeys.entityId IS NULL) AND (Inventories.itemId = InventoryKeys.itemId OR Inventories.itemId IS NULL AND InventoryKeys.itemId IS NULL) AND (Inventories.personalIssue = InventoryKeys.personalIssue OR Inventories.personalIssue IS NULL AND InventoryKeys.personalIssue IS NULL) AND (Inventories.itemStatusId = InventoryKeys.statusCodeId OR Inventories.itemStatusId IS NULL AND InventoryKeys.statusCodeId IS NULL) AND (Inventories.kitId = InventoryKeys.kitId OR Inventories.kitId IS NULL AND InventoryKeys.kitId IS NULL) AND (Inventories.ownerId = InventoryKeys.ownerId OR Inventories.ownerId IS NULL AND InventoryKeys.ownerId IS NULL) AND (Inventories.returnToId = InventoryKeys.managerId OR Inventories.returnToId IS NULL AND InventoryKeys.managerId IS NULL) LEFT OUTER JOIN InventoryTransaction ON InventoryKeys.inventoryKeyId = InventoryTransaction.inventoryKeyId LEFT OUTER JOIN TWEntities ON Inventories.returnToId = TWEntities.twEntityId LEFT OUTER JOIN TWEntities AS owners ON Inventories.ownerId = owners.twEntityId LEFT OUTER JOIN StatusCodes ON Inventories.itemStatusId = StatusCodes.statusCodeId LEFT OUTER JOIN Manufacturers ON Masters.manufacturerId = Manufacturers.manufacturerId WHERE (Items.deleted = 0 and Masters.itemType <> 'M') AND ((assignment.stockpointId = '7cd94c06-8375-4c47-b54a-9e7018434c63') OR (Inventories.ownerId IN ('7cd94c06-8375-4c47-b54a-9e7018434c63') OR (managerId IN ('7cd94c06-8375-4c47-b54a-9e7018434c63')))) AND (Inventories.kitId is null) GROUP BY MasterCategories.description, Masters.model, Masters.itemType, Items.barCode, Items.itemNumber, Items.itemId, Inventories.itemStatusId, Inventories.lastXferId, Inventories.returnDate, assignment.description, assignment.twEntityId, owners.twEntityId, owners.description, Inventories.kitId, Masters.masterId, MasterCategories.masterCategoryId, Descriptions.description, Manufacturers.name, Masters.manufacturerId, Items.organizationId, Masters.descriptionId, Masters.masterNumber, assignment.stockpointId, Items.meterLevel, Items.meterUomId, Inventories.personalIssue, Masters.upc, Items.enclosure, StatusCodes.description, StatusCodes.statusCodeNumber, Inventories.recordId, TWEntities.twEntityId, TWEntities.description, Items.deleted, assignment.stockpoint ORDER BY category, assignedTo
I would like some clarification regarding the specifications of SQL Server Compact Edition 2005 :
1)Is it required that .NET be installed in teh clinet for redistribution? 2)Will the database work even with the client not having any server (with IIS) to interact with ? 3)Does the database runs on 64 bit systems with the same binaries provided for 32 bit?
I want to write query to implement paging in sql server ce 3.0. But it seems it does not support TOP or LIMIT keyword. Can someone suggest alternative way to write a custom paging query in sql server ce 3.0.
I have a transactional replication that was running fine for about few months, but eversince there was a massive update of records at the publisher site and when I use the replication monitor, i notice Status: Performace Critical, Subscription:[localservername]:[Orders_Repl], Performance:Critical, Latency:03:34:47.
I was thinking of redoing the whole replication but before i am about to proceed. Can you guys explained what happen and what does it meant when Latency:03:34:47. I don't remember seeing a Latency:03:34:47.
I am hoping to solve this critical performance issue hope you guys can help. Thanks
I'm building a C# database application that access a remote sql 2005 database. For the moment I am using sql express edition. My application will be running in several REMOTE camps which only have an internet connection via sattelite. The sattelite connection has a very high latency. I am wondering what workarounds or solutions are available for this situation. All applications need to access the same database and preferebly be notified when changes take place on the database.
--> Cursor for fetching all publications in all databases DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM sys.databases WHERE is_published > 0
OPEN curPublishedDatabases FETCH curPublishedDatabases INTO @PublishedDBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications' INSERT INTO @Publications (PublicationDatabase, PublicationName) EXEC sp_executesql @SQL
FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName END
CLOSE curPublishedDatabases DEALLOCATE curPublishedDatabases
--> Cursor for posting tracer tokens DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications
OPEN curPublications FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName WHILE @@FETCH_STATUS = 0 BEGIN SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT'; SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT' EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
UPDATE @Publications SET TracerTokenID = @TracerTokenID, TracerTokenPostTime = GETDATE() WHERE PublicationPK = @PublicationPK
FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName END CLOSE curPublications DEALLOCATE curPublications
--> Wait two minutes for all tokens to be commited at all subscribers WAITFOR DELAY '000:02:00.000'
--> Then check the results for each posted token DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications
OPEN curTokens FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID WHILE @@FETCH_STATUS = 0 BEGIN --> Insert token history for each token SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' INSERT INTO @Latency (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency) EXEC sp_executesql @SQL
--> Make sure that the PublicationPK is added to the token history UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
--> Clean up the tracer token SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' EXEC sp_executesql @SQL
FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID END CLOSE curTokens DEALLOCATE curTokens
SELECT b.PublicationDatabase, b.PublicationName, a.Subscriber, a.SubscriberDB, a.OverallLatency, b.TracerTokenPostTime FROM @Latency a INNER JOIN @Publications b ON a.PublicationPK = b.PublicationPK
We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription. I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction.
I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber.
I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication under any circumstances. Someone on another forum told me it was not, so I thought I would ask a separate community to confirm. Regarding the hardware, the latency on ping is less than 1ms between the servers and the servers themselves are decently powered quad processor boxes w/ 4GB of ram each. It seems to have the same latency of 3-5 seconds regardless of whether I publish a single table or all of the tables in the database. I currently have it configured with the publisher on one server and the distributor/subscriber on the second with the log reader and distributor agents set to run continuously. If it is possible, please recommend configuration of the agents or the topology of the PDS to achieve this. Thanks in advance for any insight you provide.
Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log
Date10/1/2014 8:28:58 AM LogSQL Server (Current - 10/1/2014 12:00:00 AM)
Sourcespid10s
Message SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000
Hi Everybody, Can anybody tell me how to get the number of commands delivered per minute in case of Merge Replication with Publisher and subscribers. This way, we can be sure that even if there is a latency (due to high volume transaction processing), replication is in good shape and things will catch up soon. Also if there are any other similar measures which can be monitored to make sure that replication is going on fine, it would be great
Please let me know If anyone has got information on same.
Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!
I am looking into various options to improve latency of our application (we figured the latency is mainly because data persistence - writes and reads from DB). I am looking into In-Memory databases also. But, before making that decision (of using in memory databases), I would like to see if there is a way to configure SQL Server 2005 to get as close performance as in-memory databases?
My question: 1. Is there a way that I can configure SQL Server 2005 to use a CACHE that gets loaded as needed basis, so that future database reads/writes will happen to the cache as opposed to disk (db writes)? 2. Is SQL Server 2005 recoverable in such configurations? 3. Are there any ideas/resources where I can get more details? (Such as sample configurations with bench mark numbers, rpevious experiences..etc)
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete. Even with this option, if we query a read-only node before the logs have been processed, we can read old data. I would like to know a strategy to ensure that a read-only query will definitely return up to date information. I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.
I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
I have 2 servers; 1 in the head office and the 2nd at a remote location connected over a satellite. The satellite connection has typical latency and bandwidth. 2 mb down and 128kb up. Latency is average of 700ms.
Both servers have plenty of processing power and RAM and are very underutilized.
I have 1 push publication at the publisher at the remote location. The publication has 1 article. The table that is contained in the article only grows; ie inserts are the only statements applied, no updates or deletes. The table grows by 5 inserts every second(approximate). the table has 5 columns. none of the columns are blobs etc. An ID field, 1 datetime, 2 integers and 1 float column.
I have the agents set for continuous with a 2 second polling interval.
I am aware of the following command parameters for the agents but not sure which way to go to maximize the throughput of the data.
The subscriber table also has an insert after trigger to update another small table with the current value. Under normal network conditions, the trigger works great. Over the sat connection with high latency, the smaller target table seems to get a lock placed on it. I can only guess that the lock is from the inserts and perhaps a large number of inserts per transaction?
Under normal network conditions, the latency between publisher - distribution - subsciber is about 4 seconds.
What would be good command parameter settings for the agents to compensate for the latency?
How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.
[URL] ...
However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.
object_name counter_name instance_name cntr_value cntr_type SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712
I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time
End Sub this code worked well. we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).
we've got a SQL Server 2005 which replicates with an SQL Server Compact 3.5. Every 10 to 20 synchronisations we're getting the error mentioned above. A Soft reset of the device helps to make the synchronization working again.
Why is this error happening and how can we resolve this?
BTW, we've also running system that replicates with an SQL Server Compact 3.0 without having this problems.
everything is ok for first run. but i leave the program relogin than that error occured ppc2003 second edition devices. Windows mobile 5.0 device works fine. can anybody help me?
VS2005 ver 8.0.50727 SSCE31VSTools-ENU.exe loaded SSCE31SDK-ENU.msi loaded
machine 1 SQL 2005 loaded
machine 2 http://192.168.20.22/ssce/sqlcesa30.dll "Microsoft SQL Server Compact Edition Server Agent" looks fine
pocket pc side C:Program FilesMicrosoft SQL Server Compact Editionv3.1SDKinwce400armv4
End Try ------------------------------------------------------------------------------ i added the following code to very beginning of my code too. that code lock my device
Declare Function LoadLibrary Lib "coredll" Alias "LoadLibrary" (ByVal lpLibFileName As String) As IntPtr
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
No one answered in another group, please help.Using access 2000 /msdeI know how to compact the access project file (.adp), there's a menu choicefor that.How do I compact the tables and stored procedures on the server? ( The .MDFfile)I only have the DOS promp OSQL utility to work with.thanks
I'm trying to add an SQL Compact Edition database to my application running on a CE 6.0 platform that I am also developing. Unlike PB 5.0, I don't see sql CE listed as an option in the catalog and when I try to run my application I get a pInvoke sqlceme30.dll error. I have found a number of references to this same issue elsewhere but no real good solutions. Most seem to point to the cab files not being deployed. I am thinking that I should have the necessary support files deployed in the CE image and I have downloaded and installed every sql CE version/update that I can find but none of them seem to add support to PB 6.0.
I am trying to add the cab files manually but I would appreciate any suggestions if there is a better solution............ Thanks
Is SQL Server Compact Editon 3.1 supported on Windows CE 6.0?
When I install the cab file sqlce30.wce5.armv4i.CAB in CE 6.0 it says that it might not be compatible. If I continue anyway it seems to work fine, but only the first time the application starts and accesses the database. When restaring the application it fails to connect to the database until I have reinstalled the cab file.
I have an application where I need a desktop Application to manage the data and I have PDA application which talks to RFID and gathers the data. I am a bit confused if i can have only one database which can work both on PDA and Desktop. Can you please help me decide if i should use compact or express for this purpose. Also I might have to use both compact and Express but is there a way to have common database for both where they can be replication and easily synchronised.
Hi, I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one. I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb. I was shocked to see the size difference when I copied the data. As far as I can tell the copied database still contains all the records of the origional.
I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one - is there the equivalent for SQL?
Hi,I just deleted about 1 million records from a SQL Server 2000 database. Iaccess it using Access 2000.I have Compact and Repair available in the adp under Tools. Should I do thatfrom Access? Will it not mess anything up?Thanks!J~
