InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:54:37 GMT20http://www.sqlservercentral.com/Forums/Topic1275089-323-1.aspxHi,I have a SQL Compact 4.0 (.sdf) data file.I want to periodically import or preferably sync that data within SQL standard edition 2005, for regular query and reporting.What is the best way forward?Thanks.Thu, 29 Mar 2012 08:11:20 GMTiBarhttp://www.sqlservercentral.com/Forums/Topic1433199-323-1.aspxHi,I have installed SQL Server 2005 backward compatibility installed on my local machine. I am seeing it in the program & features section as installed. The version is 8.05.1054. It does not showup under all programs and i am unable to find server name on my system. I have admin rights to my system and still when i try to access the server name as localhost it says i need atleast read rights.Could some one help me.Regards,Renato. Wed, 20 Mar 2013 07:32:17 GMTrenato_mech_334http://www.sqlservercentral.com/Forums/Topic1413016-323-1.aspxWe have developed a system to grant access to procedures through certificates. This was done following Erland Sommarkog excellent article on this subject ( http://www.sommarskog.se/grantperm.html ). The procedure having a permission denied on the IDENT_CURRENT resides in a database that is exclusively composed of procedures. It was granted access to the database in question, including the table being sent as a parameter of the function but only in this case it returns a permission error. I haven't found any way, besides granting select to the user I am trying to prevent from accessing directly this table, to go around this issue. Has anyone encountered this issue and found a solution that they can share?Thanks in advance,EduardoTue, 29 Jan 2013 06:49:43 GMTEduardo Oliverahttp://www.sqlservercentral.com/Forums/Topic555236-323-1.aspxI have a desktop application that I have created in C# that is using SQLCE as the database. I would like to embed it into the application so that the user will only see the .exe application file.I know that all I have to do in C# is to set a property on the sdf file in order to make it embedded, but I'm not sure how to reference it from the application once it has been embedded.My question is, if I embed SQLCE in the application, how do I reference it in a ConnectionString?Thanks in advance. :)Tue, 19 Aug 2008 11:48:17 GMTRon Haganhttp://www.sqlservercentral.com/Forums/Topic1346169-323-1.aspxEDIT: Sorry, posted in wrong section. Updating.Thu, 16 Aug 2012 11:58:48 GMTSam73http://www.sqlservercentral.com/Forums/Topic1261801-323-1.aspxThe following error is reported when I attempt to edit a record and then click on save to save the changes:There was an error parsing the query. [Token line number = 1, Token line ofset = 38, Token in error = /]This is the button Save code: Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click Select Case state Case "n" If txtFName.Text = "" Then MsgBox("Name Cannot be null", , "My Telephone Book") Else Try Using conn = New SqlCeConnection(connString) Using cmd = New SqlCeCommand cmd.Connection = conn cmd.CommandText = "INSERT INTO Persons (" & _ "Fullname, " & "DoB, " & "DoM, " & "MPhone, " & "EMail, " & "Notes, " & "AddressID) " & "VALUES " & "(?,?,?,?,?,?,?)" With cmd.Parameters.Add("FullName", Me.txtFName.Text) cmd.Parameters.Add("DoB", Me.dtpDoB.Value) cmd.Parameters.Add("Dom", Me.dtpDoM.Value) cmd.Parameters.Add("MPhone", Me.txtMPhone.Text) cmd.Parameters.Add("EMail", Me.txtEMail.Text) cmd.Parameters.Add("Notes", Me.txtNotes.Text) cmd.Parameters.Add("AddressID", Me.txtAddressID.Text) End With conn.Open() cmd.ExecuteNonQuery() End Using End Using MsgBox("Record Saved", , "My Telephone Book") Catch sqlex As SqlCeException Dim sqlError As SqlCeError For Each sqlError In sqlex.Errors MessageBox.Show(sqlError.Message) Next Catch ex As Exception MsgBox("Error Saving Record", , "My Telephone Book") Finally conn.Close() End Try End If Case "u" If txtFName.Text = "" Then MsgBox("Full Name cannot be empty", "My Telephone Book") Else Try conn.Open() Dim cmd As SqlCeCommand = conn.CreateCommand 'This is I believe is the Error line below cmd.CommandText = "UPDATE Persons SET FullName" & txtFName.Text & "DoB" & dtpDoB.Value & "DoM" & dtpDoM.Value & "MPhone" & txtMPhone.Text & "EMail" & txtEMail.Text & "Notes" & txtNotes.Text & "FROM Persons WHERE PersonID = " & lstPersonID.Text cmd.ExecuteNonQuery() MsgBox("Record Updated", , "My Telephone Book") conn.Close() Call FillList() Catch sqlex As SqlCeException Dim sqlError As SqlCeError For Each sqlError In sqlex.Errors MessageBox.Show(sqlError.Message) Next Catch ex As Exception 'MsgBox("Error Updating Record", , "My Telephone Book") MessageBox.Show(ex.Message) Finally conn.Close() End Try End If End Select sql = "SELECT * FROM Persons ORDER BY FullName" Call FillList() txtFind.Clear() txtFName.Focus() End SubAny help will be greatly appreciated as i've spent all day attempting to resolve this error.Mon, 05 Mar 2012 13:21:32 GMTkenkobhttp://www.sqlservercentral.com/Forums/Topic594847-323-1.aspxHello,when attempting to add a database to my application in Visual Web Developer 2005 Express Edition, I get a successful connection to the Microsoft SQL Server and the browser shows my databases, I have created. But when I click 'OK' I get the message:"Can not add the database, because the file or Assembly "Microsoft.SqlServer.Management.Sdk.Sfc" can not be found."What can I do?ThomasFri, 31 Oct 2008 04:05:27 GMTthomas.pluempehttp://www.sqlservercentral.com/Forums/Topic1034200-323-1.aspxHow can I synchronize data of different user with SQL Server 2005/2008?Scope:I am planning to design a free application for Windows Mobile and this app will store data in SDF file (SQL Server Compact Edition) locally in Mobile. I also design Web Interface for this application, where users can add/delete/edit/view their information entered using Mobile App. To achieve this I have to replicate and synchronize the SDF File data with SQL Server Enterprise Edition.Issue:The application is used by different users and I want only data related to that particular user can synchronize. For example if there is user Sam and Dean then when Sam do sync, only Sam get data related to him and when Dean does, he get data related to him. Solutions:1. Use SQL Replication.I do not have much experience in using SQL Replication, but few year ago I did implemented merge replication. But it replicated the complete data.Is there any way to replicate data based on condition?2. Use custom logic to handle.Here I am thinking to have one more table and log the info Add/Delete/Edit and later based on this will sync. But it will take a lot of effort to sync properly.Please advice what can be done for syncing data for different users, while one user will not able to get others data.Please let me know if you need any further clarification.Mon, 13 Dec 2010 23:22:46 GMTsuntechttp://www.sqlservercentral.com/Forums/Topic1207383-323-1.aspxI have been using .net 2 with sql compact edition 3. When I changed to windows 7 with .net 3.5mdf created with column values (fields) inserted with additional spalces. Eg. Field Employee code will have number 101 but with additional space so that when I search for emp code where empcode =101 it will not return any values. Can someone helpThu, 17 Nov 2011 04:43:40 GMTRajesh Menon-461740http://www.sqlservercentral.com/Forums/Topic1224363-323-1.aspxI have a SQL CE database set up that I want to import an excel shee t in to. I have SQL Server 2000 and SQL CE 3.5.Can someone please advise how this is done. From searching I can't seem to find the easiest way.ThanksTue, 20 Dec 2011 05:51:50 GMTJimmy1981http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxI m mentioning the below steps what i did1. MSSQL 2008 installed2. Took Backup of databases from MSSQL 2005 and restored it in MSSQL 2008.3. then run the script of revlogin that i mentioned before.4. logins created for users.Problems i faced :-1. the users to which i gave all access like sa they can access any database no problem with them.2. but the users to which i gave limited permission on limited databases and tables..that account user are not able to access any database.3. then i tried ti give the permission to that user which was exist in MSSQL 2005... but that is also giving error like that ''the user is already exist''i think the process i followed was wrong... pls suggest me the solution.Fri, 16 Dec 2011 03:10:44 GMTpallavi.undehttp://www.sqlservercentral.com/Forums/Topic1222337-323-1.aspxWhich is upgraded product??RTM or SP2???Thu, 15 Dec 2011 05:51:31 GMTpallavi.undehttp://www.sqlservercentral.com/Forums/Topic1164741-323-1.aspxI am using sql server compact edition (.sdf) on my dell's PDA. It was working good but after refreshing database file (.sdf), suddenly it start giving error as below:Major Error 0x80040E37, Minor Error 0> select * from sys1[ sys1 ]I am not able to get the issue. Would you help me, please. Thanks in advance.Regards,Yogesh GuptaWed, 24 Aug 2011 08:44:37 GMTyguptabghhttp://www.sqlservercentral.com/Forums/Topic621428-323-1.aspxHi,I have a .sdf SQLServer CE database file.I want to view and edit data without SQL and create tables, etc... in my Windows XP desktop to develop an applicationIs there any DBA tool to do that?ThanksWed, 17 Dec 2008 10:16:54 GMTxuaxehttp://www.sqlservercentral.com/Forums/Topic1148727-323-1.aspxThe following select statment crashes with an 'Invalid Column Name' Error when executed by a C# Datareader. Claiming that [b]PlusAreaOperationIdPlusAreaOperation[/b] doesnt exist. If I run the exact same query on the VS 2010 Query analyser I get the expected results.Any thoughts?[code="sql]SELECT [b]PlusAreaOperation.IdPlusAreaOperation AS PlusAreaOperationIdPlusAreaOperation[/b], PlusAreaOperation.IdOperation AS PlusAreaOperationIdOperation, PlusAreaOperation.IdFrequency AS PlusAreaOperationIdFrequency, PlusAreaOperation.IdProduct AS PlusAreaOperationIdProduct, PlusAreaOperation.IdPlusWorkSiteArea AS PlusAreaOperationIdPlusWorkSite, PlusAreaOperation.IdEquipment AS PlusAreaOperationIdEquipment, PlusAreaOperation.IdMachineType AS PlusAreaOperationIdMachineType, PlusAreaOperation.IdUsageIndex AS PlusAreaOperationIdUsageIndex, PlusAreaOperation.Dilution AS PlusAreaOperationDilution, PlusAreaOperation.CleaningFactor AS PlusAreaOperationCleaningFactor, PlusAreaOperation.IdRecipientType AS PlusAreaOperationIdRecipientType, PlusAreaOperation.OpPerDay AS PlusAreaOperationOpPerDay, PlusRecipientType.Description AS PlusRecipientTypeDescription, PlusRecipientType.Size AS PlusRecipientTypeSize, PlusRecipientTypeTranslation.IdRecipientTypeTranslation AS PlusRecipientTypeTranslationIdRecipientTypeTranslation, PlusRecipientTypeTranslation.Description AS PlusRecipientTypeTranslationDescription, CleaningOperations.Ref AS CleaningOperationsRef, CleaningOperationTranslations.Name AS CleaningOperationTranslationsName, CleaningOperationTranslations.Description AS CleaningOperationTranslationsDescription, CleaningOperationTranslations.TechnicalFile AS CleaningOperationTranslationsTechnicalFile, Frequencies.Ref AS FrequenciesRef, Frequencies.IntervalTimeSpam AS FrequenciesIntervalTimeSpan, Frequencies.IntervalType AS FrequenciesIntervalType, Frequencies.CycleInterventions AS FrequenciesCycleInterventions, FrequencyTranslations.Name AS FrequencyTranslationsName, FrequencyTranslations.Description AS FrequencyTranslationsDescription, Products.Ref AS ProductsRef, Products.IdSupplier AS ProductsIdSupplier, Products.IdProductFamily AS ProductsIdProductFamily, Products.Code AS ProductsCode, Products.IdUsageCalculator AS ProductsIdUsageCalculator, Products.IdTypeOfUsage AS ProductsIdTypeOfUsage, Products.Concentration AS ProductsConcentration, Products.Active AS ProductsActive, Products.Price AS ProductsPrice, ProductTranslations.Name AS ProductTranslationsName, ProductTranslations.Title AS ProductTranslationsTitle, ProductTranslations.Description AS ProductTranslationsDescription, ProductTranslations.Dilution AS ProductTranslationsDilution, ProductTranslations.Aspect AS ProductTranslationsAspect, ProductTranslations.Smell AS ProductTranslationsSmell, ProductTranslations.SecurityFilename AS ProductTranslationsSecurityFilename, ProductTranslations.TechnicalFilename AS ProductTranslationsTechnicalFilename, ProductTranslations.PH AS ProductTranslationsPH, ProductTranslations.ImageBinary AS ProductTranslationsImageBinary, ProductTranslations.SecurityFile AS ProductTranslationsSecurityFile, ProductTranslations.TechnicalFile AS ProductTranslationsTecnicalFile, UsageIndexes.IdUsageCalculator AS UsageIndexesIdUsageCalculator, UsageIndexes.Ref AS UsageIndexesRef, UsageIndexTranslations.Name AS UsageIndexTranslationsName, Equipments.IdEquipmentType AS EquipmentsIDEquipmentType, Equipments.Ref AS EquipmentsRef, Equipments.CleaningFactor AS EquipmentsCleaningFactor, EquipmentTranslations.Name AS EquipmentTranslationsName, EquipmentTranslations.Description AS EquipmentTranslationsDescription, EquipmentTranslations.HourPrice AS EquipmentTranslationsHourPrice, EquipmentTranslations.ImageBinary AS EquipmentTranslationsImageBinary, Equipments.IdEquipment AS EquipmentsIdEquipment, EquipmentTranslations.IdEquipment AS EquipmentTranslationsIdEquipment, EquipmentTranslations.IdLanguage AS EquipmentTranslationsIdLanguage, ProductTranslations.IdProduct AS ProductTranslationsIdProduct, Products.IdProduct AS ProductsIdProduct, ProductTranslations.IdLanguage AS ProductTranslationsIdLanguage, FrequencyTranslations.IdFrequency AS FrequencyTranslationsIdFrequency, FrequencyTranslations.IdLanguage AS FrequencyTranslationsIdLanguage, Frequencies.IdFrequency AS FrequenciesIdFrequency, CleaningOperationTranslations.IdCleaningOperation AS CleaningOperationTranslationsIdCleaningOperation, CleaningOperationTranslations.IdLanguage AS CleaningOperationTranslationsIdLanguage, UsageIndexes.IdUsageIndex AS UsageIndexesIdUsageIndex, CleaningOperations.IdCleaningOperation AS CleaningOperationsIdCleaningOperation, UsageIndexTranslations.IdUsageIndex AS UsageIndexTranslationsIdUsageIndex, UsageIndexTranslations.IdLanguage AS UsageIndexTranslationsIdLanguage, PlusRecipientType.IdRecipientType AS PlusRecipientTypeIdRecipientType, PlusRecipientTypeTranslation.IdRecipientType AS PlusRecipientTypeTranslationIdRecipientType, PlusRecipientTypeTranslation.IdLanguage AS PlusRecipientTypeTranslationIdLanguage, MachineTypes.IdMachineType AS MachineTypesIdMachineType, MachineTypes.Ref AS MachineTypesRef, MachineTypes.CleaningFactor AS MachineTypesCleaningFactor, MachineTypeTranslations.IdMachineType AS MachineTypeTranslationsIdMachineType, MachineTypeTranslations.IdLanguage AS MachineTypeTranslationsIdLanguage, MachineTypeTranslations.Name AS MachineTypeTranslationsName, MachineTypeTranslations.HourPrice AS MachineTypeTranslationsHourPrice, MachineTypeTranslations.ImageBinary AS MachineTypeTranslationsImageBinary, PlusWorkSiteArea.IdPlusWorkSiteArea AS PlusWorkSiteAreaIdPlusWorksiteArea, PlusWorkSiteArea.IdPlusWorkSite AS PlusWorkSiteAreaIdPlusWorkSite, PlusWorkSiteArea.Name AS PlusWorkSiteAreaName, PlusWorkSiteArea.Area AS PlusWorkSiteAreaArea, PlusWorkSiteArea.IdSurfaceType AS PlusWorkSiteAreaIdSurfaceType, SurfaceTypes.IdSurfaceType AS SurfaceTypesIdSurfaceType, SurfaceTypes.Ref AS SurfaceTypesRef, SurfaceTypeTranslations.IdSurfaceType AS SurfaceTypeTranslationsIdSurfaceType, SurfaceTypeTranslations.IdLanguage AS SurfaceTypeTranslationsIdLanguage, SurfaceTypeTranslations.Name AS SurfaceTypeTranslationsName, SurfaceTypeTranslations.Description AS SurfaceTypeTranslationsDescription, SurfaceTypeTranslations.ImageBinary AS SurfaceTypeTranslationsImageBinary FROM Products INNER JOIN ProductTranslations ON Products.IdProduct = ProductTranslations.IdProduct RIGHT OUTER JOIN Equipments INNER JOIN EquipmentTranslations ON Equipments.IdEquipment = EquipmentTranslations.IdEquipment RIGHT OUTER JOIN SurfaceTypeTranslations INNER JOIN SurfaceTypes ON SurfaceTypeTranslations.IdSurfaceType = SurfaceTypes.IdSurfaceType INNER JOIN PlusWorkSiteArea ON SurfaceTypes.IdSurfaceType = PlusWorkSiteArea.IdSurfaceType INNER JOIN PlusAreaOperation ON PlusWorkSiteArea.IdPlusWorkSiteArea = PlusAreaOperation.IdPlusWorkSiteArea ON Equipments.IdEquipment = PlusAreaOperation.IdEquipment LEFT OUTER JOIN MachineTypeTranslations INNER JOIN MachineTypes ON MachineTypeTranslations.IdMachineType = MachineTypes.IdMachineType ON PlusAreaOperation.IdMachineType = MachineTypes.IdMachineType LEFT OUTER JOIN Frequencies INNER JOIN FrequencyTranslations ON Frequencies.IdFrequency = FrequencyTranslations.IdFrequency ON PlusAreaOperation.IdFrequency = Frequencies.IdFrequency LEFT OUTER JOIN CleaningOperationTranslations INNER JOIN CleaningOperations ON CleaningOperationTranslations.IdCleaningOperation = CleaningOperations.IdCleaningOperation ON PlusAreaOperation.IdOperation = CleaningOperations.IdCleaningOperation LEFT OUTER JOIN PlusRecipientTypeTranslation INNER JOIN PlusRecipientType ON PlusRecipientTypeTranslation.IdRecipientType = PlusRecipientType.IdRecipientType ON PlusAreaOperation.IdRecipientType = PlusRecipientType.IdRecipientType LEFT OUTER JOIN UsageIndexes INNER JOIN UsageIndexTranslations ON UsageIndexes.IdUsageIndex = UsageIndexTranslations.IdUsageIndex ON PlusAreaOperation.IdUsageIndex = UsageIndexes.IdUsageIndex ON Products.IdProduct = PlusAreaOperation.IdProduct[/code]Tue, 26 Jul 2011 15:41:24 GMTcamachormhttp://www.sqlservercentral.com/Forums/Topic1128922-323-1.aspxhere is a the entire details of the table with the expected outputTable name= Billdeatilsbillno customer basicamt ta date location1005 200 101008 500 15 1003 800 202015 1000 102015 300 121008 200 181003 1500 151005 2000 16 2015 400 202010 500 501003 600 251000 300 20toral record count =12query for getting the below output1) count (disctinct billno) = 72) sum (basicamt + ta) of all 12 records3) for each bill i need i need count & sumeg billno =1005 count(billno) where billno =1005 shld give 2sum (basicamt + ta) where billno =1005 shld give 22264) details of each billeg billno =1005 1005 200 101005 2000 16 the below is the quer y butSELECT DISTINCT billno,(SELECT COUNT(DISTINCT billno) FROM billdetails) AS ccount,(SELECT sum(basicamt+ta) FROM billdetails) AS amt,(SELECT COUNT( billno) FROM billdetails where billno=1005) AS paycount,(SELECT sum(basicamt+ta) FROM billdetails where billno=1005) AS payamt, (SELECT * FROM billdetails where billno=1005)FROM billdetailsTue, 21 Jun 2011 05:59:23 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic479795-323-1.aspxHi guys, I would lilke know.I have a column called Image in the database. The type of of the column is Image. I want to upade it. I have an jpg file. How can do that????Lets say I can retrieve an image file from url,then save it into the sql server ce.....not insert ...i want to update...Thank youFri, 04 Apr 2008 03:56:02 GMTsivak42http://www.sqlservercentral.com/Forums/Topic1116743-323-1.aspxHi all!I have this query, which is a bit long, and difficult to show in this forum, but the general layout is:Select field,field(Select something) As NAME1,(Select something else) AS NAME 2WHERE some conditionORDER BY NAME1, NAME 2Problem is, that NAME2 can select nothing, leaving NAME2 as <NULL>.I want to have NAME2 changed to 0 if NAME2 = <NULL>The complete query is below, its the SUBSTAT which is the problem.HOW to?Best regardsEdvard KorsbækOriginal QUERY:SELECT A.EmployeeGroupID,A.EmployeeID, (SELECT RTRIM(B.PER_FORNAVN) +' '+ B.PER_EFTERNAVN FROM dbo.PERSONAL B WHERE B.PER_PERSONAL_ID=A.EmployeeID) AS FNAME, (SELECT C.PER_TIMER FROM dbo.PERSONAL C WHERE C.PER_PERSONAL_ID=A.EmployeeID) AS PTIME, (SELECT D.PER_FASTE FROM dbo.PERSONAL D WHERE D.PER_PERSONAL_ID=A.EmployeeID) AS ACTIVE, (SELECT E.PER_FRATRAADT FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS RESIGNED, (SELECT Convert(CHAR,E.OnLeaveStart,23) FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS LSDATE, (SELECT Convert(CHAR,E.OnLeaveEnd,23) FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS LEDATE, (Select SubGroupType from dbo.subgroup where Subgroup_ID in (Select Subgroup_ID from dbo.employeegroupmaptoemployee where employeeID = A.EmployeeID and employeegroupid = A.EmployeeGroupID)) as Substat FROM dbo.EmployeeGroupMapToEmployee A, dbo.EmployeeGroup F WHERE A.EmployeeGroupID = F.Id AND (F.Enable = 0 OR F.Enable IS NULL OR A.EmployeeGroupID IN (SELECT G.EmployeeGroupID FROM dbo.dutyRosterShift G WHERE G.DutyRosterId = 3 GROUP BY G.EmployeeGroupID)) ORDER BY A.EmployeeGroupID,substat, FNAMESun, 29 May 2011 03:24:07 GMTedvard 19773http://www.sqlservercentral.com/Forums/Topic1097853-323-1.aspxI have an SDF file. I need to import some new data into a new table in that SDF. The data could be a .TXT/.csv/.xls.. What are the ways available to do the import/export into SQL 2005 CE? Also, how to query an table in SDF from an SQL 2005 Enterprise edition?Sat, 23 Apr 2011 20:20:58 GMTbalasach82http://www.sqlservercentral.com/Forums/Topic1101469-323-1.aspxHow to connect SQL 2005 CE (.SDF file) from Crystal reports? I need to access certain tables to generate reports. Are there any drivers?Mon, 02 May 2011 02:58:59 GMTbalasach82http://www.sqlservercentral.com/Forums/Topic1081690-323-1.aspxHi All I'm trying to get the identity of an inserted record from a table in SQL Server CE using a table adapter , but not able to. I tested the same in SQL Server - no problem. Can anyone help? I'm programming in C#. Thanks!Mon, 21 Mar 2011 18:03:38 GMTa.ambronhttp://www.sqlservercentral.com/Forums/Topic1094490-323-1.aspxhello i am using SQl Server 2005 . i want to send a mail through sqlserver 2005 i used same code those you have to mention in a your website but after that i dont receive a mail in my email id.so what is the problem i dont understand ? i used your code after that i got a message mail queue then i run a select query then description error is activation failed.so tell me what i the problem. i used sql server 2005 is a free version so this is not a issue tell me .Regards,Richi AgarwalSat, 16 Apr 2011 05:02:34 GMTrichi.agrawal 73244http://www.sqlservercentral.com/Forums/Topic662439-323-1.aspxI want to add a column to a table in T-SQL by using the following command:ALTER TABLE DELIVERYINTRIP ADD PODID UNIQUEIDENTIFIER NULLThis works the first time when the column doesn't exist yet.However I want to prevent the alter table command to be executed in case the column already exists.I can test if it exists with the following command:SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'DBID_TRIP' AND COLUMN_NAME = 'SESSIONID'How do I combine the test and the alter table command in T-SQL for SQL CE since BEGIN and END statements are not supported? My solution would be:IF NOT EXISTS (SELECT *WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'DBID_TRIP' AND COLUMN_NAME = 'SESSIONID') BEGIN ALTER TABLE DELIVERYINTRIP ADD PODID UNIQUEIDENTIFIER NULLENDAnyone an idea or solution in T-SQL.Regards,Harry DrenthMon, 23 Feb 2009 03:13:02 GMTHarry Drenthhttp://www.sqlservercentral.com/Forums/Topic1084053-323-1.aspxWe have a new vendor that uses SQL Compact 3.5 to query four tables within their application. The data is relatively small and all fields are data type nvarchar. This data resides in a .sdf on every desktop within the company. What is the best way to update this data each day? I would like the data to pull from the server rather than push from the server. I would prefer the pull so when a new desktop is created in the company the pull technique can be created then. Rather than adding to the server everytime a new desktop is created on the network.Thanks for you help.Fri, 25 Mar 2011 08:05:18 GMTErin.http://www.sqlservercentral.com/Forums/Topic858658-323-1.aspxusing SQL Server CE 3.5 with Visual Studio 2008, but, when I add a sdf file, I keeping getting the error "an error occurred while creating the new data source"Wed, 03 Feb 2010 07:30:54 GMTericvanderhousenhttp://www.sqlservercentral.com/Forums/Topic1073676-323-1.aspxI just installed ms sql 2003 server and I have an XP client on the same network. How do I test the coonection of my client XP to the server? I can ping it and access the Windows server but I need to know if the SQL server can be access by my XP to test connectivity. Is there any Windows XP utility I can use like the ODBC and how?I am a newbie and I will appreciate a lot any help. tiaSat, 05 Mar 2011 02:35:00 GMTrhungkoihttp://www.sqlservercentral.com/Forums/Topic1073038-323-1.aspxHi friends,Need u r expert advise We are doing migration of SQL sever 7 to SQL Server 2008 R2.first step which we are doing is taking db backup from SQL Server 7 server and restoring to SQL server 2000 server and there on backup of SQL serve 2000 will be restored on SQL Server 2008 r2 server.For this purpose I am trying to install SQL Server 2000 Dev edition on Windows 2008 R2 (64 bit).But while starting installation getting "incompatibility error"I find couple of post in which people are saying that they installed SQL server 2000 on windows 2008 server successfully.http://forums.techarena.in/operating-systems/1324371.htmThu, 03 Mar 2011 20:01:36 GMTpareshkadamhttp://www.sqlservercentral.com/Forums/Topic1009201-323-1.aspxHi,i have Windows Server 2008 with IIS7 and SQL Server 2000 sp4 Installed and sqlce30 server tools.When Calling the .Pull method i get the following error:MDAC2.8 or newer version must be installed...Any idea?Regards thgrFri, 22 Oct 2010 07:50:02 GMTthanasishttp://www.sqlservercentral.com/Forums/Topic1048847-323-1.aspxHi,I have this piece of code written for SQL Server 2008. With all the limitations of SQLCE can anybody please help to rewrite this query? It is a basic update statement, which gets the value from a pre-query.declare @tmp_RESIDENCE_LOCATION NVARCHAR(50)SET @tmp_RESIDENCE_LOCATION = ((SELECT TOP(1) FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID IN (SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP WHERE CP.COST_PROJECTION_ID = 2) ) ORDER BY SORT_INDEX)UPDATE JURISDICTION_COUNTRY_INPUTSET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATIONWHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2Thanks.Mon, 17 Jan 2011 10:14:54 GMTakhandelshttp://www.sqlservercentral.com/Forums/Topic1063868-323-1.aspxWe are having trouble setting up our SQL Compact 3.5. I have it on the server, but do the desktops require IIS?Mon, 14 Feb 2011 14:12:42 GMTErin.http://www.sqlservercentral.com/Forums/Topic1061657-323-1.aspxi used DBCC SHRINKFILE to truncate logfile but the logfile is big after insert,...i want to deincrease size of logfile when use insert, update,..please help meHai ThanhWed, 09 Feb 2011 19:11:53 GMThaithanhqbhttp://www.sqlservercentral.com/Forums/Topic1047895-323-1.aspxHi,I'm doing an application in VB 2008 to collect data from a PocketPC in batch mode (connected by USB in host computer). Data will be held in a SQL Mobile database. Host computer will be Windows 7 Pro 32 bits, so sync throught Windows Mobile Device Center and database is SQL Server 2008 R2 Express edition.How can I sync the databases? What is the best way to do this?thanks for your time and helpFri, 14 Jan 2011 07:21:36 GMTDominic Gagnéhttp://www.sqlservercentral.com/Forums/Topic1040352-323-1.aspxHello, i have a problem when i try to syncronize with sql server replication.the error is:Error 29045 Initializing SQL Server Reconcilier has failed. Try Againthe HResult code is: 2147198719in the sqlcesa LOG appear the following lines:2010/12/28 11:50:11 Thread=148C RSCB=2 Command=SCHK Hr=80045901 ERR:Initialize failed for DistributorSessionID = 02010/12/28 11:50:11 Thread=148C RSCB=2 Command=SCHK Hr=00004E74 The process could not connect to Distributor 'mercurio'. 200842010/12/28 11:50:11 Thread=148C RSCB=2 Command=SCHK Hr=00000000 Unable to complete login process due to delay in opening server connection 0The SQL Server database is in a Windows Server 2008 machine. 64 bitsThe IIS is in a Windows Server 2003 machine 32 bitsI tried with regenerate the snapshot, restart IIS, run the job "Reinitialize subscriptions having data validation failures", disable distribution and enable it again. but anything of that works.Any ideas? Is my 3 day with this issue :(Wed, 29 Dec 2010 08:38:30 GMTlmejiahttp://www.sqlservercentral.com/Forums/Topic1028504-323-1.aspxHi,I have something a little bit different happening.I have a .net framework application that runs 3 queries against a SQL CE 3.5 database (it runs more but the majority of processing is done by 3 queries) and 3 indexes for the queries.The performance is ok nothing special, but as soon as I open a connection to the database via query analyzer 3.5 on the device and leave it open while the application is running the performance becomes outstanding, almost instantaneous. As soon as I close the query analyzer connection to the database the performance goes back to normal, I can open the connection from query analyzer while my app is running and notice improved performance straight away.Can anyone explain why I am getting a performance boost when query analyzer is open? As I would love to have that sort of performance all the time not only when query analyzer is open.Couple of notes:- I am not running the queries in query analyzer I am running them from the application, all I am doing is opening query analyzer and opening a connection to the .sdf- The connection string in my app specifies the max buffer size to around 8900 (I'm not sure why I inherited the app)- Each query is within a using statement for the SqlCeCommand, therefore the command is created just before the query is run then disposed of straight afterWed, 01 Dec 2010 03:55:21 GMTchrism 16498http://www.sqlservercentral.com/Forums/Topic1028414-323-1.aspxPlease refer to this thread.http://www.sqlservercentral.com/Forums/Topic1028504-323-1.aspxTue, 30 Nov 2010 23:09:45 GMTchrism 16498http://www.sqlservercentral.com/Forums/Topic996807-323-1.aspxWould Compact Edition be a viable option to host a database on a laptop that when connected to the network that database would be updated from a server database.The situation is our users sometimes need to access a mailing list, but cannot get online to retreive the list. We want to store a copy of the database on his/her laptop and then when he/she does connect to the network the database would be updated with any changes.Originally, we thought SQL 2005 Express would be our best option. However, it appears Compact would use less laptop resources.Thanks.Fri, 01 Oct 2010 10:10:06 GMTErin.http://www.sqlservercentral.com/Forums/Topic991945-323-1.aspxMy understanding is that the Compact edition replaced the Mobile edition sometime in the final stages of SQL Server 2008. I have found a wiki article that indicates that Compact's history includes the Mobile edition (url=http://en.wikipedia.org/wiki/SQL_Server_Compact][/url]). But I can't find a Microsoft article.I have two questions: is my understanding accurate (that Compact superceded Mobile)? Second, does anyone know of a Microsoft article that indicates this lineage.Thu, 23 Sep 2010 06:22:24 GMTRussel Loskihttp://www.sqlservercentral.com/Forums/Topic975671-323-1.aspxThe following code will only return "No birthday(s) Pending". Any advice on correcting code would be appreciated.[code="vb"]Imports SystemImports System.DataImports System.Data.SqlServerCeImports System.TextImports System.IOImports System.ComponentModelImports System.Windows.FormsPublic Class frmKSBCards Dim cn As SqlCeConnection Dim PersonsCommand As SqlCeCommand Dim PersonsTable As DataTable Dim PersonsAdapter As SqlCeDataAdapter Dim Persons As SqlCeDataAdapter Public strWarnings As StringBuilder Dim bFoundDatesToRemember As Boolean Private Sub frmKSBCards_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'connect to the database Try cn = New SqlCeConnection("Data Source=|DataDirectory|DAL\Contacts.sdf") cn.Open() Catch sqlex As SqlCeException Dim SqlError As SqlCeError For Each SqlError In sqlex.Errors MessageBox.Show(SqlError.Message) Next Catch ex As Exception MessageBox.Show(ex.Message) End Try 'Select/Retrieve(records) Dim Sql As String = "SELECT Surname, P.Firstname, P.Dob, " & "DATEDIFF(Year, P.Dob, GETDATE()) AS AgeNow " & "FROM Addresses " & "INNER JOIN Persons P " & "ON Addresses.AddressID = P.AddressID" PersonsCommand = New SqlCeCommand(Sql, cn) PersonsAdapter = New SqlCeDataAdapter() PersonsAdapter.SelectCommand = PersonsCommand PersonsTable = New DataTable() Dim ds As New DataSet PersonsAdapter.Fill(PersonsTable) PersonsAdapter.Fill(ds) dgv.DataSource = ds.Tables(0) strWarnings = New StringBuilder CheckRenewals() End Sub Private Sub btnRenewals_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRenewals.Click 'Display renewal status if any is available If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then MessageBox.Show(strWarnings.ToString(), "Current Dates Due") Else MessageBox.Show("No pending Birthday(s) due", "No Dates Available") End If End Sub Private Sub CheckRenewals() 'Get Birthday and Anniversary dates Try Dim DatesView As DataView = PersonsTable.DefaultView With DatesView Dim bFoundDatesToRemember As Boolean = False Dim sDateReminders As String = String.Empty For DateCheckCounter As Integer = 0 To .Count - 1 With .Item(DateCheckCounter) 'Get Date from database fields Dob Dim CardDate1 As Date = CType(.Item("Dob"), Date) 'Days difference between two dates Dim NumberOfDays1 As Long = DateDiff(DateInterval.Day, Now, CardDate1) + 1 'Test For Dob If NumberOfDays1 < 10 Then If NumberOfDays1 > -1 And NumberOfDays1 < 0 Then Dim DayString As String = IIf(NumberOfDays1 = -1, " day", " days").ToString ElseIf NumberOfDays1 > -1 Then bFoundDatesToRemember = True If sDateReminders <> String.Empty Then sDateReminders &= Environment.NewLine Dim DayString As String = IIf(NumberOfDays1 = 1, " day", " days").ToString sDateReminders &= .Item("P.Firstname").ToString.Trim & " Birthday due in " & _ NumberOfDays1.ToString & DayString End If End If If (Not String.IsNullOrEmpty(sDateReminders)) Then strWarnings.Append(sDateReminders & Environment.NewLine) End If End With Next End With Catch ex As Exception MessageBox.Show(ex.Message, "Errror - Notification") Exit Sub Finally tmrReminders.Enabled = False If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then With niReminders .Visible = True .BalloonTipIcon = ToolTipIcon.Warning .BalloonTipText = "You have outstanding cards to action" .BalloonTipTitle = "Cards Due for Action" .ShowBalloonTip(5) End With End If End Try End Sub End Class[/code]Thu, 26 Aug 2010 08:13:03 GMTkenkobhttp://www.sqlservercentral.com/Forums/Topic958361-323-1.aspxIs it possible to use SSMSE with SqlServerCe 3.5 without having to install a version of SQL Server Express. If answer to my question is Yes. How do I configure SSMSE as I have been trying for two full days.Sat, 24 Jul 2010 01:51:43 GMTkenkobhttp://www.sqlservercentral.com/Forums/Topic968977-323-1.aspxI have looked at both ways to use a user interface as well as use a query to tell and so far the only answers I can find are for full SQL Server or involve a lot of manual math, which does not work well for us because we have a lot of variable length fields that we cannot know the amount of data in each and it will vary a lot from row to row.Fri, 13 Aug 2010 08:34:04 GMTSQL Babe