Adding Table To Dataset For SQL Server Mobile Causes VS 2005 To Lock Up.
Apr 20, 2006
I am running VS 2005 Professional Edition
Windows XP profession with Service Pack 2
SQL Server 2005 Developer Edition.
WHAT I HAVE DONE:
I have a database running in an instance of SQL Server.
I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.
PROBLEM:
I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:
Microsoft Visual Studio is Busy.
Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.
Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.
Hopefully someone out there can help.
Additional Services running:
IIS (Whatever version comes with Windows XP Pro. I think 5.1)
SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch
UPDATE: I am editing this post with an update.
I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you. Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column). Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs. Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC). At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS??? Does an error raise? Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
I have developed an application in c#.net 2.0 for Pocket PC. And even i developed a webservice to communicate between sql server 2005 and sql mobile 2005. Webservice returns the data in a dataset format from sql server 2005, but while inserting the data row by row from dataset, it is taking a huge amount of time. I would like to know, is there any way where i can copy the bulk data from dataset into the sql mobile 2005 using c#.And how this can be achieved.
I am mapping an entity from SQL 2005 to another entity in another system on SQL 2000. Since the destination system has its own ID generator, I want to keep the generated ID for each row of my table in a column of my table in SQL 2005. The new column is in the dataset now , but I don't know how to update my table to have that column values (The OleDbDestination just insert new items.)
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.
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 ?
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
When I try and connecto to SQL CE I always get an invalid operation exception. I"m afraid that I did not follow the proper install for Orcas Beta 2. I can't remember if I was supposed to uninstall SQL Mobile 2005 first or not.
All I know is when I try and use my SQL CE I can't connecto to a DB / sdf file ?
Any help would be appreciated I"m just starting to use SQL CE.
I want to add a product photo in jpeg format to a products table. I'm storing the image of the product in separate jpeg files located in a folder on the same server that houses SQL Server. I don't want to store the photo directly in the table. I just want to reference the file location of the jpeg photo in a record.
Can someone provide me with code I need to add to the photo column for each record in the products table in order to reference the location of the jpeg photo associated with that record?
Subsequently, I want the photo to pop up in SQL Server Reporting Services product report.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
I have developed an application where i am inserting all the records from the dataset into sql mobile 2005. Dataset contains a primary key which is an uniqueidentifier datatype. While inserting the data it is inserting properly into the database but it is changing the value of primary key which is in the dataset.
I am using the below syntax, please suggest me so that to avoid creating a different uniqueidentier key into the database.
conAdap = new SqlCeDataAdapter(strQuery, conSqlceConnection);
SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(conAdap);
suggested that this problem could be circumvented by getting the replication object early and then continuously using the same object for subsequent replications.
I did this and it works a lot better now but ever so often I get another error which is
Native Error 28559 SQL Mobile encountered problems when opening the database
My only option at this point is to stop and start the application which cures the problem.
I just wrote an article on how to Lock Down SQL Server 2005 that might interest people here. It has step-by-step instructions and SQL scripts for server and database hardening. A lot of concepts are explained along the way.
As an experiment I also have a world-accessible SQL2005 shell to let people experiment with a hardened database and see if they can do privilege escalation. A lot of the standard tricks have been tried so far and the server has held up without any problems. I wonder if that will last
Anyhow, the article is here: http://duartes.org/gustavo/articles/Lock-Down-SQL-Server-2005.aspx. I appreciate all feedback.
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
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 ?
I have a central database server that is runnning on SQL 2005 standard edition and Windows server 2003 standard as OS.
I realise that I can use SQL statements to encrypt and decrypt the data inside the standard SQL.
However, how do I read and write the data via an web application coded in C#.net and is also running on the same machine?
Another issue is, I need to replicate some of the data in this SQL standard over to a SQL mobile running on a mobile device running on Windows CE 5.0.
The mobile device also needs to read and write data to the encrypted data via a C#.net application.
Question is, with all these requirements to be met, can I use AES? I know that AES is not available on Windows XP and Windows Server 2000 and I cant find AES in the .net compact framework.
how do i go about ensuring security? how do I ensure that the symmetric key is the same both on the SQL standard and SQL mobile?
I have some SQL CE database .sdf file on my handheld, and I was trying to connect to that file via SQL Server 2005 Management Studio, and it does not work. I am wondering if there is any good tool that I can use on desktop to connect to sqlce .sdf database file on my handheld?
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();
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
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
We have an old VB6 app using a SLQ Server 2005 database.
This database is also used by a web service.
When accessed just by the VB6 or WebService everything runs smoothly.
The problem occur`s when both access the database in parallel.
Our web service seem`s to encounter some very long delay for queries running just fine when is alone.
Now my knowledge of VB6 is very limited (and it will stay that way :) i`ve checked the code for access part and some parts look obscur.
so here is the part where the queries gets executed
Set m_adoRecordSet = New ADODB.Recordset With m_adoRecordSet .CursorLocation = iCursorLocation .CursorType = iCursorType .LockType = iLockType Set .ActiveConnection = objConnection .Open m_strSQL End With
i looked everywhere in the code and iCursorLocation is mainly always SQLClient. i think only one time is SQLServer.
iCursorType is always SQLForwardOnly and iLockType is always SQLReadOnly.
now the curious part (for me) is that the connection (objConnection) is opened at the beginning of the app and never closed? Is that normal in VB6?
Would the cursor being sometimes on the server be the problem?
If someone could help it would be very appreciated.
I have written an app that will allow you to send a query to Teradata, return the results into a Reader and then Bulk Copy that data into SQL Server 2005.
If the query results in a large dataset (ie 20,000,000 rows) is processed then while that data is being bulk copied into SQL Server, using the SQLBulkCopy class, then it prevents users on other computers from logging into SQL Server Management Studio.
Those that are already logged in are shut down also. Everything appears fine to the users but queries do not finish running.
Everything immediatly starts working as normal when either my program finishes or I shut down my program.
Is there any type of property to the SQLBulkCopy class or any other function that will prevent Management Studio from locking up?
I'm attempting to use RDA to synchronize a pocket pc emulator with SQL Server 2005 database by following tutorial http://msdn2.microsoft.com/en-us/library/aa454892.aspx, which uses AdvWorksMobile database that comes with SQL Server 2005.
When get to testing of "To test application features " ( step 4 of the merge replication setup lab ), I am receiving SQLCe Exception errors.
ORIGINALLY, I was able to deploy an application and view the local database on the emulator, but once I clicked "InitSinc" button, I received an error.
"Failure to connect to SQL Server with provided connection information. SQL Server does not" exist, access is denied because the SQL user is not a valid user on the SQL Server, or the password is incorrect. Found this error listed in Server Agent Errors log http://technet.microsoft.com/en-us/library/ms172357.aspx, but it wasn't very helpful since it doesn't provide any suggestions on fixing the problems.
I am brand new to SQL CE Server , mobile programming and .NET framework in general so please bare with me
All the connections / security settings, etc that were set up were taken from a tutorial so I am not sure what SQLCeServer is complaining about. Reading up on the error on the web didn't help, it all pointed to making sure that the snapshot folder had correct permissions set up. I verified that MACHINENAMEUISR_MACHINENAME guest user had Read rights to the database so I don't think that piece is the problem. Otherwise ,Merge publication has been setup in Microsoft SQL Server Management Studio by following the tutorial.
I am trying to run this sync on my home pc so there should be no issues with any user permissions
I have IIS installed
I did NOT have SQL Server Agent running in management studio at this time
I am trying to run this tutorial on an XP machine, which is my regular home PC so I dont think there are any special networking settings to consider
I am able to hit http://localhost/AdvWorksMobile/sqlcesa30.dll from my pc OK, but when I try to hit it from a cradled emulator ( after replacing "localhost" with "MACHINE_NAME" ), Internet Explorer on the emulator gives me a "Cannot Connect With Current Settings" error message. This part wasn't part of tutorial but decided so not sure if I am supposed to be able to hit it from my emulator..so I am not sure if the fact that I can't hit it is related to the problem. AFTER STARTING SQL SERVER AGENTin Microsoft SQL Server Management Studio, I killed the application, and after relaunching it, it hits "The Specified table does not exist [Vendor]" error. This time it doesn't even launch first - that is I dont even get to pressing "InitSinc" button. Debugger is showing that this error is hit on the following line in AdvWorksMobile.AddWorksDataSetTableAdapters
int returnValue = this.Adapter.Fill(dataTable);
I am not sure why the errors changed on me after starting SQL Server Agent. What can I do to fix this connection problem?
I need to lock a table in SQL Server. The reason is that I need to insert a value into an identity column (rather than just taking the next sequence number). I have a program that:
1. Sets identity insert on 2. Inserts into the table 3. Sets identity insert off
The problem is that I believe I should lock the table before I turn identity insert on (otherwise, other transactions will fail). I need to ensure that no other operations will take place on the table while I am doing all three of these steps.
I believe I can set the isolation level, but I cannot find any way to establish the lock without executing the update statement itself - which is a nanosecond too late to prevent another user from getting an error.
When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute. This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).
The problem: -Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking) -Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.
For example this query will work : UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;
But this qeury will give me a "Lock timeout" : UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';
But it is the same record!! The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.
The data: The table [gwseqnumber] has the following CREATE statement: CREATE TABLE GWSEQNUMBER ( NEXTSEQNR INTEGER, NAME CHAR (20), ADMINISTRATIONCODE INTEGER, FINDHIGHESTNUMBER CHAR (1), CLOSEDYN CHAR (1), ROW_ID INT IDENTITY(1,1) NOT NULL );
CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR); CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER); CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);
Both KEY_2 and KEY_3 are unique, KEY_1 is not.
If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index. Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.
Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)
Solutions tried: - Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility). - I have already tried to disable Page-Locking on all the indexes of this table.
So why does the index lock my updates in SQL Server 2005? And how do i fix it so my database does not run into these locks?
When doing the service pack 1 install on a client side the machine is locking up on a specific file. The tech who tried it out doesn't remember the exact file name but he thinks it ended with an .msb or .msp
I checked in the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFiles directory and none of the files have been updated.
I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.
CREATE TABLE [dbo].[tblOnboardingSequence]( [OnboardingSequenceID] [uniqueidentifier] NOT NULL, [Name] [nvarchar](255) NOT NULL, [NextNumber] [bigint] NOT NULL, CONSTRAINT [PK_OnboardingSequence] PRIMARY KEY CLUSTERED( [OnboardingSequenceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
My Stored Procedure has below logic
DECLARE @NextNumber BIGINT ----------- Acquire row lock here SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO' UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO' ----------- Release row lock here
I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query. What si the best way to deal with this?
I have a parameterized report based on a MDX query (so no drag and drop of filters and fields). Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter. when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.
Has anyone experienced this problem before, or am i forgetting something here?
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??
